[pgpool-general: 5692] Re: Load balancing in transactions not behaving like expected

[Benjamin Firl]

Ah ok, I thought that queries after a write query have to be send to ALL
nodes. Thanks for clarifying.
That leaves the question why /*REPLICATION*/ fixes our problem, when it
should not be needed. But thats something I have to take a deeper look
at in our code.

Again thank you very much.

Regards
Benjamin Firl





On 08/08/2017 05:45 AM, Bo Peng wrote:
> Hi,
>
> It is the feature of Pgpool-II Load Balancing.
>
> - In an explicitly declared transaction 
>
>   -- Load balance is possible until a write query is issued.
>      The read queries will be send to "master" OR "slave" node.
>   
>   -- After a write query is issued, load balance is no longer possible.
>      The read queries should be send to "master" node only.
>
>
> On Mon, 7 Aug 2017 10:19:56 +0200
> Benjamin Firl <bf at wisit.com> wrote:
>
>> Hi,
>>
>> we are using pgpool 3.6.4 with postgresql 9.5.7 with replication and
>> load balancing. From my
>> understanding(http://www.pgpool.net/docs/pgpool-II-3.6.4/doc/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-CONDITION),
>> after a write query, every select should be replicated. But it is not:
>>
>>
>> load balancing works:
>>
>> /test=# SELECT * FROM test;//
>> //LOG:  statement: SELECT * FROM test;//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: SELECT * FROM test;//
>> // id | text//
>> //----+------//
>> //(0 rows)/
>>
>>
>> repliaction works:
>>
>> /test=# INSERT INTO test VALUES (0, 'test0');//
>> //LOG:  statement: INSERT INTO test VALUES (0, 'test0');//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: BEGIN//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: BEGIN//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: INSERT INTO test
>> VALUES (0, 'test0');//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: INSERT INTO test
>> VALUES (0, 'test0');//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: COMMIT//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: COMMIT//
>> //INSERT 0 1/
>>
>>
>>
>> selects in transactions are NEVER replicated:
>> /
>> //test=# BEGIN;//
>> //LOG:  statement: BEGIN;//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: BEGIN;//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: BEGIN;//
>> //BEGIN//
>> //test=# INSERT INTO test VALUES (1, 'test1');//
>> //LOG:  statement: INSERT INTO test VALUES (1, 'test1');//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: INSERT INTO test
>> VALUES (1, 'test1');//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: INSERT INTO test
>> VALUES (1, 'test1');//
>> //INSERT 0 1//
>> //test=# SELECT * FROM test;//
>> //LOG:  statement: SELECT * FROM test;//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: SELECT * FROM test;//
>> // id | text//
>> //----+-------//
>> //  0 | test0//
>> //  1 | test1//
>> //(2 rows)//
>> //
>> //test=# COMMIT;//
>> //LOG:  statement: COMMIT;//
>> //LOG:  DB node id: 11 backend pid: 42087 statement: COMMIT;//
>> //LOG:  DB node id: 9 backend pid: 59676 statement: COMMIT;//
>> //COMMIT/
>>
>>
>>
>> Is my understanding of how this should work correct? Can it be an
>> configuration issue?
>>
>>
>> On our production system we can see queries hanging forever in an active
>> state and holding locks. They even outlive the postgresql timeouts
>> (statement_timeout, lock_timeout) and block all future requests. I am
>> able to reproduce this, but not yet with a small example.
>> Forcing replication with /*REPLICATION*/, whenever using a select in a
>> transaction, fixed this. So from my understanding, forcing replication
>> in this case is what pgpool should do.
>>
>>
>> Thanks in advance and kind regards,
>> Benjamin Firl
>>
>> -- 
>> +++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> +++ Jetzt neu Wissensmanagement für Netzwerke +++
>>
>> +++             www.knodge.de                 +++
>>
>> +++++++++++++++++++++++++++++++++++++++++++++++++
>>
>>  
>>
>> --
>>
>> www.wisit.com
>>
>> www.knodge.de
>>
>>  
>>
>>  
>>
>> wisit media GmbH
>>
>> Ehrenbergstr. 11
>>
>> D-98693 Ilmenau
>>
>> ---------------------------------------------------------------------------
>>
>> -
>>
>> wisit media GmbH, Ehrenbergstr. 11, D-98693 Ilmenau
>>
>> Registergericht Jena HRB 512472
>>
>> Geschaeftsfuehrung: Dipl. Ing. Markus Duelli
>>
>>  
>>
>>  
>>
>> Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich
>>
>> erhalten haben, informieren Sie bitte sofort den Absender und vernichten
>>
>> Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe
>>
>> dieser E-Mail ist nicht gestattet. 
>>
>

-- 
+++++++++++++++++++++++++++++++++++++++++++++++++

+++ Jetzt neu Wissensmanagement für Netzwerke +++

+++             www.knodge.de                 +++

+++++++++++++++++++++++++++++++++++++++++++++++++

 

--

www.wisit.com

www.knodge.de

 

 

wisit media GmbH

Ehrenbergstr. 11

D-98693 Ilmenau

---------------------------------------------------------------------------

-

wisit media GmbH, Ehrenbergstr. 11, D-98693 Ilmenau

Registergericht Jena HRB 512472

Geschaeftsfuehrung: Dipl. Ing. Markus Duelli

 

 

Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich

erhalten haben, informieren Sie bitte sofort den Absender und vernichten

Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe

dieser E-Mail ist nicht gestattet.