WL#6747: InnoDB: make fill factor settable

Status: Complete   —   Priority: Medium

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.