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

Bo Peng pengbo at sraoss.co.jp
Tue Aug 8 12:45:47 JST 2017


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. 
> 


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan



More information about the pgpool-general mailing list