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 anARCHIVEtable, 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 calledmyisampackcan 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 anARCHIVEtablecustomers_myi.MYDandcustomers_myi.MYI: These are compressed MyISAMdataandindexfiles
In 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:
- ARCHIVE does not support transactions, and this is a concern, except for big tables that are only used for statistical data.
- InnoDB performance for inserts is much better, and the performance of XtraDB is even better.
- Geometric data types such as
LINESTRINGorPOLYGONare not supported in ARCHIVE. - While compressed tables are not likely to be read often, we may want to index them. ARCHIVE does not support indexing, except for the autoincremental primary keys.
- While the development of InnoDB and XtraDB is very intensive, ARCHIVE did not evolve for many years.
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.