by Kristian Nielsen
Is there anyone using partitioned archive tables in MySQL 5.1 for storing logs (or other voluminous data)?
Storing large amounts of logs in a relational database can bring special challenges. Logs can take up huge amounts of space on disk, and while disk space is cheap, disk I/O can be expensive, performance-wise. But many logs compress really well, and for this the MySQL archive storage engine (insert-only, no indexes, gzip'ed storage) can be used.
You often want to scan across a few hours or days worth of logs, and indexes are poor for this purpose as the large number of disk seeks can kill performance. And full table scans of years of logs is not all that much fun either. For this, table partioning (supported in MySQL 5.1) is very useful. Store each day or week of logs in a separate table partition, partitioned on day (maybe use the 5.1 event sceduler, or simply a cron job, to add new partitions automatically). Then efficient full-partition scans of just the days or weeks in question can be used.
So why not combine both approaches? MySQL 5.1 supports partitioned archive tables just fine:
http://kristiannielsen.livejournal.com/1589.html
Edited 2 time(s). Last edit at 09/13/2006 08:18AM by Edwin DeSouza.