WL#6747: InnoDB: make fill factor settable
Status: Complete
Currently the InnoDB index fill factor threshold is hard coded (BTR_CUR_PAGE_COMPRESS_LIMIT). It should be settable per index. There are 2 fill factor thresholds for ordinary logically. (1) merge_threshold : If the data amount in the page becomes below the threshold when deleting row or updating to shorter row, attempts to merge with neighbor page. (2) split_threshold : If the data amount in the page becomes above the threshold when inserting row or updating to longer row, split the page into 2 pages. Changing (1) is useful. Currently it is fixed to 50%. Even if the page was merged, the page become near 100% filled and might cause split page soon. If the threshold is set lower, the merged page farther from 100% and might not cause split page soon. Reducing opportunity for page merge/split benefit performance. But changing (2) is not useful on InnoDB. Because InnoDB table is always the clustered index and inserted record is not inserted to the other pages. So, even if reduces the current value (100% actually), it just causes "earlier splitting pages". It seems no merit. So, in this wl#, implementing the 'merge_threshold' for each index.
Functional requirements: F-1: The user shall be able to set a merge_threshold value to each index, by a COMMENT clause of the DDL statement (table or index). - For all the indexes in a table, will be having 50% as a default value, if not specified value for the table/index. - For compatibility reasons, the default value 50% is set. - for each index, the priority is [Index Level fillfactor] > [Table Level fillfactor] > [default (50%)] - for the hidden clustered index ("GEN_CLUST_INDEX"; no primary key/unique index in the table) cannot be set [Index Level fillfactor]. ([Table Level fillfactor] is used if specified. otherwise default 50% for the case) e.g.) CREATE TABLE `a` ( `a1` int(11) NOT NULL, `a2` int(11) DEFAULT NULL, `a3` int(11) DEFAULT NULL, `a4` int(11) DEFAULT NULL, KEY `b1` (`a2`), KEY `b2` (`a3`) COMMENT 'MERGE_THRESHOLD=40', KEY `a4` (`a4`) COMMENT 'MERGE_THRESHOLD=35', KEY `a2` (`a2`,`a4`) COMMENT 'MERGE_THRESHOLD=35', KEY `a2_2` (`a2`,`a3`) COMMENT 'MERGE_THRESHOLD=35' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='MERGE_THRESHOLD=45' ---> table default value 45 for `b1` each value specified for the other indexes. F-2: User shall be able to confirm the current value of merge_threshold by INFORMATION_SCHEMA.INNODB_SYS_INDEXES e.g.) +----------+-----------------------+----------+------+----------+---------+-------+-----------------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------+-----------------------+----------+------+----------+---------+-------+-----------------+ .... | 59 | GEN_CLUST_INDEX | 34 | 1 | 0 | 3 | 31 | 45 | | 60 | b1 | 34 | 0 | 1 | 4 | 31 | 45 | | 61 | b2 | 34 | 0 | 1 | 5 | 31 | 40 | | 62 | a4 | 34 | 0 | 1 | 6 | 31 | 35 | | 63 | a2 | 34 | 0 | 2 | 7 | 31 | 35 | | 64 | a2_2 | 34 | 0 | 2 | 8 | 31 | 35 | +----------+-----------------------+----------+------+----------+---------+-------+-----------------+
Changes to the interface specification: I-1: No new files I-2: New syntax: I-2-1: "MERGE_THRESHOLD=" specifying in comment clause of DDL is parsed e.g.) CREATE TABLE `a` ( `a1` int(11) NOT NULL, `a2` int(11) DEFAULT NULL, `a3` int(11) DEFAULT NULL, `a4` int(11) DEFAULT NULL, KEY `b1` (`a2`), KEY `b2` (`a3`) COMMENT 'MERGE_THRESHOLD=40', KEY `a4` (`a4`) COMMENT 'MERGE_THRESHOLD=35', KEY `a2` (`a2`,`a4`) COMMENT 'MERGE_THRESHOLD=35', KEY `a2_2` (`a2`,`a3`) COMMENT 'MERGE_THRESHOLD=35' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='MERGE_THRESHOLD=45' I-2-2: now column MERGE_THRESHOLD to INFORMATION_SCHEMA.INNODB_SYS_INDEXES e.g.) +----------+-----------------------+----------+------+----------+---------+-------+-----------------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------+-----------------------+----------+------+----------+---------+-------+-----------------+ .... | 59 | GEN_CLUST_INDEX | 34 | 1 | 0 | 3 | 31 | 45 | | 60 | b1 | 34 | 0 | 1 | 4 | 31 | 45 | | 61 | b2 | 34 | 0 | 1 | 5 | 31 | 40 | | 62 | a4 | 34 | 0 | 1 | 6 | 31 | 35 | | 63 | a2 | 34 | 0 | 2 | 7 | 31 | 35 | | 64 | a2_2 | 34 | 0 | 2 | 8 | 31 | 35 | +----------+-----------------------+----------+------+----------+---------+-------+-----------------+ (* because COMMENT='MERGE_THRESHOLD=45' is set to the table, GEN_CLUST_INDEX and b1(no comment) is MERGE_THRESHOLD=45) I-3: No new commands I-4: No new tools. I-5: No impact on existing functionality.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.