When mysql innodb undo log write and fsync to disk?

Before transaction commit , every dml operation will modify innodb page which will flush to disk and generate undo log , so when the undo log write and fsync to disk to ensure rollback ?

• Make data changes to the blocks in the buffer_pool, but do not write them to disk.

• Put changes to secondary indexes (eg for INSERT) into the Change Buffer for later processing. Again, no write to disk (yet).

• Write and fsync the undo log file if innodb_flush_log_at_trx_commit = 1 (else this write/fsync is delayed).

• Tell the client that the COMMIT is finished.

The idea is to have no more than one fsync per COMMIT. Otherwise, performance would be really bad.

If you are using Replication, then the binlog is written to (see log_bin) and possibly fsync'd (see sync_binlog). And the data is shipped to each Slave. I don't know exactly where these happen in the sequence.

If you are using Galera / FXC / Group Replication, the syncing to the other nodes adds another dimension to the question. (I'll assume you don't need these details.)

StackExchange Database Administrators Q#220054, answer score: 2