13.7.6.1. Information Schema Tables about Compression

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.

13.7.6.1.1. INNODB_CMP and INNODB_CMP_RESET

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.

13.7.6.1.2. INNODB_CMPMEM and INNODB_CMPMEM_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”.

13.7.6.1.3. Using the Compression Information Schema Tables

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 sizecompress opscompress ops okcompress timeuncompress opsuncompress time
102400000
204800000
409600000
819210489210610
1638400000

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).

page sizepages usedpages freerelocation opsrelocation time
645910024360
1280100
2560000
5120100
10240000
20480100
40960100
81926169050
163840000
Copyright © 2010-2024 Platon Technologies, s.r.o.           Index | Man stránky | tLDP | Dokumenty | Utilitky | O projekte
Design by styleshout