what is the difference between innodb_flush_log_at_trx_commit and sync_binlog?

user_12640305 0
Viewed 557

I am reading what affects the durability of InnoDB and find 2 config fields. I have the following questions:

  1. What is the difference between the 2 config fields? My first guess is that innodb_flush_log_at_trx_commit affects InnoDB redo log, and sync_binlog affects the MySQL standard binlog. Am I right?
  2. My second question is if the logging process is divided into 3 phases: write to buffer, write to os cache, flush to disk. Then in which phase does the secondary replication happen?
  3. about sync_binlog, I have another question. If sync_binlog is set to 0, according to innodb doc, the flush is not on commits but delegated to OS. Could it be the case that the binlog is synced before commit so that replication see data not committed?
innodb
1 Answers

Add innodb_flush_method to the list.

innodb_flush_log_at_trx_commit should be 1 for security (won't lose any data in a crash) or 2 for speed. (0, I think, is there for historic reasons and has no advantage.)

sync_binlog avoids a Slave from trying to read off the end of the Master's binlog (after a crash). Since the data was already sent to the Slaves, it is not a "data loss" issue, but an annoying error that is easily rectified by hand (move it to the next binlog).

I think this is the order of the replication steps. Note: In the case of a transaction, nothing happens until the COMMIT. (See also "binlog_cache_size".)

  • Send data to Slaves and flush the copy to the binlog if sync_binlog is on (else let it eventually be flushed). (I don't know which happens first, or even whether they are done by separate threads.)
  • The I/O thread on the Slave copies the data to its "relay log".
  • Eventually (usually right away), the execute thread performs the query.
  • (Multi-source replication and parallel execution on the Slave add further complications.)
  • Semi-sync gets involved somewhere.
  • Galera -- see "gcache", etc.

What do you mean by "secondary replication".

Your item 3 may be referring to an obscure case where something could drop through the cracks because too many pieces of hardware are involved.

If you want reliability, see Galera Cluster and/or InnoDB Cluster. This goes beyond what is available with simple Master-Slave replication even with semi-sync.

user_17668310
Related