4

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.

2

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:

  • 1
    Thanks for your answer @RolandoMySQLDBA. I've read many of your posts and have helped me tremendously. I'll change innodb_thread_concurrency = 0. Since setting innodb_concurrency_tickets is irrelevant now, that makes my life easier.. – MYoussef Oct 27 '14 at 20:27
  • do we need to restart mysql after changing the innodb_thread_concurrency setting ? – kommradHomer Dec 24 '14 at 13:45
  • @kommradHomer What version of MySQL are you using ? – RolandoMySQLDBA Dec 24 '14 at 13:47
  • @kommradHomer Never mind. You do not have to restart mysqld. Just run SET GLOBAL innodb_thread_concurrency = 0;. Then, add that setting to my.cnf so it is available after restarting mysqld. Again, you do not have to restart mysqld. – RolandoMySQLDBA Dec 24 '14 at 13:49
0

Try to turn off Query Cache, which might cause extra lock.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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