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)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.20
• OS: Any
• CPU Architecture: Any

[9 Jan 2018 12:12] Shane Bester

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.