MariaDB starting with 10.0

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables were added in MariaDB 10.0.4.

The INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET tables contain status information on compression operations related to compressed XtraDB/InnoDB tables, grouped by individual indexes. These tables are only populated if the innodb_cmp_per_index_enabled system variable is set to ON.

The PROCESS privilege is required to query this table.

These tables contains the following columns:

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.

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:

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.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.