Why does MySQL need double-write buffer if there is redo log and undo log?

Question

Why does MySQL need double-write buffer if there is redo log and undo log?

I read other posts that answer why MySQL needs double write buffer. They point to this article. http://dimitrik.free.fr/blog/archives/2011/01/mysql-performance-innodb-double-write-buffer-redo-log-size-impacts-mysql-55.html

and it will be impossible to repair it from the redo log as there are only changes saved within redo and not a whole page

That article states the above. But I still don't understand why.

Each page's block contains the LSN that was applied to it. Doesn't it? So, even if only some of the blocks of the page were written to disk due to a partial page write, I can look at the LSN associated with each block and detect if it was a partial write. If it was a partial write, I will replay the redo record for that LSN. The replay will give me the updated set of bytes to patch into the given offset range and I'll apply it to the blocks where the LSN wasn't applied previously.

Won't this work? Why do I need the double-write buffer?

Let's say the above does not work. But I can still use the undo log right? I could see that the page was partially updated, get the old value from the undo log and apply it to essentially rollback the transaction.

How does the redo log guarantee that a single log record is not partially written to disk if it crosses a block boundary?

Actually what I cannot figure out is that since a single redo log record can cross a block boundary, what mechanism does the redo log use to detect that the redo record was not fully written and truncate the log to the last known full log record instead of applying a partial log record?

Answer 1

A page can contain multiple records. In fact, it's required to contain at least two records.

The Redo Log isn't a copy of pages, it's a log of changes to specific records.

So if the original page is damaged in a partial page write, the Redo Log cannot reconstruct other records on that page.

Example: A Redo Log contains information about a change to one record, which is stored in a modified page in the buffer pool. It's like a "diff" applied to a page. When it's time to flush that page from the buffer pool, something happens to make the page write fail partially. E.g. the disk hits a damaged sector, or the mysqld process is killed or something like that. So if mysqld restarts and the dirty page in the buffer pool is lost, crash recovery must read the original page from disk and then apply the Redo Log "diff" to that page. But it can't — the original page is partially overwritten, so the diff doesn't have a starting point.

The Undo Log is a copy of original pages, but it may have been purged already by the time a page is scheduled to be flushed from the buffer pool to the tablespace.

Undo Log entries are needed only to allow rollback, or to support other open transactions that are in repeatable-read isolation level. Once the last transaction that needed to view the contents of the Undo Log entry have been closed, that Undo Log entry can be purged. Page modifications corresponding to those Undo Log entries can live in the buffer pool as "dirty pages" longer than that, until they need to be flushed.