Most often, the internal optimizations in InnoDB described in Section 184.108.40.206, “ InnoDB Data Storage and Compression ” ensure that the system runs well with compressed data. However, because the efficiency of compression depends on the nature of your data, there are some factors you should consider to get best performance. You need to choose which tables to compress, and what compressed page size to use. You might also adjust the size of the buffer pool based on run-time performance characteristics, such as the amount of time the system spends compressing and uncompressing data.
In general, compression works best on tables that include a reasonable number of character string columns and where the data is read far more often than it is written. Because there are no guaranteed ways to predict whether or not compression benefits a particular situation, always test with a specific workload and data set running on a representative configuration. Consider the following factors when deciding which tables to compress.
A key determinant of the efficiency of compression in reducing the
size of data files is the nature of the data itself. Recall that
compression works by identifying repeated strings of bytes in a
block of data. Completely randomized data is the worst case.
Typical data often has repeated values, and so compresses
effectively. Character strings often compress well, whether
BLOB columns. On the
other hand, tables containing mostly binary data (integers or
floating point numbers) or data that is previously compressed (for
example JPEG or PNG images)
may not generally compress well, significantly or at all.
You choose whether to turn on compression for each InnoDB tables.
A table and all of its indexes use the same (compressed) page
size. It might be that the primary key (clustered) index, which
contains the data for all columns of a table, compresses more
effectively than the secondary indexes. For those cases where
there are long rows, the use of compression might result in long
column values being stored “off-page”, as discussed
in Section 220.127.116.11, “Barracuda File Format:
COMPRESSED Row Formats”. Those overflow
pages may compress well. Given these considerations, for many
applications, some tables compress more effectively than others,
and you might find that your workload performs best only with a
subset of tables compressed.
Experimenting is the only way to determine whether or not to
compress a particular table. InnoDB compresses data in 16K chunks
corresponding to the uncompressed page size, and in addition to
user data, the page format includes some internal system data that
is not compressed. Compression utilities compress an entire stream
of data, and so may find more repeated strings across the entire
input stream than InnoDB would find in a table compressed in 16K
chunks. But you can get a sense of how compression efficiency by
using a utility that implements LZ77 compression (such as
gzip or WinZip) on your data file.
Another way to test compression on a specific table is to copy
some data from your uncompressed table to a similar, compressed
table (having all the same indexes) and look at the size of the
resulting file. When you do so (if nothing else using compression
is running), you can examine the ratio of successful compression
operations to overall compression operations. (In the
INNODB_CMP table, compare
for more information.) If a high percentage of compression
operations complete successfully, the table might be a good
candidate for compression.
Decide whether to compress data in your application or in the InnoDB table. It is usually not sensible to store data that is compressed by an application in an InnoDB compressed table. Further compression is extremely unlikely, and the attempt to compress just wastes CPU cycles.
The InnoDB table compression is automatic and applies to all
columns and index values. The columns can still be tested with
operators such as
LIKE, and sort operations can
still use indexes even when the index values are compressed.
Because indexes are often a significant fraction of the total size
of a database, compression could result in significant savings in
storage, I/O or processor time. The compression and decompression
operations happen on the database server, which likely is a
powerful system that is sized to handle the expected load.
If you compress data such as text in your application, before it is inserted into the database, You might save overhead for data that does not compress well by compressing some columns and not others. This approach uses CPU cycles for compression and uncompression on the client machine rather than the database server, which might be appropriate for a distributed application with many clients, or where the client machine has spare CPU cycles.
Of course, it is possible to combine these approaches. For some applications, it may be appropriate to use some compressed tables and some uncompressed tables. It may be best to externally compress some data (and store it in uncompressed InnoDB tables) and allow InnoDB to compress (some of) the other tables in the application. As always, up-front design and real-life testing are valuable in reaching the right decision.
In addition to choosing which tables to compress (and the page
size), the workload is another key determinant of performance. If
the application is dominated by reads, rather than updates, fewer
pages need to be reorganized and recompressed after the index page
runs out of room for the per-page “modification log”
that InnoDB maintains for compressed data. If the updates
predominantly change non-indexed columns or those containing
BLOBs or large strings that happen to be stored
“off-page”, the overhead of compression may be
acceptable. If the only changes to a table are
INSERTs that use a monotonically increasing
primary key, and there are few secondary indexes, there is little
need to reorganize and recompress index pages. Since InnoDB can
“delete-mark” and delete rows on compressed pages
“in place” by modifying uncompressed data,
DELETE operations on a table are relatively
For some environments, the time it takes to load data can be as important as run-time retrieval. Especially in data warehouse environments, many tables may be read-only or read-mostly. In those cases, it might or might not be acceptable to pay the price of compression in terms of increased load time, unless the resulting savings in fewer disk reads or in storage cost is significant.
Fundamentally, compression works best when the CPU time is available for compressing and uncompressing data. Thus, if your workload is I/O bound, rather than CPU-bound, you might find that compression can improve overall performance. When you test your application performance with different compression configurations, test on a platform similar to the planned configuration of the production system.
Reading and writing database pages from and to disk is the slowest aspect of system performance. Compression attempts to reduce I/O by using CPU time to compress and uncompress data, and is most effective when I/O is a relatively scarce resource compared to processor cycles.
This is often especially the case when running in a multi-user environment with fast, multi-core CPUs. When a page of a compressed table is in memory, InnoDB often uses an additional 16K in the buffer pool for an uncompressed copy of the page. The adaptive LRU algorithm in the InnoDB storage engine attempts to balance the use of memory between compressed and uncompressed pages to take into account whether the workload is running in an I/O-bound or CPU-bound manner. Still, a configuration with more memory dedicated to the InnoDB buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained.
The optimal setting of the compressed page size depends on the type and distribution of data that the table and its indexes contain. The compressed page size should always be bigger than the maximum record size, or operations may fail as noted in Section 18.104.22.168, “ Compression of B-Tree Pages ”.
Setting the compressed page size too large wastes some space, but the pages do not have to be compressed as often. If the compressed page size is set too small, inserts or updates may require time-consuming recompression, and the B-tree nodes may have to be split more frequently, leading to bigger data files and less efficient indexing.
Typically, you set the compressed page size to 8K or 4K bytes.
Given that the maximum InnoDB record size is around 8K,
KEY_BLOCK_SIZE=8 is usually a safe choice.
Overall application performance, CPU and I/O utilization and the size of disk files are good indicators of how effective compression is for your application.
To dig deeper into performance considerations for compressed tables, you can monitor compression performance at runtime. using the Information Schema tables described in Example 14.1, “Using the Compression Information Schema Tables”. These tables reflect the internal use of memory and the rates of compression used overall.
INNODB_CMP 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, compare the
number of “successful” compression operations with
the number of such operations overall. Based on the information in
INNODB_CMP tables and overall application
performance and hardware resource utilization, you might 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. Increasing the size of the InnoDB buffer pool might also help performance, so that more uncompressed pages can stay in memory, reducing the need to uncompress pages that exist in memory only in compressed form.
A large number of compression operations overall (compared to the
DELETE 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. If
so, choose a larger page size, or be more selective about which
tables you compress.
If the number of “successful” compression operations
COMPRESS_OPS_OK) is a high percentage of the
total number of compression operations
COMPRESS_OPS), then the system is likely
performing well. If the ratio is low, then InnoDB is reorganizing,
recompressing, and splitting B-tree nodes more often than is
desirable. In this case, avoid compressing some tables, or
KEY_BLOCK_SIZE for some of the
compressed tables. You might turn off compression for tables that
cause the number of “compression failures” in your
application to be more than 1% or 2% of the total. (Such a failure
ratio might be acceptable during a temporary operation such as a