Mysql – Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs"

• Ubuntu 12.04 LTS Server
• Intel(R) Xeon(R) CPU X5670
  • @ 2.93GHz
  • 12 Cores
• MySQL 5.5.29
• 64 GB RAM
• RAID10

We are currently doing Hyperthreading so we have 24 logical cores. Currently running between 2k-4k transactions/sec.

I have set the following :

• innodb_thread_concurrency = 48
• innodb_read_io_threads = 24
• innodb_write_io_threads = 24

This is strictly OLTP load (70% reads). Queries are relatively fast (milliseconds). Query cache is ON but not used too much. Temp disk tables are not created often. The InnoDB Buffer Pool is set to 48GB which covers almost the whole data set (60 GB data+index).

• Transactional load will double to 4k-8k transactions/sec. Currently CPU load is around 1000% so I anticipate that this will be the bottleneck.

How can I better tune my CPU threading and how MySQL uses these threads? Let me know if anyone has done any benchmarks on innodb_thread_concurrency.

I would appreciate any suggestions.

UPDATE

After updating from innodb_thread_concurrency = 48 to:

• innodb_thread_concurrency = 0

On a production system during peak times (4-5k transactions/sec) to see the behavior this is what I observed:

• Load average increase
• mysqld process CPU usage went from 400% – 1000% to 400% – 1400%
• About 4-10 CPUs are idle while the other 14-20 CPUs are around 70%-90%
• %wa is 0% – 0.1% for each CPU so I/O is not the problem here. (From linux top output)

It seems like setting innodb_thread_concurrency to a non-zero value throttles your CPUs so that they don't go overboard. I set it back to innodb_thread_concurrency=48 and CPU usage decreased.

I would appreciate any thoughts on these results.