[pgpool-general: 6595] Re: pgpool 4.0.5 performance

Thu Jun 13 22:37:32 JST 2019

There's an excellent blog regarding Pgpool-II performance.
http://realtechtalks.com/index.php/2019/04/02/pgpool-ii-load-balancing/

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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)
> 
> 