MySQL InnoDB: Differences between WAL, Double Write Buffer, Log Buffer, Redo Log

Question

I am learning MySQL architecture. I come up with the following illustration:

There are 4 concepts that I don't understand well:

• double write buffer
• log buffer
• write-ahead log
• redo log

I read from many documents, Write-Ahead Log (WAL) is a mechanism for database durability. MySQL WAL Design Wikipedia WAL

Like the above image, there are 2 types of buffers when flushing data from the memory buffer pool to disks: double write buffer and log buffer. Why do we need 2 buffers, and how are they related to WAL?

Last but not least, what are the differences between redo logs and WAL. I think WAL can help database recover when something wrong happens (e.g.: power outage, server crashes ...). What do we need redo log alongside with WAL?

Answer 1

The WAL design document you linked to gives a clue:

All the changes to data files are logged in the WAL (called the redo log in InnoDB).

That means WAL and redo log are two different terms for the same log. There is no difference.

The log buffer is an allocation in RAM. All writes to the redo log are saved in the log buffer first, because it's very fast to save some data in RAM. A transaction could be made of many changes affecting many individual rows, and writing to disk for every one of these rows would be too slow. So changes on their way to the redo log are saved in the log buffer first. Periodically, a group of changes in the log buffer are saved to disk, in the redo log. This happens when:

• You commit a transaction
• The log buffer is full (the log buffer has a fixed size)
• Every 1 second regardless of whether the log buffer is full

The double-write buffer has a totally different purpose. It is actually a segment of the InnoDB tablespace on disk, not in RAM (I think it's confusing that the term "buffer" is used for storage in both RAM and disk).

The purpose of the double-write buffer is to prevent data corruption from partial page writes, while modified pages are copied from the innodb buffer pool to the tablespace. That is, if MySQL Server were to crash while InnoDB is writing a given page to disk, it could overwrite a page on disk partially. Even with the redo log, there would be no way to recover this page.

So InnoDB writes first every page to a small subset of the tablespace called the doublewrite buffer. Once it has finished writing that page, it can then save the page again to the proper page in the tablespace. If this fails partially, it's okay because the page has also been written to the doublewrite buffer. Once the page has been saved to its proper location in the tablespace, the copy of that page in the doublewrite buffer is not needed, and it can be overwritten the next time there's a page flush from the buffer pool.