686169 - Compress InnoDB tables

Status: NEW

(Bugzilla :: Database, enhancement, P3)

Description

[Frédéric Buclin]

Starting with MySQL 5.1, it's possible to compress InnoDB tables using ROW_FORMAT=COMPRESSED with CREATE TABLE or ALTER TABLE, see http://dev.mysql.com/doc/refman/5.5/en/innodb-compression.html. I think compression would be valuable for tables such as longdescs or attach_data once bug 577532 is implemented.

In MySQL 5.1, the InnoDB plugin is required to enable compression and this plugin is available with the MySQL distribution since version 5.1.38, see http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-installation-overview.html. Since MySQL 5.5, this plugin is no longer necessary as it's built into MySQL itself. In both cases, the current minimum version of 5.0.15 should be bumped, either to 5.1.38 or 5.5.8 (the first stable 5.5.x release). As the InnoDB plugin must still be loaded in MySQL 5.1 by configuring my.cnf, it may be easier and safer to require MySQL 5.5.

SQLite also has support for table compression by using FTS4, see http://www.sqlite.org/fts3.html. A nice side-effect of using FTS4 is that we can also use full-text searching.

Oracle 10.2 supports table compression too, see http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm.


MySQL and Oracle recommend to enable compression for tables which are mostly read-only (compared to how often they are written to). This means it may not be appropriate for the longdescs table as users add comments very often. It may still be useful for attach_data as attachments are uploaded much less frequently. Some benchmarking would be necessary to determine if table compression would bring any performance improvement.

Comment 1

[Max Kanat-Alexander]

Ah yeah, there are also numerous other enhancements that come along with the InnoDB plugin that could be useful for us. However, requiring MySQL 5.5 would probably be a few years off for us, as 5.1 is more or less the present standard, and *very* few people have the InnoDB plugin installed.