Description:
Affects at least 5.5, 5.6, 5.7, 8.0
After deleting most of the rows, the Data_free did not increase, and the Data_length did not decrease. Instead, the Avg_row_length got 10x larger which is wrong.
So how can we tell if there is slack space here that OPTIMIZE TABLE would free up to the OS??
See Before:
mysql> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9643609
Avg_row_length: 207
Data_length: 2002780160
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: 9830256
Create_time: 2018-01-11 09:48:25
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
After:
mysql> -- now we create some holes in table by deleting 90% of the rows.
mysql> delete from t where b not like '0.1%';
Query OK, 8790601 rows affected (3 min 41.98 sec)
-- wait 10 minutes for purge/page cleaner...
mysql> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 971592
Avg_row_length: 2060 <------- ???
Data_length: 2001862656 <------- ???
Max_data_length: 0
Index_length: 0
Data_free: 5242880 <------- ???
Auto_increment: 9830256
Create_time: 2018-01-11 09:48:25
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Full outputs from 5.6, 5.7:
https://pastebin.com/raw/j4sW3APK
This is a known issue since documentation hints at it. But it doesn't make it any easier to guess how much space savings will come after table rebuild.
How to repeat:
select version();
set sql_mode='';
drop table if exists t;
create table t (
f int unsigned not null auto_increment,
a varchar(200),
b varchar(200),
c varchar(200),
primary key (f)
) engine=innodb row_format=compact 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_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
-- now we create some holes in table by deleting 90% of the rows.
delete from t where b not like '0.1%';
select sleep(600); -- wait a long time for purge/page cleaner....
analyze table t;
show table status like 't'\G
select count(*) from t;
select Data_free,Data_length+Index_length,Data_length,Index_length from information_schema.tables where table_schema='test' and table_name='t';
Suggested fix:
Either provide an accurate number for free space, or
make it clear the value counts number of free extents and label it as such.
We would like to know the fill factor details as far as possible so we can determine how much free space a table rebuild would reclaim for the OS.