5 messages in com.mysql.lists.mysqlRe: Database Layout (Design) Question
FromSent OnAttachments
send...@spiresfamily.com31 Jan 2007 06:38 
Kishore Jalleda31 Jan 2007 06:50 
Brown, Charles31 Jan 2007 07:41 
Dan Nelson31 Jan 2007 08:32 
send...@spiresfamily.com01 Feb 2007 05:49 
Subject:Re: Database Layout (Design) Question
From:Dan Nelson (dnel@allantgroup.com)
Date:01/31/2007 08:32:18 AM
List:com.mysql.lists.mysql

In the last episode (Jan 31), send@spiresfamily.com 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 dnel@allantgroup.com