my.cnf issue - mysql load exceeding 200%

[imadsani]

Hey,

A couple of weeks ago I made a thread about running ASL on a server with Percona 5.6 and then I experienced problems with ASL being unresponsive and support identified that mysql was hitting ~200%. Turns out it was a problem with the my.cnf I was using.

ASL works fine with the following, default, config

Code: Select all

[mysqld]

log-warnings=2
query_cache_size=32m
#bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=3GB
key_buffer_size=3GB
[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

But I need to use a slightly different config for an application that we are running, as soon as I setup the new config the issue with ASL returns.

Code: Select all

[mysqld]
query_cache_size=32m

log-warnings=2
#bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#key_buffer_size=3GB
slow_query_log=1
slow_query_log_file = /var/log/mysql/sloq_queries.log
long_query_time = 3
[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#default-storage-engine         = MyISAM
# MyISAM #
key-buffer-size                = 10G
myisam-recover                 = FORCE,BACKUP
max-allowed-packet             = 512M
expire-logs-days               = 14
tmp-table-size                 = 128M
max-heap-table-size            = 128M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

From what I can gather, the modified config is MYISAM centric with no setting for innodb and by looking at the the DB I can see that apart from one table all the others are innodb. Could you guys let me know what I'm missing for ASL?

Thanks

[scott]

Well the placement is kind of weird, normally you'd put that under [mysqd]. What you should do is go through each setting and enable it one by one in your old config unti you ID which one is the culprit.

[imadsani]

The thing is, once i put in the modified config the issue persists even if I revert to the original config.

[scott]

Yup, so you need the apply the differences line by line, and try each one on the working config until you isolate the source.

[faris]

The thing is, once i put in the modified config the issue persists even if I revert to the original config.

Is percona really restarting when you restart after changing the config? It might be worth checking.

And the line that stands out to me is the 10Gb key buffer size. That's massive unless you have huge amounts of memory.

Does mysqltuner work with Percona? It would be handy if it did, because it can tell you how much total memory MySQL will use with the current settings, based on a certain number of concurrent connections/things using mysql.

Remember that some settings are per connection, so if some setting or other is set to 1Gb and you have 10 connections/things using MySQL at once, 10Gb of memory is used. Not if the key buffer is one of those though.

[imadsani]

The thing is, once i put in the modified config the issue persists even if I revert to the original config.

Is percona really restarting when you restart after changing the config? It might be worth checking.

And the line that stands out to me is the 10Gb key buffer size. That's massive unless you have huge amounts of memory.

Does mysqltuner work with Percona? It would be handy if it did, because it can tell you how much total memory MySQL will use with the current settings, based on a certain number of concurrent connections/things using mysql.

Remember that some settings are per connection, so if some setting or other is set to 1Gb and you have 10 connections/things using MySQL at once, 10Gb of memory is used. Not if the key buffer is one of those though.

I'm running a server with 32GB RAM, newrelic doesn't report high MYSQL usage though.

I set up a new server yesterday and threw the, allegedly, faulty config on there with ASL but it appears to be working fine. I'm clueless here

[prupert]

Judging from your settings it looks like you are heavily using MyISAM, thus I am surprised by the rather low value for query_cache_size (32M). Are you aware that ASL is overwriting this value to 32M unless you have configured ASL otherwise? Perhaps you are already aware of this, and perhaps this has nothing to do with your issue, but I wanted to make sure that you knew (we have had issues as well with performance before realising that ASL was overriding our cache settings): https://www.atomicorp.com/wiki/index.ph ... y_Settings