MySQL Stored Procedure Performance Tricks

In our application at ControlPath, we have a pretty complex permission system. Well, imagine a permission system based off of a cyclical graph of 30,000 nodes with multiple levels of inheritance rules and you just begin to scratch the surface. I recently rewrote the permission rebuild caching system into recursive stored procedures. This improved performance from 45 minutes… to 45 seconds. There are a bunch of tricks I employed to get this kind of speedup, but today I am going to share 2 of the simplest ones with you…

Firstly, the cache table should be MyISAM. Why MyISAM? Because when you are doing a massive amounts of inserts, as I do while rebuilding the cache, you can turn key updates off! Normally MySQL updates and sorts the key indexes each time you do an insert. Well, it turns out that with MyISAM you can tell MySQL to rebuild all the keys after you have done all your inserts. This is awesome because MySQL has a separate, super optimized algorithm to do this and it goes WAY faster than if you hadn’t disabled keys! All you have to do is:

After enabling the keys, MySQL runs its super fast indexing algorithm. But wait!!! There is a serious problem here!

The cache table easily has several millions of rows in it. Even though it only takes about 45 seconds to generate these rows it is possible to deadlock this process and essentially make it never return. How?

With the MyISAM storage engine, SELECTS block INSERTS. (Only InnoDb has row level locking allowing them to execute at the same time) While the stored procedure is running, doing its inserts, KEYS ARE DISABLED. Now, if a user performed an action that required a SELECT from the cache table, it would essentially deadlock the system as a SELECT on a table with millions of rows with no keys takes forever to run and the stored procedure stops and waits for this SELECT to finish.

You might immediately suggest that I lock the tables. Unfortunately thats not possible in stored procedures. The LOCK TABLES command is not available for use in a stored procedure. Then you might suggest that I lock the tables on the conenction prior to executing the stored procedure. The problem with LOCK TABLES is that I have to lock all the tables that I select from in the stored procedure. Building this cache I select from a variety of different tables accross the app… I might as well just shutdown the app and kick all users off…. no…. there is a better way. A MUCH better way that doesn’t interupt users on the system at all and allows all queries to flow as normal. Its almost magical in its simplicity…

Do all your work in a separate cache table, then swap it out with the real one using the RENAME TABLE query.

This is a great command because

  1. Its atomic
  2. Its nearly instantaneous

While you are doing all your work on the temp_cache_table, no other queries are selecting from it, they are selecting from the table you are going to replace at the end. You don’t get any user interuption. When the rebuild is done, users essentially start using that new data immediatley and seemlessly.

You cannot use a TEMPORARY table because you can’t convert a TEMPORARY table into a real table. This is ok though because since this is a “full rebuild” we shouldn’t be running this stored procedure at the same time anyway. So, in my Java code I restrict it to running one at a time. You could do this restriction in SQL using semaphores, but that is a post for another time.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">