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.
By default, innodb_thread_concurrency is 0. That's actually the best setting. It means infinite concurrency. It allows the InnoDB storage engine to decide the best number of concurrency tickets to launch and address. Setting it to a nonzero value actually can throttle InnoDB or throttle the OS if not set properly.

I have written many posts in the DBA StackExchange about this setting:


May 26, 2011 : About single threaded versus multithreaded databases performance
Aug 16, 2011 : Why do MySQL threads often show &quot;freeing items&quot; status when the query cache is disabled?
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Aug 03, 2012 : Slow insert performance in mysql innodb table for solr indexing