[Pgpool-general] Inserts Extremely Slow With Replication

[Jack C]

Hi Everyone,

My configuration is fairly simple.  I have a server running FC-14,
pgpool-II-3.0.1, and postgresql-server 9.0.2, and another server
running FreeBSD with postgresql-server 9.0.2.  These servers are
configured with replication via pgpool, with the FC14 machine as the
primary.

The problem I am having, is that an insert done via pgpool takes an
extremely long time, hours, and I have yet to see one complete.  The
insert itself is simple:

INSERT INTO keywords (keyword) VALUES ('jacktest1');

This query works perfectly and quickly when executed via psql directly
to either back-end node, but through pgpool is where the problem
arises.  If I run:

SELECT * FROM pg_stat_activity;

Via psql on the primary (not through pgpool), I see this:

16387 | kwds    |   11951 |    16386 | dbadmin | psql             |
127.0.0.1   |       50337 | 2011-02-12 00:09:39.029072+00 | 2011-02-12
00:10:05.014054+00 | 2011-02-12 00:10:05.231779+00 | f       | SELECT
1 FROM "keywords"_id_seq FOR UPDATE

This is interesting, because the column name is actually
keywords_id_seq, not "keywords"_id_seq, so I am wondering if pgpool is
somehow mangling the column name, or if this is normal.  Furthermore,
I believe insert_lock is supposed to be set to true in order to deal
with SERIAL data types during inserts.  I have set this to true as
recommended.

The secondary server has an established session, but it is idle.

I have attached strace to both the insert and select processes, and
they are active (not hung).  I have run this with pgpool in debug
mode, and it only floods the logs with messages like this:

2011-02-12 00:06:13 DEBUG: pid 10355: do_query: kind: D

Until I eventually ctrl-c my session in psql.  I have not seen any
other errors in either pgsql or pgpool logs.  Please let me know if
you need any further information, or if you have any ideas.  Thanks
for taking the time to read this mess.

-Jack