Re: Merge Tables and Replication

[dpgirago]

> [EMAIL PROTECTED] wrote:

>> 
>> Does this sound about right? Anybody see any road hazards? If not, and
>> this line of thinking is reasonable, should the DB with the older 
records
>> also be replicated so that when a new old records table needs to be
>> created, I don't have to repeat everything on the slave?
>> 

> Most of the problems documented here
>  and
> some of it here
> 

> The problems that stand out

> - A MERGE table cannot maintain uniqueness constraints over the
>   entire table.
>
> - Key reads are slower. When you read a key, the MERGE storage engine
>   needs to issue a read on all underlying tables to check which one
>   most closely matches the given key. To read the next key, the MERGE
>   storage engine needs to search the read buffers to find the next
>   key.
>
> -- 
> raj shekhar

Thanks, raj, for underscoring the key reads issue.

That might be a deal breaker...

David

Re: Merge Tables and Replication

[Raj Shekhar]

[EMAIL PROTECTED] wrote:

>  
> Does this sound about right? Anybody see any road hazards? If not, and
> this line of thinking is reasonable, should the DB with the older records
> also be replicated so that when a new old records table needs to be
> created, I don't have to repeat everything on the slave?
> 

Most of the problems documented here
 and
some of it here


The problems that stand out

 - A MERGE table cannot maintain uniqueness constraints over the
   entire table.

 - Key reads are slower. When you read a key, the MERGE storage engine
   needs to issue a read on all underlying tables to check which one
   most closely matches the given key. To read the next key, the MERGE
   storage engine needs to search the read buffers to find the next
   key.

-- 
raj shekhar
facts: http://rajshekhar.net | opinions: http://rajshekhar.net/blog
Yoda of Borg are we: Futile is resistance. Assimilate you, we will
'Borg? Sounds Swedish.' - Lily, Star Trek First Contact


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Merge Tables and Replication

[dpgirago]

The recent thread about merge tables led me to explore using them with 
replication.

I see bug reports for the 4.0.** series regarding replication and merge 
tables, and I've read the manual about merge table problems in the 5.0.** 
series ( we are using 5.0.22 ), but I'm curious if anyone has any 
experiences to share?

We've had replication working well now for almost 4 years, and as many 
tables are approaching 2 million records, it would appear that using a 
merge table design might be one way to accomplish a type of incremental 
backup. 

By this I mean, since all our data are static ( only inserts and selects 
), the older data could be moved to a separate DB and only the "active" DB 
with the newest tables/records and the merge tables could be mysqldumped 
each night.
 
Does this sound about right? Anybody see any road hazards? If not, and 
this line of thinking is reasonable, should the DB with the older records 
also be replicated so that when a new old records table needs to be 
created, I don't have to repeat everything on the slave?

It always makes me uneasy, fiddling with something that is working... 

Thanks, 

--David.