[pgpool-general: 4603] Using Amazon RDS Replication with pgpool - bad performance though

Roman D dostick at gmail.com
Fri Apr 1 18:49:36 JST 2016


Amazon RDS Master and Read replica.
The goal is to have Read queries balanced to Read Replica using pgpool, while Replication is provided by RDS.
I thought that my case is pretty common, but there’s no mention anywhere on the net or in this list of someone ever done this.

But surprisingly this configuration works!
Using combination of these parameters:
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = ‘stream'

pgpool 3.3.4
Master configured in pgpool.paswd as 0 and replica as 1

Here’s the conf:

backend_hostname0 = ‘master’
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = ‘DISALLOW_TO_FAILOVER’

backend_hostname1 = ’slave’
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = ‘DISALLOW_TO_FAILOVER'

enable_pool_hba = on
pool_passwd = ‘pool_passwd'
ssl = off

num_init_children = 200
max_pool = 4
child_life_time = 300
child_max_connections = 2000
connection_life_time = 300
client_idle_limit = 0
listen_backlog_multiplier = 5

connection_cache = on
reset_query_list = ‘ABORT; DISCARD ALL'

replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 0

parallel_mode = off
health_check_period = 0
use_watchdog = off
memory_cache_enabled = off

The problem is that pgpool is up to 4 times slower then direct connection to database.
Without pgpool database roundtrip is 10 msec. With pgpool it’s 30-40 msec.

pgpool is hosted on separate machine form the application and database. PGPool machine CPU is at 5% ( net.core.somaxconn set to 5000)
application CPU/memory is at 50% and database CPU&memory is at 50% load
so basically pgpool is being the bottleneck, doing something that slows down the database access.

I tried changing every possible parameter, changing num_init_children, max_pool - that does not improve anything.

I am about to give up. Please help! Is there anything else I can look into ?It would be amazing if this worked without adding such significant time overhead. 

Thank you!

More information about the pgpool-general mailing list