Description:
innodb_fill_factor appears to have no effect on InnoDB tables using ROW_FORMAT=COMPRESSED, leading to fully packed index pages regardless of the configured fill factor. This prevents users from reserving free space to mitigate index page splits, which can negatively impact write performance.
How to repeat:
1. Create 10 tables with different values of innodb_fill_factor (100 -> 90 -> 80 ...10) using row_format=dynamic.
```
for num in 100 90 80 70 60 50 40 30 20 10;
do
echo "sbtest_$num"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SET GLOBAL innodb_fill_factor=${num};"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SELECT @@innodb_fill_factor;"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"CREATE DATABASE IF NOT EXISTS sbtest_${num};"
sysbench oltp_common --db-driver=mysql \
--mysql-host=${MYSQL_HOST} \
--mysql-user=sbtest --mysql-password='***' \
--mysql-db=sbtest_${num} --tables=1 --table-size=2000000 \
--threads=1 prepare
done
```
Observe the .ibd file size for each table.
```
ls -lh sbtest_*/sbtest1.ibd | sort
-rw-r-----. 1 mysql mysql 472M Jun 9 13:26 sbtest_100/sbtest1.ibd
-rw-r-----. 1 mysql mysql 472M Jun 9 13:27 sbtest_90/sbtest1.ibd
-rw-r-----. 1 mysql mysql 476M Jun 9 13:28 sbtest_80/sbtest1.ibd
-rw-r-----. 1 mysql mysql 484M Jun 9 13:29 sbtest_70/sbtest1.ibd
-rw-r-----. 1 mysql mysql 492M Jun 9 13:30 sbtest_60/sbtest1.ibd
-rw-r-----. 1 mysql mysql 500M Jun 9 13:31 sbtest_50/sbtest1.ibd
-rw-r-----. 1 mysql mysql 516M Jun 9 13:32 sbtest_40/sbtest1.ibd
-rw-r-----. 1 mysql mysql 544M Jun 9 13:32 sbtest_30/sbtest1.ibd
-rw-r-----. 1 mysql mysql 596M Jun 9 13:30 sbtest_20/sbtest1.ibd
-rw-r-----. 1 mysql mysql 768M Jun 9 13:31 sbtest_10/sbtest1.ibd
```
As expected, file size increases with lower fill factor, indicating extra free space reserved in index pages.
2. Create 10 tables with different values of innodb_fill_factor (100 -> 90 -> 80 ...10) using row_format=compressed.
```
for num in 100 90 80 70 60 50 40 30 20 10;
do
echo "sbtest_cmp_$num"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SET GLOBAL innodb_fill_factor=${num};"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SELECT @@innodb_fill_factor;"
mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"CREATE DATABASE IF NOT EXISTS sbtest_cmp_${num};"
sysbench oltp_common --db-driver=mysql \
--mysql-host=${MYSQL_HOST} \
--mysql-user=sbtest --mysql-password='***' \
--mysql-db=sbtest_cmp_${num} --tables=1 --table-size=2000000 \
--create_table_options="ROW_FORMAT=COMPRESSED" \
--threads=1 prepare
done
```
Observe the .ibd file size for each table.
```
ls -lh sbtest_cmp_*/sbtest1.ibd | sort
-rw-r-----. 1 mysql mysql 260M Jun 9 13:38 sbtest_cmp_100/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:40 sbtest_cmp_90/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:42 sbtest_cmp_80/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:44 sbtest_cmp_70/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:46 sbtest_cmp_60/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:47 sbtest_cmp_50/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:49 sbtest_cmp_40/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:51 sbtest_cmp_30/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:53 sbtest_cmp_20/sbtest1.ibd
-rw-r-----. 1 mysql mysql 260M Jun 9 13:54 sbtest_cmp_10/sbtest1.ibd
```
Regardless of the innodb_fill_factor value, the file size remains identical, suggesting the setting is ignored.
Issue:
1. innodb_fill_factor has no effect on InnoDB tables using ROW_FORMAT=COMPRESSED.
2. This prevents performance tuning by reserving free space in index pages to mitigate index page splits.
3. Documentation does not clearly mention this limitation.
Suggested fix:
1. Please consider evaluating the feasibility of supporting innodb_fill_factor for InnoDB tables using ROW_FORMAT=COMPRESSED. This can help to reduce index page splits by reserving space within compressed pages, ultimately improving write and update performance in certain workloads.
2. If it is not technically feasible to apply innodb_fill_factor to compressed tables, it would be helpful to make this limitation explicitly clear in the official MySQL documentation and include workarounds (if any).
e.g.
- innodb_fill_factor has no effect on tables using ROW_FORMAT=COMPRESSED.
Description: innodb_fill_factor appears to have no effect on InnoDB tables using ROW_FORMAT=COMPRESSED, leading to fully packed index pages regardless of the configured fill factor. This prevents users from reserving free space to mitigate index page splits, which can negatively impact write performance. How to repeat: 1. Create 10 tables with different values of innodb_fill_factor (100 -> 90 -> 80 ...10) using row_format=dynamic. ``` for num in 100 90 80 70 60 50 40 30 20 10; do echo "sbtest_$num" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SET GLOBAL innodb_fill_factor=${num};" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SELECT @@innodb_fill_factor;" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"CREATE DATABASE IF NOT EXISTS sbtest_${num};" sysbench oltp_common --db-driver=mysql \ --mysql-host=${MYSQL_HOST} \ --mysql-user=sbtest --mysql-password='***' \ --mysql-db=sbtest_${num} --tables=1 --table-size=2000000 \ --threads=1 prepare done ``` Observe the .ibd file size for each table. ``` ls -lh sbtest_*/sbtest1.ibd | sort -rw-r-----. 1 mysql mysql 472M Jun 9 13:26 sbtest_100/sbtest1.ibd -rw-r-----. 1 mysql mysql 472M Jun 9 13:27 sbtest_90/sbtest1.ibd -rw-r-----. 1 mysql mysql 476M Jun 9 13:28 sbtest_80/sbtest1.ibd -rw-r-----. 1 mysql mysql 484M Jun 9 13:29 sbtest_70/sbtest1.ibd -rw-r-----. 1 mysql mysql 492M Jun 9 13:30 sbtest_60/sbtest1.ibd -rw-r-----. 1 mysql mysql 500M Jun 9 13:31 sbtest_50/sbtest1.ibd -rw-r-----. 1 mysql mysql 516M Jun 9 13:32 sbtest_40/sbtest1.ibd -rw-r-----. 1 mysql mysql 544M Jun 9 13:32 sbtest_30/sbtest1.ibd -rw-r-----. 1 mysql mysql 596M Jun 9 13:30 sbtest_20/sbtest1.ibd -rw-r-----. 1 mysql mysql 768M Jun 9 13:31 sbtest_10/sbtest1.ibd ``` As expected, file size increases with lower fill factor, indicating extra free space reserved in index pages. 2. Create 10 tables with different values of innodb_fill_factor (100 -> 90 -> 80 ...10) using row_format=compressed. ``` for num in 100 90 80 70 60 50 40 30 20 10; do echo "sbtest_cmp_$num" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SET GLOBAL innodb_fill_factor=${num};" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"SELECT @@innodb_fill_factor;" mysql -vvv --show-warnings --host=${MYSQL_HOST} -e"CREATE DATABASE IF NOT EXISTS sbtest_cmp_${num};" sysbench oltp_common --db-driver=mysql \ --mysql-host=${MYSQL_HOST} \ --mysql-user=sbtest --mysql-password='***' \ --mysql-db=sbtest_cmp_${num} --tables=1 --table-size=2000000 \ --create_table_options="ROW_FORMAT=COMPRESSED" \ --threads=1 prepare done ``` Observe the .ibd file size for each table. ``` ls -lh sbtest_cmp_*/sbtest1.ibd | sort -rw-r-----. 1 mysql mysql 260M Jun 9 13:38 sbtest_cmp_100/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:40 sbtest_cmp_90/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:42 sbtest_cmp_80/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:44 sbtest_cmp_70/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:46 sbtest_cmp_60/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:47 sbtest_cmp_50/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:49 sbtest_cmp_40/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:51 sbtest_cmp_30/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:53 sbtest_cmp_20/sbtest1.ibd -rw-r-----. 1 mysql mysql 260M Jun 9 13:54 sbtest_cmp_10/sbtest1.ibd ``` Regardless of the innodb_fill_factor value, the file size remains identical, suggesting the setting is ignored. Issue: 1. innodb_fill_factor has no effect on InnoDB tables using ROW_FORMAT=COMPRESSED. 2. This prevents performance tuning by reserving free space in index pages to mitigate index page splits. 3. Documentation does not clearly mention this limitation. Suggested fix: 1. Please consider evaluating the feasibility of supporting innodb_fill_factor for InnoDB tables using ROW_FORMAT=COMPRESSED. This can help to reduce index page splits by reserving space within compressed pages, ultimately improving write and update performance in certain workloads. 2. If it is not technically feasible to apply innodb_fill_factor to compressed tables, it would be helpful to make this limitation explicitly clear in the official MySQL documentation and include workarounds (if any). e.g. - innodb_fill_factor has no effect on tables using ROW_FORMAT=COMPRESSED.