List:General Discussion« Previous MessageNext Message »
From:Charles Brown Date:January 31 2007 3:42pm
Subject:RE: Database Layout (Design) Question
View as plain text  
Hello, I think your major contributing problem is the database engine
that you are using, MYISAM. For this type of processing, I would highly
recommend you use INNODB to allow maximum concurrency and minimize your
outage.  With all things considered, your processing by all estimates,
whether you are on DB2 Mainframe or Oracle or IMS. Deleting 2 gig of
data from a table via an SQL is considered massive. Having said that,
there are other methods of completing this task of purging/archiving
data from a table with fewer outages.

Here is what you do: 
1. Unload the table into two flat files. The records that are less 14
days go into one flat file while records older than 14 days go into
another.

2. At the completion of the unload process.  LOAD REPLACE data using
flat file that contains data less than 14days.

Please email me if you need help with SQL statements, UNLOAD, or LOAD
data into the table.   


-----Original Message-----
From: Kishore Jalleda [mailto:kjalleda@stripped] 
Sent: Wednesday, January 31, 2007 8:51 AM
To: sendmail-admin@stripped
Cc: mysql@stripped
Subject: Re: Database Layout (Design) Question

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
<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
>
>
********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************
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