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:
None 
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
Description:
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;
\r
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.