I have a CentOs server with Plesk, it has a 24 cpu core and 32 GB of RAM.
When I use TOP in linux, I see mysql cpu usage is about 200% despite the total system cpu being about 2 or 3 % and load average is below 3.
This high mysql cpu usage causes webpages to load with a long delay or some times users get an error.
My question is, why mysql does not use the total cpu usage available on system? Is the user mysql limited in using cpu?
this is my top report :
top - 10:16:50 up 78 days, 13:03, 1 user, load average: 2.03, 2.66,
2.23 Tasks: 452 total, 1 running, 451 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.6%us, 2.7%sy, 0.0%ni, 91.7%id, 0.0%wa, 0.0%hi,
0.0%si, 0.0%st Mem: 32838268k total, 30909316k used, 1928952k free,
346760k buffers Swap: 4737016k total, 21316k used, 4715700k free, 17724552k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 56223 mysql 20 0 12.5g 1.4g 7032 S 156.7 4.5 295:44.32 mysqld 16674 baadraan 20 0 191m 16m 8308 S 6.9 0.1 0:01.48 php-cgi 16677 baadraan 20 0 193m 17m 8676 S 6.3 0.1 0:01.71 php-cgi
I checked the ulimit is unlimited for everyting
The plesk system health also shows cpu is ok but there is a service cpu problem:
MySQL CPU usage 225.1 % (?)
Total usage 10.7% used (?)
Load average 2.4
Please Help me to understand why mysql is showing as using 200%+ CPU.
my.cnf content is :
[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#basedir=/var/lib/mysql
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table=1
max_connections=5000
max_user_connections=2000
log-slow-queries
long_query_time = 2
safe-show-database
skip-name-resolve
query_cache_size = 4000M
table_cache = 4000
thread_cache_size = 16M
query_cache_limit = 1M
key_buffer_size = 6G
wait_timeout = 500
interactive_timeout = 300
innodb_buffer_pool_size = 1024M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
sort_buffer_size = 4M
myisam_sort_buffer_size = 128M
join_buffer_size = 4M
read_buffer_size = 4M
tmp_table_size = 128M
connect_timeout = 15
max_allowed_packet = 2M
max_connect_errors = 9999
open_files_limit=3496
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Remeshx,
The mysql load you see in "top" is total load of all mysql threads on all cores. If you have 24 core system, you can approximate the single core load by dividing 200%/24 which gives ~8% load, which is not bad at all. If you want to have detailed view into what is going on, use "htop" instead of "top", and in its settings enable "tree view" (settings->display options->tree view). You will see all the mysql threads with their load, and total load (one you see now) on the tree view root.
Based on what you have written your system load should not be the problem with long webpages load. I gues there is some problem with database design and/or queries. In your config I see that you have slow log enabled. I recommend you to take a close look at it and trace slow, inefficient queries.
Moreover take a look on the tables engines you are using. If you are using myisam, and have lots of writes, and big load - remember that each write to the myisam table sets a lock on a whole table.
Hope I've clarified it a little bit :)