Tuesday, June 05, 2012

LOCK_open finally removed as a bottleneck in MySQL 5.6

From the very first day at MySQL, the LOCK_open mutex have been an infamous bottleneck. The road to removing it as a bottleneck has been a long one. Already in the MySQL 5.4 beta release we had some first improvements of the LOCK_open. Other preparations were done in the solution of the bug #901 with the introduction of the MDL locking (metadata locking). Finally some preparations was done in early MySQL 5.6 DMR's where LOCK_open was removed as a mutex for a lot of activities where it wasn't really needed in opening and closing of files.

During an internal meeting in Trondheim at the end of 2010 I sat down with Dmitry Lenev and we came up with a solution where the LOCK_open is removed as a bottleneck. So finally this year we finalised this development and made it available as part of the MySQL 5.6 june 2012 labs release.

LOCK_open was used to protect a number of data structures related to the TABLE_SHARE object that contains a number of attributes about the table. It was also used to protect the cache of TABLE objects and a number of data structures related to it.

So what to do in order to fix this bottleneck. The first analysis is that most queries only need read access to the TABLE_SHARE object and they need one instance of a TABLE object. So if we have a set of TABLE objects then this can be used in parallel and their is no reason to have global data structures protecting the caches of the TABLE instances.

So the first step to fixing LOCK_open is to introduce a new set of TABLE cache instances. Each such instance will contain a list of cached TABLE objects. Each such TABLE cache instance also have a reference to the TABLE_SHARE object. So this means that we can find both a TABLE_SHARE object and a TABLE object by using one of those TABLE cache instances.

So through these set of TABLE cache instances it isn't necessary to use the LOCK_open if there are free TABLE objects in the TABLE cache instance used. LOCK_open is still needed to create new TABLE and TABLE_SHARE objects, but these objects can be reused any number of times once it's created. When performing DDL operations it's necessary to lock both LOCK_open and all TABLE cache instances.

So this means that we have replaced the LOCK_open with a set of mutexes protecting one TABLE cache instance. The number of TABLE cache instances are configurable, we used 16 instances in the published benchmarks. We also added a number of statistics counters to how many accesses of TABLE cache hits and misses we have in a MySQL Server.

We have seen at least 70% improvement based on just adding this new feature and in some cases the gain can probably be even bigger. By using 2 tables in Sysbench we can grow performance even further which would not make any difference when the LOCK_open was still a bottleneck and with this we can get the performance impact of removing LOCK_open to be even larger than 100%.

MySQL 5.6 makes leapfrog in performance

I blogged about how the April labs release 2012 made 5.6 improve performance of the Sysbench OLTP RO benchmark by more than 50%. Now we made an even more significant improvement of the MySQL 5.6 performance in the june 2012 labs release available at labs.mysql.com.

We've been able to improve Sysbench OLTP RO performance by a massive 270% going from MySQL 5.6.5 to the MySQL 5.6 june 2012 labs release. The benchmarks were made on an internal pre-release of the june labs release.

Also for Sysbench OLTP RW we made a very significant breakthrough in performance, the performance increase is 182% here.

The tests were made on a "monster"-box we have available at Oracle with 8 CPU sockets, each socket has an Intel Xeon 7540 processor that have 6 cores per socket and 2 threads per core. Thus a total of 96 CPU threads are available in the machine. The CPU frequency was 2.00 GHz and the available memory was 512 GB. The disks used in the benchmark was SSDs. The benchmarks was executed using the dbt2-0.37.50 scripts available on the dev.mysql.com site. We will upload the latest changes to those scripts early next week. The MySQL Server was locked to use up to 64 CPU threads in the benchmarks executed.

So what is the cause of such massive performance improvement. Well actually it is the result of all 5.6 changes made over a long period of time. There has been a great number of significant changes in the InnoDB engine in the 5.6 development. Most notable of those are changes by Sunny Bains to split the InnoDB kernel mutex into 10 different mutexes. Also Inaam Rana made a significant changes of the buffer pool mutexes to improve these. There has also been numerous other changes in InnoDB, one notable such one that Dimitri Kravtchuk has been instrumental in benchmarking and suggesting changes to is the new adaptive flushing algorithm. Check Dimitri's blog for more meat on the bones of that change. Most of the InnnoDB changes were in the MySQL 5.6.5 release as well, so why the large jump in performance now.

Part of the reason is that there was a cache line that became very hot in the MySQL Server as mentioned in an earlier blog. Removing this bottleneck alone made performance jump by more than 50%. However there was also other bottlenecks that caused performance to still not jump. Most notable of those were the LOCK_open mutex in the MySQL Server part. We have finally now removed this as the main bottleneck in the server. I will describe these changes more in a coming blog. So the impact of the important changes in the MySQL 5.6.5 were hidden by the cache line problems and LOCK_open problems and once those were removed we could see this leapfrog in performance making the MySQL 5.6 the best MySQL release ever.

With LOCK_open removed as a bottleneck it also made sense to see how much performance was affected in Sysbench by only having one table in the benchmark. So we did the benchmarks mentioned above using 2 tables instead of one. Using 3 tables had no impact on performance, but going to 2 tables means that we split one of the mutexes in the data dictionary into two and also the InnoDB index mutex is split into two and finally the root page of the InnoDB indexes are two instead of one. We think it's fair to say that most real-life applications would use more than one table. The second table improved performance by about 25-30%.