Lock your tables when appropriate

Every time you make a change to a table, MySQL sooner or later needs to commit that change to disk. If you change a table that has indexes, however, MySQL needs to make the change and then update the indexes, which takes even more work. Now, consider what happens if you make a large number of changes to your system all in one go - perhaps you have a database of bank accounts and want to add the interest to each of the accounts, or perhaps you want to modify your pay roll account because pay day is tomorrow, etc. In this situation, MySQL has to make potentially hundreds of thousands of writes to the same table(s) in a very short space of time, which, as you can imagine, is not fast.

The setting "delay_key_write", enabled by default, allows MySQL to bulk key writes together so as to save time, but there is a way you can squeeze even more performance out of your system in this circumstance, and that is to lock your tables. Locking a table for writing essentially says "only I can read from or write to this table until I unlock it", where "I" is the program requesting the lock. When you lock a table in this manner, MySQL does not update the key cache for the locked tables until you unlock them, potentially saving it a lot of work. The improvement that locking brings varies as to the complexity of your indexing - locking a table with one simple index will not bring any noticeable improvement, whereas locking a table with three numerical indexes and two fulltext indexes would almost certainly speed things up drastically.

One last note before we look at the actual mechanism of locking and unlocking: locking your tables for writing means that no other tables can read or write until you unlock. If you have a thousand clients connected and reading from a table, and one locks that table, the other 999 will sit and wait as long as it takes for the lock to be released. This is minor compared to the danger of deadlocks, however, which is where one program has a lock on table A and will not release it till it has a lock on table B, and another program has a lock on table B and will not release it till it has a lock on table A.

MySQL does its best to avoid deadlocking, but bad programming can always get around that - be very careful!

So, with that in mind, here's how to use locking in an example that will perform many writes:

mysqli_query($db, "LOCK TABLES mytest WRITE;");

for ($i = 1; $i < 100000; ++$i) {
    mysqli_query($db, "INSERT INTO mytest (Value, Value2, Value3) VALUES ($i, $i, $i);");
}

mysqli_query($db, "UNLOCK TABLES;");

Naturally the situation is pretty false, and you need not have to do as many writes to really get any benefit out of locking tables - if there are several indexes to update, even as few as 1000 writes will make a noticeable difference, with potentially as few as 100 being worth a table lock depending on your indexing scenario.

There is, incidentally, a READ lock available to you that stops other people writing to the same file without necessarily stopping others from reading. This is not performance enhancing, still suffers from the same potential deadlock problems, and is not really "on topic" here - a quick Google search should point you to the right part of the MySQL manual.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.