<div dir="ltr">Hello you,<div><br></div><div>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.</div>
<div><br></div><div>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.</div>
<div><br></div><div>Mem info:</div><div><div>cora@apollo:~$ free</div><div> total used free shared buffers cached</div><div>Mem: 31446876 7625428 23821448 0 9468 6312080</div>
<div>-/+ buffers/cache: 1303880 30142996</div><div>Swap: 0 0 0</div><div><br></div><div>Linux info:</div><div><div>cora@apollo:~$ cat /proc/version</div><div>Linux version 2.6.32-5-xen-amd64 (Debian 2.6.32-48squeeze1) (<a href="mailto:dannf@debian.org">dannf@debian.org</a>) (gcc version 4.3.5 (Debian 4.3.5-4) ) #1 SMP Mon Feb 25 02:51:39 UTC 2013</div>
</div><div><br></div><div>The settings of postgresql(only showing the non-default parameters)</div><div><div>listen_addresses = '*'</div><div>port = 9797 </div><div>max_connections = 750</div>
<div>ssl_renegotiation_limit = 0</div><div>shared_buffers = 15GB </div><div>temp_buffers = 32MB</div><div>work_mem = 64MB </div><div>maintenance_work_mem = 128MB </div><div>effective_io_concurrency = 1000</div>
<div>wal_level = hot_standby</div><div>checkpoint_segments = 32 </div><div>archive_mode = on</div><div>archive_command = 'rsync -a %p apollo:/var/lib/postgresql/9.2/archive/%f </dev/null' </div><div>max_wal_senders = 1</div>
<div>wal_keep_segments = 32</div><div>hot_standby = on</div><div>enable_indexscan = on</div><div>enable_seqscan = on</div><div>random_page_cost = 2.0 </div><div>effective_cache_size = 5GB</div><div>default_statistics_target = 10000</div>
<div>constraint_exclusion = on</div><div>autovacuum = on</div><div>log_autovacuum_min_duration = 100</div><div>autovacuum_max_workers = 6 </div><div>autovacuum_naptime = 30min </div><div>autovacuum_vacuum_threshold = 1000 </div>
<div>autovacuum_analyze_threshold = 5000 </div><div>autovacuum_vacuum_scale_factor = 0.2 </div><div>autovacuum_analyze_scale_factor = 0.1 </div><div>autovacuum_freeze_max_age = 200000000 </div><div>autovacuum_vacuum_cost_delay = 20ms </div>
<div>autovacuum_vacuum_cost_limit = -1 </div></div><div>hot_standby_feedback = off<br></div><div><br></div><div><br></div><div>The configuration of pgpool-II</div><div><div>listen_addresses = '*'</div><div>port = 5432</div>
<div>socket_dir = '/var/run/pgpool2'</div><div>pcp_port = 9898</div><div>pcp_socket_dir = '/var/run/pgpool2'</div><div>backend_hostname0 = 'apollo'</div><div>backend_port0 = 9797</div><div>backend_weight0 = 1</div>
<div>backend_data_directory0 = '/var/lib/postgresql/9.2/main'</div><div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div><div><br></div><div>backend_hostname1 = 'apollo2'</div><div>backend_port1 = 9797</div>
<div>backend_weight1 = 1</div><div>backend_data_directory1 = '/var/lib/postgresql/9.2/main'</div><div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div><div>enable_pool_hba = on</div><div>pool_passwd = 'pool_passwd'</div>
<div>ssl = off</div><div>num_init_children = 32</div><div>max_pool = 5</div><div>child_life_time = 0</div><div>child_max_connections = 0</div><div>connection_life_time = 0</div><div>client_idle_limit = 0</div><div>debug_level = 0</div>
<div>pid_file_name = '/var/run/pgpool2/pgpool.pid'</div><div>logdir = '/var/log/pgpool2'</div><div>connection_cache = off</div><div>replication_mode = off</div><div>insert_lock = off</div><div>replicate_select = off</div>
<div>load_balance_mode = on</div><div>ignore_leading_white_space = on</div><div>white_function_list = 'foo'</div><div>black_function_list = ''</div><div>master_slave_mode = on</div><div>master_slave_sub_mode = 'stream'</div>
<div>sr_check_user = 'postgres'</div><div>sr_check_password = '×××'</div><div>delay_threshold = 0</div><div>health_check_period = 10</div><div>health_check_timeout = 20</div><div>failover_command = '/var/lib/postgresql/9.2/main/failover.sh %d "%h" %p %D %m %M "%H" %P'</div>
<div>failback_command = '/bin/rm -f /tmp/trigger_file0'</div><div>recovery_user = 'postgres'</div><div>recovery_password = 'postgres'</div><div>recovery_1st_stage_command = 'basebackup.sh'</div>
<div>use_watchdog = off</div></div><div><br></div><div>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. </div>
<div><br></div><div>Thanks in advance.</div><div><br></div>-- <br><div dir="ltr">Thanks,<div>Cora</div></div>
</div></div>