[Pgpool-general] Pgpool load balancing working great!

[Sandy Thomson]

Hi,
Just to say I have managed to successfully get pgpool 3.1.1 working with
Postgres 8.1.4 and 2 quad processor servers, one server is a 32 bit
Intel Xeon server and the other a 64 bit AMD Opteron server. However I
had to pg_dump the databases (rather than rsync the data directories).
Thought I should share my experiences.

I missed some sequence functions (nextval etc) which caused a bit of a
nightmare with data (thought I had got all of them but evidently not).
The statement logging tool was really useful to debug these problems though.

One thing I would say about this is, say you have a custom function
SELECTNUM() or something, even if you comment the start of this
function, e.g:

/* comment for LB */ SELECT * FROM SELECTNUM()

If SELECTNUM contains a nextval() statement then the comment will not
help and your stored sequence will become inconsistent between servers.
I didn't realise this would happen.

Also, I was getting loads of errors of the form:
========
Oct 24 15:53:24 db postgres[8945]: [393-1] ERROR:  prepared statement
"dbdpg_6" does not exist
Oct 24 15:53:24 db postgres[8945]: [394-1] ERROR:  prepared statement
"dbdpg_7" does not exist
Oct 24 15:53:24 db postgres[8945]: [395-1] ERROR:  prepared statement
"dbdpg_8" does not exist
========
from each Postgres database, but this was fixed by turning off
connection caching in pgpool. Most of my queries are prepared statements
originating from perl.

So basically this email is a big thank-you, as there is nothing else
available for Postgres that will do this (there is slony-r but that is
table level using triggers, which would be a complete nightmare for my
use). The load has been spread between the two servers quite noticeably
(was peaking between 4-6 before on one server, now peaks at about 1.5-2
on both servers).

Feature request:
Error logging could report the statements last received? I am getting
the following errors still, I pretty much know what they mean but don't
know where they are coming from(!):
pgpool: 2006-10-25 12:30:01 ERROR: pid 30080: read_kind: kind does not
match between backends master(82) secondary(69)
pgpool: 2006-10-25 12:46:00 ERROR: pid 12080: pool_process_query: kind
does not match between backends master(E) secondary(C)
pgpool: 2006-10-25 12:06:43 ERROR: pid 3080: ProcessFrontendResponse:
failed to read kind from frontend. fronend abnormally exited

Cheers.

Sandy.