List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:January 18 2005 8:30pm
Subject:Re: Logging Data: Should I use MyIsam or InnoDB?
View as plain text  
Hi Thomas,

> Thanks, this is what I was originally thinking of, but how I am
> getting rid of the data in log_view_today?  OR, are you saying that
> log_view_today is a merge table for only the current day?  That table
> def is defined every night?  Then I would go about dropping
> everything in whatever fashion I need.

Basically, at midnight you would do a sequence like this:

CREATE TABLE log_2005_01_19 ( ... );
ALTER TABLE log_view_today UNION=(log_2005_01_19);
ALTER TABLE log_view_7day UNION=(..., log_2005_01_19);

etc. etc.

You could actually create the tables beforehand, and only do the ALTER 
TABLEs themselves at midnight.

Note that this procedure has a race condition in that, depending on when 
the table actually switches over, you may have some records on either 
side of the split that don't belong.  You can always move those back 
manually with something like:

INSERT INTO log_2005_01_18 SELECT * from log_2005_01_19 WHERE datefield 
< "2005-01-19 00:00:00";
DELETE FROM log_2005_01_19 WHERE datefield < "2005-01-19 00:00:00";

or

INSERT INTO log_2005_01_19 SELECT * from log_2005_01_18 WHERE datefield 
 >= "2005-01-19 00:00:00";
DELETE FROM log_2005_01_18 WHERE datefield >= "2005-01-19 00:00:00";

In some cases it might be easier to do the switch always e.g. 5 seconds 
before midnight, so that any records falling on the wrong side of the 
edge will always be on the same side.  That makes things easier sometimes.

> When I recreate the merge table for just the current day, don't I
> have to drop the merge table, or it just gets recreated
> automatically.  I am not sure why you reference "atomic" on ALTER
> TABLE . . . , if there is a log writer attached to that table, won't
> I have to wait for a lock?  What do you mean by atomic?  I understand
> the term atomic transaction, just not sure of your context to this
> example.

What I mean is, you can use ALTER TABLE to change the definition (e.g. 
which tables it contains) of the MERGE table.  This happens atomically 
(no INSERTs will error, and no records could conceivably be "split" by 
the sudden change).

Does that all make sense?

Regards,

Jeremy

-- 
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
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