Information Schema INNODB_CMP and INNODB_CMP_RESET Tables
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.
Examples
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 ...
See Also
Other tables that can be used to monitor XtraDB/InnoDB compressed tables: