Question posted in 
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?
- cpu usgae for all cores show most of the cpus is idle in more than 90%
-
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 cachedPID 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
2
Answers
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 🙂
Suggestions to consider for your configuration [mysqld] section
Make read_buffer_size=64K rather than 4M for immediate improvement.
Make max_connect_errors=10 rather than 9999 to stop the hacker/cracker trying to break in.
Make query_cache_size=50M rather than 4000M (4G) for more reasonable limit that is manageable.
Make query_cache_min_res_unit=512 to store more entries in your query cache space.
stop/start instance for improved performance.
View profile for contact info and get in touch for Free Utility Scripts to assist with performance tuning.