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
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