Last Comment Bug 686169 - Compress InnoDB tables
: Compress InnoDB tables
Status: NEW
:
Product: Bugzilla
Classification: Server Software
Component: Database (show other bugs)
: 4.3
: All All
: P3 enhancement (vote)
: ---
Assigned To: Nobody; OK to take it and work on it
: default-qa
:
Mentors:
http://dev.mysql.com/doc/refman/5.5/e...
Depends on: 577532 868867
Blocks:
  Show dependency treegraph
 
Reported: 2011-09-10 16:27 PDT by Frédéric Buclin
Modified: 2013-08-14 10:24 PDT (History)
0 users
See Also:
QA Whiteboard:
Iteration: ---
Points: ---


Attachments

Description Frédéric Buclin 2011-09-10 16:27:33 PDT
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 2011-09-12 16:59:38 PDT
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.

Note You need to log in before you can comment on or make changes to this bug.