Page compression is implemented in InnoDB using the “Compressed InnoDB Tables” feature. This feature compresses the data stored in individual pages of the InnoDB tables, reducing their size and thus reducing the amount of disk I/O required to access the data. InnoDB uses the zlib library for compression. The compression level can be specified in the table definition and can range from 1 (minimum compression) to 9 (maximum compression). Compressed pages are uncompressed in memory, so the compressed data is transparent to the applications accessing the data. The compression ratio achieved depends on the data, but a compression ratio of 2:1 or higher is common.
When InnoDB page compression is recommended?
InnoDB page compression is recommended when you have data that is highly compressible and storage space is a concern. Compression can also improve the performance of read-intensive workloads by reducing the amount of data that needs to be read from disk and by reducing the amount of memory required to store the data. However, it is important to consider the trade-off between the storage and performance benefits of compression and the additional CPU overhead required to compress and decompress the data. It is generally recommended to enable page compression only on tables with a large number of rows and a high data-to-index ratio.
How to configure page compression in MySQL 8?
In MySQL 8, InnoDB page compression is configured by modifying the innodb_file_per_table setting and setting the innodb_compression_level setting to a value between 0 and 9. The former setting must be set to 1 to allow compression for individual tables, while the latter setting specifies the compression level to use. To configure page compression, you can add these settings to the my.cnf configuration file:
|
1 2 3 |
[mysqld] innodb_file_per_table=1 innodb_compression_level=6 |
After modifying the configuration file, you need to restart the MySQL server to apply the changes. Once the server is restarted, you can enable compression for a table using the ALTER TABLE statement:
|
1 |
ALTER TABLE table_name ROW_FORMAT=COMPRESSED; |
Replace table_name with the name of the table you want to enable compression for. Note that you can’t change the compression settings of a table once it has been created, so you’ll need to create a new table with the desired compression settings and copy the data from the old table to the new one.
How InnoDB page compression affects MySQL
Page compression in InnoDB can cause locks during the compression and decompression operations. These locks can impact the performance of the database by causing a slowdown in query processing. Additionally, since the compression operation is resource-intensive, it can also consume significant CPU and memory resources, which can further impact performance. To minimize the impact of page compression on performance, it is recommended to schedule the compression operation during periods of low database activity. Additionally, using fast storage with high I/O bandwidth can also help minimize the performance impact.
