MySQL high CPU usage on small website

Question

I have a small website hosted on a dedicated server (FreeBSD 9.2, MySQL 5.6.1 and php 5.5.5). The problem is that MySQL eats a lot of cpu resources even if the website have a few visitors. The database is small (the biggest table is about ~12k rows)

I have enabled slow queries log in my.conf but there are no logged queries.

Error log file does not contain any useful information (actually it's empty)

Here are the server specs

CPU

hw.machine: amd64
hw.model: Intel(R) Xeon(R) CPU           X3330  @ 2.66GHz
hw.ncpu: 4
hw.machine_arch: amd64

HHD transfer info

Seek times:
    Full stroke:      250 iter in   1.507837 sec =    6.031 msec
    Half stroke:      250 iter in   1.461401 sec =    5.846 msec
    Quarter stroke:   500 iter in   5.829283 sec =   11.659 msec
    Short forward:    400 iter in   2.668680 sec =    6.672 msec
    Short backward:   400 iter in   3.261849 sec =    8.155 msec
    Seq outer:   2048 iter in   0.247219 sec =    0.121 msec
    Seq inner:   2048 iter in   0.246036 sec =    0.120 msec
Transfer rates:
    outside:       102400 kbytes in   1.207812 sec =    84781 kbytes/sec
    middle:        102400 kbytes in   1.342168 sec =    76294 kbytes/sec
    inside:        102400 kbytes in   1.893578 sec =    54078 kbytes/sec
I/O command overhead:
    time to read 10MB block      0.131751 sec   =    0.006 msec/sector
    time to read 20480 sectors   2.170542 sec   =    0.106 msec/sector
    calculated command overhead 

    =    0.100 msec/sector

top (at the moments where the load is high)

 Mem: 1518M Active, 1396M Inact, 732M Wired, 50M Cache, 435M Buf, 239M Free
 4949 mysql          27  52    0  2236M  1434M uwait   3 180:02 66.26% mysqld

my.cnf

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
skip-external-locking
max_connections=5000
max_connect_errors=1500
ft_min_word_len = 2
myisam_sort_buffer_size = 64M

# Log slow queries
slow_query_log = 1
slow_query_log_file = /var/log/slow-queries.log
long_query_time = 1
key_buffer              = 384M
max_allowed_packet      = 32M
thread_stack            = 128K
thread_cache_size       = 128
thread_concurrency      = 16

wait_timeout            = 2000
interactive_timeout     = 10000
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

I have no idea what's wrong.

Solution

Some general troubleshooting tips:

• Check if you have other/unknown/external connections to the database (for ex: netstat -tapn )
• Check what queries are running and status of the server with with mysqladmin processlist and mysqladmin process status , this may lead to adding indexes to tables.
• Check slow query log (you've done this)
• Check the mysql error log (you've done this)
• Check general status of the server regarding RAM memory and available disk space: free -m , df -h
• If you have memory available, increment the innodb_buffer_pool_size value in the config file, so there's more caching

Comments

[Admin]

Analyze queries and indices. You should not use that much CPU - something "smells wrong".

[Admin]

Try running show full processlist in the MySQL client. Something might be stuck running the same query over and over.

[Admin]

Run the MySQL Tuner-perl script to see what suggestions it makes for your configuration

[Admin]

strace -p [pid of mysql] - strace is a debugging tool give more details by tracing system calls and signals, it might help knowing what that process doing.