pgpool: Features andDevelopment
Tatsuo Ishii
pgpool Global Development GroupSRA OSS, Inc. Japan

Agenda●Developers●History●Existing pgpool project●Ongoing pgpool-II project●Demonstration

Who are we?
●pgpool Global Development Group– Tatsuo Ishii(SRA OSS, Inc. Japan)– Devrim Gunduz(Command Prompt, Inc.)– Yoshiyuki Asaba(SRA OSS, Inc. Japan)– Taiki Yamaguchi(SRA OSS, Inc. Japan)– pgpoo-II development team
●In addition to Tatsuo, Yoshiyuki and Taiki:– Tomoaki Sato, Yoshiharu Mori, Kaori Inaba (all from SRAOSS, Inc. Japan)

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

3

Developers!
TomoakiCommunicationmanager

Kaori
Project manager
System DBTaikiPCPQuery Cache
pgpooldeveloper

TatsuoEnhancepgpool-I
pgpooldeveloper
2006/07/09 Tronto

Yoshiharu
Query rewriting
Parallel execution
engin

Our Boss“Green Turtle”

Copyright(c) 2006 pgpool DG

Yoshiyuki
Project leader
pgpooldeveloper4

pgpool-I: the history●

V0.1: Started as a personal project(2003/6/27)●

V1.0: Synchronous replication (2004/3)●

V2.0: Load balance (2004/6)●

V3.0: pgpool Global Development
Group(2006/2)

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

5Why pgpool?
●No general purpose connection poolingsoftware was available– Java has its own, but PHP does not...

●No small to mid scale/light weightsynchronous replication tool wasavailable

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

6pgpool process architecture
pgpoolparent process
pre-fork

pgpoolchild process

PostgreSQL
backend process

pgpool.conf
2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

7pgpool functionality:
connection pooling●●Reduce connection overhead
Limit maximum number of connections tothe PostgreSQL backend–New incoming connections are queued in
the kernel if all pgpool processes are busy

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

8How does connection poolingwork?(1)pgpoolPostgreSQL

user1/db1
user1/db1
user1/db1
user1/db1

user1/db1
user1/db1

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

PostgreSQL

PostgreSQL

PostgreSQL

9

How does connection poolingwork?(2)pgpooluser1/db1
user2/db2

user2/db2

user3/db3

user2/db2

user3/db3
user3/db3

2006/07/09 Tronto

user2/db2

Copyright(c) 2006 pgpool DG

PostgreSQL

PostgreSQL

PostgreSQL

10

pgpool understands
frontend/backend protocol●●Pgpool is transparent to both applications
and PostgreSQL
Virtually no modifications to applications
are needed●

No special APIs are needed●

Can be used with existing language APIs●

Can be more efficient than libpq becauseof smaller controlling granuality

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

11Limitations of current
implementation●

resetting pooled connection may not beperfect(need modification to applications)– temp tables– need help from PostgreSQL●SSL is not supported– fall back to non SSL mode●no pg_hba.conf like IP based
authentication

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

12pgpool functionality: replication●Queries are duplicated and sent toPostgreSQL servers

query

PostgreSQLpgpool
query

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

PostgreSQL

13

Dead lock problemtmastersession 1
session 2
lock

wait

secondarysession 1
session 2

locklock

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

waitlock

14

“Strict” mode in replication
to avoid deadlock problem
Querypgpool

Querypacket

PostgreSQL
PostgreSQL

pgpoolpgpoolWait until
somethingreturns

Query

PostgreSQL
PostgreSQL
PostgreSQL
PostgreSQL

reply backthe result
2006/07/09 Tronto

pgpoolWait until
somethingreturns

Copyright(c) 2006 pgpool DG

PostgreSQL
PostgreSQL
15

pgpool functionality: loadbalanace●●SELECT queries are sent to randomly
chosen backend
The ratio for load balancing can bechangedPostgreSQL
pgpoolPostgreSQL

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG16

Limitations of current
implementation in replication
●

●
●

●

CURRENT_TIMESTAMP and server- dependentvalue-returning-functions cannot be replicatedMD5 and crypt authentication are not supported
Sequences and SERIAL needs table locking if
there are more than 1 connections
Functions having side effects cannot be loadbalanced

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

17pgpool-II project
●

●

●

●

Information-Technology Promotion
Agency, Japan (IPA: http://www.ipa.go.jp)
granted projectStarted in February 2006, expected torelease the first version in September2006 under BSD license
Features including parallel query andenhancement to pgpool
Successor to pgpool

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

18Goal of pgpool-II project●Implement parallel query processing●Enhance pgpool– Allow to have more than 2 DB nodes– More precise control using shared memory– Easy to manage

Control port/protocol● Detailed statistics on node status● GUI management tool●

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

19pgpool-II architecture overview
ClientpgpoolAdmin

PCP command

PCP libCommunication
Managersharedmemory
manager

Replication/loadbalanceengineSQL
Parser

QueryCache

Query
Rewriting

pgpoolCatalog

ParallelexecutionenginePostgreSQL

System DB

pgpool-II
PostgreSQL

DB node2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

20

pgpool-I and pgpool-II mode
replicationload balance
fail overvirtually compatible with pgpool

parallel query

pgpoo-I
mode

1000
2000
3000
4000

2006/07/09 Tronto

pgpoo-II
mode

1000
2000
3000
4000

1000
2000

Copyright(c) 2006 pgpool DG

3000
4000

21

Table partitioning control

CREATE TABLE dist_def (dbname TEXT,-- database nameschema_name TEXT, --schema nametable_name TEXT, -- table namecol_name TEXT, -- key col namecol_list TEXT[], -- col names
type_list TEXT[], -- col types
dist_def_func TEXT, -- function name
PRIMARY KEY (dbname, schema_name, table_name));

INSERT INTO dist_def VALUES ('y-mori','public','accounts','aid',
ARRAY['aid','bid','abalance','filler'],
ARRAY['integer','integer','integer','character(84)'],'dist_def_accounts');
CREATE OR REPLACE FUNCTION dist_def_accounts (val ANYELEMENT)
RETURNS INTEGER AS 'SELECT CASE WHEN $1 >= 0 and $1 < 100001 THEN 0
WHEN $1 >= 100001 and $1 < 200001 THEN 1
WHEN $1 >= 200001 and $1 < 300000 THEN 2END'LANGUAGE
2006/07/09
Tronto SQL;
Copyright(c) 2006 pgpool DG

22

Simple parallel query
SELECT * FROM accounts
WHERE aid = 1000;

SELECT * FROM accounts
WHERE aid = 1000;

PostgreSQL
pgpoolPostgreSQLSELECT * FROM accounts
WHERE aid = 1000;

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

23Complex query example
SELECT * FROM accounts
WHERE aid = 1000
ORDER BY aid;

SELECT * dblink('con','SELECT pool_parallel('SELECT * FROM accounts
WHERE aid = 1000')') AS foo(...)
ORDER BY aid;

pgpoolSystem DB
PostgreSQL
pgpoolPostgreSQL

2006/07/09 Tronto

SELECT pool_parallel('SELECT * FROM accounts
WHERE aid = 1000')')

SELECT * FROM accounts
WHERE aid = 1000;

Copyright(c) 2006 pgpool DG

24

DML
●

●

INSERT recognize partition key value in aquery and INSERT into appropreate DBnode
UPDATE/DELETE simply issues the same
query to all DB nodes

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG25

INSERT
Syetm DB

call
INSERT INTO accounts(aid, bid)
VALUES(500,100);

return
DB node = 1

SELECT
dist_def_accounts(500);

INSERT

DB node 0

DB node 1

aid = 0-499

aid = 500-999

2006/07/09 Tronto

DB node 2
aid = 1000-1499

Copyright(c) 2006 pgpool DG

26

Query Cache●Caches query result●Caches can be validated by pgpoolAdmin
CREATE TABLE pgpool_catalog.query_cache (hash TEXT,-- query string(MD5 hashed)query TEXT,-- query stringvalue bytea,-- query result(RowDescription andDataRow packets)
dbname TEXT,-- database namecreate_time TIMESTAMP WITH TIME ZONE, -- cache creation time
PRIMARY KEY(hash));2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

27

Using parallel query andreplication together
pgpool-II
with parallel query

pgpool-II
with replication

PostgreSQL

2006/07/09 Tronto

PostgreSQL

Parallel querylayer

pgpool-II
with replication

PostgreSQL

Copyright(c) 2006 pgpool DG

Replicationand loadbalance
layer

PostgreSQL

28

pgpoolAdmin●Web based pgpool
management tool

●Apache/PHP/Smarty●functions–Stopping pgpool–Switch over–Monitoring connection pool status–

Monitoring process status–Editing configuration file

–Query Cache management

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG29

Benchmarks●Hitachi Blade Symphony BS1000– 10 blades– Xeon 3.0GHz x 1– 1GB Mem– UL320 SCSI Disks●Cent OS 4.3●

PostgreSQL 8.1.4/pgbench●Please note that these results are measured on
pre-alpha version of pgpool-II and maybe slightly
different from the future official release version

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG30

Sequencial Scan case(mid size)●
PostgreSQL

pgpool-II

1.3
1.2

●

1.1
10.9

TPS

0.8
0.7
0.6

●

0.5
0.4
0.3
0.2
0.1
012481632

scale factor = 90
(90M rows)
SELECT * FROM
accounts WHERE
aid = :aid
pgpool-II is 7 times
faster at the best
(32 concurrentusers)

concurrent users
2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

31

Index Scan case(mid size)●

TPS

PostgreSQL

3750
3500
3250
3000
2750
2500
2250
2000
1750
1500
1250
1000
750
500
250
0pgpool-II●●

12481632

scale factor = 90(90M rows)
SELECT * FROM
accounts WHERE
aid = :aid
pgpool-II is 9 times
faster at the best(16 concurrentusers)

concurrent users
2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

32

Index Scan case(large size)●PostgreSQL

pgpool-II

1300
1200

●

1100
1000
900

TPS

800
700
600

●

500
400
300
200
100
0

1248

1632

scale factor = 900(900M rows)
SELECT * FROM
accounts WHERE
aid = :aid
pgpool-II is 18
times faster at thebest (32concurrent users)

concurrent users
2006/07/09 Tronto

Copyright(c) 2006 pgpool DG33complex query case●
PostgreSQLpgpool-II4540●

35TPS30252015●

10501248scale factor = 900(900M rows)
SELECT a1.abalance
FROM accounts as a1,accounts as a2
WHERE a1.aid = :aid1
and a2.aid = :aid2

pgpool-II is faster
only when at 8
concurrent users

concurrent users
2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

34

pgpool-II restrictions●●

SQL restrictions–

COPY is not supported–

SELECT INTO, INSERT INTO ... SELECT is not supported–

Extended protocol is not supported–INSERT requires explicit values if target column is the key for datapatitioning–

UPDATE with WHERE clause including function call is not supported–

CREATE TABLE/ALTER TABLE requires pgpool restartingTransactions–

If a DB node fails during INSERT/UPDATE/DELETE, data consistency willbe broken–

SQL commands via dblink will be executed as separate transactions

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

35

Future plans(TODO)●

More intelligent query rewriting●Remove SQL restrictions●●Employ two phase commit to keep consistency
among DB nodes. However this technique canbe used only for queries outside transaction
block since PREPARE TRANSACTION closes
current transaction block
More intelligent cache validation

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

36

References●

pgpool page–●

http://pgpool.projects.postgresql.org/

pgpool-II page–English page●–

http://pgpool.projects.postgresql.org/pgpool-II/en/

Japanese page●

http://pgpool.projects.postgresql.org/pgpool-II/ja/

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG

37

Thank you!

2006/07/09 Tronto

Copyright(c) 2006 pgpool DG38