|
Home > Archive > Slony1 PostgreSQL Replication > July 2005 > [PATCH] subscribe set performance enhancements
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
[PATCH] subscribe set performance enhancements
|
|
| Darcy Buskermolen 2005-07-14, 11:24 am |
| Hello,
I have implemented an experimental patch that avoids index maintainance during
the SUBSCRIBE SET phase of replication. This work is based apon theory and
findings of Jan. My initial testing shows that subscribing a large heavily
hit pgbench (scalling factor of 100 with 50 concurrent clients) database can
subscribe set in the same amount of time as it takes to pg_dump | psql.
Those of you who are interested can find the patch for 1.1 and CVS HEAD at
http://www.dbitech.ca/slony/patches...tion-perf-patch . I make no firm
claim that, at this time, this patch will act as intended for everybody. It
would help us in getting this patch fully ironed out and applied to the main
tree, if those of you who do try this patch would report your findings back
to the list, wether it be yes it works or I got an error.
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| Christopher Browne 2005-07-14, 11:24 am |
| Darcy Buskermolen wrote:
>Hello,
>
>I have implemented an experimental patch that avoids index maintainance during
>the SUBSCRIBE SET phase of replication. This work is based apon theory and
>findings of Jan. My initial testing shows that subscribing a large heavily
>hit pgbench (scalling factor of 100 with 50 concurrent clients) database can
>subscribe set in the same amount of time as it takes to pg_dump | psql.
>
>Those of you who are interested can find the patch for 1.1 and CVS HEAD at
>http://www.dbitech.ca/slony/patches...tion-perf-patch . I make no firm
>claim that, at this time, this patch will act as intended for everybody. It
>would help us in getting this patch fully ironed out and applied to the main
>tree, if those of you who do try this patch would report your findings back
>to the list, wether it be yes it works or I got an error.
>
>
It didn't work as provided; the 'oid' reference in the second line of
the query was ambiguous.
Use 'where pg_catalog.pg_class.oid' instead of 'where oid' in the two
updates on pg_catalog.pg_class, and that turns out better...
+#if 1
+ "update pg_catalog.pg_class set relhasindex = 'f'
FROM %s.sl_table "
+ "where pg_catalog.pg_class.oid = %s.sl_table.tab_reloid "
+ "and %s.slon_quote_input('\"' ||
%s.sl_table.tab_nspname || '\".\"' || %s.sl_table.tab_relname || '\"') =
'%s'; "
+#endif
| |
| Darcy Buskermolen 2005-07-14, 11:24 am |
| On Thursday 14 July 2005 09:23, Christopher Browne wrote:
> Darcy Buskermolen wrote:
>
> It didn't work as provided; the 'oid' reference in the second line of
> the query was ambiguous.
>
> Use 'where pg_catalog.pg_class.oid' instead of 'where oid' in the two
> updates on pg_catalog.pg_class, and that turns out better...
>
> +#if 1
> + "update pg_catalog.pg_class set relhasindex = 'f'
> FROM %s.sl_table "
> + "where pg_catalog.pg_class.oid = %s.sl_table.tab_reloid "
> + "and %s.slon_quote_input('\"' ||
> %s.sl_table.tab_nspname || '\".\"' || %s.sl_table.tab_relname || '\"') =
> '%s'; "
> +#endif
Good catch Christopher. I have updated the patch to reflect this change.
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| David Parker 2005-07-14, 1:23 pm |
| Wow, I was just starting to try and improve our initial subscription
performance in slony 1.0.2 (I know, I know....) by figuring out what
indexes to drop, saving off appropriate create statements, etc. - but
your way is much cooler! It looks like we'll have the opportunity to
upgrade to 1.1 in the next few months as part of our own app upgrade, so
maybe I can just avoid all this work ;-)
One question: the code in the patch seems to assume that relhasindex
should always be set back to 't' - is this safe because of the slony
primary key requirement for replicated tables, i.e., we don't have to
worry about setting relhasindex=3D't' on a table that actually had no
indexes to begin with?
Thanks!
- DAP=20
-----Original Message-----
From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
[mailto:slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Darcy
Buskermolen
Sent: Thursday, July 14, 2005 12:39 PM
To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
Subject: Re: [Slony1-general] [PATCH] subscribe set performance
enhancements
On Thursday 14 July 2005 09:23, Christopher Browne wrote:
> Darcy Buskermolen wrote:
work is based apon[color=darkred]
subscribing[color=da
rkred]
[color=darkred]
[color=darkred]
>
> It didn't work as provided; the 'oid' reference in the second line of=20
> the query was ambiguous.
>
> Use 'where pg_catalog.pg_class.oid' instead of 'where oid' in the two=20
> updates on pg_catalog.pg_class, and that turns out better...
>
> +#if 1
> + "update pg_catalog.pg_class set relhasindex =3D 'f'
> FROM %s.sl_table "
> + "where pg_catalog.pg_class.oid =3D
%s.sl_table.tab_reloid "
> + "and %s.slon_quote_input('\"' ||
> %s.sl_table.tab_nspname || '\".\"' || %s.sl_table.tab_relname || '\"')
> =3D '%s'; "
> +#endif
Good catch Christopher. I have updated the patch to reflect this
change.
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
____________________
____________________
_______
Slony1-general mailing list
Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
http://gborg.postgresql.org/mailman.../slony1-general
| |
| Jan Wieck 2005-07-14, 1:23 pm |
| On 7/14/2005 1:50 PM, David Parker wrote:
> Wow, I was just starting to try and improve our initial subscription
> performance in slony 1.0.2 (I know, I know....) by figuring out what
> indexes to drop, saving off appropriate create statements, etc. - but
> your way is much cooler! It looks like we'll have the opportunity to
> upgrade to 1.1 in the next few months as part of our own app upgrade, so
> maybe I can just avoid all this work ;-)
>
> One question: the code in the patch seems to assume that relhasindex
> should always be set back to 't' - is this safe because of the slony
> primary key requirement for replicated tables, i.e., we don't have to
> worry about setting relhasindex='t' on a table that actually had no
> indexes to begin with?
Slony strictly requires at least one unique index on every replicated
table. So if you happen to get it to replicate a table that doesn't have
any index at all, this adds very little to the damage already done :-)
Jan
>
> Thanks!
>
> - DAP
>
> -----Original Message-----
> From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> [mailto:slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Darcy
> Buskermolen
> Sent: Thursday, July 14, 2005 12:39 PM
> To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: Re: [Slony1-general] [PATCH] subscribe set performance
> enhancements
>
> On Thursday 14 July 2005 09:23, Christopher Browne wrote:
> work is based apon
> subscribing
>
>
> %s.sl_table.tab_reloid "
>
>
> Good catch Christopher. I have updated the patch to reflect this
> change.
>
> --
> Darcy Buskermolen
> Wavefire Technologies Corp.
>
> http://www.wavefire.com
> ph: 250.717.0200
> fx: 250.763.1759
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general
--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #
| |
| Hannu Krosing 2005-07-14, 8:24 pm |
| On N, 2005-07-14 at 08:36 -0700, Darcy Buskermolen wrote:
> Hello,
>
> I have implemented an experimental patch that avoids index maintainance during
> the SUBSCRIBE SET phase of replication. This work is based apon theory and
> findings of Jan. My initial testing shows that subscribing a large heavily
> hit pgbench (scalling factor of 100 with 50 concurrent clients) database can
> subscribe set in the same amount of time as it takes to pg_dump | psql.
>
> Those of you who are interested can find the patch for 1.1 and CVS HEAD at
> http://www.dbitech.ca/slony/patches...tion-perf-patch . I make no firm
> claim that, at this time, this patch will act as intended for everybody. It
> would help us in getting this patch fully ironed out and applied to the main
> tree, if those of you who do try this patch would report your findings back
> to the list, wether it be yes it works or I got an error.
Would something similar also work for enabling real truncate on a table
with FK-s pointing to it ?
--
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>
| |
| Darcy Buskermolen 2005-07-15, 8:25 pm |
| On Thursday 14 July 2005 15:55, Hannu Krosing wrote:
> On N, 2005-07-14 at 08:36 -0700, Darcy Buskermolen wrote:
>
> Would something similar also work for enabling real truncate on a table
> with FK-s pointing to it ?
Well it requires something a bit different but here is my initial work on it:
http://www.dbitech.ca/slony/patches...-truncate-patch
There a few more things I need to test with it, but a run of test_3_pgbench
with this patch results in 100% equal DB's
Once again the same claim is attached, as is the request for comments.
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| Darcy Buskermolen 2005-07-27, 1:29 pm |
| On Thursday 14 July 2005 08:36, Darcy Buskermolen wrote:
> Hello,
>
> I have implemented an experimental patch that avoids index maintainance
> during the SUBSCRIBE SET phase of replication. This work is based apon
> theory and findings of Jan. My initial testing shows that subscribing a
> large heavily hit pgbench (scalling factor of 100 with 50 concurrent
> clients) database can subscribe set in the same amount of time as it takes
> to pg_dump | psql.
>
> Those of you who are interested can find the patch for 1.1 and CVS HEAD at
> http://www.dbitech.ca/slony/patches...tion-perf-patch . I make no
> firm claim that, at this time, this patch will act as intended for
> everybody. It would help us in getting this patch fully ironed out and
> applied to the main tree, if those of you who do try this patch would
> report your findings back to the list, wether it be yes it works or I got
> an error.
This patch has been update with a new version, this new version has some
additional functionality, truncate is now used instead of delete to bypass
the fkey limits that were there before. A new slon.conf option has been
introduced to allow the setting of maintenance_work_mem
(vaccum_memory pre
8.0) which is used on the subscriber nodes for reindexing/analyzing the
tables during the initial subscription phase. This new option is named
maintenance_work_mem
and is only settable in slon.conf the default setting of
0 means use sessions default value, otherwise we override it using the value
specified (and reset it to the session default at the end of the
subscription). Any further feedback on this is most welcome.
Outstanding TODO related to this patch:
A) ample testing in as many odd setups as possible
B) documentation
C) determine if the desired log shipping behavior is observed.
--
Darcy Buskermolen
Wavefire Technologies Corp.
http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759
| |
| Christopher Browne 2005-07-27, 8:24 pm |
| Darcy Buskermolen wrote:
>C) determine if the desired log shipping behavior is observed.
>
>
I'll take a particular look at that.
I tried applying the patch on CVS HEAD; some of the recent Win32 ^M
fixing seemed to have been redone in the patch.
You may want to revisit the "line endings" aspect of it a little...
| |
| Christopher Browne 2005-07-27, 8:24 pm |
| Some difficulties...
Investigating...
2005-07-27 16:18:16 EDT WARN remoteWorkerThread_1
1: "select
"_T1". maintenance_work_mem
('0');" ERROR: function
_T1. maintenance_work_mem
("unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
ERROR: function _T1. maintenance_work_mem
("unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
2005-07-27 16:18:16 EDT ERROR remoteWorkerThread_1
1: "select
"_T1". disableSubscriptionI
ndexes('"public"."accounts"'); select
"_T1".truncateTable('"public"."accounts"'); copy "public"."accounts"
from stdin; " ERROR: current transaction is aborted, commands ignored
until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of
transaction block
| |
| Christopher Browne 2005-07-27, 8:24 pm |
| Darcy Buskermolen wrote:
>C) determine if the desired log shipping behavior is observed.
>
>
Not the case for the SUBSCRIBE SET event.
It's easy to characterize by grabbing an extract of what is generated
for one table.
------------------------------------------------------------------------------------------------------------------------------------------------
delete from "public"."branches";copy "public"."branches" from stdin;
1 2112818 \N
\.
select "_T1". enableSubscriptionIn
dexes('"public"."branches"'); reindex
table "public"."branches"; analyze "public"."branches"; select
"_T1". maintenance_work_mem
('16384');
------------------------------------------------------------------------------------------------------------------------------------------------
Problems:
1. There should be a query of the following form at the beginning...
select "_T1". disableSubscriptionI
ndexes('"public"."branches"');
2. The functions enableSubscriptionIn
dexes(),
disableSubscriptionI
ndexes(), and maintenance_work_mem
() are not defined
in tools/slony1_dump.sh
I'll see about sending you a patch for the latter...
|
|
|
|
|