You may consider the tables INNODB_CMPMEM and INNODB_CMPMEM_RESET as the status information on the compressed pages that reside in the buffer pool. Please consult Chapter 3, InnoDB Data Compression for further information on compressed tables and the use of the buffer pool. The tables INNODB_CMP and INNODB_CMP_RESET should provide more useful statistics on compression.

The InnoDB Plugin uses a so-called buddy allocator system to manage memory allocated to pages of various sizes, from 1KB to 16KB. Each row of the two tables described here corresponds to a single page size.

These two tables have identical contents, but reading from INNODB_CMPMEM_RESET resets the statistics on relocation operations. For example, if every 60 minutes you archived the output of INNODB_CMPMEM_RESET, it would show the hourly statistics. If you never read INNODB_CMPMEM_RESET and monitored the output of INNODB_CMPMEM instead, it would show the cumulated statistics since InnoDB was started.


Column nameDescription
PAGE_SIZEBlock size in bytes. Each record of this table describes blocks of this size.
PAGES_USEDNumber of blocks of the size PAGE_SIZE that are currently in use.
PAGES_FREENumber of blocks of the size PAGE_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_OPSNumber of times a block of the size PAGE_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 the table INNODB_CMPMEM_RESET resets this count.
RELOCATION_TIMETotal time in microseconds spent in relocating blocks of the size PAGE_SIZE. Reading from the table INNODB_CMPMEM_RESET resets this count.

User Comments
Sign Up Login You must be logged in to post a comment.