Description:
Hi,
when committing a transaction bigger than binlog_cache_size [1], the binary log temporary file used to spill binlog events to disk is copied to the binary log. For big / huge transactions, this takes time, blocks concurrent transactions, and incurs a 2x free disk space requirement. These delay, blockage and 2x disk space requirement can all be removed by leveraging the binary log temporary file as the next binary log file. See Suggested fix for details.
[1]: https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html#sysvar_binlog_...
Many thanks for considering this,
Jean-François Gagné
How to repeat:
Not applicable to a Feature Request.
Suggested fix:
First, this needs introducing a new global variable to indicate when this optimization is used (suggested name: binlog_trx_size_for_tmp_file_reuse). With a value of -1 (default) the current behavior of MySQL (without below tmp file overhead) is used. When a transaction size in the binary log is bigger than this variable, the binary log temporary file would be renamed (and fixed, see below) to become the next binary log file. IMHO, a good value in production for this variable would be 64 to 128 MiB, avoiding commit delays of about one second.
The optimization needs "augmenting" the binary log temporary file. This file must now have the right header to become a new binary log file. This includes the binary log magic number, the Start event, and the Previous-GTIDs event. Before renaming the file, the Start event and the Previous-GTIDs event have to be "fixed" (like the GTID event).
Obviously, this can only work if no UUID is added to GTID-Executed while the transaction is executed. This should not happen very often, and if it does, falling back to a full copy can be done.
