MySQL Forums
Forum List  »  Merge Storage Engine

Re: Merge tables and indexes
Posted by: Vinayak Javaly
Date: June 02, 2006 01:28PM

Yes, I did mean separate physical machines. I cannot find the info about the DATADIR in the other threads.

Please indulge me -- I would like to "take a step back" and state the fundamental problem I'm trying to solve. I am designing the server environment to support a rapidly growing database (200-300 million rows inserted per year into 1 logical table). After the insert, the data will only be read, and it needs to be available for many years. The application needs to be able to access this data as a single object (almost always accessed by a BIGINT primary key). Here's my rough plan (each MySQL server instance on a separate physical machine):
1) 2 Masters (M1 & M2) responsible for all inserts (auto_increment_increment and auto_increment_offset used to insure unique primary keys)
2) Master M1 replicates to 2 Slaves (S1a & S1b). Master M2 also replicates to the same 2 Slaves (S1a & S1b). Initially, Slaves S1a & S1b each contain all the data, AND the application will read only from these Slaves.
3) When Slaves S1a & S1b reach a certain limit (not yet determined), do the following:
A) Setup 2 additional Slaves (S2a & S2b)
B) Stop and disable replication from Masters M1 & M2 to Slaves S1a & S1b. Slaves S1a & S1b will be frozen (no more writes).
C) Truncate data from Masters M1 & M2
D) Start replication from Master M1 to Slaves S2a & S2b. Also, start replication from Master M2 to Slaves S2a & S2b.
E) Setup 2 new Merge instances (G1 & G2). G1 merges data from tables in Slaves S1a & S2a. G2 merges data from tables in Slaves in S1b & S2b. The application now reads only from these 2 new Merge instances.
4) When Slaves S2a & S2b reach their limit, repeat steps 3A-D, and add new tables to Merge instances G1 & G2.
5) Repeat step 4 as needed.

This is the reason for my various questions. Please comment on any aspect of my plan. Any advice would be greatly appreciated.

Thank you in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
7447
June 01, 2006 02:13PM
5089
June 01, 2006 06:48PM
5048
June 02, 2006 08:36AM
4499
June 02, 2006 09:43AM
Re: Merge tables and indexes
4226
June 02, 2006 01:28PM
4078
June 06, 2006 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.