MySQL Forums
Forum List  »  Partitioning

Partitioned Archive Tables for storing Logs
Posted by: Edwin DeSouza
Date: September 12, 2006 03:57PM

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:

Edited 2 time(s). Last edit at 09/13/2006 08:18AM by Edwin DeSouza.

Options: ReplyQuote

Written By
Partitioned Archive Tables for storing Logs
September 12, 2006 03:57PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.