One of the approaches we are using to look at MySQL scalability is to identify and if possible eliminate hot locks inside MySQL. For locks that cannot be eliminated, we are looking at ways to reduce the mutex hold times, or replace the critical sections with lock-free algorithms. It won't happen overnight, but it needs to be done if we are to make MySQL more scalable.

Along the way we're planning to blog about how we identified and eliminated performance bottlenecks and provide you with all the gory details about the process and technique. This will hopefully entice you all to contribute to fixing performance issues We can reach our goal of a highly scalable MySQL much faster!. If you any other ideas about how we can scale the MySQL scalability effort (pun intended), or you would like to bust some locks, please feel free to let us know.

Fixing locks is sometimes very tricky as sometimes eliminating a really hot lock can only give a modest improvement in performance, or sometimes, no improvements at all. For example, sometimes fixing a hot lock might only move the bottleneck to the next lock. We have to address all the scalability issues before we actually see a dramatic improvement. A recent example illustrates this point.

LOCK_plugin: Not so hot after all

If you have been following MySQL scalability issues, you must be well aware of the LOCK_plugin lock. LOCK_plugin is used to protect the list of plugins used by MySQL. These plugins can be storage engine plugins, udf plugins, full text parser plugins, etc. LOCK_plugin got extremely hot for several benchmarks. Analysis of the contention showed that LOCK_plugin was being acquired and released at the end of every query execute as part of sending results to the clients. A closer look at the usage reveals that this lock is held while providing a hint to Innodb to release any shared locks and release concurrency tickets. The technique used by the MySQL server was to iterate over the plugin list and call ha_release_temporary_latches() for all storage engine plugins. The method used to do this (plugin_for_each_with_mask() grabs LOCK_plugin while iterating over the plugins list. For more details, checkout Bug#34409 LOCK_plugin contention via ha_release_temporary_latches/plugin_foreach

Sergei Golubchik recently fixed it in the most elegant way possible -- do not acquire a lock over the plugins list while releasing temporary latches. Looking at Sergei's fix and searching through the source, we see ha_release_temporary_latches() is called by MySQL server at multiple places without holding the LOCK_plugin lock! We are unnecessarily holding LOCK_plugin! Since holding LOCK_plugin is the side effect of iterating the plugins list, do not use the plugins list to iterate over storage engines; just iterate over the storage engines that are used in this transaction. What I really liked about the fix is that Sergei identified that there is no need for a lock, and got rid of it, rather than trying to break up the lock, or other things!. Kudos to Sergei for fixing this in such an efficient manner. If you are using 5.1.28 or 6.0.7-alpha, you already have this fix.

With this bugfix, we see around a 5% improvement in sysbench read-only tests. The reason we see only a 5% improvement and not more is that eliminating this lock made other locks more contented, and they are now the bottleneck. We are one step closer to a more scalable MySQL. Back to lock hunting for now.