Bug #82184 table status inconsistent, requires ANALYZE TABLE executed twice
Submitted: 11 Jul 2016 10:52
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[11 Jul 2016 10:52] Andrii Nikitin
SHOW TABLE STATUS may show inconsistent numbers with big blobs.

E.g. with script below:
- Avg_row_length is twice bigger than actual
- Data_length is twice bigger than actual
- size of idb file doesn't match Data_length+Index_length+Data_free

I understand that the script shows edge case scenario - but on practice the problem still exist and often size of .idb file differs too much from data_length+index_length+data_free (when long transactions exist / existed).

It looks that workaround is to run ANALYZE TABLE twice in a row.

How to repeat:
# make sure innodb_log_file_size is >1G
# make sure innodb_stats_persistent=1

set global max_allowed_packet=128*1024*1024;
create table t(a longblob) engine=innodb;
# 1.
insert into t select repeat('a',128*1024*1024);
# ibd grows to ~130M (as expected), but STATUS reports only 16K:
show table status like 't';
# Actual: Avg_row_length=16K Data_length=16K Data_free=0
# Expect: Avg_row_length=128M Data_length=128M Data_free=0
# analyze table to display correct values:
analyze table t;
show table status like 't';
# Avg_row_length=134M Data_length=134M Data_free=0

# 2.
# Now, after update .idb will grow twice up to ~260M, but STATUS shows 127M:
update t set a = repeat('b',128*1024*1024);
show table status like 't';
# Actual: Avg_row_length=134M Data_length=134M Data_free=0
# Expect: Avg_row_length=134M Data_length=134M Data_free=134M

# 3.
# Try ANALYZE TABLE and see that it doesn't fix, but confuses even more:
analyze table t;
show table status like 't';
# Actual: Avg_row_length=270M Data_length=270M Data_free=95M
# Expect: Avg_row_length=134M Data_length=134M Data_free=134M
# (now it makes think that size of ibd is ~370M, while it is 260M)

# to work around - try ANALYZE TABLE once again
analyze table t;
show table status like 't';
# Avg_row_length=135M Data_length=135M Data_free=135M
# Those are correct values ^^

Suggested fix:
Make behavior consistent or document that TABLE STATUS may be inconsistent for InnoDB with blobs and persistent stats
[11 Jul 2016 10:55] Andrii Nikitin
related to bug #82185, just this was not repeatable on 5.6 and doesn't cover effect of parallel transaction
[18 Aug 2016 13:30] Andrii Nikitin
Posted by developer:
Most probably second ANALYZE is needed to give some time to purge thread remove old versions of the rows.
This bug report probably may be solved by introducing a way to see number and size of "old rows" in table which are going to be purged "soon".