[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?
Hmm, something like that, but Dumbo does not look truly "fell" ;-)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.