The current version of the InnoDB storage engine provides only a limited means to monitor the performance of compression at runtime. Overall application performance, CPU and I/O utilization and the size of disk files are the best indicators of how effective compression is for your application.
The InnoDB storage engine does include some Information Schema tables
Example 6.1, “Using the Compression Information Schema Tables”)
that reflect the internal use of memory and the rates of
compression used overall. The
tables report information about compression activity for each
compressed page size (
KEY_BLOCK_SIZE) in use. The information
in these tables is system-wide, and includes summary data across
all compressed tables in your database. You can use this data to
help decide whether or not to compress a table by examining
these tables when no other compressed tables are being accessed.
The key statistics to consider are the number of, and amount of
time spent performing, compression and uncompression operations.
Since InnoDB must split B-tree nodes when they are too full to
contain the compressed data following a modification, you should
also compare the number of “successful” compression
operations with the number of such operations overall. Based on
the information in the
INNODB_CMP tables and
overall application performance and hardware resource
utilization, you may decide to make changes in your hardware
configuration, adjust the size of the InnoDB buffer pool,
choose a different page size, or select a different set of
tables to compress.
If the amount of CPU time required for compressing and uncompressing is high, changing to faster CPUs, or those with more cores, can help improve performance with the same data, application workload and set of compressed tables. You may also benefit by increasing the size of the InnoDB buffer pool, so that more uncompressed pages can stay in memory, reducing the need to uncompress pages which exist in memory only in compressed form.
A large number of compression operations overall (compared to
the number of
operations in your application and the size of the database)
could indicate that some of your compressed tables are being
updated too heavily for effective compression. You may want to
choose a larger page size, or be more selective about which
tables you compress.
If the number of “successful” compression
COMPRESS_OPS_OK) is a high
percentage of the total number of compression operations
COMPRESS_OPS), then the system is likely
performing well. However, if the ratio is low, then InnoDB is
being caused to reorganize, recompress and split B-tree nodes
more often than is desirable. In this case, you may want to
avoid compressing some tables or choose a larger
KEY_BLOCK_SIZE for some of the tables for which you are using
compression. You may not want to compress tables which cause the
number of “compression failures” in your
application to be more than 1% or 2% of the total (although this
may be acceptable during a data load, for example, if your
application does not encounter such a ratio during normal
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .