Information Schema INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables - Source

Revision: 93192
User: Federico Razzoli
Date: 2020-02-13 17:09

The ##INNODB_CMPMEM## and ##INNODB_CMPMEM_RESET## tables contain status information on compressed pages in the [[innodb-buffer-pool|buffer pool]] (see InnoDB [[innodb-storage-formats#compressed|COMPRESSED]] format).

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

These tables contain the following columns:
<<style class="darkheader-nospace-borders">>
|= 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. |
| ##BUFFER_POOL_INSTANCE## | Buffer Pool identifier. From MariaDB 10.5.1 returns a value of 0, since multiple InnoDB buffer pool instances has been removed. |
| ##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 unfragmented 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 shrunk 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. |
<</style>>

These tables can be used to measure the effectiveness of 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.

== Example
<<code>>
SELECT * FROM information_schema.INNODB_CMPMEM\G
********************** 1. row **********************
            page_size: 1024
 buffer_pool_instance: 0
           pages_used: 0
           pages_free: 0
      reloacation_ops: 0
      relocation_time: 0
<</code>>

==See Also
Other tables that can be used to monitor InnoDB compressed tables:
* [[information_schemainnodb_cmp-and-innodb_cmp_reset-tables|INNODB_CMP and INNODB_CMP_RESET]]
* [[information_schemainnodb_cmp_per_index-and-innodb_cmp_per_index_reset-table|INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_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_CMPMEM and INNODB_CMPMEM_RESET Tables - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB