InnoDB Table Compression • innodb_compression_failure_threshold_pct  specifies a cutoff point for compression failures during updates to a compressed table. When this threshold is passed, MySQL begins to leave additional free space within each new compressed page, dynamically adjusting the amount of free space up to the percentage of page size specified by  innodb_compression_pad_pct_max • innodb_compression_pad_pct_max  lets you adjust the maximum amount of space reserved within each page to record changes to compressed rows, without needing to compress the entire page again. The higher the value, the more changes can be recorded without recompressing the page. MySQL uses a variable amount of free space for the pages within each compressed table, only when a designated percentage of compression operations “ fail”  at runtime, requiring an expensive operation to split the compressed page. • innodb_log_compressed_pages  lets you disable writing of images of re-compressed pages to the redo log. Re-compression may occur when changes are made to compressed data. This option is enabled by default to prevent corruption that could occur if a different version of the  zlib  compression algorithm is used during recovery. If you are certain that the  zlib  version is not subject to change, disable  innodb_log_compressed_pages  to reduce redo log generation for workloads that modify compressed data. Because working with compressed data sometimes involves keeping both compressed and uncompressed versions of a page in memory at the same time, when using compression with an OLTP-style workload, be prepared to increase the value of the  innodb_buffer_pool_size  configuration option. 15.9.1.7 SQL Compression Syntax Warnings and Errors SQL Compression Syntax Warnings and Errors for File-Per-Table Tablespaces Note Note This section describes syntax warnings and errors that you may encounter when using the table compression feature with file-per-table tablespaces and general tablespaces. When  innodb_strict_mode  is enabled (the default), specifying  ROW_FORMAT=COMPRESSED  or KEY_BLOCK_SIZE  in  CREATE TABLE  or  ALTER TABLE  statements produces the following error if innodb_file_per_table  is disabled. ERROR 1031 (HY000): Table storage engine for ’t1’ doesn’t have this option The table is not created if the current configuration does not permit using compressed tables. When  innodb_strict_mode  is disabled, specifying  ROW_FORMAT=COMPRESSED  or KEY_BLOCK_SIZE  in  CREATE TABLE  or  ALTER TABLE  statements produces the following warnings if innodb_file_per_table  is disabled. mysql>  SHOW WARNINGS; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. | | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4. | | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC. | +---------+------+---------------------------------------------------------------+ These messages are only warnings, not errors, and the table is created without compression, as if the options were not specified. 3158