INFORMATION_SCHEMA.INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
About
The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on compressed pages in the buffer pool (see XtraDB/InnoDB COMPRESSED format).
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. |
PAGES_USED | Number of pages of the size PAGE_SIZE which are currently in the buffer pool. |
PAGES_FREE | Number of pages of the size PAGE_SIZE which are currently free, and thus are available for allocation. This value represents the buffer pool's fragmentation. A totally unframmented buffer pool has at most 1 free page. |
RELOCATION_OPS | How many times a page of the size PAGE_SIZE has been relocated. This happens when data exceeds a page (because a row must be copied into a new page) and when two pages are merged (because their data shrinked and can now be contained in one page). |
RELOCATION_TIME | Time (in seconds) spent in relocation operations for pages of the size PAGE_SIZE. This column is reset when the INNODB_CMPMEM_RESET table is queried. |
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_CMPMEM and INNODB_CMPMEM_RESET have the same columns and always contain the same values, but when INNODB_CMPMEM_RESET is queried, the RELOCATION_TIME column from both the tables are cleared. INNODB_CMPMEM_RESET can be used, for example, if a script periodically logs the performances of compression in the last period of time. INNODB_CMPMEM can be used to see the cumulated statistics.
