As I understand it there are two failure modes:

1. A write to the doublewrite buffer fails. In this case the data in the table hasn’t changed, so the redo log can be applied to update the data.

2. If the write to the doublewrite buffer succeeds and the write to the table fails, the data can be read from the doublewrite buffer to update the table.

If we were writing directly to the table, the failed write would’ve left the table in an inconsistent state where the redo log couldn’t be applied and there’s been no successful write to a buffer that we can read the data back from. The doublewrite buffer means that we always have a way of bringing the data up to date after a failed write, regardless of failure mode.

I haven’t looked at the internals, so this is an educated guess based on the post and comments. I’d be grateful if someone more knowledgeable could confirm.

I write to the doublewrite buffer: AAAAAAAAAA

then flush to the table: AAAAAAAAAA

fine, all’s fine. But now I write again to the doublewrite buffer and the write fails halfway:

BBBBB…..

We “recover” and find that the doublewrite buffer is BBBBB….., with a bad checksum, or however we determine that it isn’t complete, so we “just discard it” per the mysql docs.

Really? This is good?

Now I’ve got AAAAAAAAAA in the table where I expect to have BBBBBBBBBB. AAAAAAAAAA maybe “consistent”, but it isn’t “correct”, and I stil have a problem. If that data is correlated to other data, I still have to recover from a backup to get back to true consistency, i.e. my data all being consistent with each other.

So I don’t really see how this benefits me.

To take the example further, if doublewrite is good, why wouldn’t triplewrite or fourplewrite be even better? Answer: It isn’t, for exactly the reasons I’ve described above. You still have bad data. “Consistent”, perhaps, but not correct.

And now the obligatory “Or am I simply not understanding this?”

Let me try to answer your question.

The problem comes from fact that when we issue pwrite , and there is crash during
this operation, then there is no way to check state of operation.
It may happen that for 16K operation we wrote only 4K or 8K.

So we may end up with situation when half of page contains new information and another half – old information. InnoDB of course will detect corruption using checksum, but it won’t help much, as page is broken.

So solution to this is to have 2 copies of page. if we crash during writing of one of copies – we always have another consistent copy, which we can work with.

thank you, it is so long time you post this great article, don’t know if you can still answer me. I am thinking this for days, but can’t understand yet.

Checksums are also used. Checksums allow to find which pages are corrupted, ie partially written but checksums can’t help to recover page if it was corrupted.

Hi Baron,

Could you please help me understand this a bit better. So is this double write buffer a special file on the disk and can we see this file on the File system using a ls cmd?
Or is this just a space withing the ibdata1 and/or ib_logfile? Because when you say it is useful during recovery then it has to be a file within the Mysql space.

–
Ratheesh

Zonker, the doublewrite buffer works as kind of a two-phase commit. It allows recovering a failed write. The write either succeeded or not, and if it didn’t, on recovery it will be replayed from either the doublewrite buffer or the redo logs. It doesn’t work the way you think, and the scenario you listed can’t happen.

Zonker,

As I understand it there are two failure modes:

1. A write to the doublewrite buffer fails. In this case the data in the table hasn’t changed, so the redo log can be applied to update the data.

2. If the write to the doublewrite buffer succeeds and the write to the table fails, the data can be read from the doublewrite buffer to update the table.

If we were writing directly to the table, the failed write would’ve left the table in an inconsistent state where the redo log couldn’t be applied and there’s been no successful write to a buffer that we can read the data back from. The doublewrite buffer means that we always have a way of bringing the data up to date after a failed write, regardless of failure mode.

I haven’t looked at the internals, so this is an educated guess based on the post and comments. I’d be grateful if someone more knowledgeable could confirm.

I guess I’m not buying it.

I write to the doublewrite buffer: AAAAAAAAAA

then flush to the table: AAAAAAAAAA

fine, all’s fine. But now I write again to the doublewrite buffer and the write fails halfway:

BBBBB…..

We “recover” and find that the doublewrite buffer is BBBBB….., with a bad checksum, or however we determine that it isn’t complete, so we “just discard it” per the mysql docs.

Really? This is good?

Now I’ve got AAAAAAAAAA in the table where I expect to have BBBBBBBBBB. AAAAAAAAAA maybe “consistent”, but it isn’t “correct”, and I stil have a problem. If that data is correlated to other data, I still have to recover from a backup to get back to true consistency, i.e. my data all being consistent with each other.

So I don’t really see how this benefits me.

To take the example further, if doublewrite is good, why wouldn’t triplewrite or fourplewrite be even better? Answer: It isn’t, for exactly the reasons I’ve described above. You still have bad data. “Consistent”, perhaps, but not correct.

And now the obligatory “Or am I simply not understanding this?”

markgoat.

Let me try to answer your question.

The problem comes from fact that when we issue pwrite , and there is crash during
this operation, then there is no way to check state of operation.
It may happen that for 16K operation we wrote only 4K or 8K.

So we may end up with situation when half of page contains new information and another half – old information. InnoDB of course will detect corruption using checksum, but it won’t help much, as page is broken.

So solution to this is to have 2 copies of page. if we crash during writing of one of copies – we always have another consistent copy, which we can work with.

The redo logs in InnoDB don’t have complete page images. This is different from some other database servers.

Sorry, I am still not understand why we need this.
Each entry in the log should be redo when doing recovery? I think even the page is “partial written”, it doesn’t matter, redo log know the after image. So regardless of the page itself, redo will do every change again. If part of the page are not wrtten due to a crash, redo will do the write? In other words, the “consistent page” is saved in the redo log. Unless you mean “partial write” will modify someting not saved in the redo log??
I mean, EVERY change are in the redo log entries. Partial write , in my mind, is that it failed to make some changes to the disk. But when we apply redo log, we will redo the planned actions.
For example, we have 4 blocks each page, we update 2 rows in two of the blocks, and then we have two entries in the redo log. then there is a commit log. As a result of Partial write, only block 1 was updated, block 2,3,4 did not. But I didn’t see any issue in this case, because redo will reimage block 1 and block 3 correctly? We don’t care about if the disk is consistent or not, this is what redo in theory to handle, isn’t it?
So I think I still didn’t understand innoDB , why it need “double write”? I am new to innoDB, not knowing any internal details, I just think this from a textbook point of view ^_^

thank you, it is so long time you post this great article, don’t know if you can still answer me. I am thinking this for days, but can’t understand yet.