INFORMATION_SCHEMA.INNODB_CMP and INNODB_CMP_RESET tables
About
The INNODB_CMP and INNODB_CMP_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables.
The PROCESS privilege is required to query this table.
They table contains the following columns:
| Column Name | Description |
|---|---|
PAGE_SIZE | Compressed page size, in bytes. This happens when a new page is create because the compression log runs out of space. |
COMPRESS_OPS | How many times a page of the size PAGE_SIZE has been compressed. This values includes both successful operations and compression failures. |
COMPRESS_OPS_OK | How many times a page of the size PAGE_SIZE has been successfully compressed. This value should be as close as possible to COMPRESS_OPS. |
COMPRESS_TIME | Time (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures. |
UNCOMPRESS_OPS | How many times a page of the size PAGE_SIZE has been uncompressed. This happens when an uncompressed version of a page is created in the buffer pool, or when a compression failure occurs. |
UNCOMPRESS_TIME | Time (in seconds) spent to uncompress pages of the size PAGE_SIZE. |
These tables can be used to measure the effectiveness of XtraDB/InnoDB table compression. When you have to decide a value for KEY_BLOCK_SIZE, you can create more than one version of the table (one for each candidate value) and run a realistic workload on them. Then, these tables can be used to see how the operations performed on different page sizes.
INNODB_CMP and INNODB_CMP_RESET have the same columns and always contain the same values, but when INNODB_CMP_RESET is queried, both the tables are cleared. INNODB_CMP_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMP_RESET can be used to see the cumulated statistics.
