This is to follow up my previous post with kernel_mutex problem.
First, I may have an explanation why the performance degrades to significantly and why innodb_sync_spin_loops may fix it.
Second, if that is correct ( or not, but we can try anyway), than playing with innodb_thread_concurrency also may help. So I ran some benchmarks with innodb_thread_concurrency.
My explanation on the performance degradation is following:
InnoDB still uses some strange mutex implementation, based on sync_arrays (hello 1990ies), I do not have a good reason why it is not yet replaced.
Sync_array internally uses pthread_cond_wait / pthread_cond_broadcast construction, and on pthread_cond_broadcast call, all threads, competing on mutex, wake up and start racing.
This effect has name thundering herd.
Davi Arnaut does not agree with me, where I do not agree with him either. This is the healthy discussion, and it is possible only because InnoDB is still Open Source and we all can check source code. If the problem were in the closed extension Thread Pool I could not participate in it.
We will probably argue more on that topic, but that does not stop us from trying different
innodb_thread_concurrency ( 0 by default, that is no restrictions).
There is results for workload as in previous post, 256 threads and
Wow, this is something. I expected improvement, but not almost 3x times ( 194537÷68369 = 2.8).
The best throughput is with innodb_thread_concurrency=8.
So now let’s compare results for innodb_thread_concurrency= 0 vs 8 for all range of threads:
|Threads||innodb concurrency=0||innodb concurrency=8|
So innodb_thread_concurrency is even more helpful innodb_sync_spin_loops, and allows to get stable result even with 1024 threads. It is yet early to say it useless, and you may play with it.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.