List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 31 2007 4:32pm
Subject:Re: Database Layout (Design) Question
View as plain text  
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
Thread
Database Layout (Design) Questionsendmail-admin31 Jan
  • Re: Database Layout (Design) QuestionKishore Jalleda31 Jan
    • RE: Database Layout (Design) QuestionCharles Brown31 Jan
    • Re: Database Layout (Design) Questionsendmail-admin1 Feb
  • Re: Database Layout (Design) QuestionDan Nelson31 Jan