Обсуждение: [OT] Slony (initial) Replication - Slow
I'm just wetting my hands with slony and during the setup of the slave, I did and dump and restore of the master DB to the Slave DB. However during the startup of slony, I noticed that it issues a truncate command to the (to be) replicated table. Hence, this means that there's no such need for me to do a dump/restore in the 1st place. can someone confirm this? It _is_ taking long time (for slony) to do the \copy (~60GB in multiple tables being replicated, including (on the fly) index creation)
On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote: > I'm just wetting my hands with slony and during the setup of the slave, > I did and dump and restore of the master DB to the Slave DB. Nope, you don't need to do that. You need a copy of the _schema_ on the target machine. But slony will remove all the contents and build the replica anew. > can someone confirm this? It _is_ taking long time (for slony) to do the > \copy (~60GB in multiple tables being replicated, including (on the fly) > index creation) It takes approximately the same time as it would to do a psql -h [remotehost] -f dumpfile.sql restore (i.e. copying the entire data contents across the network). A
ajs@crankycanuck.ca (Andrew Sullivan) writes: > On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote: >> I'm just wetting my hands with slony and during the setup of the slave, >> I did and dump and restore of the master DB to the Slave DB. > > Nope, you don't need to do that. You need a copy of the _schema_ on the > target machine. But slony will remove all the contents and build the > replica anew. Right. The argument for doing so is that this approach (TRUNCATE + COPY on the subscriber) is the only way that Slony-I can be certain that it has all data on the subscriber that was on the provider. That way, it doesn't need to trust any dodgy claims that "oh, I copied all the data - honest!" >> can someone confirm this? It _is_ taking long time (for slony) to do the >> \copy (~60GB in multiple tables being replicated, including (on the fly) >> index creation) > > It takes approximately the same time as it would to do a psql -h > [remotehost] -f dumpfile.sql restore (i.e. copying the entire data > contents across the network). In 1.2.x, it should be a little bit quicker than the "pg_dump | psql" approach as all index generation takes place together for each table. When you do a restore of a pg_dump, the indexes are generated in a somewhat arbitrary order, where there may be a separation in time between when different indexes on a given table get created. In contrast, Slony-I regenerates all the indexes on a given table in a "one swell foop" fashion, which might be expected to allow cacheing to provide a bit better performance than you could get with "pg_dump | psql". -- "cbbrowne","@","cbbrowne.com" http://linuxdatabases.info/info/emacs.html Microsoft Outlook: Deploying Viruses Has Never Been This Easy!
Chris Browne wrote: > In contrast, Slony-I regenerates all the indexes on a given table in a > "one swell foop" fashion, which might be expected to allow cacheing to > provide a bit better performance than you could get with "pg_dump | > psql". I'm left wondering whether the swoop is actually fell. I imagine most people would not survive an elephant herd, or slony, turning fell. Now, swooping elephants must be an interesting sight. If pigs can fly ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote: > swooping elephants must be an interesting sight. If pigs can fly ... Is this what you had in mind? http://www.amoeba.com/dynamic-images/blog/dumbo.gif A
Andrew Sullivan wrote: > On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote: > > swooping elephants must be an interesting sight. If pigs can fly ... > > Is this what you had in mind? > > http://www.amoeba.com/dynamic-images/blog/dumbo.gif Hmm, something like that, but Dumbo does not look truly "fell" ;-) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.