Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

OBSERVATION #1

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

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

Give it a Try !!!

share|improve this answer
    
Thank you for the suggestions. It turned up pretty well at the end. Now the CPU levels are back to normal and overall performance is good. –  adinindu Aug 3 '13 at 20:53
    
OBSERVATION #4 : Barracuda != compressed row_format. Dynamic row format is used for the latest InnoDB file format by default. –  eroomydna Dec 31 '13 at 0:37
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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