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 14.4.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 14.4.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 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.
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 14.1. Using the Compression Information Schema Tables
The following is sample output from a database that contains
compressed tables (see Section 14.4.3, “InnoDB Data Compression”,
INNODB_CMP,
and
INNODB_CMPMEM).
The following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMP under a light
workload. 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 following table shows the contents of
INFORMATION_SCHEMA.INNODB_CMPMEM under a
light workload. 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).