Date: Mon, 31 Mar 2014 01:31:39 -0700 (PDT)
From: Cora Ma <machengcheng001@hotmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1396254699987-5797953.post@n5.nabble.com>
Subject: performance degradation after launching postgres cluster using
 pgpool-II
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 =3D '*'
port =3D 9797=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20
max_connections =3D 750
ssl_renegotiation_limit =3D 0
shared_buffers =3D 15GB=20=20
temp_buffers =3D 32MB
work_mem =3D 64MB=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
maintenance_work_mem =3D 128MB=20=20=20=20=20=20=20=20=20=20=20=20
effective_io_concurrency =3D 1000
wal_level =3D hot_standby
checkpoint_segments =3D 32=20
archive_mode =3D on
archive_command =3D 'rsync -a %p apollo:/var/lib/postgresql/9.2/archive/%f
</dev/null'=20
max_wal_senders =3D 1
wal_keep_segments =3D 32
hot_standby =3D on
enable_indexscan =3D on
enable_seqscan =3D on
random_page_cost =3D 2.0=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
effective_cache_size =3D 5GB
default_statistics_target =3D 10000
constraint_exclusion =3D on
autovacuum =3D on
log_autovacuum_min_duration =3D 100
autovacuum_max_workers =3D 6=20=20=20
autovacuum_naptime =3D 30min=20=20=20=20=20=20=20=20=20=20=20=20=20=20
autovacuum_vacuum_threshold =3D 1000=20=20=20=20=20=20
autovacuum_analyze_threshold =3D 5000=20=20=20=20=20
autovacuum_vacuum_scale_factor =3D 0.2=20=20=20
autovacuum_analyze_scale_factor =3D 0.1=20=20=20
autovacuum_freeze_max_age =3D 200000000=20=20=20
autovacuum_vacuum_cost_delay =3D 20ms=20=20=20=20
autovacuum_vacuum_cost_limit =3D -1=20=20=20
hot_standby_feedback =3D off


The configuration of pgpool-II
listen_addresses =3D '*'
port =3D 5432
socket_dir =3D '/var/run/pgpool2'
pcp_port =3D 9898
pcp_socket_dir =3D '/var/run/pgpool2'
backend_hostname0 =3D 'apollo'
backend_port0 =3D 9797
backend_weight0 =3D 1
backend_data_directory0 =3D '/var/lib/postgresql/9.2/main'
backend_flag0 =3D 'ALLOW_TO_FAILOVER'

backend_hostname1 =3D 'apollo2'
backend_port1 =3D 9797
backend_weight1 =3D 1
backend_data_directory1 =3D '/var/lib/postgresql/9.2/main'
backend_flag1 =3D 'ALLOW_TO_FAILOVER'
enable_pool_hba =3D on
pool_passwd =3D 'pool_passwd'
ssl =3D off
num_init_children =3D 32
max_pool =3D 5
child_life_time =3D 0
child_max_connections =3D 0
connection_life_time =3D 0
client_idle_limit =3D 0
debug_level =3D 0
pid_file_name =3D '/var/run/pgpool2/pgpool.pid'
logdir =3D '/var/log/pgpool2'
connection_cache =3D off
replication_mode =3D off
insert_lock =3D off
replicate_select =3D off
load_balance_mode =3D on
ignore_leading_white_space =3D on
white_function_list =3D 'foo'
black_function_list =3D ''
master_slave_mode =3D on
master_slave_sub_mode =3D 'stream'
sr_check_user =3D 'postgres'
sr_check_password =3D '=C3=97=C3=97=C3=97'
delay_threshold =3D 0
health_check_period =3D 10
health_check_timeout =3D 20
failover_command =3D '/var/lib/postgresql/9.2/main/failover.sh %d "%h" %p %D
%m %M "%H" %P'
failback_command =3D '/bin/rm -f /tmp/trigger_file0'
recovery_user =3D 'postgres'
recovery_password =3D 'postgres'
recovery_1st_stage_command =3D 'basebackup.sh'
use_watchdog =3D 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.=20

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/perfo=
rmance-degradation-after-launching-postgres-cluster-using-pgpool-II-tp57979=
53.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance