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.