From: Dan Nelson
Date: January 31 2007 4:32pm
Subject: Re: Database Layout (Design) Question
List-Archive: http://lists.mysql.com/mysql/204713
Message-Id: <20070131163241.GB82910@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Jan 31), sendmail-admin@stripped said:
> I'm trying to wrap my head around a performance problem our
> institution is having on our Mysql Server which hosts all of our logs
> from around campus.
> 
> Specifically our MailLogs tables.
> 
> What I'm having a hard time with is we have metadata such as:
> 
> CREATE TABLE mail00 (
(mail archive table)
> ) TYPE=MyISAM MAX_ROWS=99000000;
> 
> We might end up storing two to three gigs of logging data per day
> from our mail servers.
> 
> When we had the process setup to purge data out of this table that is
> older than 14 days, it would lock and take great deal of time process
> the request.

I say move to MySQL 5.1, and set up a range partitioned table, with one
partition per day.  Then before midnight you run a script that adds a
new partition covering the next day and drop the oldest one.

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

-- 
	Dan Nelson
	dnelson@stripped