Marko Mäkelä added a comment - 2022-01-18 07:33 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.

Marko Mäkelä added a comment - 2023-01-17 07:21 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: 10.7 1e04cafcba88e1801e828a5bbab7fe9fdd7ca61c 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).

Marko Mäkelä added a comment - 2023-12-20 08:36 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.

Matthias Leich added a comment - 2024-01-10 14:29 origin/10.6- MDEV-26740 2005dedbcab3cf0550e5870ea80ea680875900b9 2024-01-05T13:11:38+05:30 behaved well in RQG testing. No new problems.