5

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.