I consider innodb_thread_concurrency a vestigial tail of the “built-in InnoDB” that ships by default with MySQL 5.0 or 5.1, and should generally be set to 0 with recent versions of XtraDB or the InnoDB Plugin.Can this be? I cannot wait for innodb_thread_concurrency to be made obsolete. I run a lot of CPU-bound benchmarks on 8 and 16 core servers and I always set it to 0 in my benchmark framework. A few times I have repeated tests with it set to a non-zero value to understand whether that helps and it has never helped. Alas, this will also make my FLIFO patch obsolete.
I agree with Baron that it should be set to 0 with the InnoDB plugin and XtraDB. This is a big deal that has not received enough attention. InnoDB and XtraDB have gotten much better at supporting highly-concurrent workloads on many-core servers. For me highly-concurrent means 100 to 1000 concurrent transactions and many-core means 8 and 16 core servers.
This is not an easy workload to support. MySQL is getting much better at it. A lot of work remains to be done. MySQL 5.5 has even more improvements and several problems have yet to be fixed in InnoDB. But this is a huge deal. Maybe we can have a going away party for innodb_thread_concurrency at the conference?
Posts
The sample config file in support-files/my-innodb-heavy-4G.cnf for 5.5 still has:
ReplyDelete# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16
I always considered this setting a 'workaround'. One of the problems with workarounds is that people will keep using them forever.
I'll bring a mutex cake to your party though ;) The rules of the mutex cake is that only I can eat it.
Mark,
ReplyDeleteIn my recent benchmarks on FusionIO I still see needs for innodb_thread_concurrency.
Otherwise sysbench oltp (100mln rows on 8GB buffer_pool) is going crazy on 256+ threads dropping down.
innodb_thread_concurrency=16 keeps it under control.
Well, the major problem now is having i/o concurrency control together with CPU concurrency control - as these are entirely separate resources that shouldn't be under one variable.
ReplyDeleteHaving zero concurrency control entirely relies on idea that OS will schedule properly, will not cause any lock convoying, etc
Always trusting that? Ummm, dunno...
I quite like the concurrency control for CPU workloads. :)
@Morgan - it would better to share it with 4 other people, bring 5 forks -- http://en.wikipedia.org/wiki/Dining_philosophers_problem
ReplyDelete@Domas - if you must use it, then someone should change it to occasionally suspend threads blocked on prepare_commit_mutex and an disk IO.
@Vadim - do you know what mutexes cause the problems? Or can you give me more details on your test setup? I have not been running many IO bound tests recently.
@Mark,
ReplyDeleteI posted bug there
https://bugs.launchpad.net/percona-xtradb/+bug/548939
it is regular sysbench oltp
sysbench --test=oltp --oltp-table-size=100000000 --init-rng=1 --num-threads=$N --max-requests=0 --oltp-dist-type=uniform --max-time=180 --mysql-user=root --mysql-socket=/tmp/mysql.sock run
but on XtraDB, so it may hit different mutex than in InnoDB-plugin case.
Mark,
ReplyDeleteUnfortunately, I think innodb_thread_concurrency is needed forever,
unless MySQL/InnoDB and Linux kernel to be integrated into one product and optimized enough for too much thread scheduling.
innodb_thread_concurrency can avoid useless contention at thread scheduling of OS layer for the too much threads.
For example, some Solaris-ist may insist the Solaris' performance advantage from Linux at too much sessions. But I think if we use innodb_thread_concurrency properly, there are no such advantage at the situation.
I think innodb_thread_concurrency allows the easy thread system of OS, and the light weight thread system and innodb_thread_concurrency may achieve the one of the best local optimized.
Mark,
ReplyDeleteIn some recent benchmarks conducted by me I observed setting innodb_commit_concurrency=0 gave me a marginal performance edge over higher values.
@Hareesh - how many concurrent clients did you use? And how many CPU cores did the server have?
ReplyDeleteConcurrent threads ranges from 16 to 1024.
ReplyDeleteI used a 8 core CPU and ran sysbench oltp benchmarks with the data set comfortably fitting in buffer pool . I am planning to run some IO bound tests this week.
Heh, you know you're a db geek when you find immense enjoyment in a reference to the dining philosopher's dilemma and a mutex cake.
ReplyDelete