Bug #89184 innodb: Data_free not increasing after deleting data, how to calculate slack...
Submitted: 11 Jan 2018 10:36
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.6.38, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[11 Jan 2018 10:36] Shane Bester
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.