List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:June 19 2001 11:35pm
Subject:Back again to MERGEs...
View as plain text  
Hi!

First, please email questions like this to internals@stripped;
You have a better change to get an answer from this than from me (as I
may be unreachable some days).

> Hi,
> 
> I'm trying to realize fail-safe billing database mirroring from other =
> set of tables (DBFs) and found nice way to do that:
> 1) Convert all data into "temporary" tables named like regular tables =
> with '_tmp' suffix;
> 2) Issue "FLUSH TABLES" query and compress newly created tables;
> 3) Provide single 'RENAME TABLE ...' to rename all existing tables to =
> ones with '_old' suffix and rename newly generated tables with '_tmp' =
> suffix to regular (without suffix).
> 
> Conversion working on daily basis, and most problem is to make it some =
> faster (it works and lives on P5-200 with 64 RAM). Common data for two =
> fiscal monthes (current and last closed) is about 1 million records for =
> each month. Conversion time runs from 15 minutes at new month began to =
> about 30 minutes at the end of a month.
> 
> Visible soultion is to not convert closed month on daily basis in =
> current month, just convert it last time when month is just closed. To =
> realize it I'm trying to create MERGE table which hold all converted =
> month, and convert only current (or, closed and current at "month is =
> just closed" situation) month's datas.
> 
> The problem which I figured out is just renaming real tables owned by
> MERGE. I can't guarantee what no queries to real (not temporary) tables
> will appear while I've renaming tables, and renaming tables owned by
> MERGE can bring many problems like incorrect datas. Using LOCK TABLE 
> <merge name> READ is not possible because RENAME can't be issued until
> MERGE's member table(s) are locked. :(

> Can you say something to help me?

Can't you for now do:

ALTER TABLE merge_table UNION=(empty_copy_of_table);

RENAME ...

ALTER TABLE merge_table UNION=(old-union)

We will look in 4.0 to see what we can do to handle MERGE tables
better in this case.

Regards,
Monty
Thread
Back again to MERGEs...Michael Widenius20 Jun
Re: Back again to MERGEs...Paul Cadach20 Jun
  • Re: Back again to MERGEs...Michael Widenius20 Jun