How To Reduce table_locks_waited In MySQL/MyISAM
The scourge of parallelism and scaling everywhere: locking. Or in MySQL/MyISAM — and to be more precise — table locks. Here’s an overview of what to look out for and how one might go about reducing the frequency at which they occur.
Before you embark on this please read How To Speed Up MySQL: An Introduction To Optimizing. It suggests some things you should consider first before getting down to the nitty gritty of reducing table_locks_waited. After all it’s usually too many queries that are running too slowly that cause locked tables. Fix those first!
Know Your Enemy: show status like ‘%_locks_%’;
mysql> show status like '%_locks_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 53148 | | Table_locks_waited | 17716 | +-----------------------+-------+ 2 rows in set (0.00 sec)
That’s an upsetting ratio. For every query that had to twiddle its thumbs, waiting in anticipation for a shot at said table, only 3 flew through immediately. You could throw more hardware at it I suppose, it might help. The chances are however you can increase your throughput using one or all of the following methods without reaching for another CPU, more memory or even faster disks.
Why Table Locking Occurs in MyISAM
Locks exist, in a nutshell, to prevent queries from altering data while that data is being read by another process. Or vice-versa.
There are different types of locking in the database world. MyISAM happens to use table locks which are very fast. They are easier to implement when compared to the row-level locking employed by InnoDB and permit a higher query throughput. That assumes of course the number of writes that occur on your database is few. Or, and this is sometimes overlooked, that no one query takes more than an instant.
After all it’s the being locked out that hurts — writes tend to go through pretty quickly — but if they have to wait for a query to complete…
Imagine The Scenario
- 0.00 seconds A select query (ie. read-only) accesses the table, it will take around 2 seconds to complete.
- 0.01 seconds Another select query accesses the table, it takes no time and will complete in an instant as it can run in parallel.
- 0.02 seconds An insert, delete or update query (ie. write) attempts to write to that very same table before the first select has completed.
- 0.03 seconds A select query comes in, again waiting for the first select to complete.
- 0.04 seconds Another select query comes in, again waiting for the first select to complete.
- 0.05 seconds Yet another select query comes in, again waiting for the first select to complete.
- 1.99 seconds (~1000 queries later) Yet another select query comes in, again waiting for the first select to complete.
And so on and so forth. The queries in orange and red are blocked and have to wait for the first select to complete before they can be executed. The selects in red are blocked by the write operation in orange which is in turn blocked by the long-running select in green. This is what causes the table_locks_waited value to grow.
See what happens if you’ve a query that takes any more than an instant and another query comes along, needing to write to the table? A whopping great long queue that’s what. And therefore a collapse in parallelism. You’re not going to scale like this.
How To Avoid Or Reduce Table Locking
There’s a comprehensive list of things you might consider here on MySQL’s site. While they’re all worthy of consideration I can’t help feeling some of them will only serve to delay the inevitable.
Divide And Conquer: Your Queries
As described in How To Speed Up MySQL: An Introduction To Optimizing consider splitting up your queries. Ten queries that take an instant is preferable to one when locking is an issue. Make your application do the work: it’ll scale more easily than MySQL.
Row-level Locking: InnoDB
InnoDB uses row-level locking. That is to say during normal operation it won’t lock entire tables when only updating a single row. The locking mechanism itself is slower than MyISAM but if your table is heavily contended it might be that the table in question should be moved to InnoDB.
That has its downsides of course. Your indexes will grow in physical size, chances are you may need more memory to cope.
Divide And Conquer: Your Data
Divide your data. Shard. Partition. Ensure no one table is too big. What’s too big? It depends. MySQL barely imposes any artificial limits on the size a table can grow to but you’re going to have to eventually make a call to split the table into shards or chunks.
That’s easier said than done of course. Most people shy away from sharding in MySQL because it’s difficult. MySQL are beginning to include partitioning themselves. It’s the sort of thing that Hadoop does very well though.
Alternatives To MySQL
There are a lot of non-traditional DBMS out there that can be superior to MySQL in some situations. Don’t believe it if anyone suggests your data must reside in either MySQL, PostgreSQL, Oracle or perhaps Microsoft’s SQL Server.
I hope we’ve given you some idea of how to start dealing with lock contended tables or at least food for thought as it were. Reducing table_locks_waited can tricky to get right and depends very much on your data and the nature of your queries. Make sure you do review MySQL’s page on the matter. A tweak to your configuration may be enough for your application in the short term.