How to manage a herdof elephants:

Introducing new features ofpgpool-II
SRA OSS, Inc. Japan
Tatsuo Ishii
石井 達夫

About me
Came from Tokyo,Japan●PostgreSQL committer●Original author ofpgpool-II●
Working for SRA OSS for10 years●
Visiting China 7 times(including this time)●
Love Chinese food!
●2Copyright(c) 2015 SRA OSS, Inc. Japan

About SRA OSS, Inc. JapanOne of the oldest PostgreSQL companies in theworld●
Doing PostgreSQL and other OSS related businessfor 10 years●Main business is support. Has over 600 supportcontracts●Contributions to PostgreSQL include:●

Multi byte support●
Recursive queries (WITH RECURSIVE)●
64 bit large objects●

3Copyright(c) 2015 SRA OSS, Inc. JapanA herd of PostgreSQL
An elephant ispowerful●
However, a herd ofelephants is evenmore powerful!
●
Problem is, how tomanage the herd ofelephants?
●

4Copyright(c) 2015 SRA OSS, Inc. Japan

Problems of the herd of elephants
include:It needs a leader (a primary server)●If the leader retires, a new leader must take over its role (failover)●Other elephants must follow the new leader●
If a non leader elephant cannot continue to work, it must retire
and leave the herd (standby fail over)●If a new elephant wants to join the herd, it should be acceptedwithout disturbing the herd●
Elephants should help each other to perform a task in an
efficient way (load balancing)●
There are tasks which can only be performed by the leader(write queries – needs query dispatching)●5Copyright(c) 2015 SRA OSS, Inc. Japan

Solving the problems by using
pgpool-II
By using pgpool-II, a streaming
replication PostgreSQL cluster almost
looks like a single PostgreSQL server●
User supplied “fail over script”could
define which standby server shouldtake over when the primary server goesdown●
User supplied “follow mastercommand” allow other standbys follow
the new primary server●If a standby server goes down, it isremoved from the cluster definitionand clients can continue to use thecluster●
pgpool-II examines each query. If thequery is a read only one or not. If not,it is forwarded to one of servers (loadbalancing).
●If a query is a write query, it will be
forwarded to the primary server●6Copyright(c) 2015 SRA OSS, Inc. Japan

pgpool-II

A herd of elephants
looks like single
big elephantQuery dispatching
Read and write queries

Writequeries

Readqueries

PrimaryStandbyStandby
7Standby
Copyright(c) 2015 SRA OSS, Inc. Japan

Load balancing
Read queries

70% ofqueries
10 % ofqueries 10 % ofqueries

10 % ofqueries
Readqueries

PrimaryStandbyStandby
8Standby
Copyright(c) 2015 SRA OSS, Inc. Japan

It is possible todispatch to particluar
server by application
name or databasename.

Standby server fails

PrimaryStandby
9X

If a standby goes down, it issimply take off from the cluster
and user can continue to use
the service

Copyright(c) 2015 SRA OSS, Inc. Japan

Primary server fails

PrimaryX

If primary goes down, one of standbys
is promoted to become the newprimary.

Other standby servers recognize
the new primary and follows it.

Standby
10Copyright(c) 2015 SRA OSS, Inc. Japan

Adding new server

Adding new server is easy.
pgpool-II copies the databasefrom the primary to the newstandby server withoutdisturbing other servers.

PrimarySame procedure can beapplied when re-sync
broken standby.

Standby
11Copyright(c) 2015 SRA OSS, Inc. Japan

Upcoming pgpool-II 3.5
Improving performance●Improving watchdog●Importing PostgreSQL 9.5's SQL parser●Others●Expected to release on December 15th●

12Copyright(c) 2015 SRA OSS, Inc. Japan

Improving performance

13Copyright(c) 2015 SRA OSS, Inc. Japan

Improving extended query
perforamance
Using extended protocol (typically used inJava) in pgpool-II is slow (as slow as half ofsimple protocol)●
Current implementation of pgpool-II for
extended protocol is not so great: it requires
additional flush messages, and this is thesource of poor performance●First, need to understand: how extended
protocol is handled?
●14

Copyright(c) 2015 SRA OSS, Inc. Japan

Some details are omitted
ClientServer

Clientquery

result“Sync”
requestssend
the result
from server

ServerClientServer

Parse

Parse

Bind

Flush

ExecuteResult

Sync

Bind

Flush is neededto handle
multiple
PostgreSQL

Flush
resultresult
ExecuteSync
resultsimple protocol

extended protocol
15Copyright(c) 2015 SRA OSS, Inc. Japan

extended protocol
with pgpool-II

more traffic

ClientServer

ClientParseServerParseSync

Too many Flush

Result

Bind
ExecuteBind
In streaming replication
we could omit some ofFlush messages

Flushresultsync
resultExecuteenhanced!
Sync
resultextended protocol
with pgpool-II
16extended protocol
with pgpool-II in 3.5
Copyright(c) 2015 SRA OSS, Inc. Japan

Benchmarking Extended protocol
query performance
12,000pgpool-II 3.5 is
20% to 250%
faster thanpgpool-II 3.4!

10,000

TPS

8,000

6,000

pgpool-II 3.5
pgpool-II 3.4

4,000

2,00001248163264128256

Number of clients

17Copyright(c) 2015 SRA OSS, Inc. JapanAWS m4.large instance
CentOS 6
PostgreSQL 9.4 x2(streaming replication)
pgbench -S

Overcoming Thundering herdproblem●

What is “the thundering herd problem”?
●“The thundering herd problem occurs when a large numberof processes waiting for an event are awoken when thatevent occurs, but only one process is able to proceed at atime. After the processes wake up, they all demand theresource and a decision must be made as to which process
can continue. After the decision is made, the remaining
processes are put back to sleep, only to all wake up again torequest access to the resource.”

From Wikipedia●
pgpool-II forks off many child process and they are waiting for connection
requests from clients●
If a connection request arrives, all of the child process are awoken but only
one of them is allowed to accept the request●
Other child process start sleeping again, to wait for next connection request●This leads to an excessive context switching and results in poor performance
18Copyright(c) 2015 SRA OSS, Inc. Japan

Overcoming Thundering herd
problem
pgpool-II childpgpool-II childpgpool-II childsleeping

sleeping

sleeping

pgpool-II childpgpool-II childpgpool-II child

awake

awake

awake

pgpool-II childpgpool-II childpgpool-II childsleeping

processing

sleeping

pgpool-II 3.4

19

Copyright(c) 2015 SRA OSS, Inc. Japan

ThunderingHerd!

Overcoming Thundering herd
problem
pgpool-II childpgpool-II childpgpool-II childsleeping

sleeping

sleeping

pgpool-II childpgpool-II childpgpool-II childsleeping

awakesleeping

pgpool-II 3.5

20

pgpool-II childpgpool-II childpgpool-II childsleeping

processing

sleeping

Copyright(c) 2015 SRA OSS, Inc. JapanNo thundering
Herd problem

Overcoming Thundering herdproblem
3,000

If concurrent clientsare fewer than numberof pgpool child process,pgpool-II 3.5 is
40% to 150%
faster than pgpool-II 3.4

2,500

TPS

2,000

1,500

PostgreSQL
pgpool-II 3.5
pgpool-II 3.4

1,000

Note PC with 16GB Mem,
CORE i7 x2, 512GB SSD
Ubuntu 14.04
PostgreSQL 9.4 x2(streaming replication)
pgbench -S -C -T 300

50001248163264128256398

Number of clients

21Copyright(c) 2015 SRA OSS, Inc. Japan

Settings to avoid the thundering
herd problem in pgpool-II 3.5
set “serialize_accept” to on●
set “child_life_time” to 0●
If concurrent connections are roughly equal
to num_init_children, this function does not
do the best (see previous slide)●

22Copyright(c) 2015 SRA OSS, Inc. Japan

Improving watchdog

23Copyright(c) 2015 SRA OSS, Inc. Japan

What is “Watchdog”?
Because pgpool-II works as aproxy, pgpool-II could be Single
point of failure (SPOF)●“Watchdog” is a built-in High
Availability (HA) feature ofpgpool-II●Two or more pgpool-II instances
monitors each other. If “Active”
pgpool-II goes down, “Standby”
pgpool-II takes over and becomes
new active pgpool-II●
Active pgpool-II holds Virtual IP(VIP). Client connects to the VIP
and are not worried about whichis pgpool-II is alive●24VIP
ActiveStandby

pgpool-II

pgpool-II

PostgreSQL

PostgreSQL

Copyright(c) 2015 SRA OSS, Inc. Japan

New Watchdog Enhances
Robustness against Split-brain●Split-brain syndrome●●It can't be decided which pgpool-II should be elected as the master when
the network is participatedQuorum support
Check if more than half of nodes are belong to the group which local pgpool-II
is belonging to– Number of pgpool-II nodes must be odd to make quorum working (in othercase you can use “trusted_servers”)–

Master is elected fromthis grouppgpool-II

pgpool-II

pgpool-II

network
partitioning
pgpool-II

25

Copyright(c) 2015 SRA OSS, Inc. Japan

pgpool-II

New Watchdog enhancesinter-process communication●

Change in inter-process communication method with inwatchdog●●UNIX domain socket & JSON format data

This allows pgpool-II to work with third-party tools●ex) health-check by a third party tool. Etc.

previousNew version

pgpool-II

other pgpoo-II

pgpool-II
JSON

Cluster mgr

Life-check

JSONCluster mgr
Packet recv
Sharedmemorychangelifecheck
Packet send
26

Copyright(c) 2015 SRA OSS, Inc. Japan

3rd party tool

Watchdog enhancement
others●Verifies the consistency of important configuration
parameters among all nodes
make sure they are consistent among all nodes●
help to eliminate problems caused by ll-configuredpgpool-II nodes●●

Watchdog nodes can have priorities
Watchdog nodes can be assigned with differentpriorities●Nodes with higher watchdog will get a preference when
the cluster is electing its new leader node.
●

27Copyright(c) 2015 SRA OSS, Inc. Japan

Importing PostgreSQL 9.5's
parser28

Copyright(c) 2015 SRA OSS, Inc. Japan

Importing PostgreSQL 9.5 parser●

Parser of PostgreSQL 9.5 is ported into pgpool-II 3.5●●●

Previous parser was imported from PostgreSQL 9.4

Load-balancing and query-cache supports the new selectsyntax.
●GROUPING SET, CUBE, ROLLUP,●TABLESAMPLE

Query-rewriting in the native replication mode supports thenew insert/update syntax.
●INSERT … ON CONFLICT●UPDATE tab SET (col1,col2,...) = (SELECT ...), ...

29

Copyright(c) 2015 SRA OSS, Inc. Japan

Improvements in pgpool-II 3.5:others30

Copyright(c) 2015 SRA OSS, Inc. Japan

Health check and replication
delay checking target database
In some systems (for example, Heroku) donot allow to connect to “postgres” or“template1” database●
pgpool-II issues query to those databases forhealth check and streaming replication delay●
pgpool-II allows to use particular databaseinstead of the databases●

health_check_database●sr_check_database●

31

Copyright(c) 2015 SRA OSS, Inc. Japan

Showing SELECT count●“show pool_nodes” command now shows
the number of SELECTs issued to each DB
nodeNew!
test=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role
| select_cnt---------+----------+-------+--------+-----------+---------+-----------0| /tmp
| 11002 | 2| 0.500000 | primary | 338230
1| /tmp| 11003 | 2| 0.500000 | standby | 163939(2 rows)

32Copyright(c) 2015 SRA OSS, Inc. Japan

Availability of pgpool-II 3.5
We expect to release pgpool-II 3.5 on
December 15th●
pgpool-II 3.5 alpha1 was released this week●
Stay tuned!
●

33Copyright(c) 2015 SRA OSS, Inc. Japan

URLs●pgpool-II official site●●

http://www.pgpool.net

SRA OSS●

http://www.sraoss.co.jp

34

Copyright(c) 2015 SRA OSS, Inc. JapanThank you!

35

Copyright(c) 2015 SRA OSS, Inc. Japan