The cost of table locking and index flushing in MySQL

This post may not apply to the large majority of applications out there, but I think it’s interesting enough. When doing sequential bulk inserts into MyISAM tables, you may think you’re getting rather good performance number wise. On low end commodity hardware with not too wide tables, you very easily get up into the thousands per second.

When you perform an INSERT on a MyISAM table, you first implicitly aqcuire a full table write lock, then make the insert followed by a flushing of the index. The index flushing is done as the lock is released, so if you insert 1000 rows, you aqcuire a lock and flush the index 1000 times. This is of course a bit of an overhead.

Luckily, if your application allows for it - mitigating the overhead is very simple and effective. If you aquire a table write lock at the beginning of the session, run your inserts and then release the lock, you get rid of quite a big chunk of code execution. I’ve benchmarked this, using a simple int,varchar table with an index on the int column, and the difference is quite noticeable:

Inserts per second, implicit locking: 4272 rows per second

Inserts per second, explicit locking: 5186 rows per second

So in this case, we gained nearly 900 inserts per second, just by prepending the torrent of inserts with LOCK TABLES a WRITE and append it with UNLOCK TABLES;

In contrast, using MySQLs extended insert functionality, these numbers can further be drastically increased. The same benchmark, but with 310 rows inserted with each INSERT statement yields these figures:

Inserts per second, implicit locking: 138666 rows per second

Inserts per second, explicit locking: 159786 rows per second

So a combination of extended inserts and explicit table locking may give your insert heavy application quite a boost! For obvious reasons, this does not really apply for InnoDB!