2

I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it.

MySQL is constantly using too much cpu.

top - 17:40:36 up 112 days, 12:13,  1 user,  load average: 1.24, 1.72, 1.82
Tasks: 222 total,   1 running, 221 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.3%us, 11.6%sy,  0.4%ni, 72.9%id,  2.8%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  16329040k total, 12946136k used,  3382904k free,   587252k buffers
Swap:  1051832k total,  1050988k used,      844k free,  4986272k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                 
25368 mysql     20   0 8854m 5.3g 5064 S 295.4 33.9  11912:37 mysqld                                                                                                                                 
 1757 apache    20   0  982m  80m  28m S  2.0  0.5   0:03.04 httpd                                                                                                                                   
 2494 apache    20   0  878m  68m  27m S  2.0  0.4   0:00.92 httpd   

my.cnf

[client]
default-character-set=utf8

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
bind-address=127.0.0.1

key_buffer        = 800M
max_allowed_packet    = 32M
thread_stack        = 256K
thread_cache        = 8
thread_cache_size    = 20
thread_concurrency = 4
max_connections        = 510
table_cache            = 9000
interactive_timeout = 80
tmp_table_size = 540M
max_heap_table_size = 540M
max_connect_errors = 150
wait_timeout = 20
read_rnd_buffer_size = 1024K
connect_timeout = 10
query_cache_type = 1
query_cache_limit   = 40M
query_cache_size        = 128M
innodb_buffer_pool_size = 256M

join_buffer_size = 14M
sort_buffer_size = 14M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

SHOW FULL PROCESSLIST; is returning me on average 2-5 queries.

Here is the output of SHOW VARIABLES; command.

We got MyISAM and InnoDB tables in this system.

Thank you for any suggestions.

EDIT 1

Here is the vmstat output.

EDIT 2

SHOW GLOBAL STATUS output is available below in the comments.

  • Your CPU is 72.9%idle . How many CPUs your server has? – Antonios Aug 26 '14 at 15:58
  • @Antonis He may have ~8-12 cores. High CPU usage is not necessarily a bad thing- it means that your load is not IO-bound. The problem is what you are using those cycles for. Check your query plans and latency though the slow log to see if that is reasonable. – jynus Aug 26 '14 at 16:40
  • @jynus if he has 8-12 cores and iowait is 2.8% then probably one thread might have more than 20% iowait which is IO-bound. I agree on the query plans usually high CPU usage come from poor queries. – Antonios Aug 26 '14 at 16:47
  • It's a quad core machine with HT support but not enabled. – jithujose Aug 26 '14 at 19:50
  • Your tables are InnoDB or MyISAM or both? I notice that your swap space is occupied. Could you please run a vmstat 1 for a minute and share the si/so? – Antonios Aug 27 '14 at 7:55
1

Let's see the slowest query in the system. Quite likely, fixing it (adding an index, reformulating the SELECT, etc) is the best solution. You mentioned SHOW FULL PROCESSLIST -- That is likely to show the naughty queries; let's see them, plus the associated SHOW CREATE TABLEs.

query_cache_size = 128M -- too big; no more than 50M. A big value slows down the system with pruning.

innodb_buffer_pool_size = 256M and key_buffer = 800M -- What ENGINE are you using? Neither of these values is likely to be 'good'. See http://mysql.rjweb.org/doc.php/memory for further advice. A poorly-sized cache could lead to extra I/O, which would be seen as slow queries.

table_cache = 9000 -- Yikes! How many tables do you have? (Thousands of tables is an OS problem.)

0

The problem was caused by an inactive legacy website hosted on this server. It was a target of heavy spamming. I solved this issue by putting some tables in read-only mode.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.