[pgpool-general: 6594] pgpool 4.0.5 performance

Thu Jun 13 22:09:27 JST 2019

Hi, I'm trying to measure the performance impact of pgpool in a PostgreSQL stream-replication landscape.
I have done a lot of tests, in all of them:

1) we achieve best performance when connecting directly to single (master) database
2) when using pgpool that connects to a single (master) database performance decrease
3) when using pgpool that connects to multiple databases (loadbalancing) performance decrease further

Therefore, it seem there are no performance advantages using pgpool.

Is this normal or should we expect better performance when using pgpool that connects to multiple databases (loadbalancing)?
Is there any pgpool configuration we could be missing?

Thanks

This is my test:

#------------------------------------------------------------------------------
# PERFORMACE TEST CASE
#------------------------------------------------------------------------------

#------------------------------------------------------------------------------
# HARDWARE
#------------------------------------------------------------------------------
DATABASE_SERVER1:
vmware esx 6.0,
2x VCPU (Intel(R) Xeon(R) CPU E5-2665 0 @ 2.40GHz),
3GB RAM,
CentOS 7.6 (minimal)
psql (PostgreSQL) 10.8 (RPMs from PGDG)

DATABASE_SERVER2:
vmware esx 6.0,
2x VCPU (Intel(R) Xeon(R) CPU E5-2665 0 @ 2.40GHz),
3GB RAM,
CentOS 7.6 (minimal)
psql (PostgreSQL) 10.8 (RPMs from PGDG)

PGPOOL_SERVER:
vmware esx 6.0,
2 VCPU (Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz),
3GB RAM,
CentOS 7.6 (minimal)
pgpool-II version 4.0.5 (torokiboshi) (RPMs from www.pgpool.net)

#------------------------------------------------------------------------------
# ARCHITETTURE
#------------------------------------------------------------------------------
database1 is the master, database2 is a follower in stream replication with replication_splot
PostgreSQL default configuration plus

[postgresql.conf]
listen_addresses = '*'
wal_level = 'logical'
max_worker_processes = 4
max_replication_slots = 4
max_wal_senders = 4
track_commit_timestamp = on
log_statement = 'all'
hot_standby = on
hot_standby_feedback = on

#
# PGPOOL CONFIGURATION BALANCING (FOR ONLY MASTER I HAVE COMMENTED BACKEND_1)
# [pgpool.conf]
#

#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
backend_hostname0 = 'DATABASE_SERVER1'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_MASTER'
backend_hostname1 = 'DATABASE_SERVER2'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
allow_clear_text_frontend_auth = off
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = off
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
log_destination = 'syslog,stderr'
log_line_prefix = '%t: pid %p: '
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_client_messages = off
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
# - Debug -
# log_min_messages = info           # comment to default
# client_min_messages = log         # comment to default
# log_error_verbosity = verbose     # comment to default
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval,f_unaccent,safeNext10digit,business_partners_earth_func,crm.mde_audit_func,update_crm_record_status_branch_from_business_partners,update_crm_record_status_empl_from_business_partners,port_searth_func,crm.customer_audit_func'
black_query_pattern_list = ''
database_redirect_preference_list = 'pgbench:standby'
app_name_redirect_preference_list = ''
allow_sql_comments = off
disable_load_balance_on_write = 'transaction'
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'secret'
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_master_command = ''
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_database = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = ''
failback_command = ''
failover_on_backend_error = on
detach_false_primary = off
search_primary_node_timeout = 300
#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''

# ------------------------------------------------------------------------------
# TEST CASE
#------------------------------------------------------------------------------

# -----------------------------
# INIT
# -----------------------------

dropdb   -U postgres -h DATABASE_SERVER1 --if-exists pgbench
createdb -U postgres -h DATABASE_SERVER1 pgbench
pgbench  -U postgres -h DATABASE_SERVER1 -i pgbench -s 10 pgbench

# -----------------------------
# TESTS 1
# -----------------------------

# I have done the same test for each configuration type
pgbench --client=8 --jobs=4 --progress=60 --transactions=5000 --host=$server --username=postgres pgbench

# -----------------------------
# RESULTS
# -----------------------------

# -------------------------------------------
# direct-to-master without pgpool
# -------------------------------------------
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 8.211 ms
latency stddev = 12.908 ms
tps = 966.141510 (including connections establishing)
tps = 966.557028 (excluding connections establishing)

# -------------------------------------------
# via pgpool in balance mode
# -------------------------------------------
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 15.468 ms
latency stddev = 19.637 ms
tps = 511.431795 (including connections establishing)
tps = 511.572327 (excluding connections establishing)

# -------------------------------------------
# via pgpool with only master db
# -------------------------------------------
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 14.047 ms
latency stddev = 19.320 ms
tps = 565.119523 (including connections establishing)
tps = 565.271648 (excluding connections establishing)

# -----------------------------
# TESTS 2
# -----------------------------

# I have done the same test for each configuration type
# scaling 50
# memory_cache_enabled = off
pgbench --client=8 --jobs=4 --progress=60 --transactions=5000 --host=$server --username=postgres pgbench

# -------------------------------------------
# via pgpool with only master db
# -------------------------------------------
scaling factor: 50
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 8.494 ms
latency stddev = 12.007 ms
tps = 934.019342 (including connections establishing)
tps = 934.392798 (excluding connections establishing)

# -------------------------------------------
# via pgpool in balance mode
# -------------------------------------------
scaling factor: 50
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 32.112 ms
latency stddev = 191.597 ms
tps = 248.271885 (including connections establishing)
tps = 248.303337 (excluding connections establishing)

# -------------------------------------------
# direct-to-master without pgpool
# -------------------------------------------
scaling factor: 50
query mode: simple
number of clients: 8
number of threads: 4
number of transactions per client: 5000
number of transactions actually processed: 40000/40000
latency average = 18.134 ms
latency stddev = 58.995 ms
tps = 438.824935 (including connections establishing)
tps = 438.918575 (excluding connections establishing)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20190613/7821be45/attachment-0001.html>