Most databases have an important characteristic: they constantly grow. Usually, this means that they become more useful for the users. But this also causes problems to the DBA. This chapter covers an important feature that can sometimes be used to reduce the size of physical files: InnoDB table compression.
InnoDB allows the user to compress InnoDB tables using SQL commands. No external tool is needed. InnoDB compressed tables can still be read and written, just like uncompressed tables. As will be explained in this chapter, this may or may not improve the performance of the queries. It is, however, an important feature to focus on if our data needs too much disk space.
The following sections will be covered in this chapter:
- InnoDB compression requirements
- Creating InnoDB compressed tables
- Implementing the InnoDB compression
- Monitoring the InnoDB compression performance
- Other compression solutions
InnoDB supports the compression of tables. There are two reasons why we may want to use this feature:
- Saving space on disks
- Reducing the I/O
The first reason is more relevant if we use SSD storage devices because many I/O operations reduce their lifetime.
On many workloads, the I/O from and to disks is the bottleneck. Reducing the data size obviously reduces the amount of data that needs to be moved from disks to the buffer pool and vice versa.
However, InnoDB compression has a cost. The pages read from disks need to be uncompressed before being inserted into the buffer pool. Changes to the buffer pool and the change buffer must be compressed before being written onto the disk. This requires additional work by the CPU. For this reason, using compression may result in a slower performance.
Also, if there is enough memory, InnoDB keeps both compressed and uncompressed versions of all the data in the buffer pool. This means that the buffer pool needs more space, or it will contain less data. In this case, useful pages can be evicted from the buffer pool so that it contains compressed pages. For this reason, InnoDB compression performs much better on read-intensive workloads, and should generally be avoided on write-intensive workloads.
It is also important to consider how much of a table's data can be compressed. InnoDB uses the LZ77 algorithm for compression, which replaces repeated long data patterns with shorter strings. This technique suits texts better and usually does not work very well with numbers. Compressed data formats, such as JPEG images or MP3 videos, will not benefit much from a further LZ77 compression.
The TEXT, BLOB, and VARCHAR columns are stored in separate pages when necessary, as explained in Chapter 6, Caches. If a row does not fit its page, the largest columns are moved to special pages called
overflow pages. The clustered index contains a 20-byte pointer for each overflow page used. This slows down all the operations that involve such columns. If a table compression sensibly reduces the number of overflow pages used by a table, it will probably improve the performance.
While knowing the InnoDB compression characteristics is important, the only exact way to determine whether our workload will benefit from compression is by testing it. We need to test it on a compression basis, execute a realistic workload, and monitor some information_schema tables.