[pgpool-general: 2689] Re: The bulk updates take twice amount of time after the pgpool launched

[Tatsuo Ishii]

One thing I noticed is you turn off connection_cache which will
descease performance. Is there any specific reason for this? Also what
kind of application/language you are running?

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

> Hello you,
> 
> I am a freshman to postgresql, also pgpool-II. I have some performance
> issues once I bring in the pgpool-II to build the pg cluster. Here I post
> some system info and the configurations of postgresql and pgpool, hopping
> you can help me to solve this problem.
> 
> BTW, I am using the postgres 9.2.4 installed on the Amazon AWS with debian
> OS(64bits), and the version of pgpool is 3.2.7. There are two nodes in the
> cluster, working in master/slave mode and replicating data using the
> streaming replication feature.
> 
> Mem info:
> cora at apollo:~$ free
>              total       used       free     shared    buffers     cached
> Mem:      31446876    7625428   23821448          0       9468    6312080
> -/+ buffers/cache:    1303880   30142996
> Swap:            0          0          0
> 
> Linux info:
> cora at apollo:~$ cat /proc/version
> Linux version 2.6.32-5-xen-amd64 (Debian 2.6.32-48squeeze1) (
> dannf at debian.org) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Mon Feb 25
> 02:51:39 UTC 2013
> 
> The settings of postgresql(only showing the non-default parameters)
> listen_addresses = '*'
> port = 9797
> max_connections = 750
> ssl_renegotiation_limit = 0
> shared_buffers = 15GB
> temp_buffers = 32MB
> work_mem = 64MB
> maintenance_work_mem = 128MB
> effective_io_concurrency = 1000
> wal_level = hot_standby
> checkpoint_segments = 32
> archive_mode = on
> archive_command = 'rsync -a %p apollo:/var/lib/postgresql/9.2/archive/%f
> </dev/null'
> max_wal_senders = 1
> wal_keep_segments = 32
> hot_standby = on
> enable_indexscan = on
> enable_seqscan = on
> random_page_cost = 2.0
> effective_cache_size = 5GB
> default_statistics_target = 10000
> constraint_exclusion = on
> autovacuum = on
> log_autovacuum_min_duration = 100
> autovacuum_max_workers = 6
> autovacuum_naptime = 30min
> autovacuum_vacuum_threshold = 1000
> autovacuum_analyze_threshold = 5000
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_freeze_max_age = 200000000
> autovacuum_vacuum_cost_delay = 20ms
> autovacuum_vacuum_cost_limit = -1
> hot_standby_feedback = off
> 
> 
> The configuration of pgpool-II
> listen_addresses = '*'
> port = 5432
> socket_dir = '/var/run/pgpool2'
> pcp_port = 9898
> pcp_socket_dir = '/var/run/pgpool2'
> backend_hostname0 = 'apollo'
> backend_port0 = 9797
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/postgresql/9.2/main'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_hostname1 = 'apollo2'
> backend_port1 = 9797
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/postgresql/9.2/main'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> enable_pool_hba = on
> pool_passwd = 'pool_passwd'
> ssl = off
> num_init_children = 32
> max_pool = 5
> child_life_time = 0
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> debug_level = 0
> pid_file_name = '/var/run/pgpool2/pgpool.pid'
> logdir = '/var/log/pgpool2'
> connection_cache = off
> replication_mode = off
> insert_lock = off
> replicate_select = off
> load_balance_mode = on
> ignore_leading_white_space = on
> white_function_list = 'foo'
> black_function_list = ''
> master_slave_mode = on
> master_slave_sub_mode = 'stream'
> sr_check_user = 'postgres'
> sr_check_password = '×××'
> delay_threshold = 0
> health_check_period = 10
> health_check_timeout = 20
> failover_command = '/var/lib/postgresql/9.2/main/failover.sh %d "%h" %p %D
> %m %M "%H" %P'
> failback_command = '/bin/rm -f /tmp/trigger_file0'
> recovery_user = 'postgres'
> recovery_password = 'postgres'
> recovery_1st_stage_command = 'basebackup.sh'
> use_watchdog = off
> 
> At first, I turn on the hot_standby_feedback, but after getting customers'
> complaint about the poor performance, I decide to turn off, but it seems
> that it didn't help.
> 
> Thanks in advance.
> 
> -- 
> Thanks,
> Cora