MySQL 5.5 High CPU Usage

Question

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was high CPU usage in the server. All the time CPU load is 15-20. We used litespeed and MySQL 5.1 with CentOS 5.9 in Dual Xeon L5506, 12GB RAM server with Sata HDD.

So I analyzed the database and found there is only 4GB of data and index size of that DB and decided to convert to InnoDB. Once we did, we ended up having 80-150 CPU load and server was about to crash. So we transferred MySQL to another server with same config but to MySQL 5.5.

In New DB server CPU load is 1-2 and web server still on 4-6 constant CPU load.

Here is my my.cnf

[mysqld]
innodb_file_per_table=1
local-infile = 0
default-storage-engine = InnoDB
max_connections = 1000

innodb_buffer_pool_size = 8G
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_file_format = Barracuda

myisam_sort_buffer_size = 16MB

query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 256M
thread_cache_size = 16K

key_buffer_size = 128M
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 1M
sort_buffer_size = 32M
table_cache = 4K
open_files_limit = 65535

log-slow-queries = /var/log/mysql/slowqueries.log
long_query_time = 3

PS : Not all of our DB's are InnoDB, so MyISAM values are placed after good analysis.

Statistics : For 4 hours

Questions since startup: 7,339,471 Documentation 
ø per hour: 1,704,102
ø per minute: 28,402
ø per second: 473

   Traffic              ø per hour
Received 4.8 GiB    1.1 GiB
Sent     248.5 GiB  57.7 GiB
Total    253.3 GiB  58.8 Gi

Answer 1

OBSERVATION #1

When committing to use InnoDB, you also need to commit to tuning for multiple cores.

• innodb_read_io_threads : The default is 4. I would raise it 16.
• innodb_write_io_threads : The default is 4. I would raise it 16.

OBSERVATION #2

I see you have innodb_thread_concurrency = 8. If you set innodb_thread_concurrency to 0 (which is the default), you will have infinite concurrency. That let's the InnoDB storage engine decide how to many threads it feels it needs and can handle.

OBSERVATION #3

Your DB Server has 12GB of RAM. Your InnoDB Buffer Pool is bigger than half the RAM. You need to partition the Buffer Pool by setting innodb_buffer_pool_instances to 2. In conjunction with this, you need to run numactl --interleave=all (Not applicable to VMs).

OBSERVATION #4

I see you have innodb_file_format = Barracuda. I wish you can go back to innodb_file_format = Antelope. Why go back to uncompressed? It tends to bloat the InnoDB Buffer Pool because compressed and uncompressed data and index pages coexist in the Buffer Pool. I just wrote about this : See my post innodb_file_format Barracuda

EPILOGUE

Here are some of my past posts on tuning InnoDB

• Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
• Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
• Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
• Sep 20, 2011 : Multi cores and MySQL Performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?
• Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

Give it a Try !!!

Answer 2

I suppose that you're worried by the web server CPU load right now, since load 1-2 on a busy database like that is quite nice. I have noticed that you have:

long_query_time = 3

in your configuration. With this setting you are not catching all long running queries - 3 seconds is very long for a busy website. When PHP makes a request to a database and it waits for 3 seconds the process on the webserver is in runnable state and increases the load. To see that effect write a PHP script that loops a SELECT SLEEP(3) query, run a hundred of those scripts in parallel and see how this affects the load).

My suggestion is to lower long_query_time to something more reasonable (0.2? 0.1?), process the slow query log with pt-query-digest and deal with the queries that take most time cumulatively. Since you possibly can't change the queries themselves you might have some luck with indexing the popular tables.