I believe that the size difference must be related to the B-tree index page fill factor, or to the way how pages are allocated (MDEV-13013, MDEV-20094).

I remember that there was some discussion of page fill factor during the implementation of the MySQL 5.7 bulk insert. I cannot remember if it was about intentional or accidental changes to the fill factor. Based on my findings in MDEV-19747, I would expect that the performance impact was not thoroughly tested.

The page fill factor can affect the performance of subsequent operations, for the better or worse.

• If there will be no changes to the index after the index was created or the table was rebuilt, a lower fill factor will unnecessarily increase the storage footprint and increase the I/O cost.
• If there will be certain types of changes, a lower fill factor may significantly reduce, or maybe even completely avoid page splits.

It turns out that MDEV-24621 did not enable the bulk load mechanism for ALTER TABLE…ALGORITHM=COPY:

--source include/have_innodb.inc

--source include/have_sequence.inc

let $datadir=`SELECT @@datadir`;

create table t1(f1 int not null primary key, b char(255) CHARACTER SET utf8)engine=innodb;

INSERT INTO t1(f1) SELECT * FROM seq_1_to_100000;

--exec wc -c $datadir/test/t1.ibd

alter table t1 force, algorithm=copy;

--exec wc -c $datadir/test/t1.ibd

alter table t1 force, algorithm=inplace;

--exec wc -c $datadir/test/t1.ibd

drop table t1;

This will report the file size 10,485,760 bytes (10 MiB) for the first two steps, and 9,437,184 bytes (9 MiB) for the last step:

create table t1(f1 int not null primary key, b char(255) CHARACTER SET utf8)engine=innodb;

INSERT INTO t1(f1) SELECT * FROM seq_1_to_100000;

10485760 /dev/shm/10.7/mysql-test/var/mysqld.1/data//test/t1.ibd

alter table t1 force, algorithm=copy;

10485760 /dev/shm/10.7/mysql-test/var/mysqld.1/data//test/t1.ibd

alter table t1 force, algorithm=inplace;

9437184 /dev/shm/10.7/mysql-test/var/mysqld.1/data//test/t1.ibd

drop table t1;

If we insert 1,000,000 rows instead of 100,000, then the reported file sizes will be 32 MiB and 36 MiB (larger with the bulk load enabled).

Thank you, this is a very simple fix. I approved the 10.6 version, but I think that this should be applied to 10.4 already. As far as I understand, this regression was introduced in 10.2 by importing the MySQL 5.7 bulk index creation changes.

origin/10.6-MDEV-26740 2005dedbcab3cf0550e5870ea80ea680875900b9 2024-01-05T13:11:38+05:30
behaved well in RQG testing. No new problems.