headers
Christopher Browne | 2 Apr 2005 01:18

Need to freeze for 1.1...

We're at the point now where we need to see about freezing 1.1 against
further changes so we can see about getting a release out.  There are
rather a lot of improvements, and it seems worthwhile to see about
taking advantage of some of them :-).

I am aware of two things still undergoing some effort:

 1.  Quoting handling - so that identifiers like "user" can be names of tables/columns

In CVS logs:
revision 1.29
date: 2005/03/30 15:24:13;  author: xfade;  state: Exp;  lines: +133 -10
First step of implementing slon_quote_ident. This adds code taken from PostgreSQL -HEAD. Our function
now quotes all identifiers known by your version of PostgreSQL.

 2.  Attempts to support Win32

 Evidently 1.1 may have moved things a bit backwards from 1.0, which
 is, alas, life.

Methinks we need to stick a stick in the sand, so that we address what
we can of this, in the next week, and then get a 1.1.0 release
candidate ready.  

There may be room for some changes to documentation; that's pretty
malleable.  There may be a bit of room for changes to scripts; that
can be pretty malleable too.  (I just realized, the other day, that
the new "slon.conf" file approach lends itself to setting up
slon.node-1, slon.node-2, ... slon.node-n files, and adding a
conf-file generator could be done easily even at a somewhat late
(Continue reading)

Permalink | Reply | 5 comments |
headers
Christopher Browne | 4 Apr 2005 04:55

Re: replicating entire database

"Garry Saddington" <garry@...> writes:
> Hello, I am new to this list and to Slony so excuse me if I ask
> simple questions. I was wondering if there is a way to specify an
> entire database to replicate rather than having to list each table
> specifically.

No, not as such.

You could run queries on information_schema.tables, looking for
entries where table_type is 'BASE TABLE'.  You would also presumably
want to replicate a subset of the sequences, too.

There is a script in the tools directory that will draw out this sort
of information.  Look for "build_env.pl".

What I find is that in systems that warrant replication, there will be
tables that oughtn't be replicated so that blindly replicating
everything turns out badly.  If it "warrants replication," there will
be some complexity to what is going on, and, very likely, temporary
"cruft" will head in as "messy situations" arise.  (If handled well,
the "messes" ought to be confined to their own namespaces/schemas so
that, once resolved, they can be dropped out cleanly...)
--

-- 
let name="cbbrowne" and tld="ca.afilias.info" in name ^ " <at> " ^ tld;;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)
Permalink | Reply | 1 comment |
headers
Hannu Krosing | 4 Apr 2005 14:12
Picon

Re: Execution Plan Problem

On T, 2005-03-29 at 11:22 -0500, Christopher Browne wrote:
> Rod Taylor wrote:
> 
> >
> >It takes me about 12 to 24 hours to copy the largest tables (that's
> >each, not collectively); so there are a number of blocks where it
> >rescans from transaction ID X to some larger number many times if I use
> >grouping sets less than 7000.

The real solution would probably be doing the initial copy without
indexes, especially the PK.

It can speed up things tremendously - I had a table, about 5 GB in size
with a 1.5GB index. the initial copy went on fairly well during the
first GB, which was copied in less than 30 min, but then it started to
progressively slow down. up to a point, where the whole copy took more
than 24 hours. The first time I tried to do the same thing between two
database on the same computer and it took over 48 hours.

when transferring the table manually, the dump/load cycle took about an
hour, with ~40 min being load from dump and 15 min building the index.

This was on Postgres 7.4.5, linux 2.4, dual Xeon 2.8GH, 4GB memory,
database on mirrored 72GB/15000rpm SCSI disks and pg_xlog on another
mirrored 36G/15k disk set.

--

-- 
Hannu Krosing <hannu@...>
Permalink | Reply | 2 comments |
headers
Rod Taylor | 4 Apr 2005 15:07
Picon

Re: Execution Plan Problem

On Mon, 2005-04-04 at 15:12 +0300, Hannu Krosing wrote:
> On T, 2005-03-29 at 11:22 -0500, Christopher Browne wrote:
> > Rod Taylor wrote:
> > 
> > >
> > >It takes me about 12 to 24 hours to copy the largest tables (that's
> > >each, not collectively); so there are a number of blocks where it
> > >rescans from transaction ID X to some larger number many times if I use
> > >grouping sets less than 7000.
> 
> The real solution would probably be doing the initial copy without
> indexes, especially the PK.

I did go without indexes for the initial copy but I do keep the PK in
place since Slony seems to prefer it. It may be appropriate for Slony
itself to remove and re-add the primary key around the copy, but indexes
on integer columns don't really get in the way too much (IO was
maintained at about 20MB/sec for the copy, which was reasonable for a
busy SAN).

There are lots of TB+ sized DBs using PostgreSQL out there, and Slony
needs to be able to deal with them reasonably.

So with respect, the real solution is to clean up Slony to handle an
active 200GB table without adding road blocks for itself. Dealing with a
variable group size (extend group size to transaction min/max boundaries
automatically) and query tuning for multiple sets are 2 of the items I
look forward to.

Here is a summary of issues I've run into and worked around. All have
(Continue reading)

Permalink | Reply | 1 comment |
headers
Hannu Krosing | 4 Apr 2005 15:23
Picon

Re: Execution Plan Problem

On E, 2005-04-04 at 09:07 -0400, Rod Taylor wrote:
> On Mon, 2005-04-04 at 15:12 +0300, Hannu Krosing wrote:
> > On T, 2005-03-29 at 11:22 -0500, Christopher Browne wrote:
> > > Rod Taylor wrote:
> > > 
> > > >
> > > >It takes me about 12 to 24 hours to copy the largest tables (that's
> > > >each, not collectively); so there are a number of blocks where it
> > > >rescans from transaction ID X to some larger number many times if I use
> > > >grouping sets less than 7000.
> > 
> > The real solution would probably be doing the initial copy without
> > indexes, especially the PK.
> 
> I did go without indexes for the initial copy but I do keep the PK in
> place since Slony seems to prefer it. It may be appropriate for Slony
> itself to remove and re-add the primary key around the copy,

That's what I had in mind.

> but indexes
> on integer columns don't really get in the way too much (IO was
> maintained at about 20MB/sec for the copy, which was reasonable for a
> busy SAN).

Still it seems, that once the index does not fit in postgresql's shared
memory, it slows down an order of magnitude, even if it still fits in
OS's disk cache. At least this seems to be the case on linux 2.4. 

I was able to speed up the initial copy of a table with 1.2GB index
(Continue reading)

Permalink | Reply | 0 comments |
headers
Hannu Krosing | 4 Apr 2005 15:27
Picon

Re: replicating entire database

On P, 2005-04-03 at 22:55 -0400, Christopher Browne wrote:
> "Garry Saddington" <garry@...> writes:
> > Hello, I am new to this list and to Slony so excuse me if I ask
> > simple questions. I was wondering if there is a way to specify an
> > entire database to replicate rather than having to list each table
> > specifically.
> 
> No, not as such.
...
> What I find is that in systems that warrant replication, there will be
> tables that oughtn't be replicated so that blindly replicating
> everything turns out badly.

The main (only ?) use of blindly replicating whole databasse is for
having a standby database to be used in case of failure on master.

For this, one could use the WAL-shipping replication mode available in
postgresql itself starting from version 8.0.

This should work well together with slony. 

--

-- 
Hannu Krosing <hannu@...>
Permalink | Reply | 0 comments |
headers
Cezary Bołdak | 6 Apr 2005 09:40
Picon

Re: strange failover problem

In reponse to 
http://gborg.postgresql.org/pipermail/slony1-general/2005-January/001493.html

I have similar problem with the failover problem described as above.

I have tried another thing (it also crashes):

*** 4 nodes in a cascade: 1<-2<-3<-4

*** 'postmaster' running locally under 'postgres' account and using the 
Unix sockets ('postmaster -D /var/local/pgsql/data')

*** created 4 databases as (from 'postgres' account also):
    createdb stats1
    createlang plpgsql stats1
    psql stats1 << _EOF_
        CREATE TABLE stats_test(ID serial PRIMARY KEY, name varchar(32) );
    _EOF_
 and son on for 'stats2', 'stats3', 'stats4'

*** then:
slonik << _EOF_
    cluster name = stats;
    node 1 admin conninfo = 'dbname=stats1';
    node 2 admin conninfo = 'dbname=stats2';
    node 3 admin conninfo = 'dbname=stats3';
    node 4 admin conninfo = 'dbname=stats4';

    init cluster (id=1, comment='Master node');
(Continue reading)

Permalink | Reply | 0 comments |
headers
Cezary Bołdak | 6 Apr 2005 09:44
Picon

Re: strange failover problem

In reponse to 
http://gborg.postgresql.org/pipermail/slony1-general/2005-January/001493.html 

I have similar problem with the failover problem described as above.

I have tried another thing (it also crashes):

*** 4 nodes in a cascade: 1<-2<-3<-4

*** 'postmaster' running locally under 'postgres' account and using the 
Unix sockets ('postmaster -D /var/local/pgsql/data')

*** created 4 databases as (from 'postgres' account also):
   createdb stats1
   createlang plpgsql stats1
   psql stats1 << _EOF_
       CREATE TABLE stats_test(ID serial PRIMARY KEY, name varchar(32) );
   _EOF_
and son on for 'stats2', 'stats3', 'stats4'

*** then:
slonik << _EOF_
   cluster name = stats;
   node 1 admin conninfo = 'dbname=stats1';
   node 2 admin conninfo = 'dbname=stats2';
   node 3 admin conninfo = 'dbname=stats3';
   node 4 admin conninfo = 'dbname=stats4';

   init cluster (id=1, comment='Master node');
(Continue reading)

Permalink | Reply | 1 comment |
headers
Fernando Schapachnik | 8 Apr 2005 16:11
Picon

Re: Failing over

Hate to reply to my own message, but as I have received no replies, I've 
switched to PG 7.4.7, to obtain the same behavior. Has anybody managed to 
successfully failover on RHES 3?

Thanks.

Fernando.

En un mensaje anterior, Fernando Schapachnik escribió:
> I have a little bit of extra info:
> 
> En un mensaje anterior, Fernando Schapachnik escribió:
> > <stdin>:6: NOTICE:  failedNode: set 2 has no other direct receivers - move now
> > INFO   localListenThread: got restart notification - signal scheduler
> > 
> > And slonik never returns.
> 
> It keeps polling the local DB:
> 
> select listenerpid from "pg_catalog".pg_listener     where relname = 
> '_CLUSTERNAME_Restart' and listenerpid <> 10095;
> 
> What else should be there?
Permalink | Reply | 0 comments |
headers
Marc G. Fournier | 11 Apr 2005 07:52
Favicon

Quick "performance" question ...


Does anyone have any #s on how fast Slony works?

On all my current uses, there aren't a heavy number of 
'insert/update/delete's happening, so of course, it never falls behind ...

If one were to have a 'write master' with a bunch of 'read subscribers', I 
imagine there is a point where the writes to the subscribers would start 
to get delayed, right?  Has anyone hit such a limit?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@...           Yahoo!: yscrappy              ICQ: 7615664
Permalink | Reply | 6 comments |

Gmane