MySQL 5.1 High Cpu usage on CentOS 6

Question

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.

• Antonios: Your CPU is 72.9%idle . How many CPUs your server has?
• jynus: @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.
• Antonios: @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.
• jithujose: It's a quad core machine with HT support but not enabled.
• Antonios: 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?

Answer 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.)

Answer 2

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.