EDB & PGPOOL Relationship andPGPOOL II 3.4 Benchmarking
results on AWSMay, 2015
© 2014 EnterpriseDB Corporation. All rights reserved.

!1

!
!

• Ahsan Hadi!
!

• Senior Director of Product Development with EnterpriseDB!
!

• Managing PGPOOL II development from EDB© 2014 EnterpriseDB Corporation. All rights reserved.

!2

EnterpriseDB Product Overview
Cloud Enablement
Cloud Database forAmazon Web Services

Open Stack Support*

APIs to enable thePlatform as a Service*

Management andMonitoring

Tools
xDB Replication
ServerBack and Recovery
Tool

Failover Manager

Migration Tool Kit

Postgres EnterpriseManager (PEM)
Stack Builder &Update Monitor

The Database
PostgreSQL / Postgres Plus Advanced ServerCore

Extension

PostgreSQL

Components (PostGIS, pgPool, pgBouncer, SQL/Protect, PL/Perl Python TCL, FDW)

OR
Postgres Plus
Advanced Server

Connectors (.Net, JDBC, ODBC,OCL)
Utilities (EDBLoader, EDBPlus, ECPGPlus)* Roadmap© 2014 EnterpriseDB Corporation. All rights reserved.

!3

About my© 2014 EnterpriseDB Corporation. All rights reserved.

!4

How EDB uses PGPOOL II© 2014 EnterpriseDB Corporation. All rights reserved.

!5

PPCD - pgpool-II in the cloud© 2014 EnterpriseDB Corporation. All rights reserved.

!6PPCD/pgpool II…
!

• PPCD allows the user to host an instance of PG and/or PPAS inthe cloud.
!

• PGPOOL II runs on the master node!

• Client apps connect to PGPOOL.
!

• Performs load balancing by sending the write queries to masterand read queries across read replicas!

• Performs connection pooling© 2014 EnterpriseDB Corporation. All rights reserved.

!7

Other services!

• EDB offering support and services for pgpool II!

• Some BIG companies using pgpool II!

• US Based Credit card company• Indian Government Organisations• Healthcare• Telecom• IT solution providers and consultancy services
and More….

© 2014 EnterpriseDB Corporation. All rights reserved.

!8

Most commonly used features!

In order of priority…
!

• Load Balancing (sql routing)!

• Failover with streaming replication!

• Connection Pooling!

• Watchdog PGPOOL II HA© 2014 EnterpriseDB Corporation. All rights reserved.

!9

Features not used!
!
!

• Parallel Query!
!
!

• PGPOOL Replication© 2014 EnterpriseDB Corporation. All rights reserved.

!10

A production customer scenario

Stand-bydyStreaming Replication

arelnoRepMgrApplications

RHCS Active/Passive

PGPOOL-II
1

read/write

PGPOOL-II
2Primary© 2014 EnterpriseDB Corporation. All rights reserved.

!11

2013

2014

2015

60
30

Customer issues/Queries

45
15© 2014 EnterpriseDB Corporation. All rights reserved.

!1215
20

14
20

20

130

YEAR

!
• Overall it has gotten very stable and resilient in last few majorreleases.

!

• Types of customer issues reported in last two years (most of theseissues have been resolved)!

• Handling SSL based authentication• Performance issues with extended query
protocol (performance not as good as simple
query protocol)• Issues with failover (not able to connect tostand-by immediately when failoverhappens)• Sequence functions don’t work with pgpool• Performance issues with load balancing, theTPS is not very high• Configuration issues• Memory leaks (memory issues addressed byplugging in PG memory and exceptionmanagement)!

!

!
© 2014 EnterpriseDB Corporation. All rights reserved.

!13!

!
•Improve performance with extended query protocol!
•Watchdog enhancements (making it more resilient and handle scenarios
like split-brain, automatic failover)!
•Being able to install individual features of pgpool…I am only interested inconnection pooling…
!
•Enhancements with Failover functionality..seamless failover!
•Support more platforms, HPUX, solaris, windows…
!
•GUI Installer and GUI monitoring tool!
•Better documentation!

© 2014 EnterpriseDB Corporation. All rights reserved.

!14

Benchmarking PGPOOL-II 3.4 on AWS - Environment!
• PGPOOL-II version 3.4!
• PG version 9.3.5!
• Benchmarking tool pgbench!
• Benchmarking done on AWS instances!
• One pgpool-II instance with 5 read replicas!
• Compared with PG running stand-alone!
• PGPOOL-II configures with streaming replication, master/slave mode.

!
• Apples-to-Apples Comparison (Same optimal real-world PG configuration changes
across standalone PG and pgpool-II nodes)© 2014 EnterpriseDB Corporation. All rights reserved.

!15

Benchmarking PGPOOL-II 3.4 on AWS - Conclusions!

• Horizontal scalability when the database grows
out of single server memory!

• With big data, concurrent clients and 5 read
replicas the TPS is 2/3 times then community PG!

• After 4 read replica’s the performance bar comes
down with the given workload.
!

• Bad Performance with extended query protocol© 2014 EnterpriseDB Corporation. All rights reserved.

!16

Benchmarking PGPOOL-II 3.4 on AWS - Actual Results© 2014 EnterpriseDB Corporation. All rights reserved.

!17

Benchmarking PGPOOL-II 3.4 on AWS - Actual Results© 2014 EnterpriseDB Corporation. All rights reserved.

!18

Benchmarking PGPOOL-II 3.4 on AWS - Actual Results© 2014 EnterpriseDB Corporation. All rights reserved.

!19

PGPOOL II Future!

• Very Bright!

• More remaining to be done for performance andfailover management. Need to invest in tooling.
!

• Will become the middleware product of choice
for Load Balancing, Failover and HA for PG.
!

• Need more funding and volunteers© 2014 EnterpriseDB Corporation. All rights reserved.

!20

© 2014 EnterpriseDB Corporation. All rights reserved.

!21