[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