MySQL 5.5 High CPU Usage

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

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

StackExchange Database Administrators Q#46752, answer score: 3