List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 19 2005 4:26pm
Subject:Re: Logging Data: Should I use MyIsam or InnoDB?
View as plain text  
> > > > I have a curious issue here, maybe someone can help.
> > > >
> > > > I have a single process that inserts data into tables that contain
> > > > purely logging information.  This table is then searched by our Care
> > > > department to troubleshoot issues.  I am looking for the best way to
> > > > store this data, and the structure on the backend.
> > > >
> > > > There are 50 million inserts into table LOG a day.  The primary
> index
> > > > on the table is seconds from 1971.  I only need to keep 60 days
> worth
> > > > of data, and the table is only used for read purposes.  This is my
> > > > design criteria, but my problem is how to delete old data without
> > > > crashing the log writer that is atteched to the table.
> > >
> > > OK, how about this:
> > >
> > > Use MyISAM and MERGE tables.  Keep one table per day.  E.g.:
> > >
> > > log_2005_01_15
> > > log_2005_01_16
> > > log_2005_01_17
> > > log_2005_01_18
> > >
> > > etc.
> > >
> > > Use MERGE tables to give you the 60 day (and perhaps e.g. 14 day, 30
> > > day, 7 day, etc.) read views that you need, like so:
> > >
> > > CREATE TABLE log_view_7day (
> > >    ...
> > > ) TYPE=MERGE UNION=(
> > >    log_2005_01_12,
> > > You can then do all of your inserts from the log writer into the
> "today"
> > > table, and do your reads against the various MERGEs.
> > >
> > > Every day at exactly midnight, you would use ALTER TABLE (which is
> > > atomic) to redefine the UNION of the MERGE definition of the various
> > tables.
> >
> > Modifying metadata because you need a different view at your
> > data.
> >
> > Am I the only one to which this sound ugly?
>
> It sounds just fine to me. IMHO this is an excellent usage of MySQL
> features to solve a technical issue. The original poster wants to maintain
> only 60 days worth of data. He also needs really fast inserts. MyISAM can
> perform concurrent, non-blocking inserts so long as there are no deletion
> gaps in the table to which it is inserting. One solution to this is to
> create what is in effect a "view" that spans 60 days worth of data, each
> day's data in its own table. However, since views wont be officially
> available until later, a MERGE table is an effective substitute.

I understand the usage or MERGE here, but I have my doubts
at the "table per day" ...

Besides:
>There are 50 million inserts into table LOG a day

This doesn't sound right -- how can this be alright:
>The primary index on the table is seconds from 1971

?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

Thread
Logging Data: Should I use MyIsam or InnoDB?Thomas Lekai18 Jan
  • Re: Logging Data: Should I use MyIsam or InnoDB?Jeremy Cole18 Jan
  • Re: Logging Data: Should I use MyIsam or InnoDB?Martijn Tonies19 Jan
    • Re: Logging Data: Should I use MyIsam or InnoDB?Javier Armend├íriz19 Jan
    • Re: Logging Data: Should I use MyIsam or InnoDB?SGreen19 Jan
      • Re: Logging Data: Should I use MyIsam or InnoDB?Martijn Tonies19 Jan
RE: Logging Data: Should I use MyIsam or InnoDB?Thomas Lekai18 Jan
  • Re: Logging Data: Should I use MyIsam or InnoDB?Jeremy Cole18 Jan
RE: Logging Data: Should I use MyIsam or InnoDB?Thomas Lekai18 Jan