Bug #74512 excessive split/merge for unique sec index
Submitted: 22 Oct 2014 17:21 Modified: 22 Oct 2014 17:22
Reporter: Inaam Rana (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 2014 17:21] Inaam Rana 
Description:
    Sec index are never upadted in place. When a sec index is updated
    (remember that PK is stored as part of sec index) it results in delete
    marking the existing record and inserting a new record. If the sec
    index is unique and the update is to the non-key columns i.e.: only
    the PK columns are updated then the new record gets inserted in the
    same page where the old version was delete marked.
    If the page where this operation happens is fully packed it will
    cause a split. And subsequently when purge proceses the delete mark
    record it will cause a merge (likely with a reorg) and we'll end up
    having a fully packed page again.
    This can result in kind of thrashing where each update to secondary
    index results in a split plus (subseqent) reorg and merge.

How to repeat:
see btr_compress() and btr_can_merge_with_page().

Suggested fix:
Have a new config parameter to control the decision to merge the pages. In case of unique secondary index leaf pages if the combined size after merge is going to be greater than some threshold percent of full page size then the merge will not be performed.

I have a patch for this which I'll upload.
[22 Oct 2014 17:22] Inaam Rana 
Would just like to add that we noticed this behaviour in production because of more performant purge as we moved to 5.6. Wages of writing performing code!!! :)
[22 Oct 2014 17:23] Inaam Rana 
unique_sec_index_merge_threshold

Attachment: 428_v1.patch (application/octet-stream, text), 17.26 KiB.