[Pgpool-general] Inserts Extremely Slow With Replication

[Jack C]

On Fri, Feb 11, 2011 at 11:28 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 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
>

Thank you Tatsuo!  My pgpool installation now works perfectly :)