pgpool-II - Streaming Replication
pgpool-II - Streaming Replication
pgpool-II - Streaming Replication
of elephants:
About me
A herd of PostgreSQL
An elephant is powerful
However, a herd of
elephants is even more
powerful!
Problem is, how to manage
the herd of elephants?
pgpool-II
A herd of elephants
looks like single
big elephant
Query dispatching/routing
Minimize modifications
to existing single PostgreSQL
applications
Write/read
queries
Read
queries
Primary
Standby
Standby
Standby
Load balancing
Read queries
The primary
concentrate
on write!
(performance
benefit)
0% of
read queries
100% of
write queries
40 % of
read queries
Primary
30 % of
read queries
Read
queries
Standby
Standby
Standby
30 % of
read queries
It is possible to
dispatch to particular
server by application
name or database
name.
Primary
Standby
Primary
Standby
10
New member!
Adding new server is easy.
pgpool-II copies the database
from the primary to the new
standby server without
disturbing other servers.
Primary
Standby
11
Primary
Take over
Standby pgpool
Standby
12
Query
cache
No access to
PostgreSQL
at all!
Support policy
Pgpool-II hire major releases and minor releases method like PostgreSQL
Keep on providing back patches for five years after initial release
Because there's one major release each year, we keep on supporting last 5 to 6
versions
If you need longer support period, please contact to commercial support providers
14
Improved performance
Improved watchdog
Ready for PostgreSQL 9.5
Enhanced pcp commands
and more
15
Improved performance
16
17
Server
Client
query
Server
Client
Parse
Parse
Bind
Flush
Execute
result
Sync
Sync
requests
send
the result
from server
Server
Result
Bind
Flush
result
result
Execute
Sync
result
simple protocol
extended protocol
18
extended protocol
with pgpool-II
Flush is needed
to handle
multiple
PostgreSQL
more traffic
Client
Server
Client
Parse
Server
Parse
Sync
Result
Bind
In streaming replication
we could omit some of
Flush messages
Flush
result
Execute
Bind
Execute
sync
result
enhanced!
Sync
result
extended protocol
with pgpool-II
extended protocol
with pgpool-II in 3.5
19
pgpool-II 3.5 is
20% to 250%
faster than
pgpool-II 3.4!
10,000
TPS
8,000
6,000
pgpool-II 3.5
pgpool-II 3.4
4,000
2,000
0
1
16
32
64
128
256
Number of clients
20
clients
From Wikipedia
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
21
pgpool-II child
sleeping
pgpool-II child
sleeping
pgpool-II child
awake
pgpool-II child
awake
pgpool-II child
awake
pgpool-II child
sleeping
pgpool-II child
processing
pgpool-II child
sleeping
pgpool-II 3.4
22
Thundering
Herd!
pgpool-II child
sleeping
pgpool-II child
sleeping
pgpool-II child
sleeping
pgpool-II child
awake
pgpool-II child
sleeping
pgpool-II 3.5
pgpool-II child
sleeping
23
pgpool-II child
processing
pgpool-II child
sleeping
No thundering
Herd problem
3,000
2,500
TPS
2,000
1,500
PostgreSQL
pgpool-II 3.5
pgpool-II 3.4
1,000
500
0
1
16
32
64
128
256
398
Number of clients
24
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)
25
Improving watchdog
26
What is Watchdog?
27
VIP
Standby
Active
pgpool-II
PostgreSQL
pgpool-II
PostgreSQL
Split-brain syndrome
It can't be decided which pgpool-II should be elected as the master when the network is
participated
Quorum 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 other case you can use
trusted_servers)
pgpool-II
network
partitioning
pgpool-II
28
pgpool-II
previous
New version
pgpool-II
pgpool-II
JSON
Cluster mgr
Packet recv
Shared
memory
lifecheck
Packet send
29
change
Life-check
other pgpoo-II
Cluster mgr
JSON
Watchdog enhancement
others
30
31
INSERT ON CONFLICT
UPDATE tab SET (col1,col2,...) = (SELECT ...), ...
32
33
health_check_database
sr_check_database
34
36
Chinese translations!
Pgpool-II documentation
pgpoolAdmin message catalog
37
Caution!
38
Future plans
39
http://www.pgpool.net
SRA OSS
http://www.sraoss.co.jp
Commercial support is available
40
Thank you!
!
41