If there is one thing that a DBA or data warehouse architect can
count on, it is that data volumes will increase while budgets
will decrease.
This is why MySQL 5.1 and its partitioning capabilities are so
interesting. I’m going to demonstrate how you can build a
small/medium-sized data warehouse or data mart (1-10 TB range) on
a shoe-string budget.
the mission
I decided to convert a relatively large statistics table (750m
rows, 140GB in size in about 10 partitions) on a test machine
from MyISAM to the Archive storage engine. After a long
conversion process, my data, on disk, ended up being about 21GB,
for an impressive compression ratio of 6.7:1.
Prior to MySQL 5.1, one of the drawbacks to the archive storage
engine was that you could not index it; however, with partition
pruning, you can get yourself a “free” index on a large archive
table by splitting it into date-based chunks, whether by …