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
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
I would appreciate any suggestions.
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.