Bug #107059 Bad compression stats in I_S.INNODB_CMP starting in 5.7.27 (8.0 not affected).
Submitted: 19 Apr 20:45 Modified: 20 Apr 9:47
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.37,5.7.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[19 Apr 20:45] Jean-François Gagné
Description:
Hi,

I have data I am not expecting in INNODB_CMP.  I traced this down to a regression introduced in 5.7.27, 8.0.28 not affected.  See How to repeat for details.

Thanks for looking into this, Jean-François Gagné

How to repeat:
# Create a sandbox.
dbdeployer deploy single mysql_5.7.37

# Create schema, table and put data in them.
./use <<< "create database test_jfg"
./use test_jfg <<< "create table t (id int primary key, v int) KEY_BLOCK_SIZE=8"
./use test_jfg <<< "insert into t values (1,1)"

# Restart MySQL for reset of buffer pool.
./stop; ./start

# Read from the compressed table and check stats.
./use test_jfg <<< "select * from t" > /dev/null
./use information_schema <<< "select * from INNODB_CMP"
page_size       compress_ops    compress_ops_ok compress_time   uncompress_ops  uncompress_time
1024    0       0       0       0       0
2048    0       0       0       0       0
4096    0       0       0       0       0
8192    0       0       0       1       4838
16384   0       0       0       0       0

# The number 4838 is very high for an uncompress_time in seconds [1].
# [1]: https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-cmp-table.html

# Same problem in 5.7.27.
[...]
page_size       compress_ops    compress_ops_ok compress_time   uncompress_ops  uncompress_time
1024    0       0       0       0       0
2048    0       0       0       0       0
4096    0       0       0       0       0
8192    0       0       0       1       6968
16384   0       0       0       0       0

# No problem in 5.7.26.
[...]
page_size       compress_ops    compress_ops_ok compress_time   uncompress_ops  uncompress_time
1024    0       0       0       0       0
2048    0       0       0       0       0
4096    0       0       0       0       0
8192    0       0       0       1       0
16384   0       0       0       0       0

# And no problem in 8.0.28.
page_size       compress_ops    compress_ops_ok compress_time   uncompress_ops  uncompress_time
1024    0       0       0       0       0
2048    0       0       0       0       0
4096    0       0       0       0       0
8192    0       0       0       1       0
16384   0       0       0       0       0
[20 Apr 9:47] MySQL Verification Team
Hello Jean-François,

Thank you for the report and feedback.

regards,
Umesh
[20 Apr 12:16] huahua xu
fix the bug about page compression statistics

Attachment: innodb_cmp_stat_bugfix.patch (application/octet-stream, text), 557 bytes.