A growing trend: InnoDB mutex contention
I’ve been noticing an undeniable trend in my consulting engagements in the last year or so, and when I vocalized this today, heads nodded all around me. Everyone sees a growth in the number of cases where otherwise well-optimized systems are artificially limited by InnoDB contention problems.
A year ago, I simply wasn’t seeing the need for analysis of GDB backtraces en masse. These days, I’m writing custom tools to gather and analyze backtraces. A year ago, I simply looked at the SEMAPHORE section of SHOW INNODB STATUS. These days I’m writing custom tools to aggregate and reformat that data so I can interpret it more easily. And I’m actually seeing cases of this type of problem multiple times every week. I remember the first time I ran into a server that was literally optimized to the limit, but struggling under the load. It was something new for me, not that long ago. Oh, I’d seen it before, plenty, but was always able to point out where something could be improved without changing InnoDB itself. Now it’s commonplace: schemas are fine — check. Queries are all well-indexed — check. Everything else — check. InnoDB is bottlenecked and absolutely nothing can be improved — check.
Part of the difference is the rapidly improving hardware. It’s getting hard to buy a server with fewer than 8 or even 16 cores, and 16GB of RAM feels like something I’d install in a wristwatch. But I also suspect that if I’d been characterizing the workload of servers over time in a way that was easy to compare, I’d see a clear trend towards bigger data and more queries per second. We’re just pushing MySQL + InnoDB harder today than we ever have before.
What can be done? Well, InnoDB needs to be improved, that’s all. Oracle, Percona, Google, Facebook and others are working on it, and in many cases these efforts have yielded dramatic results. But there is still much room for improvement.
Intel Nehalem CPU and SSD will make new age for database systems.
Especially SSD will do it. Disk speed is the main bottleneck in the database systems in the past several years. The SSD have change a lot to DB. And many database systems should change it’s base assumes for the DB.
jametong
4 Mar 10 at 3:42 am
SSDs won’t help if your mutex contention is on buffer pool operations
Gavin Towey
4 Mar 10 at 5:45 pm
yes. Disk seek speed will not change the contention in the db or any other subsystems.
jametong
5 Mar 10 at 1:36 am
Actually, hardware improvements, including faster disks, will “change the contention.” They’ll make it worse. They already are. That’s my point.
Xaprb
8 Mar 10 at 8:17 am
In general, how often is part of the solution reducing innodb_thread_concurrency?
Recently I did a bunch of benchmarks on a cpu bound workload on a box with a bunch of cores. Across all version performance was optimal with innodb_thread_concurrency in the neighborhood of 3. This left me feeling sad.
Some of the query pileup issues I have come across appear to be able to be resolved or reduced by reducing innodb_thread_concurrency. Any thoughts?
Rob Wultsch
12 Mar 10 at 5:28 pm
Rob, how do you define performance? That should not be the case for this specific benchmark: latest XtraDB, 32 cores (for example), and “performance” is defined as the sum of the throughput of all threads.
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.
Xaprb
13 Mar 10 at 10:41 am
Baron, for that workload I was looking for peak throughput as you defined it rather than peak responsiveness.
Using the 5.1.41 with the innodb plugin I saw a couple interesting things:
I observed a double peak in terms of peak throughput with the greater maxima by a hair happening at low sql and innodb concurrency and another maxima at high sql and innodb concurrency.
Throughput was not dramatically reduced with very high sql and innodb concurrency unlike the 4.X series.
Throughput was significantly lower with very low sql or innodb concurrency.
I think that I was probably hitting a corner case with the workload. I am looking forward to benchmarking several other workloads in the somewhat near future. I am also fairly green when it comes to performing large sets of benchmarks so perhaps I botched the test.
From my (limited) testing it looks to me like you are correct that innodb_thread_concurrency is no longer a critical variable, however it seems like it could still be useful when workload is very consistent.
mk-query-digest has made it much easier to emulate production workloads. Thank you for a great tool.
Rob Wultsch
13 Mar 10 at 1:09 pm