Fun MySQL fact of the day: binlog cache

By now, I think we have a shared understanding that the binary log is an important feature in MySQL. And maybe, you may have been wondering, "how does the binary log get recorded"? And that's a good question with a couple fun facts we'll look at next!

Let's first imagine a "trivial" implementation wherein every change is serialized into the binary log as they're issued. The first thing that comes to mind in such an implementation would be serialization: writing to the binary log in such a fashion would require each change to happen sequentially, one starting only after the previous one completes. More, in such an implementation, imagine how replication or point-in-time recovery would (not) work: being able to identify which change was rolled-back would be rather difficult, and enforcing the properties of ACID would be pretty much impossible. And, well, for these reasons, MySQL doesn't do this.

Instead, MySQL allocates a cache of size binlog_cache_size bytes (default 32KB) in memory for each thread. Then, when changes are made, the thread serializes the binary log events into this dedicated cache. Finally, when a transaction commits but before any locks acquired during the transaction are released, the binary log events are read from the thread's binary log cache and serialized into the actual binary log file. If, on the other hand, the transaction is rolled-back, the cache is discarded as if nothing ever happened.