How to troubleshoot mysql database server high cpu usage/slowness

Jephe Wu - http://linuxtechres.blogspot.com

Objective: find out what is causing mysql database slowness
Environment: CentOS 6.1 64bit, Mysql 5.1


Steps:

1. Firstly find out what's causing server CPU high usage
Normally, we firstly will notice that server cpu load is high, run 'top' to confirm which process is contributing the cpu high usage, it's mysql or other process. Also, run uptime, vmstat 2 and iostat -x to find out if there's any abnormal situation.

Here, we need some baseline to compare with, before the problem happens and server is running fine, we should have recorded some baseline information first such as the output of:

vmstat 2 20
top -b -n 5
iostat -x 5 | grep sdb

2. check mysql error log , slow query log etc from /etc/my.cnf

log_error                = /srv/mysql/log/error.log
log_slow_queries        = /srv/mysql/log/mysql-slow.log
innodb_buffer_pool_size=20000M


3. mysql> show engine innodb status\G
look at the end of the output as follows:
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22981924466; in additional pool allocated 1048576
Dictionary memory allocated 2323288
Buffer pool size   1280000
Free buffers       0
Database pages     1115883
Modified db pages  12101
Pending reads 0
Pending writes: LRU 0, flush list 32, single page 0
Pages read 2075693, created 11255097, written 1339038405
0.00 reads/s, 2.98 creates/s, 427.44 writes/s
Buffer pool hit rate 1000 / 1000
----------------

The bufer pool hit rate shoule be nearly 1000/1000. page write/s is 427.44 for this case.

The innodb_buffer_pool_size system variable specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.


4. show processlist; then 'kill query [number]' to kill process.
# mysql -uroot -ppassword -e "show processlist" | grep client1_hostname | awk '{print "kill query "$1";"}' | mysql -uroot -ppassword