MySQL Compression : myisam vs archive engine

Before or after every dba have to look after problems like 'not enough space on device'. According to MySQL the possible solution is implementing myisam's compression or altering the table's engine to an archive engine.

Let's look at both, shall we ?

To compress a myisam table you have to do the following :

•  myisamchk -dvv Table

With this command you can obtain useful information about the table that you are going to compress.

•  myisampack Table.MYI
  

Be careful ! this command has to applied only on the file with extention .MYI and partitioned tables aren't supported.

Remember that after you run the compression command, you also have to re-create any indexes with :

•  myisamchk -rq --sort-index --analyze Table.MYI

As you probably have noticed this command also create index statistics and sorts out index blocks. The compression is completely different from the archive engine. In this case you don't have to use an external utility command like 'myisampack' instead you can use directly the SQL command :

• CREATE TABLE .... ENGINE=ARCHIVE;
• ALTER TABLE .... ENGINE=ARCHIVE;

That means archive tables don't require offline operations !

An archive table supports read and insert operations instead myisam compression only read. According to manuals an archive engine is also able to compress a table until 75% of its original size and observing this comparative test that I did on a Test environment  I have to admin that this is partially true.

[mysql test] > du -sh *

16K     Table_myisam.frm
2.4G    Table_myisam.MYD
1.6G    Table_myisam.MYI

570M    Table_Engine_Archive.ARZ
16K      Table_Engine_Archive.frm

16K     Table_myisam_Compressed.frm
815M    Table_myisam_Compressed.MYD
8.0K    Table_myisam_Compressed.MYI


What about read operation ?

As one can see in manuals, an archive engine doesn't support indexes and therefore read access should be slower than myisam read access. As usual I did test between a myisam compressed table and an archive table and these are the results :

mysql> select count(*) from Table_Engine_Archive. where id=5;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (38.17 sec)

mysql>  select count(*) from Table_myisam_Compressed  where id=5;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (2 min 56.05 sec)

Do you belive it ? The archive engine seems to be faster than myisam table ! By the way, this is only a test, but an archive engine seem to be a handy engine especially when you don't get enough space on a device !

You'll try, won't you ?

See you in my next blog .

Byeeeee