headers
Henry | 1 Apr 2008 08:33
Picon

Feature request for next slony version: initial replication of large DBs

Good morning all,

I seem to recall (I think it was touched on by someone else) that this
aspect *might* be addressed in a near-future version of slony:

Given that some users of slony have large (your definition of large will
vary, but let's say DBs in excess of 100GB) databases, and that the
initial replication of a DB can take weeks, would it not be a great idea
to have a more efficient initial 'copy' mode?  ie, either allow a user to
dump/restore to all slaves, then start replication from that point
(without truncating/copying/etc), or, build this functionality into slony
so it does the dump/restore, but does not then truncate all tables and
start from scratch?

For smaller DBs this doesn't present a problem, but for larger DDs coupled
with LARGE clusters, this is a major problem.

Regards
Henry
Permalink | Reply | 7 comments |
headers
Cyril SCETBON | 1 Apr 2008 11:26
Picon

Re: Feature request for next slony version: initial replication of large DBs

As said Christopher Brown new commands are coming : CLONE PREPARE and 
CLONE FINISH to bypass this mecanism

see http://lists.slony.info/pipermail/slony1-commit/2008-January/002145.html

Henry wrote:
> Good morning all,
>
> I seem to recall (I think it was touched on by someone else) that this
> aspect *might* be addressed in a near-future version of slony:
>
> Given that some users of slony have large (your definition of large will
> vary, but let's say DBs in excess of 100GB) databases, and that the
> initial replication of a DB can take weeks, would it not be a great idea
> to have a more efficient initial 'copy' mode?  ie, either allow a user to
> dump/restore to all slaves, then start replication from that point
> (without truncating/copying/etc), or, build this functionality into slony
> so it does the dump/restore, but does not then truncate all tables and
> start from scratch?
>
> For smaller DBs this doesn't present a problem, but for larger DDs coupled
> with LARGE clusters, this is a major problem.
>
> Regards
> Henry
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general@...
> http://lists.slony.info/mailman/listinfo/slony1-general
(Continue reading)

Permalink | Reply | 5 comments |
headers
Andreas Kostyrka | 1 Apr 2008 12:02

Re: Feature request for next slony version: initial replication of large DBs

One thing that should be also pointed out, that while a
pg_dump/pg_restore might be faster than slony initial copy, both use
basically the same mechanisms (COPY FROM/TO STDIN/STDOUT), so I'd guess
that you wouldn't shave to much of the initial replication time via
pg_dump. (Well, slony does need to get some locks to get going when I
remember right, which means that pg_dump/pg_restore has the potential to
be faster anyway.) OTOH, a filesystem level dump, can be, especially
when using LVM snapshots be produced rather quickly :)

Andreas

Am Dienstag, den 01.04.2008, 11:26 +0200 schrieb Cyril SCETBON:
> As said Christopher Brown new commands are coming : CLONE PREPARE and 
> CLONE FINISH to bypass this mecanism
> 
> see http://lists.slony.info/pipermail/slony1-commit/2008-January/002145.html
> 
> Henry wrote:
> > Good morning all,
> >
> > I seem to recall (I think it was touched on by someone else) that this
> > aspect *might* be addressed in a near-future version of slony:
> >
> > Given that some users of slony have large (your definition of large will
> > vary, but let's say DBs in excess of 100GB) databases, and that the
> > initial replication of a DB can take weeks, would it not be a great idea
> > to have a more efficient initial 'copy' mode?  ie, either allow a user to
> > dump/restore to all slaves, then start replication from that point
> > (without truncating/copying/etc), or, build this functionality into slony
> > so it does the dump/restore, but does not then truncate all tables and
(Continue reading)

Permalink | Reply | 1 comment |
headers
Henry | 1 Apr 2008 12:27
Picon

Re: Feature request for next slony version: initial replication of large DBs


On Tue, April 1, 2008 11:26 am, Cyril SCETBON wrote:
> As said Christopher Brown new commands are coming : CLONE PREPARE and
> CLONE FINISH to bypass this mecanism
>
> see
> http://lists.slony.info/pipermail/slony1-commit/2008-January/002145.html

Excellent.  This will save so much time, you have no idea.

Regards
Henry
Permalink | Reply | 0 comments |
headers
Henry | 1 Apr 2008 12:28
Picon

Re: Feature request for next slony version: initial replication of large DBs


On Tue, April 1, 2008 12:02 pm, Andreas Kostyrka wrote:
> One thing that should be also pointed out, that while a
> pg_dump/pg_restore might be faster than slony initial copy, both use
> basically the same mechanisms (COPY FROM/TO STDIN/STDOUT), so I'd guess
> that you wouldn't shave to much of the initial replication time via
> pg_dump. (Well, slony does need to get some locks to get going when I
> remember right, which means that pg_dump/pg_restore has the potential to
> be faster anyway.) OTOH, a filesystem level dump, can be, especially
> when using LVM snapshots be produced rather quickly :)

Exactly - dumping/restoring is but one option.  For example, on our
cluster slaves, we don't wast time manually installing the OS on each node
- we install once, them dump that image onto slaves...
Permalink | Reply | 0 comments |
headers
Jacques Caron | 2 Apr 2008 17:30
Favicon

Re: sl_log indexes

Hi Christopher,

At 17:37 31/03/2008, Christopher Browne wrote:
>The introduction of these partial indices dates back to the following
>discussion thread on pgsql-hackers:
>http://archives.postgresql.org/pgsql-hackers/2006-06/msg01516.php
>
>At one point, we had this as a second index:
>  create index sl_log_1_idx2 on  <at> NAMESPACE <at> .sl_log_1
>         (log_xid  <at> NAMESPACE <at> .xxid_ops);
>
>Unfortunately, it was apparently leading to problems in that data
>sourced from different origins might have xxid values of varying sign.
>
>So, in lieu of that, I introduced code that would generate a
>per-origin partial index, which would necessarily not suffer from the
>rollover problem that sl_log_1_idx2 would run into.
>
>It is quite likely that the partial indices will be preferred, as the
>first column in sl_log_1_idx1 doesn't discriminate much.

OK, so now I understand why there are partial indexes, but these 
partial indexes are redundant with quite a bit of the full index. The 
next logical step would be to remove the full index, however it seems 
there are some cases where it's still needed (i.e. queries with a 
where log_origin = node for which there is no partial index), at 
least some DELETEs (which my wild guess is that they actually always 
return 0 rows?).

So, possible options:
(Continue reading)

Permalink | Reply | 20 comments |
headers
Craig James | 3 Apr 2008 03:52
Favicon

Version mismatch? It's a new system...

I get the following error when I try to create a new Slony node:

  ERROR: Slonik version: 1.2.13 != Slony-I version in PG build 1.2.9

This is on a brand new server.  Postgres is 8.3.0, Slony was built from slony1-1.2.13.tar.bz2.  There has
never been any other version of Slony installed on this system.  I am running everything from this system,
including the slon daemons ('tho I haven't gotten that far yet).

The other system (the one that will become the master database) has had Slony 1.2.9 installed, but I did an
"uninstall node" on it a long time ago.

I can only assume that the 1.2.9 is coming from the old system, but where?  How do I get rid of it?

Thanks!
Craig
Permalink | Reply | 18 comments |
headers
Craig James | 3 Apr 2008 04:30
Favicon

Re: Version mismatch? It's a new system...

Earlier I wrote:
> I get the following error when I try to create a new Slony node:
> 
>  ERROR: Slonik version: 1.2.13 != Slony-I version in PG build 1.2.9
> 
> This is on a brand new server.  Postgres is 8.3.0, Slony was built from 
> slony1-1.2.13.tar.bz2.  There has never been any other version of Slony 
> installed on this system.  I am running everything from this system, 
> including the slon daemons ('tho I haven't gotten that far yet).

I have since double checked everything.  There is no Slony schema on either database:

  select * from pg_tables where tableowner = 'postgres'

shows now sl_* tables at all.  So there is no old version of Slony.  This seems to be coming from the Slony 1.2.13
installation itself.

Can anyone shed any light on this?  In case anyone is still awake, I was hoping to get the thing started tonight.

Thanks.
Craig
Permalink | Reply | 17 comments |
headers
Jeff Frost | 3 Apr 2008 04:33
Gravatar

Re: Version mismatch? It's a new system...

On Wed, 2 Apr 2008, Craig James wrote:

> I have since double checked everything.  There is no Slony schema on either 
> database:
>
> select * from pg_tables where tableowner = 'postgres'
>
> shows now sl_* tables at all.  So there is no old version of Slony.  This 
> seems to be coming from the Slony 1.2.13 installation itself.
>
> Can anyone shed any light on this?  In case anyone is still awake, I was 
> hoping to get the thing started tonight.

Craig,

Look in your postgresql libdir for xxid.so.  That could be in /usr/lib/pgsql 
or /usr/lib64/pgsql or /usr/local/pgsql/lib, etc depending on how it was 
installed.  BTW, if this is a brand new server, why not start with postgresql 
8.3.1 instead of 8.3.0?

--

-- 
Jeff Frost, Owner 	<jeff@...>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954
Permalink | Reply | 16 comments |
headers
Craig James | 3 Apr 2008 04:48
Favicon

Re: Version mismatch? It's a new system...

Jeff Frost wrote:
> On Wed, 2 Apr 2008, Craig James wrote:
> 
>> I have since double checked everything.  There is no Slony schema on 
>> either database:
>>
>> select * from pg_tables where tableowner = 'postgres'
>>
>> shows now sl_* tables at all.  So there is no old version of Slony.  
>> This seems to be coming from the Slony 1.2.13 installation itself.
>>
>> Can anyone shed any light on this?  In case anyone is still awake, I 
>> was hoping to get the thing started tonight.
> 
> Craig,
> 
> Look in your postgresql libdir for xxid.so.  That could be in 
> /usr/lib/pgsql or /usr/lib64/pgsql or /usr/local/pgsql/lib, etc 
> depending on how it was installed.

But what am I looking for?  There are no other slony libraries -- this system has never had any other version of
Slony on it.

If I remove xxid.so, and everything else slony-related in /usr/local/pgsql/*, INCLUDING all of the sql
files in /usr/local/pgsql/share, and then

  cd slony-1.2.13
  make install

then all the files are restored, including xxid.so.  But the problem persists -- same error.
(Continue reading)

Permalink | Reply | 15 comments |

Gmane