0

I've got some trouble with Percona Server 5.5: suddenly abnormous MySQL CPU usage caused the system to become unresponsive as it was eating up all CPU with a large number of threads.

It's a 24 core system with 64GB RAM that's usually idle (avg load around 0.xx). I checked the process list but found only a few queries and an average number of connections. Also there were only very few visitors on the site.

In fact the main DB there is around 15GB a bit fragmented and always well indexed (shop, plan to fix that soon). But even when dumping all tables or running magento crons the load is pretty low.

When I did a service mysql restart the issue was gone.

What's the best way to debug this?

Also could it be related to some optimizations I've applied to my.cnf? I don't think that I added something weird just caches, buffers, open tables, time_wait and max_packet. I didn't overcommit RAM or put options there without thinking.

[mysqld]
# * Basic Settings
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/sql/
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

## security
local-infile=0
## disable for security
old_passwords = no
## combat lock wait timeouts
wait_timeout=300
##innodb_lock_wait_timeout set to debug transaction wait timeout (default 50)
innodb_lock_wait_timeout=200

# * Fine Tuning
key_buffer              = 16M
thread_stack            = 192K
thread_cache_size       = 8
## increased to combat server has gone away issue with magento (usually sign of MP)
max_allowed_packet      = 32M

## increased
max_connections        = 400
## skipped, synonym for table_open_cache
#table_cache            = 128
## don't use this it's a waste of time
#thread_concurrency     = 10
## keep this fair
table_open_cache       = 1500
## generally high enough to match pref table sum
table_definition_cache = 5000

# * Query Cache Configuration
## if frequent prunes happen: decrease to prevent performance hog (defaults to 1M)
query_cache_limit       = 256K
## doubled to 32MB - monitor hitrate and adapt accordingly in 12MB steps
query_cache_size        = 64M
## don't increase too much as inefficient queries will perform even worse
#tmp_table_size = 32M
#max_heap_table_size = 32M

## Feed the beast!
innodb_buffer_pool_size = 24G
## enable stats (debug + BF prevention)
userstat = 1

I'd be grateful for suggestions as I'll certainly sleep better knowing that the system is stable ;)

  • Your my.cnf would be helpful to find out what causes this – Gabor Vincze Mar 2 '15 at 22:13
  • Was just adding it .. – Tsolen Mar 2 '15 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.