Other storage engines offer the ability to compress tables. These engines are:
TokuDB
: This engine always compresses tables; there is no way to avoid this. This is a part of its strategy, which aims to reach high performance by reducing the amount of output to disks.ARCHIVE
: This storage engine is specifically designed for compressed tables with limited functionalities. It is possible to add new data to an ARCHIVE
table, but the existing rows cannot be deleted or updated. Index support is very limited.MyISAM
: While normal MyISAM tables are not compressed, a special tool called myisampack
can be used to compress tables. A compressed MyISAM table is read only.Compression methods other than the InnoDB COMPRESSED
row format are beyond the scope of this chapter. They are not widely used because other storage engines, including TokuDB
, do not reach the performance of InnoDB on most workloads. Also, the ARCHIVE
storage engine and the myisampack
tool are documented and easy to use. However, in most cases, their limitations are not acceptable. The TokuDB
storage engine is also capable of a very good compression. However, since TokuDB
is not a simple topic, and since it is not enabled by default, it is not covered in this book.
However, an advanced user should know that such solutions exist. The following example shows a comparison between the size of the files of a typical customer's tables:
root@this:/usr/local/mysql/data/test# ls -l customers* -rw-rw---- 1 mysql mysql 201326592 mar 31 00:51 customers_non_comp.ibd -rw-rw---- 1 mysql mysql 100663296 mar 31 01:03 customers_4.ibd -rw-rw---- 1 mysql mysql 96468992 mar 31 00:56 customers_8.ibd -rw-rw---- 1 mysql mysql 188743680 mar 31 00:54 customers_16.ibd -rw-rw---- 1 mysql mysql 955044 mar 31 01:04 customers_arch.ARZ -rw-rw---- 1 mysql mysql 82379202 mar 31 01:04 customers_myi.MYD -rw-rw---- 1 mysql mysql 25600000 mar 31 01:13 customers_myi.MYI
The preceding output has been edited to make it more readable, but the values are real. Of course, there are cases when we can get completely different results, so proper tests should be executed if they are relevant for our case. But still, this represents a real case with a table containing many short text fields, an auto increment primary key, and an indexed username field.
The data
files in the example are:
customers_non_comp.ibd
: This is a noncompressed InnoDB tablecustomers_*.ibd
: This is a compressed InnoDB table with a key block size of 4, 8, and 16 KBcustomers_arch.ARZ
: This is an ARCHIVE
tablecustomers_myi.MYD
and customers_myi.MYI
: These are compressed MyISAM data
and index
filesIn this example, the compressed MyISAM files are a bit bigger than the best compressed InnoDB tablespace, and thus, ARCHIVE
wins.
Since the difference between ARCHIVE
and InnoDB is so high, we can conclude that ARCHIVE is a better choice for the general case. However, there are several reasons why InnoDB is more useful:
LINESTRING
or POLYGON
are not supported in ARCHIVE.If these limitations are not a concern, ARCHIVE is a very good choice. In other cases, if really needed, modifying the innodb_compression_level
variable is usually a more acceptable solution than using the ARCHIVE
storage engine.