Celebrating its 15th Anniversary:
Pgpool-II Past, Present and FuturePart 2: New Features of Pgpool-II 4.0
December 11, 2018
PGConf.ASIA

Bo Peng
SRA OSS, Inc. Japan
pengbo@sraoss.co.jp
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

1

New Features of Pgpool-II 4.0

Detecting "false" Primary PostgreSQL ServerImprovement of Load Balanding

Enhancement of "SHOW POOL NODES"
Import PostgreSQL 11 SQL ParserLogging Client Messages
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

2

Detecting "false" Primary PostgreSQL ServerCopyright © 2018 SRA OSS, Inc. Japan All rights reserved.

3

Detecting "false" Primary PostgreSQL Server (1)Normal cluster status

Abnormal cluster status

PrimaryPrimary

WAL
READ

WAL
READ

WAL
StandbyStandbyStandby

Can't getlatest data・・・

Primary

PromoteCopyright © 2018 SRA OSS, Inc. Japan All rights reserved.

4

Detecting "false" Primary PostgreSQL Server (2)How can we detect the situation and fix it?

New parameter:detach_false_primary◼ Detect the "false" Primary and detach it◼ "true" primary : a Primary node which connects to all Standby nodes"false" primary : other than above◼ Check the connectivity between Primary and Standby nodes by using"pg_stat_wal_receiver"◼ Require PostgreSQL 9.6 or later
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role---------+----------+-------+--------+-----------+-------0| /tmp| 11002 | up
| 0.333333 | primary
1| /tmp| 11003 | up
| 0.333333 | standby
2| /tmp| 11004 | down
| 0.333333 | standby"false"Primary| last_status_change+------------------| 2018-09-08 23:36:24| 2018-09-08 23:36:24| 2018-09-08 23:37:05

detached

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

5Detecting "false" Primary PostgreSQL Server (3)There's only 1 primarynormal status

StandbyPrimary

PrimaryStandbyThere are multiple Standbys
but no Primary.

StandbyStandbyThere are multiple Primaries
and multiple Standbys.

PrimaryStandbyPrimaryStandby

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

In this case, the"false" primary
detection doesn'twork.

6

Improvement of load balancing (1)disable_load_balance_on_write

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

7

Improvement of Load Balancing (1) disable_load_balance_on_write◼ Load Balancing
Load balancing improves system performance by distributing READ queries to any PostgreSQL server◼ Before Pgpool-II 4.0

When a WRITE query is executed inside an explicit truncation, subsequent queries will be sent to Primary inorder to avoid the replication delay
Load balancing is performed again in subsequent explicit transactions

LoadBalancingnodeWALBEGIN
SELECT …

PrimaryStandby

READ
UPDATE …
May not belatest data

WRITE
SELECT …

Latest data

READ
Latest data

COMMIT
May not belatest data

BEGIN
SELECT …

READ
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

8

Improvement of Load Balancing (1) disable_load_balance_on_write

Problems until 3.7
 Although this feature maintains consistency of data, it may cause degradation ofperformance when load balancing can be executed

 If the delay occurs, the latest data updated on the Primary may not be visible in theStandby

disable_load_balance_on_write It's possible to control the behavior of load balancing when a WRITE query isexecuted.
 off/transaction/trans_transaction/always

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

9

Improvement of Load Balancing (1) disable_load_balance_on_write

disable_load_balance_on_write = off
 READ queries are load balanced even if a WRITE query appears

PrimaryStandby

BEGIN
SELECT …
READ
UPDATE …
WRITE
SELECT …
READ

Read queries areload balanced
even if a WRITE
query appears

COMMIT

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

10

Improvement of Load Balancing (1) disable_load_balance_on_write
disable_load_balance_on_write = transaction
 If a WRITE query appears in an explicit transaction, the subsequent READ queries
are not load balanced until the transaction ends
 Default value The same behavior as Pgpool-II 3.7 or before

PrimaryStandby

BEGIN
SELECT …
READ
UPDATE …
WRITE
SELECT …
READ

No load balancing
until the transaction ends

COMMITCopyright © 2018 SRA OSS, Inc. Japan All rights reserved.

11

Improvement of Load Balancing (1) disable_load_balance_on_write
disable_load_balance_on_write = trans_transaction
 If a WRITE query appears in an explicit transaction, the subsequent READ queries
are not load balanced until the transaction ends
 Also, the READ queries are not load balanced in subsequent explicit transactions

BEGIN
SELECT …

PrimaryStandbyREAD
UPDATE …
WRITE
SELECT …
READ

No load balancing
until the transaction ends

COMMIT
SELECT …
READ

Load balancing

BEGIN
SELECT …
READ

No load balancing
in subsequent
explicit transactions

COMMIT

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

12

Improvement of Load Balancing (1) disable_load_balance_on_write
disable_load_balance_on_write = always If a WRITE query appears in an explicit transaction, load balancing is not performed
until the session ends, regardless of whether it is in an explicit transaction or not

BEGIN
SELECT …

StandbyPrimaryREAD
UPDATE …
WRITE
SELECT …

No load balancing
until the session ends

SELECT …

No load balancing
until the session ends

READ

COMMITREAD
BEGIN

Latest data
SELECT …

READ
COMMITNo load balancing
until the session ends
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

13

Improvement of Load Balancing (1) disable_load_balance_on_write
Compatibility with
not-clustering-aware
applications
highdisable_load_balance_on_write = always
disable_load_balance_on_write = trans_transaction
compatibility withapplications

disable_load_balance_on_write = transaction
disable_load_balance_on_write = off

low
Performancelowperformance
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

high14

Improvement of load balancing (2)black_query_pattern_list

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

15

Improvement of Load Balancing (2) black_query_pattern_list

User's request
Don't want toperform loadbalancing byspecific SQL

How about/*NO LOAD BALANCE*/・・・

However, thesource code ofapplication can't bechanged・・・

New parameter:
black_query_pattern_list
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

16

Improvement of Load Balancing (2) black_query_pattern_list◼ black_query_pattern_list
 SQLs that matched the specified SQL pattern by this parameter aresent only to the Primary Specify a semicolon separated list of SQL patterns
 Allow to use regular expression
 Special characters need to be escaped with "¥"black_query_pattern_list = '.*table1.*;.*f1.* ';PrimarySELECT * FROM table1;
SELECT f1(1);Loadbalancing
node

SELECT * FROM table2;

StandbyNote: If SQL matches both black_query_pattern_list and white_function_list,
white_function_list setting is ignored and the SQL should be sent only to thePrimary node.
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

17Improvement of load balancing (3)Specify load balancing weight by load balancing parameters

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

18

Improvement of Load Balancing (3) Specify load balanc weight
Send 30% READ query to Primary➢ backend_weight0 = 0.3➢ backend_weight1 = 0.7

node 0node 0Primary

Failoverbackend_weight0 = 0.3

Standbybackend_weight0 = 0.3

node 1

node 1

Standbybackend_weight1 = 0.7

The load balancing
weight of primary
becomes 0.7 after
failover…

Primary

backend_weight1 = 0.7

Keep the load balancing weight of the Primary in a constant value
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

19

Improvement of Load Balancing (3) Specify load balance weight
app_name_redirect_preference_list
Send READ queries to a particular backend node for aparticular client application connection

database_redirect_preference_list
Send READ queries to a particular backend node for aparticular database connection

Pgpool-II 4.0 or later
Allow to specify loadbalancing weight
app_name:primary(0.3)
Pgpool-II 3.7 or before
Can't specify loadbalancing weight
app_name:primarySend all READ
queries to Primary◼ Send 30% READ
queries to Primary◼ Load balancing
weight does notchange even after
failoverCopyright © 2018 SRA OSS, Inc. Japan All rights reserved.

20

Enhance SHOW POOL_NODES command
Add "last_status_change" Column

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

21

Add "last_status_change" Column
last_status_change:
Time when "status" or "role" changed

◼
◼
◼
◼
◼

unusedconnect_waitup
down
quarantine◼ primary◼ standby

Be useful when failover happens and need tofind out the cause in pgpool log file

postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role---------+----------+-------+--------+-----------+-------0| /tmp| 11002 | up
| 0.500000 | primary
1| /tmp| 11003 | up
| 0.500000 | standby| last_status_change+------------------| 2018-09-10 10:36:24| 2018-09-10 10:36:24$ pcp_node_info -U pengbo -p 11001 -n 0/tmp 11002 2 0.500000 up primary 0 2018-09-10 10:37:36

Also added
to PCP andpgpool_adm

test=# SELECT * FROM pcp_node_info(0,'',11001,'pengbo','pengbo');host | port |status| weight | role
| replication_delay | last_status_change------+-------+-------------------+--------+---------+ ------------------+-------------------/tmp | 11002 | Connection in use |0 | Primary |0 | 2018-09-10 11:06:18

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

22

Import PostgreSQL 11 SQL Parser

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

23

Import PostgreSQL 11 SQL Parser◼ Pgpool-II has SQL parser
 To accurately parse the SQLs
 To rewrite the query◼ In every major release, we import the latest version ofPostgreSQL's SQL parser to Pgpool-II◼ Import PostgreSQL 11 parser to Pgpool-II 4.0
 CREATE/ALTER/DROP PROCEDURE CALL ALTER/DROP ROUTINE
 CREATE INDEX ... INCLUDE ...
 { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] VACUUM/ANALYZE <table1>, <table2>
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

24

Logging Client Messages

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

25

Logging Client Messages◼ Client messages
 Messages from the client to Pgpool-II◼ 3.7 or before
 In order to record the client message, it was necessary to enable the debug messages
 This prodoces huge amount of debug logs

◼ New parameter: log_client_messages
 If log_client_messages = on, only client messages can be logged without debugging
messages

log_client_messages = on
ParseBind
ExecuteClose

LOG: Parse message from frontend.
DETAIL: statement: "S2", query: "SELECT 1 FROM pgbench_accounts"
LOG: Bind message from frontend.
DETAIL: portal: "P1", statement: "S2"
LOG: DB node id: 0 backend pid: 24797 statement: B message
LOG: Execute message from frontend.
DETAIL: portal: "P1"
LOG: DB node id: 0 backend pid: 24797 statement: Execute: SELECT 1 …
LOG: Close message from frontend.
DETAIL: statement: "S2"
LOG: DB node id: 0 backend pid: 24797 statement: C message…
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

26Incompatible changes◼ Recovery script now accepts 5 parameters $5: node number to be recovered Existing pgpool_recovery () function can be used if you don't care about
information provided by the 5th parameter◼ Change of parameter name fail_over_on_backend_error => failover_on_backend_error

 Now a warning message is displayed when old config namefail_over_on_backend_error is used instead of
failover_on_backend_error◼ Allow to specify AES encrypted password in pgpool.confCopyright © 2018 SRA OSS, Inc. Japan All rights reserved.

27Thank you!

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

28