List:General Discussion« Previous MessageNext Message »
From:sendmail-admin Date:February 1 2007 1:50pm
Subject:Re: Database Layout (Design) Question
View as plain text  
This isn't exactly what I was looking for, but it works like a charm for 
both my needs and the sys admins.

Go figure everyone is happy now!

Many Thanks!

-Tyler

Kishore Jalleda wrote:
> The delete would definitely depend upon the size of the record set being 
> deleted, anyway assuming I comprehended your situation correctly ,I 
> would suggest using the Merge storage engine for your needs , and keep 
> every single day of data in a seperate MyISAM table, and merge all those 
> tables together into a single merge table, so when you want to do any 
> maintenance on any data older than x days just alter the table and take 
> that <mailxx> table out of the merge table , delete it compress it, do 
> anyhting with it and then just add a other one (you could easily run a 
> nightly cron job for this)
> 
> Please look at 
> http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html
> 
> Kishore Jalleda
> 
> On 1/31/07, *sendmail-admin@stripped 
> <mailto:sendmail-admin@stripped>* 
> <sendmail-admin@stripped 
> <mailto:sendmail-admin@stripped>> wrote:
> 
>     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 (
>        host varchar(32) default NULL,
>        fromMTA varchar(44) default NULL,
>        nextMTA varchar(44) default NULL,
>        messageID varchar(44) default NULL,
>        messageID2 varchar(44) default NULL,
>        sender varchar(80) default NULL,
>        recipient varchar(120) default NULL,
>        recipient2 varchar(120) default NULL,
>        date date default NULL,
>        time time default NULL,
>        program varchar(44) default NULL,
>        ACTION varchar(44) default NULL,
>        detail varchar(120) default NULL,
>        msg text,
>        seq int(10) unsigned NOT NULL auto_increment,
>        PRIMARY KEY  (seq),
>        KEY host (host),
>        KEY sender (sender),
>        KEY recipient (recipient),
>        KEY MessageID (messageID),
>        KEY seq (seq),
>        KEY time (time),
>        KEY date (date),
>        KEY ACTION (ACTION),
>        KEY messageID2 (messageID2),
>        KEY fromMTA (fromMTA)
>     ) 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.
> 
>     My question is, is the structure of my table the problem or is it
>     just the
>     size of the data that is just going to take that long due to Disk IO?
> 
>     This isn't a table structure that I came up with, I'm just looking to
>     optimize the performance of the server.
> 
>     Currently around midnight the sysadmin currently drops table 13,
>     then moves
>     12 -> 13, 11 -> 12, etc... and creates a 00.
> 
>     All of this is because it takes to long to purge out one days worth
>     of data
>     .  This will eventually cause a problem when we try to develop a
>     program to
>     scan the logs, it will need to scan through all 14 tables instead of
>     just one.
> 
>     Is there a better way that mitigates the performance and flexibility?
> 
>     Or just a better way in general?
> 
>     Thanks,
> 
>     -Tyler
> 
> 
>     --
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 
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