MySQL Bugs: #89158: "inplace" table rebuild creates much larger ibd file than "copy" rebuild!

Bug #89158	"inplace" table rebuild creates much larger ibd file than "copy" rebuild!
Submitted:	9 Jan 2018 12:12
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Patch pending	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.20, 8.0.46, 9.7.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See last part of testcase here,  it seems unexpected to me.
There is no concurrent workload on the server.

mysql> -- now we rebuild the table in an attempt to defragment.
mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
Query OK, 8789890 rows affected (2 min 1.74 sec)
Records: 8789890  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8739801
 Avg_row_length: 238
    Data_length: 2084569088
Max_data_length: 0
   Index_length: 0
      Data_free: 6291456
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:22:40
    Update_time: 2018-01-09 13:40:15
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8789890 |
+----------+
1 row in set (1.78 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               2084569088 |  2084569088 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> -- now we rebuild the table in an attempt to defragment.
mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (3 min 14.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8682637
 Avg_row_length: 274
    Data_length: 2382348288 <----------
Max_data_length: 0
   Index_length: 0
      Data_free: 2097152
 Auto_increment: 9830256
    Create_time: 2018-01-09 13:41:19
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8789890 |
+----------+
1 row in set (1.81 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | data_length | index_length |
+--------------------------+-------------+--------------+
|               2382348288 |  2382348288 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

How is it the table grew in size by ~300MB after the inplace alter?

Here is my testcase output in full on 5.7.20:
https://pastebin.com/raw/TA8Bzvws

How to repeat:
#A pseudo-random testcase to show the phenomenon.

set sql_mode='';
drop table if exists t;
create table `t` (
 `a` varchar(100) not null default '',
 `b` varchar(50) not null default '',
 `c` varchar(5000) default null,
 `d` timestamp null default current_timestamp on update current_timestamp,
 `e` timestamp null default current_timestamp,
 `f` bigint(25) not null auto_increment,
 primary key (`f`)
) engine=innodb default charset=latin1 ;

-- we insert some random data
insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
insert into t(a,b,c) select concat(rand(),uuid()),concat(rand(),uuid()),concat(rand(),uuid()) from t a,t b,t c,t d,t e,t f,t g,t h,t i,t j;
analyze table t;
show table status like 't'\G
select count(*) from t;
select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';

-- now we create some fragmentation by updating and deleting rows.
update t set c = repeat('a',floor(100*rand())) where b like '0.1%';
update t set c = repeat('b',floor(500*rand())) where b like '0.5%';
delete from t where b like '0.7%';
analyze table t;
show table status like 't'\G
select count(*) from t;
select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';

-- now we rebuild the table in an attempt to defragment (I).
alter table t engine=innodb, algorithm=inplace, lock=none;
analyze table t;
show table status like 't'\G
select count(*) from t;
select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';

-- now we rebuild the table in an attempt to defragment (II).
alter table t engine=innodb, algorithm=copy, lock=shared;
analyze table t;
show table status like 't'\G
select count(*) from t;
select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';

Suggested fix:
o) find out why the table is larger after inplace rebuild?
o) document it or point to source code/documentation that explains 
   if it is okay.

I've tried this on 8.0.46 today, and results are as follows:

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.03 sec)

mysql> create table `t` (
    ->  `a` varchar(100) not null default '',
    ->  `b` varchar(50) not null default '',
    ->  `c` varchar(5000) default null,
    ->  `d` timestamp null default current_timestamp on update current_timestamp,
    ->  `e` timestamp null default current_timestamp,
    ->  `f` bigint(25) not null auto_increment,
    ->  primary key (`f`)
    -> ) engine=innodb default charset=latin1 ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql> insert into t(a,b,c) values (uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid()),(uuid(),uuid(),uuid());
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t(a,b,c) select concat(rand(),uuid()),concat(rand(),uuid()),concat(rand(),uuid()) from t a,t b,t c,t d,t e,t f,t g,t h,t i,t j;
Query OK, 9765625 rows affected, 65535 warnings (1 min 32.52 sec)
Records: 9765625  Duplicates: 0  Warnings: 9764880

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9639916
 Avg_row_length: 215
    Data_length: 2081423360
Max_data_length: 0
   Index_length: 0
      Data_free: 7340032
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:17:32
    Update_time: 2026-08-01 13:19:12
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  9765630 |
+----------+
1 row in set (0.14 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2081423360 |  2081423360 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> update t set c = repeat('a',floor(100*rand())) where b like '0.1%';
Query OK, 975856 rows affected (11.43 sec)
Rows matched: 975856  Changed: 975856  Warnings: 0

mysql> update t set c = repeat('b',floor(500*rand())) where b like '0.5%';
Query OK, 976358 rows affected (1 min 2.83 sec)
Rows matched: 976358  Changed: 976358  Warnings: 0

mysql> delete from t where b like '0.7%';
Query OK, 975440 rows affected (7.95 sec)

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10685556
 Avg_row_length: 378
    Data_length: 4044341248
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:17:32
    Update_time: 2026-08-01 13:21:09
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790190 |
+----------+
1 row in set (0.20 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               4044341248 |  4044341248 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (1 min 33.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8683184
 Avg_row_length: 274
    Data_length: 2383396864
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:21:24
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790190 |
+----------+
1 row in set (4.39 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2383396864 |  2383396864 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
Query OK, 8790190 rows affected (35.91 sec)
Records: 8790190  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8778394
 Avg_row_length: 237
    Data_length: 2084569088
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:23:22
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790190 |
+----------+
1 row in set (0.14 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2084569088 |  2084569088 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 8.0.46-commercial |
+-------------------+
1 row in set (0.00 sec)

Following on from last note.  If I then do an INPLACE rebuild, the size increases again.

mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (30.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8435456
 Avg_row_length: 282
    Data_length: 2383396864
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:27:30
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790190 |
+----------+
1 row in set (3.54 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2383396864 |  2383396864 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

same behaviour even on 9.7.2.  
...

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               4047470592 |  4047470592 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql> -- now we rebuild the table in an attempt to defragment (I).
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (1 min 23.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8657776
 Avg_row_length: 275
    Data_length: 2383396864
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:33:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790282 |
+----------+
1 row in set (0.35 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2383396864 |  2383396864 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql> -- now we rebuild the table in an attempt to defragment (II).
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t engine=innodb, algorithm=copy, lock=shared;
Query OK, 8790282 rows affected (46.42 sec)
Records: 8790282  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8594120
 Avg_row_length: 242
    Data_length: 2084569088
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:35:14
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790282 |
+----------+
1 row in set (0.14 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2084569088 |  2084569088 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql> -- now we rebuild the table in an attempt to defragment (I).
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t engine=innodb, algorithm=inplace, lock=none;
Query OK, 0 rows affected (27.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8854688
 Avg_row_length: 269
    Data_length: 2383396864
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 9830256
    Create_time: 2026-08-01 13:36:01
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  8790282 |
+----------+
1 row in set (0.62 sec)

mysql> select data_length+index_length,data_length,index_length from information_schema.tables where table_schema='test' and table_name='t';
+--------------------------+-------------+--------------+
| data_length+index_length | DATA_LENGTH | INDEX_LENGTH |
+--------------------------+-------------+--------------+
|               2383396864 |  2383396864 |            0 |
+--------------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
+------------------+
| version()        |
+------------------+
| 9.7.2-commercial |
+------------------+
1 row in set (0.00 sec)

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer:
 
For affected users on existing builds, a possible workaround is to set innodb_segment_reserve_factor=0.03. This reduces the amount of segment reserve space and should significantly reduce the .ibd file growth seen with INPLACE table rebuilds.