Hi,
1. Firstly find out what's causing server CPU high usage
Normally when we notices high CPU usage in the server, check which process is causing abuse to server CPU load. We can use the following commands/tools to monitor the server loads. Use "top" command to list the processes with corresponding CPU loads. Also run uptime,vmstat 2 and iostat -x to findout if there any abnormal situation.
Here, we need some baseline to compare with, before the problem happens, we needed to record 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
*client1_hostname can be varied according to the situation.
Thanks,
