As far as I understand, if sync_binlog=1, at transaction commit we could skip not only the fsync() call for the InnoDB redo log files, but also the call log_write_up_to(mtr.commit_lsn()). That is, we could group all writes from the log_sys buffer to the InnoDB redo log files in bigger batches.

Furthermore, my understanding is that the internal use of 2-phase commit (XA distributed transactions) can be removed in this case. That mechanism would only be needed when XA START/END/PREPARE/COMMIT/ROLLBACK statements are being issued from SQL.

The fsync() in InnoDB would still be needed for preventing harmful reordering of writes (to stick to write-ahead logging). The primary mechanisms for driving that should be redo log checkpoints and dirty page replacement in the buffer pool.

The MDEV would implement a MDEV-16589 requirement.

I wonder whether we need innobase_flush_logs() or handlerton::flush_logs at all. In InnoDB, this function is invoking log_buffer_flush_to_disk(), which in turn is initiating a write of all buffered redo log to the log files, instead of merely flushing the log up to the state change of the current transaction (trx->commit_lsn, which is what trx_flush_log_if_needed() should have written already.

All this code should be reviewed and cleaned up as part of this task.

sysbench 1.1.0-1327e79
Time taken to do '1000000' inserts is measured using sysbench on latest 10.5.

Sysbench commands:

./src/sysbench /sysbench-master/src/lua/oltp_insert.lua --tables=1 --threads=8 --time=0  --table-size=1000000 --mysql-user=root --mysql-socket=/10.5/bld/var/tmp/mysqld.1.sock --mysql-password=root prepare

./src/sysbench/sysbench-master/src/lua/oltp_insert.lua --tables=1 --threads=8 --time=0 --events=1000000  --table-size=1000000 --mysql-user=root --mysql-socket=/10.5/bld/var/tmp/mysqld.1.sock --mysql-password=root run

(1,0), (1,1) and (0,1) correspond to 'innodb_flush_log_at_trx_commit' and 'sync_binlog' respectively.
"binlog_commit_wait_usec" is mentioned in table as "usec"
"binlog_commit_wait_count" is mentioned in table as "wait_count"
"tables_N" refers to number of sysbench tables used.
"tables_1" corresponds to "sbtest1".
"threads" refers to number of threads used by sysbench

SYSBENCH Results

The more the binlog group commit rate the new proposal will fare better.

I believe that the correct operation of this change depends on the ability of RESET MASTER to reset the binlog position that is persisted in InnoDB (MDEV-22351).

That's correct Marko.

Alibaba seems to have implemented something similar: 云数据库 RDS > AliSQL 内核 > Binlog in Redo

当事务提交时，只需要将Redo Log保存到磁盘中，从而减少一次对磁盘的操作，而Binlog文件则采用异步的方式，用单独的线程周期性的保存到磁盘中。

Google translation:

When the transaction is committed, only the Redo Log needs to be saved to the disk, thereby reducing the operation on the disk, and the Binlog file uses an asynchronous method and is periodically saved to the disk with a separate thread.

They introduced 2 parameters to control this:

• persist_binlog_to_redo: a Boolean flag to enable the functionality (to write a little more to the redo log)
• sync_binlog_interval (default: 50ms) the time period for flushing the binlog files (which are not removed)

The main difference from this task is that the binlog is (almost) guaranteed to lag behind the InnoDB redo log at all times. MDEV-18959 aims to guarantee that the redo log is never ahead of the binlog.

Here, a set of thoughts/suggestions:

• handlerton gets a new method, binlog_pos(). Returns last binlog position persistently committed.
• If at least one engine in a transaction has binlog_pos != NULL — it means a transaction should be recovered from a binlog
• corollary: InnoDB sets binlog_pos == NULL if flush_log_at_trx_commit is 0 or 2
• for recovery to work every binlog event must modify at most one engine. In ROW it happens automatically, in STMT or MIXED it's not guaranteed
  • let's cover only ROW in this MDEV-18959
  • then let's cover MIXED as: mark a statement as unsafe if it affects more than one engine and at least one of those engines has binlog_pos != NULL.

serg, thanks for a constructive feedback. To

InnoDB sets binlog_pos == NULL if flush_log_at_trx_commit is 0 or 2

though, flush_log_at_trx_commit = 0|2 actually must increment binlog_pos (I believe it does so currently), just not that eagerly as the value 1 does, to reflect the last safely/persistently committed.

While debugging MDEV-26603, I was reminded again that the XA PREPARE step that is internally (mis)used by the binlog (using an internally generated MySQLXID identifier) will require an fsync() or fdatasync() operation inside InnoDB.

I think that when a single storage engine is being used, we must replace the internal 3-phase commit mechanism with 2-phase commit. We only have to ensure that everything up to the commit has been durably written to the binlog before a (normal) commit is written to the engine log. Edit: The main point of this task is to ensure that only one log write (the binlog) needs to be durable.

To achieve acceptable performance, I think that we’d want something similar to MDEV-24341 to cover binlog writes, so that no transaction commits inside InnoDB would be prematurely written to the InnoDB write-ahead log.

This could require rewriting the current group commit logic, and turning the current binlog/InnoDB notification mechanism ‘upside down’. The current mechanism is known to be incorrect, as reported in MDEV-25611.

marko, as discussed on slack, there have been two major issues that raise in your comments. This ticket is about roll-forward recovery (the subject #1) which infers (the subject #2) how to find the last stably committed transaction so next to it would be the 1st one to start the roll-forward "replay".
Through implementing mechanisms responsible for #2 we'd optimize away a complicated and troublesome binlog-background-thread and binlog-checkpoint.

To #1 and your 'misuse' qualification though, identification with XID at trx prepare is still not a bad idea as the prepared trx:s might just need the commit decision/operation for their roll-forward (otherwise it'd be a full trx replay). The engine prepared does not require `fsync()` under #1.

Somewhat related to this and https://smalldatum.blogspot.com/2022/10/early-lock-release-and-innodb.html I dug up the commit that imported the InnoDB revision history when it had been maintained separately from MySQL. The log can be viewed with the following command:

git log --name-only 5f9ba24f91989d68ff90d453dbfbc189464b89b9^..5f9ba24f91989d68ff90d453dbfbc189464b89b9^2^

The log includes an interesting change: Enable group commit functionality. I think that some cleanup (review and removal) of this kind of code needs to be part of this task.

Last week, I discussed an alternative solution with knielsen: implement an API that allows a storage engine to durably write binlog. For InnoDB, this would involve buffering a page-oriented binlog in the buffer pool and using the normal write-ahead logging mechanism.

Over the weekend, I realized that in case the binlog is written strictly append-only, there is no need to introduce any additional page framing, checksums, or fields like FIL_PAGE_LSN. Not having a field like FIL_PAGE_LSN means that recovery will ‘blindly’ execute any recovered binlog writes to the file even though the data might already have been written.

A further optimization might be that instead of writing the binlog via the InnoDB buffer pool, we could write it roughly in the current way, but with a few additions:

• write the binlog also to the InnoDB redo log (in MDEV-12353 we reserved record type codes that can be used for this)
• implement an InnoDB log_checkpoint() hook that would ensure that fdatasync() is called on the pending binlog writes that would be ‘discarded’ by the checkpoint
• on recovery, recover the binlog to correspond to exactly the InnoDB redo log (rewrite what was missed, and truncate any extra writes)
• use asynchronous writes rather than synchronous ones (this was found to help a lot in MDEV-23855 and MDEV-23399)

A major benefit of this approach is that it is possible to get the binlog and the InnoDB transactions completely consistent with each other, even when there are no fdatasync() calls at all during normal operation. Around InnoDB log checkpoints they are unavoidable, to ensure the correct ordering of page and log writes.

Thanks for the ideas marko! I have to review the existing patch more in-depth (which was originally authored by Sachin and Sujatha), but from my understanding, it is similar to your suggestion of

on recovery, recover the binlog to correspond to exactly the InnoDB redo log (rewrite what was missed, and truncate any extra writes)

I'll review with a closer eye to that point.

Perhaps we can create individual follow-up JIRAs for the other optimization suggestions.