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.