Slony1 PostgreSQL Replication - Slony1-1.1.0: Triggers are making other things

Author

Slony1-1.1.0: Triggers are making other things

Aldor

2005-09-24, 1:23 pm

Hi,

I don't really know how Slony works internally but I know that it uses
some triggers for getting out new transactions.

The config of my Slony cluster:

--- CONFIG START ---

cluster name = $CLUSTERNAME;

node 1 admin conninfo = 'dbname=$MASTERDBNAM
E host=$MASTERHOST
port=$MASTERPORT user=$REPLICATIONUSE
R password=$MASTERPASS
';
node 2 admin conninfo = 'dbname=$SLAVE1DBNAM
E host=$SLAVE1HOST
port=$SLAVE1PORT user=$REPLICATIONUSE
R password=$SLAVE1PASS
';

init cluster ( id=1, comment = $CLUSTERNAME);

create set (id=1, origin=1, comment=$CLUSTERTABL
E1);
set add table (set id=1, origin=1, id=1, fully qualified name =
'public.[table]', comment=$CLUSTERTABL
E1);

--- CONFIG END ---

Of course I have also other tables in the same database, which I don't
want to cluster. When making big data dumps into that tables I noticed
that since I'm using Slony on that database, buf for another table, the
big dumps are getting 5-10 times slower. The only explanation I have is
that it has maybe something to do with Slony - because nothing else have
been changed. May be Slony do in each row dump of the process any things
which can slow down the process - even if Slony should not care about
this other table?!

Thanks,

Aldor

Christopher Browne

2005-09-24, 8:24 pm

Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
> I don't really know how Slony works internally but I know that it uses
> some triggers for getting out new transactions.
>
> Of course I have also other tables in the same database, which I
> don't want to cluster. When making big data dumps into that tables I
> noticed that since I'm using Slony on that database, buf for another
> table, the big dumps are getting 5-10 times slower. The only
> explanation I have is that it has maybe something to do with Slony -
> because nothing else have been changed. May be Slony do in each row
> dump of the process any things which can slow down the process -
> even if Slony should not care about this other table?!

Can you be more precise about what it is that is slowing down?

It is not surprising that it would take longer to make modifications
to replicated tables... For each row modified (whether
insert/delete/update), you add firing a trigger, as well as writing
information about the modified row to sl_log_1.

I could imagine cases where that would slow down insertions by a
factor of 2 or more.

But when you say "big dumps," that sounds as though you are drawing
data FROM a replicated table, and Slony-I should have NO EFFECT on
performance in that regard. Triggers don't fire on SELECT or on a
COPY out.
--
let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Aldor

2005-09-24, 8:24 pm

Hi Christopher,

I was talking about tables which are NOT in the replication set.

Christopher Browne wrote:
> Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
>
>
>
> Can you be more precise about what it is that is slowing down?
>
> It is not surprising that it would take longer to make modifications
> to replicated tables... For each row modified (whether
> insert/delete/update), you add firing a trigger, as well as writing
> information about the modified row to sl_log_1.
>
> I could imagine cases where that would slow down insertions by a
> factor of 2 or more.
>
> But when you say "big dumps," that sounds as though you are drawing
> data FROM a replicated table, and Slony-I should have NO EFFECT on
> performance in that regard. Triggers don't fire on SELECT or on a
> COPY out.

Christopher Browne

2005-09-24, 8:24 pm

Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
> I was talking about tables which are NOT in the replication set.

And what are you doing with them?

Is it inserts that wind up slow?

Or is it SELECTs?

Or is is it COPY?

In any case, if those tables aren't involved in replication, Slony-I
won't touch them.

There CAN be systematic effects from having replication around, in
terms of:
a) Slony-I doing work to replicate data, which consumes resources;
b) Replication work consuming cache, meaning unrelated processes
won't get to use that cache.

But generally the negative side-effects go the other way around.
- Long running transactions cause build-up of dead tuples in
Slony-I specific tables;
- This slows replication performance so that subscribers can
fall behind.

It's usually that exogenous things adversely affect Slony-I, not that
it adversely affects things that are seemingly unrelated.
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Aldor

2005-09-26, 3:24 am

I did some very detailed tests and found out that Slony wasn't really
guilty. Running slony with many daemons for another database and two
daemons for the mentioned problem database was hurting the power of the
database server just a little bit.

The problem was something else and had nothing to do with Slony.

Slony is doing his work quite good.

Christopher Browne wrote:
> Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
>
>
>
> Can you be more precise about what it is that is slowing down?
>
> It is not surprising that it would take longer to make modifications
> to replicated tables... For each row modified (whether
> insert/delete/update), you add firing a trigger, as well as writing
> information about the modified row to sl_log_1.
>
> I could imagine cases where that would slow down insertions by a
> factor of 2 or more.
>
> But when you say "big dumps," that sounds as though you are drawing
> data FROM a replicated table, and Slony-I should have NO EFFECT on
> performance in that regard. Triggers don't fire on SELECT or on a
> COPY out.

Christopher Browne

2005-09-26, 3:24 am

Aldor <an- 3jXkk0SXEtUhFhg+JK9F
0w@public.gmane.org> writes:
> I did some very detailed tests and found out that Slony wasn't really
> guilty. Running slony with many daemons for another database and two
> daemons for the mentioned problem database was hurting the power of the
> database server just a little bit.
>
> The problem was something else and had nothing to do with Slony.
>
> Slony is doing his work quite good.

It's certainly worth the investigation.

One of the fundamental principles of complex distributed systems (and
if Slony-I is involved, you've definitely got that, simply out of the
sheer number of components) is that there are _always_ bottlenecks
that result from the combination of components.

And the more components there are, the more opportunities there are
for unexpected interactions.
--
output = ("cbbrowne" "@" "ca.afilias.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Sponsored Links