INFORMATION_SCHEMA.INNODB_CMP and INNODB_CMP_RESET tables

You are viewing an old version of this article. View the current version here.

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 NameDescription
PAGE_SIZECompressed page size, in bytes. This happens when a new page is create because the compression log runs out of space.
COMPRESS_OPSHow many times a page of the size PAGE_SIZE has been compressed. This values includes both successful operations and compression failures.
COMPRESS_OPS_OKHow 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_TIMETime (in seconds) spent to compress pages of the size PAGE_SIZE. This value includes time spent in compression failures.
UNCOMPRESS_OPSHow 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_TIMETime (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.

Comments

Comments loading...
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.