Boosting Performance andReliability by using pgpool-II
Tatsuo Ishii
SRA OSS, Inc. Japan

Introducing myself●●●

Working on thePostgreSQL project formany years
Enjoying working for an
open source company asa manager/engineer
Original author ofpgpool-II3

What is pgpool-II anyway?
●●

One of open source cluster middle ware for PostgreSQL
Add functionalities which are currently missing in
PostgreSQL●Connection pooling●Load balancing●On memory query cache●Fail over●●Replication(either built in synchronous replication or external
replications engines such as streaming replication and Slony-I)
Built in HA(watchdog)

2012/10/24

Copyright(c)2012 Tatsuo Ishii

4On memory query cache

2012/10/24

Copyright(c)2012 Tatsuo Ishii

Major features of “on memoryquery cache”●If cache hits, no access to PostgreSQL is involved, thus very fast●

Choices of cache storage●

Shared memory–●Fast and easy to use but cache size is limited

Memcached–Can be used as very large cache storage, but network overhead is involved●Many of applications do not need to modify to use cache●

Automatic cache invalidation●●Table updates (including ALTER TABLE)●Cache expiration by specified duration●There are some cases which pgpool cannot recognize table update

Can specify which table should be in cache and which is not by using
regular expression

2012/10/24

Copyright(c)2012 Tatsuo Ishii

5Data flow of “on memory query cache”
If there's cache for the query result,
returns it directly to client
Cache
storage
SELECT

Result cache
Available?

SharedmemoryORmemcachedNOSQL parser

At commit save the query result
to the cache storage

Access toPostgreSQL
If there's no cache for thequery result, get result from
PostgreSQL and returns to client
2012/10/24

Copyright(c)2012 Tatsuo Ishii

6

How to identify
cache objects?
●●●

Cache is essentially identified by query string
Pgpool does not store query string itself. Rather , stores md5 result
to save space for very long query string
Simple query case●●

Cache id = md5(SQL string + user name + database name)

Extended protocol case(prepared statements)●

Cache id = md5(SQL string + bind parameters + user name + database name)2012/10/24

Copyright(c)2012 Tatsuo Ishii

7

When pgpool does notcreate cache●

SELECTs including views●●Pgpool does not know changes of underlying tables

SELECTs including non immutable functions●

Consider “SELECT current_timestamp;”●

SELECTs including temp tables, unlogged tables●

SELECT result is too large (memqcache_maxcache)●

SELECT FOR SHARE/UPDATE●SELECT starting with "/*NO QUERY CACHE*/" comment●

SELECT including system catalogs●Tables listed in “black_memqcache_table_list”●Tables listed in “white_memqcache_table_list” will be cached even above
conditions are met

2012/10/24

Copyright(c)2012 Tatsuo Ishii

8

SELECT in an explicit
transaction
BEGIN;INSERT INTO table1...;INSERT INTO table2...;
SELECT * FROM table1;
SELECT * FROM table2;::
ROLLBACK;2012/10/24

Cannot cache these results
because previous INSERTs
canceled.

Copyright(c)2012 Tatsuo Ishii

9Committing to cache storage
when transaction commits
BEGIN;INSERT INTO table1...;INSERT INTO table2...;
SELECT * FROM table1;
SELECT * FROM table2;::COMMIT;2012/10/24

Cache buffer

Copyright(c)2012 Tatsuo Ishii

CommitCache storage

10

Invalidation/expirationof cache●●

Cache is invalidated when corresponding
tables are updated
Cache is expired when memcache_expire
reached2012/10/24

Copyright(c)2012 Tatsuo Ishii

1112Limitations●●●

If a table is modified, all caches for the table are invalidated●

Not recommended for update intensive systems●

Best for systems whose cache hit ratio > 70%

Users can only access to cache which was created by the sameuser(security reason)
Views are not cached●Pgpool-II is unable to know the updates to the underlying tables●

Cascade update to tables are not recognized●

Table change by trigger is not recognized●Too large query result cannot be cached(~1MB, depending on
the configuration)

2012/10/24

Copyright(c)2012 Tatsuo Ishii

13

Shmem cache data structure
Hash index
Cache id/md5

Cache bodyheaderheaderdatadata

Cache block

Cache id/md5
Cache id/md5

Cache block

Cache id/md5
Cache block
Cache block

2012/10/24

Copyright(c)2012 Tatsuo IshiiClock hand

14

Benchmark configuration
memcached

PostgreSQL 9.1(primary)Pgpool-II
pgbenchStreaming
Replication

All the machines share same spec:
Cent OS 5.5
Core 2 Duo 2.33GHz
Mem: 2GB

2012/10/24

PostgreSQL 9.1standby)

Copyright(c)2012 Tatsuo Ishii

15

Benchmark configuration●●

Scale factor = 1(13MB DB)
pgbench_accounts primary key is removed tosimulate non trivial SELECTs●●

SELECT only test●●

Each SELECT takes about 6.7ms
Pgbench -S -T 10 (1 run is 10seconds)

Cache storage is 64MB(for both shmem andmemcached)

2012/10/24

Copyright(c)2012 Tatsuo Ishii

16First 20 run of pgbench
Filling up cache

Effect of loadbalance

2012/10/24

Copyright(c)2012 Tatsuo Ishii

17

After cache is filled up
After cache is filled up
both cache method keephigh performance.
●
Shmemoutperforms memcachedsignificantly.
●

2012/10/24

Copyright(c)2012 Tatsuo Ishii

18

Built in HA: Watchdog

2012/10/24

Copyright(c)2012 Tatsuo Ishii

Pgpool-II manages fail overAP server
detachbackend1

What if pgpool
server goes down?

Pgpool processPostgreSQL

detachCopyright © 2012 SRA OSS, Inc. Japan All rights reserved.

19

What about adding standby Pgpool-II?
Problem: needs operator control

AP server

Pgpool process

Pgpool process

PostgreSQL

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

20

Solution: Watchdog
AP server

Virtual IP

Watchdog process

Mutual monitoring

Self monitoring

Watchdog process
Self monitoring

Pgpool process

Pgpool process

PostgreSQL
replication
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

21

22

Why we need watch dog?
●●We already have pgpool-HA
Watchdog is a better substitute for pgpoolHA because:●●Easy to install/manage
Can control multiple pgpool-II instances, which
is important in multi master configuration

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

Multi master configuration

23

AP

AP
Load balancer
All pgpool are active●No VIP is used●AP
ServerWatchdog process
Self monitoring

Pgpool process

APServerWatchdog process
Self monitoring

Pgpool process

APServerWatchdog process
Self monitoring

Pgpool process

PostgreSQL
replication
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

Watchdog synchronizes status
APServerWatchdog process
Self monitoring

Pgpool process

APServerWatchdog process
Self monitoring

Pgpool process

APServer24

Watchdog process
Self monitoring

Pgpool process

SwitchPostgreSQL
replication

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

Synchronizing backend statusamong pgpool instances●

Attaching node●●Detaching node●●

pcp_attach_node
Either by pcp_detach_node or fail over event

Promoting node●

pcp_promote_node

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

25

Watchdog coordinates online recovery

APServerWatchdog process
Self monitoring

Pgpool process

APServerWatchdog process
Self monitoring

Pgpool process

APServer26

Watchdog process
Self monitoring

Pgpool process

PostgreSQL
Online recovery

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

27

Future plans for watchdog●

Secure protocol●●

Currently communication between watchdog is
not secure (no authentication required)

Allow to handle multiple communication
path between watchdog

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

28Summary●●Pgpool-II enhances DB performance●Connection pooling●

Load balancing●On memory query cachePgpool-II enhances DB availability●

Replication●

Fail over●

Watchdog
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

29References●

Pgpool-II official site●

http://www.pgpool.net●

Download

●

Docs
–

There are nice tutorials.
Please take a look at!

–

http://www.pgpool.net/media
wiki/index.php/Documentation●

Mailing lists●

Bug track

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