my.cnf setting for 512 ram

clove28

Active member
Hi guys,

I've read different configuration of my.cnf. But I really don't know is the best for 512 ram server. Please help me to set it up properly. I'm receiving error "Zend_Db_Adapter_Mysqli_Exception: User admin already has more than 'max_user_connections' active connections - library/Zend/Db/Adapter/Mysqli.php:333

Here's my current my.cnf

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=500
max_user_connections=30
wait_timeout=600
interactive_timeout=50
long_query_time=5

#GENERAL#
performance_schema             = off

# MyISAM #
myisam-recover                 = FORCE,BACKUP

#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=1024
thread_cache_size=286
interactive_timeout=25
connect_timeout=5
max_allowed_packet=64M
max_connect_errors=1000
query_cache_limit=1M
query_cache_size=8M
query_cache_type=1
tmp_table_size=16M

#innodb_use_native_aio = 0


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
Turning performance schema off was the best thing right there. It sucks a huge amount of ram, talked about my experiences with it here http://www.rainmotorsports.com/blog/29/configuring-mysql-5-6-for-low-memory-usage/

The biggest issue I see is that 30 limit on max user connections. I really dont remember what my tests had me at, but I want to say under a large load I saw no more than 100 connections. Xenforo as an application accessing the database is 1 user, so limiting user connection to 30 is limiting xenforo, not workers or real users. I would up that.

The innodb buffer pool is one of the more important settings for innodb performance, though default (not set) is probably fine. I wouldn't worry too much about guides that are tweaking for myisam as much as innodb. As I found most guides about lowering ram usage were not for innodb setups. But you can get a server to host a hundred or more concurrent users on 512mb of ram no problem.
 
Turning performance schema off was the best thing right there. It sucks a huge amount of ram, talked about my experiences with it here http://www.rainmotorsports.com/blog/29/configuring-mysql-5-6-for-low-memory-usage/

The biggest issue I see is that 30 limit on max user connections. I really dont remember what my tests had me at, but I want to say under a large load I saw no more than 100 connections. Xenforo as an application accessing the database is 1 user, so limiting user connection to 30 is limiting xenforo, not workers or real users. I would up that.

The innodb buffer pool is one of the more important settings for innodb performance, though default (not set) is probably fine. I wouldn't worry too much about guides that are tweaking for myisam as much as innodb. As I found most guides about lowering ram usage were not for innodb setups. But you can get a server to host a hundred or more concurrent users on 512mb of ram no problem.

Hi Rain,

Thank you for your reply. I also adjusted the max user connections to 100. I'd like to put innodb buffer pool but I don't know how. Do I just need to put it in there or set it up somewhere else?
 

It failed to start when I followed this configuration from percona..

Code:
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208


[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
performance_schema             = off

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 700M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
 
Yeah, most likely that's the reason of the error.
or you can post the whole mysql error log here.
 
Yeah, most likely that's the reason of the error.
or you can post the whole mysql error log here.

I didn't change the value, but followed the link you provided. The restart is working now. Thanks a lot! One quick question, how will I know that the new my.cnf is working properly? :)
 
Top Bottom