performance degradation after launching postgres cluster using pgpool-II - Mailing list pgsql-performance

Hi,

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@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@apollo:~$ cat /proc/version
Linux version 2.6.32-5-xen-amd64 (Debian 2.6.32-48squeeze1)
(dannf@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.

Before starting the cluster, one bulk updates through java code would cost
about 1 hour to finish, but then it would take twice amount of time.

So many thanks for giving your advises...




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/performance-degradation-after-launching-postgres-cluster-using-pgpool-II-tp5797953.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.