Fun MySQL fact of the day: sync_binlog is a counter

2019-02-26 21:08UTC

The D in ACID stands for Durability, which is the property that guarantees a committed transaction will be permanently recorded. Perfect durability is impossible, but MySQL allows us to improve our ability to offer this guarantee this through the sync_binlog flag.

This flag is actually a counter of the number of transactions MySQL will allow to happen before "forcing" the binary log from memory/cache to disk by calling fsync. fsync is a system call to flush a file (descriptor) to disk. In new kernels, fsync can/might "wait" until the write has made it past the disk cache.

A setting of 1 (the default as of MySQL 5.7.7) tells MySQL to call fsync after each transaction is committed and written to the binary log. This mechanism allows for a trade-off between durability and write speed.