Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables - Source

Revision: 109828
User: Federico Razzoli
Date: 2021-09-07 09:16

The ##INNODB_CMP_PER_INDEX## and ##INNODB_CMP_PER_INDEX_RESET## tables contain status information on compression operations related to [[innodb-storage-formats#compressed|compressed XtraDB/InnoDB tables]], grouped by individual indexes. These tables are only populated if the [[innodb-system-variables#innodb_cmp_per_index_enabled|innodb_cmp_per_index_enabled]] system variable is set to ##ON##.

The [[grant#global-privileges|PROCESS]] privilege is required to query this table.

These tables contains the following columns:
<<style class="darkheader-nospace-borders">>
|= Column Name |= Description
| ##DATABASE_NAME## | Database containing the index. |
| ##TABLE_NAME## | Table containing the index. |
| ##INDEX_NAME## | Other values are totals which refer to this index's compression. |
| ##COMPRESS_OPS## | How many times a page of ##INDEX_NAME## 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 ##INDEX_NAME## 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 ##INDEX_NAME## 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 ##INDEX_NAME##. |
<</style>>

These tables can be used to measure the effectiveness of XtraDB/InnoDB compression, per table or per index. The values in these tables show which tables perform better with index compression, and which tables cause too many //compression failures// or perform too many compression/uncompression operations. When compression performs badly for a table, this might mean that you should change its ##KEY_BLOCK_SIZE##, or that the table should not be compressed.

##INNODB_CMP_PER_INDEX## and ##INNODB_CMP_PER_INDEX_RESET## have the same columns and always contain the same values, but when ##INNODB_CMP_PER_INDEX_RESET## is queried, both the tables are cleared. ##INNODB_CMP_PER_INDEX_RESET## can be used, for example, if a script periodically logs the performances of compression in the last period of time. ##INNODB_CMP_PER_INDEX## can be used to see the cumulated statistics.

==See Also
Other tables that can be used to monitor XtraDB/InnoDB compressed tables:
* [[information_schemainnodb_cmp-and-innodb_cmp_reset-tables|INNODB_CMP and INNODB_CMP_RESET]]
* [[information_schemainnodb_cmpmem-and-innodb_cmpmem_reset-tables|INNODB_CMPMEM and INNODB_CMPMEM_RESET]]

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Information Schema INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB