0

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?

|improve this question
  • if you are read this question, please comment here if you need further information from my side. – Dinesh Kumar Oct 25 '18 at 18:48
1

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.

|improve this answer
  • I got "Merge" attempts during inserts also. I will re-run the case and let it know here. – Dinesh Kumar Oct 27 '18 at 16:55
  • I also came across "Merges" during inserts in another case (inserted in random order). But I am guessing that 'Merges' happens during 'Random' inserts. Because, in the same case , during ordered inserts no 'merge' happens – Dinesh Kumar Oct 27 '18 at 17:01
  • To answer you question 'How big are the rows?' , only two column, one pk and one text.. You question also rise a discussion that 'splits' also relates to row size... – Dinesh Kumar Oct 27 '18 at 17:03
  • I added some more. Oh, is there a FULLTEXT index? – Rick James Oct 27 '18 at 22:45
  • Nope there are no fulltext indexes.. – Dinesh Kumar Oct 28 '18 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.