[Pgpool-general] Inserts Extremely Slow With Replication

[Tatsuo Ishii]

I think this is a known bug with pgpool-II 3.0.x.

What actually following SELECT:

SELECT 1 FROM "keywords"_id_seq FOR UPDATE

being interpreted by PostgreSQL is:

SELECT 1 FROM keywords AS _id_seq FOR UPDATE

which tries to obtain row locks for "all" rows in table keywords,
which will take very long time if the table has many rows.

Please wait 3.0.2 (will be released in the next week) or grab
V3_0_STABLE HEAD from CVS.  Or you could apply included patches which
is being committed to V3_0_STABLE.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: insert_lock.patch
Type: text/x-patch
Size: 2301 bytes
Desc: not available
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20110212/e1c69dbd/attachment.bin>