Description:
btr_cur_optimistic_insert() can return DB_FAIL for compressed pages where the
record could fit after reorganizing the page.
This affect users of compressed tables, because the .ibd file sizes
would become bigger than necessary. There are some users complaining about
too many page splits, and failure to merge pages.
This patch disables a condition for splitting clustered index leaf pages, on
compressed tables. The page split is only done with the intention to reserve
space for future updates, so that page splits could be avoided in the future.
What we should really have is a per-index fill-factor parameter that can be
set by the user.
How to repeat:
Read the code.
Suggested fix:
On compressed pages, ensure that page reorganize could not have helped,
before returning DB_FAIL.
This could reduce the table sizes somewhat:
=== modified file
'mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result'
---
mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result revid:marko.make
la@oracle.com-20130225084744-bqq0ifx7dtxz1w4l
+++ mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result 2013-02-26
10:52:43 +0000
@@ -1633,7 +1633,7 @@ AND compress_ops BETWEEN @inl_val AND 10
AND table_name='tab5' AND database_name='test'
AND index_name like 'idx%' ;
compress_stat 1
-The size of the tab5.ibd file: 344064
+The size of the tab5.ibd file: 327680
# fetch the compressed page and check the stats
===============
Fetch Records
The only essential (and compression-related) change in the patch is to
disable extra splitting of compressed pages:
/* If there have been many consecutive inserts to the
clustered index leaf page of an uncompressed table, check if
we have to split the page to reserve enough free space for
future updates of records. */
if (leaf && !zip_size && dict_index_is_clust(index)
&& page_get_n_recs(page) >= 2
&& dict_index_get_space_reserve() + rec_size > max_size
&& (btr_page_get_split_rec_to_right(cursor, &dummy)
|| btr_page_get_split_rec_to_left(cursor, &dummy))) {
goto fail;
}
Perhaps we should consider obeying some per-table fill-factor attribute here
even for uncompressed tables.
© 2025, Oracle Corporation and/or its affiliates
Description: btr_cur_optimistic_insert() can return DB_FAIL for compressed pages where the record could fit after reorganizing the page. This affect users of compressed tables, because the .ibd file sizes would become bigger than necessary. There are some users complaining about too many page splits, and failure to merge pages. This patch disables a condition for splitting clustered index leaf pages, on compressed tables. The page split is only done with the intention to reserve space for future updates, so that page splits could be avoided in the future. What we should really have is a per-index fill-factor parameter that can be set by the user. How to repeat: Read the code. Suggested fix: On compressed pages, ensure that page reorganize could not have helped, before returning DB_FAIL. This could reduce the table sizes somewhat: === modified file 'mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result' --- mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result revid:marko.make la@oracle.com-20130225084744-bqq0ifx7dtxz1w4l +++ mysql-test/suite/innodb/r/innodb_wl6347_comp_indx_stat.result 2013-02-26 10:52:43 +0000 @@ -1633,7 +1633,7 @@ AND compress_ops BETWEEN @inl_val AND 10 AND table_name='tab5' AND database_name='test' AND index_name like 'idx%' ; compress_stat 1 -The size of the tab5.ibd file: 344064 +The size of the tab5.ibd file: 327680 # fetch the compressed page and check the stats =============== Fetch Records The only essential (and compression-related) change in the patch is to disable extra splitting of compressed pages: /* If there have been many consecutive inserts to the clustered index leaf page of an uncompressed table, check if we have to split the page to reserve enough free space for future updates of records. */ if (leaf && !zip_size && dict_index_is_clust(index) && page_get_n_recs(page) >= 2 && dict_index_get_space_reserve() + rec_size > max_size && (btr_page_get_split_rec_to_right(cursor, &dummy) || btr_page_get_split_rec_to_left(cursor, &dummy))) { goto fail; } Perhaps we should consider obeying some per-table fill-factor attribute here even for uncompressed tables.