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
Please look at
On 1/31/07, sendmail-admin@stripped <sendmail-admin@stripped>
> 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
> than 14 days, it would lock and take great deal of time process the
> 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
> 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
> . This will eventually cause a problem when we try to develop a program
> scan the logs, it will need to scan through all 14 tables instead of just
> Is there a better way that mitigates the performance and flexibility?
> Or just a better way in general?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1