Difference between flushing and synchronisation and writing to disk (MySQL)

Question

I get confused with the terminology such as flushing and writing to disk and synchronising to the disk. Could someone please explain me what is the difference between flushing and writing and synchronising to disk especially in context of MySQL.

I used to think that they are both the same operations, but while reading various concepts it confuses me.

For example. The documentation says for rpl_semi_sync_source_wait_point variable that if I set it to AFTER_SYNC then the source writes transactions to bin log and replica first and then syncs the binlog to disk.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_rpl_semi_sync_source_wait_point

AFTER_SYNC (the default): The source writes each transaction to its binary log and the replica, and syncs the binary log to disk. 

This statement is itself confusing. First it says source writes each transaction to its binary log and the replica and then it says syncs the binary log to disk.

So what exactly is happening in this case. What exactly is flushing, syncing and writing to the disk in the context of MySQL? The question is not about sync_binlog or any other parameter in MySQL. The question is about flushing, syncing and writing and about when do these activities happen?

Again when I tried to read more, in the following link, it explains that, there's a binlog cache, a page cache and an fsync operation. Thus, when a transaction happens, if I understand correctly, the buffer is made dirty, the contents of the transaction are written to binlog cache (at MySQL level) which is separate cache for each session, then binlog cache is written to page cache (at Linux level) then depending upon sync_binlog value, it is flushed/written/synced to the files on disk.

https://betterprogramming.pub/understanding-mysqls-binlog-4ac8de4d20ee

Answer 1

This is a complex topic but let’s try to talk about it a little bit. When it comes to “regular” writes (INSERTs, UPDATEs, DELETEs) then the process is as follows (and, to make it clear, I’m not going to clarify all of the details like double-write buffer and what not).

1. Application executes some queries. Modified rows are stored in the buffer pool as dirty pages

2. Changes are written to the redo logs (InnoDB redo log)

3. Changes are also stored in the binary log, if it is enabled

4. Then, depending on the sync_binlog setting (1), binlogs might be synced (persisted, flushed) to disk before COMMIT happens, after every write.

5. COMMIT happens

6. Depending on the innodb_flush_logs_at_trx_commit redo logs are either:

   a. Flushed (synced) to disk at the COMMIT (1) b. Written to disk at the COMMIT but flushed after 1 second (2) c. Written to disk and flushed after 1 second (0)

7. Later, at some point in time, data from InnoDB buffer pool is written down to the tablespaces and persisted there eventually (clearing dirty pages in the buffer pool)

Now, what’s the difference between write and flush (sync)? Write is just write. MySQL writes to a file. It goes then to the operating system filesystem cache and it stays there in memory. So, writes are not persistent. They will eventually be (each OS eventually will write it down to the file itself) but not immediately. This is the fasters write as it is cached in memory. Then we have syncing the data to disk. In that case, MySQL will attempt to force the write to be persisted on disk. How it does that is defined in innodb_flush_method variable, defaulting to O_DIRECT + fsync() on most of the linux distributions. In that case, write bypasses the OS filesystem buffers and is executed directly on disk drive. Or at least that’s what MySQL thinks.

In reality, you have to consider also configuration of the operating system, disk storage or even disk drives themselves. If you have SAN or some sort of a RAID controller, most likely flushed write is not persisted on disk but in the SAN or RAID controller cache. Even disk itself might “fake” flushing data and just store it in its write buffer.

In the case of your example, if you have sync_binlog=1 then any other cache should be irrelevant. Binlogs are persisted after every write. If you have sync_binlog=N then MySQL may cache N writes in the binlog cache before they are persisted to disk.

As for rpl_semi_sync_source_wait_point, it defines when the source node is waiting for the ACK from the semi-sync replicas. It goes like this on the application side:

1. BEGIN

2. Execute queries

3. COMMIT

The difference is in point 3. If it is AFTER_SYNC, COMMIT will be executed only after replicas confirm that the data is stored on a replica, ensuring that no matter what connection you use on the source to read the data, you will see the same content. If it is AFTER_COMMIT, COMMIT will be executed while the data is still being persisted on replicas. It means that if you connect to the source using another connection, you will see the committed data even though the client that committed it still waits on the replicas to acknowledge they received the data.