17 Mar 2010

Compressed MyISAM is..

..kind of cool.

Here is a good use case for compressed MyISAM:
We have a rather large database that doesn't get updated very frequently.
We have several slave servers that have this database, but because the database is big, they have a bit of a hard time querying it.

Disk I/O is a big issue for us and compressing the data, offloads some of that onto the CPU for decompression. It also allows the system cache to keep more of the tables in memory.

This database only gets updated every few weeks and it's pretty much a manual process.
To update the slave servers, we simply copy the MyISAM tables over to them (yes, you can do that) and because they are compressed, they are easier to send over the network.

Unlike Archive tables, compressed MyISAM can have indexes (Archive tables now allows 1 index) so you can use them for fast querying.

Compressed MyISAM tables are read-only, which for us means they are much less likely to get corrupted.

So there you have it. One good use case for compressed MyISAM.


  1. I was pondering the same and raised the issue in #mysql, especially after I read the following in the mysql documentation comments: "The mmap() behaviour described here is not optional and may cause a 32-bit server to run out of address space sooner than it otherwise would.

    Therefore I strongly recommend factoring address space usage into any feasibility study of myisampack on 32-bit systems."

    I was then pointed at the following bug report, but I am not sure how the fix really addresses the issue:

  2. It is very nice for some use cases. Now we just need to convince MySQL to show it some love and start improving it again.

  3. Useful, but not easy to use.

    For example, there is no SQL interface to myisampack. There is no locking which stops myisampack interfering with MySQL. The mmap() cannot be disabled (which will break 32-bit systems).

    If you have < 2G data nothing matters much; if you have more, mmap will break on 32-bit systems.