MySQL sync_binlog performance and reliability

Testing

sync_binlog is a setting that (when set to 0) forces MySQL to flush a write to binary log to disk after every transaction. I have performed a quick test to see how does the setting affect the performance of MySQL and the reliability of the binary log.

Test setup:

  • Ubuntu Server 13.10 running in VirtualBox
  • MySQL 5.5.32 from the standard package, default configuration
  • InnoDB storage engine used
  • SSD storage but no battery-backed disk

I have tested the performance of a script that inserts 10000 short rows. Each test was repeated 20 times, then machine restarted and series of another 20 tests performed. Higher times for the initial tests (1-4 and 21-24) are most likely related to warm-up after reboot and should really be ignored. After each test, I’ve checked the value of the dirty (not-flushed) caches to validate the result. Note that this is pretty extreme situation – I’m emulating an application that only does writes to the database.

sync_binlog=0

Time and kB of dirty cache with sync_binlog=0

cc

Time and kB of dirty cache with sync_binlog=1

Performance

As expected, with sync_binlog=0 there are plenty of binary log changes to be flushed to disk. There is none when running with sync_binlog=1. On the other hand, the average time to perform 10000 INSERTs jumped from 23 seconds to 41 seconds – nearly doubled.

Reliability

I’ve tested how reliable is each setting by running the script with 10000 INSERTs, forcefully powering off the whole system, starting up again and then comparing records (transactions) written to the database versus those written to binary log. I have tried it few times with both setting and the same results each time:

  • with sync_binlog=1 the data in the database was always in sync with the binary log. That is, the last record succesfully inserted into the database, matches the last entry in binary log.
  • with sync_binlog=0, after reboot there were thousands of records persisted in the database but missing from the binary log.

Summary

If you care about binary log (think about master-slave replication for example) set sync_binlog to 1, if you want to squeeze a bit more performance, set it to 0.

Leave a Comment


*


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">