Re: Growth of .ibd and .ibdata files

[Tiago 504]

Hello,

Thanks for answering.

The question is, there are two distinct events when innodb access the related files: commits on the redo log files, and checkpoints.
By reading many mysql documentation and some blogs of innodb (many of them provided at the beginning of this forum), as well as by reading some parts of the source code and seeing some practical examples, I found that a checkpoint ends when there is a write access in the ib_logfile0 file at the offset 512 or 1536 (which is where is stored the information about the place in the log where the latest MLOG_CHEKCPOINT record is).

I also learned that, in case of a crash, the log records subsequent to the latest MLOG_CHEKCPOINT record (found on the offset 512 or 1536 of ib_logfile0) are executed to recover the state of the database.

The problem is that, imagine that i save the database state after the finish of a checkpoint, i.e., after a write access to the offset 512 or 1536 of ib_logfile0, and after that I only save the commits to the redo log files (i.e., i don't save any .ibdata and .idb file I/O). After a while I introduce a disk crash and then try to perform recover.

This kind of test only works sometimes. When it does not work I found an error in the log error of mysql saying that, while doing the recovery, it tries to access a page (of a .ibd file) that does not exist. I suspect that this is because the growth of the file that I mentioned in the first post isn't reflected on the redo log files.

Note that each time I run recover with the latest checkpoint it always works.

I just would like to understand why it does not work in that specific cases since the redo logs supposedly have information to perform all the updates performed since the last checkpoint.

PS: My test is not so small since i'm catching all the disk I/O performed by mysql when loaded with a TPC-C workload.

Thanks in advance,
Best regards,
Tiago Oliveira