my.cnf setting for 512 ram

[clove28]

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

[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

 

[rainmotorsports]

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.

 

[clove28]

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?

 

[rdn]

Try: https://tools.percona.com/wizard

 

[clove28]

Try: https://tools.percona.com/wizard

Thanks Roldan,

Do I need to restart mysql after saving the new my.cnf?

 

[rdn]

Yes.

 

[clove28]

Yes.

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

# 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

 

[rdn]

You changed
innodb-log-file-size = 64M

Delete mysql log files first.

http://dba.stackexchange.com/questi...ge-mysql-innodb-variable-innodb-log-file-size

 

[clove28]

You changed
innodb-log-file-size = 64M

Delete mysql log files first.

http://dba.stackexchange.com/questi...ge-mysql-innodb-variable-innodb-log-file-size

Thanks Roland,

I didn't change the innodb-log-file-size = 64M . It is the config given by percona.

 

[rdn]

Yeah, most likely that's the reason of the error.
or you can post the whole mysql error log here.

 

[clove28]

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?