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.
These tables contain the following columns:
| Column Name | Description |
|---|---|
PAGE_SIZE |
Compressed page size, in bytes. This value is unique in the table; other values are totals which refer to pages of this size. |
COMPRESS_OPS |
How many times a page of the size PAGE_SIZE has been compressed. This happens when a new page is created because the compression log runs out of space. This value 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. If it is notably lower, either avoid compressing some tables, or increase the KEY_BLOCK_SIZE for some compressed tables. |
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 with 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 can be used to see the cumulated statistics.
SELECT * FROM information_schema.INNODB_CMP\G
**************************** 1. row *****************************
page_size: 1024
compress_ops: 0
compress_ops_ok: 0
compress_time: 0
uncompress_ops: 0
uncompress_time: 0
...
Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-innodb_cmp-and-innodb_cmp_reset-tables/