Question :
I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb.
For sequencial Inserts I got:
index_page_splits: 3199 index_page_merge_attempts: 0 index_page_merge_successful: 0 index_page_reorg_attempts: 0 index_page_reorg_successful: 0
For random Inserts I got:
index_page_splits: 4423 index_page_merge_attempts: 1 index_page_merge_successful: 1 index_page_reorg_attempts: 1 index_page_reorg_successful: 1
I am having two questions:
1.Why there are increments in index_page_merge_attempts,index_page_merge_successful,
index_page_reorg_attempts,index_page_reorg_successful variables?
2.What is the difference between attempts and successfull variables?
Answer :
How big are the rows? I’m surprised that the “splits” numbers are similar.
“Merge” probably refers to what may happen after DELETEing a row or UPDATEing a row such that the row is smaller. The block contains less. If that shrunken block is next to another block that is also rather shrunken, then it behooves InnoDB to “merge” the two blocks together. This won’t happen if all you are doing is INSERTs.
“Attempts” vs “Successful” — Suppose some row is locked. Or something else is going on that would be disrupted by the “merge”. It would be better to abort the merge, than to mess up the ‘real’ operation.
What is the typical size of the TEXT column? It could be stored in the row, or it could be stored elsewhere. The “merges” could be happening in the off-record storage.