MySQL 5.5.59中InnoDB表行格式不一致及压缩失效求助

Let's break down why your __new_logs table isn't actually compressed (despite what CREATE_OPTIONS suggests) and how to fix this issue with MySQL 5.5.59 and Percona Toolkit.

Possible Root Causes #

1. MySQL 5.5's information_schema.tables Reliability Gap
   In MySQL 5.5, the ROW_FORMAT column in information_schema.tables doesn't always reflect the actual row format of compressed tables. It might incorrectly show Compact even when compression settings are present in CREATE_OPTIONS. For a more accurate view, use SHOW TABLE STATUS instead.

2. Percona Toolkit's Implicit Schema Inheritance Quirk
   When you only specified ENGINE=InnoDB in the --alter parameter, pt-online-schema-change may not have properly carried over the original table's compression settings. Even though it copied the CREATE_OPTIONS string, older versions of the tool have compatibility issues with MySQL 5.5 that prevent compression from being applied during table creation or data migration.

3. Missing Explicit Compression Flags in the Alter Command
   MySQL 5.5's InnoDB requires explicit ROW_FORMAT=COMPRESSED and KEY_BLOCK_SIZE declarations in the alter statement to enable compression. Without these, it falls back to the default Compact row format, ignoring inherited create options.

Step-by-Step Fixes #

1. Verify the Actual Row Format
   Run this command to get the real row format of your __new_logs table:

   SHOW TABLE STATUS LIKE '__new_logs'\G
   

   If the Row_format field here shows Compact, that confirms the table isn't using compression.

2. Re-run pt-online-schema-change with Explicit Compression Settings
   Modify your command to explicitly include compression parameters in the --alter clause. This ensures the new table is created with compression enabled from the start:

   pt-online-schema-change --alter "ENGINE=InnoDB, ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE=8" \
     --nocheck-replication-filters --execute --statistics --progress=percentage,1 \
     --set-vars='lock_wait_timeout=60' --check-alter --no-swap-tables \
     --no-drop-triggers --no-drop-old-table --no-drop-new-table \
     --chunk-time=1 --chunk-size=20 --new-table-name='__new_logs' \
     h=**HOST_#########**,D=DB_#########,t=logs,u=root --ask-pass
   

3. Manually Enable Compression on the Existing __new_logs Table
   If you don't want to re-run the full schema change, you can alter the existing table to force compression (note: this will temporarily lock the table):

   ALTER TABLE __new_logs ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
   

   After running this, recheck the table status to confirm compression is active.

Why the New Table is Larger #

If __new_logs is using the Compact row format instead of Compressed, it won't benefit from InnoDB's page-level compression. Uncompressed rows take up significantly more disk space, which explains why the new table is larger than the original logs table.

内容的提问来源于stack exchange，提问作者nemenems