Movatterモバイル変換

MySQL 5.7 Reference Manual

24.4.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

TheINNODB_CMPMEM andINNODB_CMPMEM_RESET tables provide status information on compressedpages within theInnoDBbuffer pool.

TheINNODB_CMPMEM andINNODB_CMPMEM_RESET tables have these columns:

PAGE_SIZE
The block size in bytes. Each record of this table describes blocks of this size.
BUFFER_POOL_INSTANCE
A unique identifier for the buffer pool instance.
PAGES_USED
The number of blocks of sizePAGE_SIZE that are currently in use.
PAGES_FREE
The number of blocks of sizePAGE_SIZE that are currently available for allocation. This column shows the external fragmentation in the memory pool. Ideally, these numbers should be at most 1.
RELOCATION_OPS
The number of times a block of sizePAGE_SIZE has been relocated. The buddy system can relocate the allocated“buddy neighbor” of a freed block when it tries to form a bigger freed block. Reading from theINNODB_CMPMEM_RESET table resets this count.
RELOCATION_TIME
The total time in microseconds used for relocating blocks of sizePAGE_SIZE. Reading from the tableINNODB_CMPMEM_RESET resets this count.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM\G*************************** 1. row ***************************           page_size: 1024buffer_pool_instance: 0          pages_used: 0          pages_free: 0      relocation_ops: 0     relocation_time: 0*************************** 2. row ***************************           page_size: 2048buffer_pool_instance: 0          pages_used: 0          pages_free: 0      relocation_ops: 0     relocation_time: 0*************************** 3. row ***************************           page_size: 4096buffer_pool_instance: 0          pages_used: 0          pages_free: 0      relocation_ops: 0     relocation_time: 0*************************** 4. row ***************************           page_size: 8192buffer_pool_instance: 0          pages_used: 7673          pages_free: 15      relocation_ops: 4638     relocation_time: 0*************************** 5. row ***************************           page_size: 16384buffer_pool_instance: 0          pages_used: 0          pages_free: 0      relocation_ops: 0     relocation_time: 0

Notes

Use these tables to measure the effectiveness ofInnoDB tablecompression in your database.
You must have thePROCESS privilege to query this table.
Use theINFORMATION_SCHEMACOLUMNS table or theSHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.
For usage information, seeSection 14.9.1.4, “Monitoring InnoDB Table Compression at Runtime” andSection 14.16.1.3, “Using the Compression Information Schema Tables”. For general information aboutInnoDB table compression, seeSection 14.9, “InnoDB Table and Page Compression”.

PREV HOME UP NEXT