[Pgcluster-general] PGCluster 1.9 stored procedures (Again?)

[Michael Aronsen]

Hi,

I've been looking through the archives for information on when pgcluster 
replicates stored procedures and the general info I can find says that 
using SELECT on the stored procedure would avoid replication.

But even so, calls to the stored procedures I use (they only use select 
to join some data from different tables and return a result set) are 
MUCH slower:

Single database server, no pgcluster code.

=# explain analyze select get_spam_policy(ARRAY['test at domain.tld']);
                                      QUERY PLAN 

------------------------------------------------------------------------------------
  Result  (cost=0.00..0.26 rows=1 width=0) (actual time=6.495..6.495 
rows=1 loops=1)
  Total runtime: 6.549 ms
(2 rows)


PGCluster setup with 2 cluster nodes, 1 load balancer and 2 replicators:

=# explain analyze select get_spam_policy(ARRAY['test at domain.tld']);
                                        QUERY PLAN 

----------------------------------------------------------------------------------------
  Result  (cost=0.00..0.26 rows=1 width=0) (actual time=239.360..239.361 
rows=1 loops=1)
  Total runtime: 239.406 ms
(2 rows)


Data set are exactly the same, pg_dump'ed from production server to 
pgcluster setup.

Servers are basically identical, with the pgcluster servers having 
slightly faster processors and better i/o subsystem.

I tried changing not_replicate_prepared_select = true, but this didn't 
change the numbers.

Are there any more postgresql.conf options to tweak this or anything 
else I can do to speed it up?

I'm setting up pgmemcache for some extra performance, but would still 
like pgcluster performance (in SELECT cases) closer to stand alone servers.

-- 

Michael Aronsen
Systems Engineer
____________________________________________________________
Cohaesio A/S - Lautrupsgade 9, 1 - 2100 Copenhagen - Denmark
Phone: +45 45 880 888 - Direct: +45 45 207 818 - Fax: +45 45 880 777
Mail: mar at cohaesio.com - www.cohaesio.com