[pgpool-general: 4300] Very slow write performance on write queries only

[the Rat]

   Hello,  
  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.  
  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).  I know there is some overhead, but it's cited to be around 20%.  
  Could someone please assist in tuning the write performance of the installation.  
  Latest stable version of pgpool 3.4.3-1  
  Here is the config:  
  
  listen_addresses = '0.0.0.0'  port = 9999  socket_dir = '/tmp'  listen_backlog_multiplier = 2  pcp_listen_addresses = '*'  pcp_port = 9898  pcp_socket_dir = '/tmp'  backend_hostname0 = '192.168.10.38'  backend_port0 = 5432  backend_weight0 = 1  backend_data_directory0 = '/var/lib/pgsql/9.4/data'  backend_flag0 = 'ALLOW_TO_FAILOVER'  backend_hostname1 = '192.168.10.40'  backend_port1 = 5432  backend_weight1 = 1  backend_data_directory1 = '/var/lib/pgsql/9.4/data'  backend_flag1 = 'ALLOW_TO_FAILOVER'  backend_hostname2 = '192.168.10.41'  backend_port2 = 5432  backend_weight2 = 1  backend_data_directory2 = '/var/lib/pgsql/9.4/data'  backend_flag2 = 'ALLOW_TO_FAILOVER'  enable_pool_hba = on  pool_passwd = 'pool_passwd'  authentication_timeout = 60  ssl = on  ssl_key = './server.key'  ssl_cert = './server.crt'  num_init_children = 3000  max_pool = 2  child_life_time = 600  child_max_connections = 0  connection_life_time = 0  client_idle_limit = 0  log_destination = 'stderr syslo
 g'  log_line_prefix = '%t: pid %p: ' 
 # printf-style string to output at beginning of each log line.  log_connections = off  log_hostname = off  log_statement = off  log_per_node_statement = off  log_standby_delay = 'none'  syslog_facility = 'LOCAL0'  syslog_ident = 'pgpool'  debug_level = 1  log_error_verbosity = verbose 
 
 
 
 
# terse, default, or verbose messages  pid_file_name = '/var/run/pgpool/pgpool.pid'  logdir = '/var/log/pgpool'  connection_cache = off  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,nextval,setval'  database_redirect_preference_list = ''  app_name_redirect_preference_list = ''  allow_sql_comments = off  master_slave_mode = on  master_slave_sub_mode = 'stream'  sr_check_period = 0  sr_check_user = 'postgres'  sr_check_password = 'hiddenpass'  delay_threshold = 1000  follow_master_command = '/usr/local/pgsql/scripts/followmaster.sh %d %h %P %H 9898'  health_check_period = 5  health_check_timeout = 20  health_check_user = 'postgres'  health_check_password = 'hiddenpass'  health_check_max_retries = 3
   health_check_retry_delay = 2  connect_timeout = 10000  failover_command = '/usr/local/pgsql/scripts/failover.sh %d %H /var/lib/pgsql/9.4/data/mastertrigger.txt'  failback_command = '/usr/local/pgsql/scripts/failback.sh %d %M'  fail_over_on_backend_error = off  search_primary_node_timeout = 10  recovery_user = 'postgres'  recovery_password = ''  recovery_1st_stage_command = ''  recovery_2nd_stage_command = ''  recovery_timeout = 90  client_idle_limit_in_recovery = 0  use_watchdog = on  trusted_servers = '192.168.10.43,192.168.10.44'  ping_path = '/bin'  wd_hostname = '192.168.10.43'  wd_port = 9000  wd_authkey = ''  delegate_IP = '192.168.10.100'  ifconfig_path = '/usr/bin'  if_up_cmd = 'sudo /sbin/ifconfig 
eth0:0 inet $_IP_$ netmask 255.255.255.0'  if_down_cmd = 'sudo /sbin/ifconfig eth0:0 
down'  arping_path = '/usr/bin' 
 
 
 
 
 # arping command path  arping_cmd = 'sudo /usr/sbin/arping -I eth0 -U $_IP_$ -w 1'  clear_memqcache_on_escalation = on  wd_escalation_command = ''  wd_lifecheck_method = 'heartbeat'  wd_interval = 10  wd_heartbeat_port = 9694  wd_heartbeat_keepalive = 2  wd_heartbeat_deadtime = 30  heartbeat_destination0 = '192.168.10.44'  heartbeat_destination_port0 = 9694  heartbeat_device0 = 'eth0'  wd_life_point = 3  wd_lifecheck_query = 'SELECT 1'  wd_lifecheck_dbname = 'template1'  wd_lifecheck_user = 'postgres'  wd_lifecheck_password = 'hiddenpass'  other_pgpool_hostname0 = '192.168.10.44'  other_pgpool_port0 = 9999  other_wd_port0 = 9000  relcache_expire = 0  relcache_size = 256  check_temp_table = on  check_unlogged_table = on  memory_cache_enabled = off  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 = ''  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20160107/9c41aa8e/attachment.htm>