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

Cora Ma cora at insparqinc.com
Wed Apr 2 10:07:04 JST 2014


Why I turn off the 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.


On Wed, Apr 2, 2014 at 7:20 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> 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
>



-- 
Thanks,
Cora
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140402/0c7971e2/attachment.html>


More information about the pgpool-general mailing list