<div dir="ltr">Why I turn off the <span style="font-family:arial,sans-serif;font-size:13px">connection_cache is because we have tomcat to cache the connections, according to clients' requirements. We use java code to bulk update the table. But for the code details, I have no idea about it.</span></div>
<div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Apr 2, 2014 at 7:20 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">One thing I noticed is you turn off connection_cache which will<br>
descease performance. Is there any specific reason for this? Also what<br>
kind of application/language you are running?<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<div class="HOEnZb"><div class="h5"><br>
> Hello you,<br>
><br>
> I am a freshman to postgresql, also pgpool-II. I have some performance<br>
> issues once I bring in the pgpool-II to build the pg cluster. Here I post<br>
> some system info and the configurations of postgresql and pgpool, hopping<br>
> you can help me to solve this problem.<br>
><br>
> BTW, I am using the postgres 9.2.4 installed on the Amazon AWS with debian<br>
> OS(64bits), and the version of pgpool is 3.2.7. There are two nodes in the<br>
> cluster, working in master/slave mode and replicating data using the<br>
> streaming replication feature.<br>
><br>
> Mem info:<br>
> cora@apollo:~$ free<br>
> total used free shared buffers cached<br>
> Mem: 31446876 7625428 23821448 0 9468 6312080<br>
> -/+ buffers/cache: 1303880 30142996<br>
> Swap: 0 0 0<br>
><br>
> Linux info:<br>
> cora@apollo:~$ cat /proc/version<br>
> Linux version 2.6.32-5-xen-amd64 (Debian 2.6.32-48squeeze1) (<br>
> <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<br>
> 02:51:39 UTC 2013<br>
><br>
> The settings of postgresql(only showing the non-default parameters)<br>
> listen_addresses = '*'<br>
> port = 9797<br>
> max_connections = 750<br>
> ssl_renegotiation_limit = 0<br>
> shared_buffers = 15GB<br>
> temp_buffers = 32MB<br>
> work_mem = 64MB<br>
> maintenance_work_mem = 128MB<br>
> effective_io_concurrency = 1000<br>
> wal_level = hot_standby<br>
> checkpoint_segments = 32<br>
> archive_mode = on<br>
> archive_command = 'rsync -a %p apollo:/var/lib/postgresql/9.2/archive/%f<br>
> </dev/null'<br>
> max_wal_senders = 1<br>
> wal_keep_segments = 32<br>
> hot_standby = on<br>
> enable_indexscan = on<br>
> enable_seqscan = on<br>
> random_page_cost = 2.0<br>
> effective_cache_size = 5GB<br>
> default_statistics_target = 10000<br>
> constraint_exclusion = on<br>
> autovacuum = on<br>
> log_autovacuum_min_duration = 100<br>
> autovacuum_max_workers = 6<br>
> autovacuum_naptime = 30min<br>
> autovacuum_vacuum_threshold = 1000<br>
> autovacuum_analyze_threshold = 5000<br>
> autovacuum_vacuum_scale_factor = 0.2<br>
> autovacuum_analyze_scale_factor = 0.1<br>
> autovacuum_freeze_max_age = 200000000<br>
> autovacuum_vacuum_cost_delay = 20ms<br>
> autovacuum_vacuum_cost_limit = -1<br>
> hot_standby_feedback = off<br>
><br>
><br>
> The configuration of pgpool-II<br>
> listen_addresses = '*'<br>
> port = 5432<br>
> socket_dir = '/var/run/pgpool2'<br>
> pcp_port = 9898<br>
> pcp_socket_dir = '/var/run/pgpool2'<br>
> backend_hostname0 = 'apollo'<br>
> backend_port0 = 9797<br>
> backend_weight0 = 1<br>
> backend_data_directory0 = '/var/lib/postgresql/9.2/main'<br>
> backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
><br>
> backend_hostname1 = 'apollo2'<br>
> backend_port1 = 9797<br>
> backend_weight1 = 1<br>
> backend_data_directory1 = '/var/lib/postgresql/9.2/main'<br>
> backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
> enable_pool_hba = on<br>
> pool_passwd = 'pool_passwd'<br>
> ssl = off<br>
> num_init_children = 32<br>
> max_pool = 5<br>
> child_life_time = 0<br>
> child_max_connections = 0<br>
> connection_life_time = 0<br>
> client_idle_limit = 0<br>
> debug_level = 0<br>
> pid_file_name = '/var/run/pgpool2/pgpool.pid'<br>
> logdir = '/var/log/pgpool2'<br>
> connection_cache = off<br>
> replication_mode = off<br>
> insert_lock = off<br>
> replicate_select = off<br>
> load_balance_mode = on<br>
> ignore_leading_white_space = on<br>
> white_function_list = 'foo'<br>
> black_function_list = ''<br>
> master_slave_mode = on<br>
> master_slave_sub_mode = 'stream'<br>
> sr_check_user = 'postgres'<br>
> sr_check_password = '×××'<br>
> delay_threshold = 0<br>
> health_check_period = 10<br>
> health_check_timeout = 20<br>
> failover_command = '/var/lib/postgresql/9.2/main/failover.sh %d "%h" %p %D<br>
> %m %M "%H" %P'<br>
> failback_command = '/bin/rm -f /tmp/trigger_file0'<br>
> recovery_user = 'postgres'<br>
> recovery_password = 'postgres'<br>
> recovery_1st_stage_command = 'basebackup.sh'<br>
> use_watchdog = off<br>
><br>
> At first, I turn on the hot_standby_feedback, but after getting customers'<br>
> complaint about the poor performance, I decide to turn off, but it seems<br>
> that it didn't help.<br>
><br>
> Thanks in advance.<br>
><br>
> --<br>
> Thanks,<br>
> Cora<br>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr">Thanks,<div>Cora</div></div>
</div>