mysql cpu usage is more than system cpu

Question

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 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

Plz post contents of my.cnf file(/etc/my.cnf) and What is the RAM size? — Surendheran
If your system doesn't have high concurrency on operations that require high CPU usage, i don't see any reason to worry about it. — Antonios
Antonis , i am not sure if mysql uses all cpu cores. beacuse it slow down loading web pges although there is a lot of idle proccess (more than 90% ) and server load — RezaMM

fonfi fonfi · Accepted Answer · 2014-06-04T11:00:29

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 :)

mysql cpu usage is more than system cpu

1 Answers