Two new pairs of Information Schema tables provided by the InnoDB storage engine can give you some insight into how well compression is working overall. One pair of tables contains information about the number of compression operations and the amount of time spent performing compression. Another pair of tables contains information on the way memory is allocated for compression.
The tables INNODB_CMP and INNODB_CMP_RESET contain status
information on the operations related to compressed tables,
which are covered in Section 13.7.3, “InnoDB Data Compression”. The
compressed page size is in the column
PAGE_SIZE.
These two tables have identical contents, but reading from
INNODB_CMP_RESET resets the statistics on compression and
uncompression operations. For example, if you archive the output
of INNODB_CMP_RESET every 60 minutes, you see the statistics
for each hourly period. If you monitor the output of
INNODB_CMP (making sure never to read
INNODB_CMP_RESET), you see the cumulated
statistics since InnoDB was started.
For the table definition, see
Table 20.1, “Columns of INNODB_CMP and
INNODB_CMP_RESET”.
The tables INNODB_CMPMEM and INNODB_CMPMEM_RESET contain
status information on the compressed pages that reside in the
buffer pool. Please consult Section 13.7.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.
Internal Details
The InnoDB storage engine 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, except for
rows with PAGE_SIZE<1024, which are
implementation artifacts. The smallest blocks
(PAGE_SIZE=64 or
PAGE_SIZE=128, depending on the server
platform) are used for keeping track of compressed pages for
which no uncompressed page has been allocated in the buffer
pool. Other blocks of PAGE_SIZE<1024
should never be allocated (PAGES_USED=0).
They exist because the memory allocator allocates smaller blocks
by splitting bigger ones into halves.
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.
For the table definition, see Table 20.2, “Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET”.
Example 13.1. Using the Compression Information Schema Tables
The following is sample output from a database that contains
compressed tables (see Section 13.7.3, “InnoDB Data Compression”,
INNODB_CMP, and INNODB_CMPMEM).
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMP under light
load. The only compressed page size that the buffer pool
contains is 8K. Compressing or uncompressing pages has
consumed less than a second since the time the statistics were
reset, because the columns COMPRESS_TIME
and UNCOMPRESS_TIME are zero.
| page size | compress ops | compress ops ok | compress time | uncompress ops | uncompress time |
|---|---|---|---|---|---|
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 1048 | 921 | 0 | 61 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
According to INNODB_CMPMEM, there are 6169 compressed 8KB
pages in the buffer pool. The only other allocated block size
is 64 bytes. The smallest PAGE_SIZE in
INNODB_CMPMEM is used for block descriptors of those
compressed pages for which no uncompressed page exists in the
buffer pool. We see that there are 5910 such pages.
Indirectly, we see that 259 (6169-5910) compressed pages also
exist in the buffer pool in uncompressed form.
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMPMEM under
light load. We can see that some memory is unusable due to
fragmentation of the InnoDB memory allocator for compressed
pages: SUM(PAGE_SIZE*PAGES_FREE)=6784. This
is because small memory allocation requests are fulfilled by
splitting bigger blocks, starting from the 16K blocks that are
allocated from the main buffer pool, using the buddy
allocation system. The fragmentation is this low, because some
allocated blocks have been relocated (copied) to form bigger
adjacent free blocks. This copying of
SUM(PAGE_SIZE*RELOCATION_OPS) bytes has
consumed less than a second
(SUM(RELOCATION_TIME)=0).