Postgresql – Poor performance in the pgpool cluster

performancepgpoolpostgresql

I've configured a postgres cluster with pgpool2 using the following link.

The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for example:

pgbench -c 16 -j 16 -T 600 -S bench2 -h "ONE SINGLE POSTGRES NODE" -p
5432 number of transactions actually processed: 7752147 tps =
12920.095988 (including connections establishing) tps = 12921.661448 (excluding connections establishing)

pgbench -c 16 -j 16 -T 600 -S bench2 -h "PGPOOLNODE" -p 5432 number of
transactions actually processed: 389800 tps = 648.857810 (including
connections establishing) tps = 648.886713 (excluding connections
establishing)

pgbench -c 16 -j 16 -T 600 -S bench2 -h "MASTERNODE"-p 5432 number of
transactions actually processed: 7093473 tps = 11822.379159 (including
connections establishing) tps = 11823.337051 (excluding connections
establishing)

Each node is configured default.
All nodes are identical virtaul machines and over the same network.

This is my pgpool.conf:

listen_addresses = '*'
port = 5432
socket_dir = '/var/run/postgresql'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = '192.168.1.177'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.175'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.1.176'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = off
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr'
print_timestamp = on
log_connections = on
log_hostname = off
log_statement = on
log_per_node_statement = on
log_standby_delay = 'none'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/var/log/postgresql'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'nextval,setval'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 0
sr_check_user = 'ZZZ'
sr_check_password = 'YYY'
delay_threshold = 0
follow_master_command = ''
parallel_mode = off
enable_query_cache = off
pgpool2_hostname = ''
system_db_hostname  = 'localhost'
system_db_port = 5432 
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = 'zzz'
health_check_period = 0
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'zzz'
failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'
failback_command = ''
fail_over_on_backend_error = on
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
relcache_expire = 0

Any suggestion? Thanks

Best Answer

Relationship between max pool, num init children, and max connections

max_pool parameter configures how many connections to cache per child. So if num_init_children is configured to 100, and max_pool is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.

A child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3

Your config

num_init_children = 32
max_pool = 4

That means You pgpool instance is able to open up to 128 connections (4*32). But pgbench is using only one user and is executed with concurrency 16. According to second quoted paragraph above pgpool won't open a new backend connection, because there is already user,database combination in the connection cache.

Also number of tps in pgpool test is ~16 times lower (a little bit more than that) then the other two tests.

Unfortunately I'm not so familiar with pgpool and I don't know how to improve the performance.

Related Question