pgpool-II 3.5
How it will look like?

pgpool-II Global Development Group
Tatsuo Ishii

Upcoming pgpool-II 3.5
Performance enhancement●
Improving watchdog●
Overhauling pcp commands●

Improved handling of command argument●
Do not pass password via command line any more (whichcauses security risks)●
Concurrent execution of pcp commands●●●

Obsoleting parallel query mode●●

Allow to execute a pcp command while running ｐｃp_recovery_node
which takes sometime to run

Very few users and maintenance effort does not worth

Expect to release in 2015 fall2Copyright(c) 2015 SRA OSS, Inc. Japan

Major improvements inpgpool-II 3.5●

Improving PCP commands●pcp commands are not easy to use (fixedargument positions, for example)–●

Single threaded – while executing pcp
command, other pcp commands cannot doanything–●

Now PCP commands can execute simultaneously

Enhancing security – current PCP commands
need to pass password through an argument–●

Standard command style introduced

psql like .pcprc file is instroduced

All of above are already in git repositoy
3Copyright(c) 2015 SRA OSS, Inc. Japan

Watchdog issues●

Not easy to configure–●Split brain problem–●

More robust way to avoid split brain problem by using voting

Want to work with multiple networks–●

Want to have same configuration files for each pgpool-II node

For example AWS needs replacement for VIP

We are in the design discussion phase. All
developers/users are encouraged to go into thediscussion–

More details can be found here:●

http://pgpool.net/mediawiki/index.php/watchdog_feature_enhancem
entPerforamance enhancement
Using extended protocol (used in Javaprepared statement) in pgpool-II is slow (asslow as half of simple protocol)●
Current implementation of pgpool-II
extended protocol is not so great: it requires
additional sync of protocol, and this is thesource of the performance degration●●●

BTW, extended protocol is not as fast as simple
protocol even if pgpool-II is not involved (80% ofspeed of simple protocol)

First, need to understand: how extended
protocol is handled?
5Copyright(c) 2015 SRA OSS, Inc. Japan

Some details are omittedClientServer

Clientquery

result“Sync”
requestssend
the result
from server

ServerClientServer

Parse

Parse

Bind

Sync

ExecuteServerSync

Bind

Sync is needed
to handle
multiple
PostgreSQL

Sync
resultresultExecuteSync
resultsimple protocol

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

extended protocol
with pgpool-II

more traffic

ClientServer

ClientParse

ServerParse

Sync

Too many Sync

Server

Bind
ExecuteBind
In streaming replication
we could omit some ofSync

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

Some details are omitted
ClientServer

Clientquery

result“Sync”
requests
sending
the result
from server

ServerClientServer

Parse

Parse

Bind

Sync

ExecuteServerSync

Bind

Sync is needed
to handle
multiple
PostgreSQL

Sync
resultresultExecuteSync
resultsimple protocol

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

extended protocol
with pgpool-II

more trafficPerformancecomparision

Number of SELECTs
per second
issued by pgbench
7,000
6,0005,000
4,000
3,000
2,000
1,000
0

pgpool-II 3.4
9pgpool-II 3.5

Copyright(c) 2015 SRA OSS, Inc. Japan

1.7 times
faster than
3.4!

Thank you!

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