Disk Divergence across Master and Slaves

So, InnoDB stores rows in b-trees. Pages in a b-tree can be anywhere between half filled (50%) and filled (100%). Let’s assume there is a long running read query running on the replica forcing a consistent read view to stay opened. A row is deleted from an innodb page and another one is later inserted to the same page. The page in question is almost full.

Since there is no long running query on the master, the old rows is removed by the purge thread almost immediately and then the new row is added. The new row fit in the page because the old one has been removed.

On the replica, there is a long running transaction (a select) which prevent the removal of the deleted row, the row is just flagged as deleted. Then, the new row is added. Since there is not enough space in the page, the page has to be split in 2 before the new row is finally added. Eventually the long select finishes and the purge thread removes the deleted row. The data is still the same but you now have 2 pages on the replica and 1 on the master.

As I said, you cannot assume the sizes are the same on both the master and the replica, at least within a factor of 2. I hope my explanations are clear, it is a subtle subject involving dynamic systems.

1 Like