<HTML><BODY>
<div><br></div><div><div>Hello,</div><div><br></div><div>We're experiencing very slow write performance while using pgpool with 3 backend postgres nodes. We noticed it now that we have an import job running trough tomcat that imports millions of rows in the database using insert and update statements.</div><div><br></div><div>When running without pgpool the time for completion of the import is literally 10 times less. (e.g 20 minutes instead of more than 3 hours).</div><div>I know there is some overhead, but it's cited to be around 20%.</div><div><br></div><div>Could someone please assist in tuning the write performance of the installation.</div><div><br></div><div>Latest stable version of pgpool 3.4.3-1</div><div><br></div><div>Here is the config:</div><div><br></div><div><br></div><div>listen_addresses = '0.0.0.0'</div><div>port = 9999</div><div>socket_dir = '/tmp'</div><div>listen_backlog_multiplier = 2</div><div>pcp_listen_addresses = '*'</div><div>pcp_port = 9898</div><div>pcp_socket_dir = '/tmp'</div><div>backend_hostname0 = '192.168.10.38'</div><div>backend_port0 = 5432</div><div>backend_weight0 = 1</div><div>backend_data_directory0 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div><div>backend_hostname1 = '192.168.10.40'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div><div>backend_hostname2 = '192.168.10.41'</div><div>backend_port2 = 5432</div><div>backend_weight2 = 1</div><div>backend_data_directory2 = '/var/lib/pgsql/9.4/data'</div><div>backend_flag2 = 'ALLOW_TO_FAILOVER'</div><div>enable_pool_hba = on</div><div>pool_passwd = 'pool_passwd'</div><div>authentication_timeout = 60</div><div>ssl = on</div><div>ssl_key = './server.key'</div><div>ssl_cert = './server.crt'</div><div>num_init_children = 3000</div><div>max_pool = 2</div><div>child_life_time = 600</div><div>child_max_connections = 0</div><div>connection_life_time = 0</div><div>client_idle_limit = 0</div><div>log_destination = 'stderr syslog'</div><div>log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.</div><div>log_connections = off</div><div>log_hostname = off</div><div>log_statement = off</div><div>log_per_node_statement = off</div><div>log_standby_delay = 'none'</div><div>syslog_facility = 'LOCAL0'</div><div>syslog_ident = 'pgpool'</div><div>debug_level = 1</div><div>log_error_verbosity = verbose # terse, default, or verbose messages</div><div>pid_file_name = '/var/run/pgpool/pgpool.pid'</div><div>logdir = '/var/log/pgpool'</div><div>connection_cache = off</div><div>reset_query_list = 'ABORT; DISCARD ALL'</div><div>replication_mode = off</div><div>replicate_select = off</div><div>insert_lock = on</div><div>lobj_lock_table = ''</div><div>replication_stop_on_mismatch = off</div><div>failover_if_affected_tuples_mismatch = off</div><div>load_balance_mode = on</div><div>ignore_leading_white_space = on</div><div>white_function_list = ''</div><div>black_function_list = 'nextval,setval,nextval,setval'</div><div>database_redirect_preference_list = ''</div><div>app_name_redirect_preference_list = ''</div><div>allow_sql_comments = off</div><div>master_slave_mode = on</div><div>master_slave_sub_mode = 'stream'</div><div>sr_check_period = 0</div><div>sr_check_user = 'postgres'</div><div>sr_check_password = 'hiddenpass'</div><div>delay_threshold = 1000</div><div>follow_master_command = '/usr/local/pgsql/scripts/followmaster.sh %d %h %P %H 9898'</div><div>health_check_period = 5</div><div>health_check_timeout = 20</div><div>health_check_user = 'postgres'</div><div>health_check_password = 'hiddenpass'</div><div>health_check_max_retries = 3</div><div>health_check_retry_delay = 2</div><div>connect_timeout = 10000</div><div>failover_command = '/usr/local/pgsql/scripts/failover.sh %d %H /var/lib/pgsql/9.4/data/mastertrigger.txt'</div><div>failback_command = '/usr/local/pgsql/scripts/failback.sh %d %M'</div><div>fail_over_on_backend_error = off</div><div>search_primary_node_timeout = 10</div><div>recovery_user = 'postgres'</div><div>recovery_password = ''</div><div>recovery_1st_stage_command = ''</div><div>recovery_2nd_stage_command = ''</div><div>recovery_timeout = 90</div><div>client_idle_limit_in_recovery = 0</div><div>use_watchdog = on</div><div>trusted_servers = '192.168.10.43,192.168.10.44'</div><div>ping_path = '/bin'</div><div>wd_hostname = '192.168.10.43'</div><div>wd_port = 9000</div><div>wd_authkey = ''</div><div>delegate_IP = '192.168.10.100'</div><div>ifconfig_path = '/usr/bin'</div><div>if_up_cmd = 'sudo /sbin/ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'</div><div>if_down_cmd = 'sudo /sbin/ifconfig eth0:0 down'</div><div>arping_path = '/usr/bin' # arping command path</div><div>arping_cmd = 'sudo /usr/sbin/arping -I eth0 -U $_IP_$ -w 1'</div><div>clear_memqcache_on_escalation = on</div><div>wd_escalation_command = ''</div><div>wd_lifecheck_method = 'heartbeat'</div><div>wd_interval = 10</div><div>wd_heartbeat_port = 9694</div><div>wd_heartbeat_keepalive = 2</div><div>wd_heartbeat_deadtime = 30</div><div>heartbeat_destination0 = '192.168.10.44'</div><div>heartbeat_destination_port0 = 9694</div><div>heartbeat_device0 = 'eth0'</div><div>wd_life_point = 3</div><div>wd_lifecheck_query = 'SELECT 1'</div><div>wd_lifecheck_dbname = 'template1'</div><div>wd_lifecheck_user = 'postgres'</div><div>wd_lifecheck_password = 'hiddenpass'</div><div>other_pgpool_hostname0 = '192.168.10.44'</div><div>other_pgpool_port0 = 9999</div><div>other_wd_port0 = 9000</div><div>relcache_expire = 0</div><div>relcache_size = 256</div><div>check_temp_table = on</div><div>check_unlogged_table = on</div><div>memory_cache_enabled = off</div><div>memqcache_method = 'shmem'</div><div>memqcache_memcached_host = 'localhost'</div><div>memqcache_memcached_port = 11211</div><div>memqcache_total_size = 67108864</div><div>memqcache_max_num_cache = 1000000</div><div>memqcache_expire = 0</div><div>memqcache_auto_cache_invalidation = on</div><div>memqcache_maxcache = 409600</div><div>memqcache_cache_block_size = 1048576</div><div>memqcache_oiddir = '/var/log/pgpool/oiddir'</div><div>white_memqcache_table_list = ''</div><div>black_memqcache_table_list = ''</div></div></BODY></HTML>