Chinaunix

标题: 引入pgpool-II搭建集群后，批量化的update花费的时间比之前多了一倍！！！ [打印本页]

作者: cheng194421 时间: 2014-03-28 17:12
标题: 引入pgpool-II搭建集群后，批量化的update花费的时间比之前多了一倍！！！
Hi 各位高手，
我是一个postgresql的新手，刚刚接触这个数据库半年的样子，根据客户的要求搭建了postgres集群，引入的pgpool-II中间件，以下是系统跟数据库配置，pgpool的配置，希望高手们能给予解决这个性能问题，先谢谢了。

我们现在用的是亚马逊的AWS sever，自己安装的9.2.4的postgresql。引入pgpool之后，总共有两个节点，运行在master/slave模式，使用的复制是PG内置的streaming replication。

内存信息：
cora@apollo:~$ free
         total    used    free    shared buffers    cached
Mem:    31446876 7625428 23821448       0    9468 6312080
-/+ buffers/cache: 1303880 30142996
Swap:          0       0       0
系统版本信心：
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

Postgresql配置文件（只列出来了非默认值），master跟slave的配置是完全一致的。
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

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

在最开始的设置中，我有打开hot_standby_feedback（设置成ON），客户抱怨性能问题啊，我以为会是这个参数的问题，关闭之后，还是不行。希望各位高手能给予指点啊。。。谢谢啦（本人小女子，故不要嫌弃我的语气词吧）

作者: skykiker 时间: 2014-04-04 11:34
流复制和pgpool都会带来一些性能损耗，你先绕过pgpool直接连后面的Master，看看性能瓶颈主要由谁带来的。

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)