libeatmydata - Feed me, Seymour!

Whilst supporting customers at SkySQL I often have to load gigabytes of SQL data into MySQL servers to run tests.  This process can be slow especially for InnoDB because in a standard dump file every insert is a transaction and every transaction has to be synchronised to disk for crash safety.  The thing is, most of the time I don't care if the machine I'm using crashes whilst I'm loading this data into the server.

There are of course many ways around this, such as editing the SQL files and wrapping transactions around batches of inserts and editing the configuration files to disable all the syncing involved.  But I don't want one configuration to load in data and then another to play with the data, so this is where libeatmydata comes in.

libeatmydata is a preloaded library that disables disk syncing functionality from doing just that.  The OS will decide when to sync the data to disk.  This is great for loading in an SQL dump file, taking single insert dumps on default configuration down from hours to minutes.  But you wouldn't want to do it during the production running of your server because power failure would certainly lose you some data.

So, how do you use libeatmydata with MySQL?  Simple, this is the command to start it:

LD_PRELOAD=/usr/lib/libeatmydata.so mysqld

Then you can load in your dump file, shutdown mysqld safely and start it up again without libeatmydata.

A great application I could see for this is scripting the startup of slaves, feeding a dump file into the server with libeatmydata and then restarting without this once the slave is ready.

UPDATE

Kristian Nielsen asked in the comments on SkySQL's blog how much faster it is, so I have run a quick benchmark to find out. In this test I am using a 218MB test file of single row inserts I had generated for an old support issue. I am also using a clean MySQL 5.1.51 installation (cleaned on each run) on my i7 based laptop:

Vanilla MySQL 5.1.51

real    166m19.504s
user    0m23.891s
sys     0m6.084s

MySQL 5.1.51 with --sync-binlog=0 --innodb_flush_log_at_trx_commit=0

real    5m33.578s
user    0m11.096s
sys     0m3.215s

MySQL 5.1.51 with libeatmydata

real    3m14.123s
user    0m10.932s
sys     0m3.108s