Fun MySQL fact of the day: binlog cache overflow

Yesterday, we started to consider how MySQL writes its binary log file, and we started to look at how MySQL uses a per-thread binary log cache, which, by default, is 32KB. Depending on your systems' access patterns, however, 32KB may seem rather small. For example, what if you frequently update a table with a 16MB MEDIUMTEXT column? Just that one update would easily blow through your cache.

Well, in the case that your transaction's binary log events exceed binlog_cache_size bytes, MySQL will evaluate another global variable, max_binlog_cache_size, which defaults to 18.44 exabytes! Then, in the extremely likely case that the transaction size does not exceed max_binlog_cache_size, MySQL will create a new temporary file in the same fashion much as we described back on April 23 for filesorts. This temporary file will store the overflow for the current thread's binary log cache, meaning that the binary log will start hitting disk even before a transaction is committed. But, what happens if your change is larger than max_binlog_cache_size? In that case, MySQL will kindly abort your change (without rolling back your transaction):

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

Now, just like the temporary files MySQL creates for filesorts, the temporary file is unlinked immediately after it is created. This means you won't see the file by looking at your filesystem, but if you're crafty with a debugger, you can catch MySQL in the act. Of course, running a debugger in production is usually a really bad idea, and, thankfully, MySQL publishes a global counter, Binlog_cache_disk_use, which shows the number of times the binary log cache has overflown to disk. You can SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use' to view it, and if it's not 0, you may want to consider increasing your binlog_cache_size.