[pgpool-general-jp: 1635] Re: pgpool-IIの処理性能について

2020年 9月 3日 (木) 16:46:35 JST

石井様

お世話になっております。北村です。
早々にご確認いただきありがとうございます。
考えられる問題パターンもあげていただきありがとうございます。

> 1) Javaアプリ<-->Pgpool-II<-->PostgreSQLのどこかでネットワークが遅い

今回はJavaアプリとPgpool-IIが同一サーバに存在している環境になりますので、
1)の可能性は薄いと考えております。

> 2) Pgpool-IIが走っているマシンのCPUリソース and/or メモリが足りない

こちらについては詳しく確認できておりませんので、可能性があるかもしれません。

> 3) テストに使用したINSERT文やUPDATE文が長大で、Pgpool-IIがパースするの
>    に時間がかかっている(これは4.1で改善されています)

テストに使用したSELECT文やINSERT文はさほど長大ではないので、
3)の可能性も薄いのではないかと考えております。
（INSERT文は単純なINSERT VALUESで、カラム数は50個ほどです。）

> 4) 一部の問い合わせが遠隔地のスタンバイにも送られていてその応答待ちで
>    時間がかかっている(お伺いした設定ではその可能性はないはずですが、一応。)

もしかすると4)の可能性があるのではないかと考えております。
Pgpool-IIサーバからDBサーバへPingの応答時間を確認したところ、
DBプライマリは0.1ミリ秒、DBスタンバイは19ミリ秒ほどで、
ちょうど1SQLあたりに生じている差と同じくらいでした。

別システムでの調査結果にはなるのですが、ご相談している環境と同じような
設定の環境で、アプリケーション実行時にtcpdumpを使用して通信を
監視したところ、Pgpool-IIサーバとDBスタンバイサーバ間の通信が、
1トラン内のSQLの実行回数分行われているようでした。

load_balance_mode = offを設定しているため負荷分散機能が無効となり
DBスタンバイへSQLは送られない認識なのですが、その設定であっても
SQL文ごとにDBスタンバイへの通信は行われるものなのでしょうか？

現在のPgpool-IIの設定パラメータは下記の通りです。
何かお気づきの点などございましたら、ご教示いただければ幸いです。
よろしくお願いいたします。

■pgpool.confに設定されている内容（pgpool.conf.sample-streamを元に作成）
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
backend_hostname0 = (PostgreSQLプライマリのホスト名)
backend_port0 = (PostgreSQLプライマリのポート番号)
backend_data_directory0 = (PostgreSQLプライマリのインスタンス)
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = (PostgreSQLスタンバイのホスト名)
backend_port1 = (PostgreSQLスタンバイのポート番号)
backend_data_directory1 = (PostgreSQLスタンバイのインスタンス)
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
allow_clear_text_frontend_auth = off
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = off
num_init_children = 600
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'syslog'
log_line_prefix = '[%l] user=%u, db=%d, proc=%P, pid=%p, app=%a '
log_connections = on
log_hostname = off
log_statement = on
log_per_node_statement = on
log_client_messages = off
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
pid_file_name = （Pgpool-IIのpidファイルのパス）
logdir = '/tmp'
connection_cache = off
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off 
load_balance_mode = off 
ignore_leading_white_space = on 
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
black_query_pattern_list = ''
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off
disable_load_balance_on_write = 'transaction'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 0
sr_check_user = （PostgreSQLユーザ名）
sr_check_password = （PostgreSQLユーザのパスワード）
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_master_command = ''
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_database = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = ''
failback_command = ''
failover_on_backend_error = on
detach_false_primary = off
search_primary_node_timeout = 300
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1 -I eth0'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off 
wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''

以上、よろしくお願いいたします。
-----

> 石井です。
> 
> Pgpool-II経由のときに、1 SQLあたり19ミリ秒余計にかかるのはちょっとオー
> バヘッドが大きすぎるように思います。
> 
> 手元の環境で(pgbench, Pgpool-II, PostgreSQL, すべて同じマシン上に環境
> 構築)pgbenchを使ってテストすると[1]、
> 
> Pgpool-II経由:latency average = 27.318 ms
> PostgreSQL直接: latency average = 25.578 ms
> 
> その差は1.7ミリ秒ほどです(ただし、1 SQLあたりではなく、1トランザクショ
> ンあたり。1 SQLあたりなら差はもっと小さいはず)。北村さんの環境でオーバ
> ヘッドが大きい理由として考えられるのは、
> 
> 1) Javaアプリ<-->Pgpool-II<-->PostgreSQLのどこかでネットワークが遅い
> 
> 2) Pgpool-IIが走っているマシンのCPUリソース and/or メモリが足りない
> 
> 3) テストに使用したINSERT文やUPDATE文が長大で、Pgpool-IIがパースするの
>    に時間がかかっている(これは4.1で改善されています)
> 
> 4) 一部の問い合わせが遠隔地のスタンバイにも送られていてその応答待ちで
>    時間がかかっている(お伺いした設定ではその可能性はないはずですが、一応。)
> 
> などです。まずは問題の切り分けを行ってみてはいかがでしょう？
> 
> あと、4.0.2は2年前にリリースされただいぶ古いバージョンで、その後性能改
> 善も行われているので、同じ4.0系の最新版を試してみるのも良いと思います。
> 
> [1]実際に使ったコマンドは、
> pgbench -M extended -n -p 11000 -T 10 -c 10 test
> 
> > はじめまして、お世話になります。
> > 北村と申します。
> >
> > 現在Pgpool-IIの検証を行っているのですが、アプリケーションを
> > postgreSQLに直接接続した場合とPgpool-IIに接続した場合で
> > 性能差が出ており、Pgpool-II経由の方が約6倍処理時間がかかっています。
> >
> > この差が妥当な値なのか、チューニングにより性能改善が見込める
> > 可能性があるのか、ご教示いただきたくメールさせていただきました。
> >
> > 【検証環境】
> > 　○PostgreSQL 11
> > 　　- RHEL 7に構築
> > 　　- 2台構成で非同期ストリーミングレプリケーションを構築
> > 　　- スタンバイサーバは遠隔地に配置している
> > 　○Pgpool-II 4.0.2
> > 　　- RHEL 7に構築（DBサーバとは別サーバ）
> > 　　- コネクションプーリングは使用しない（connection_cache = off）
> > 　　　（javaのコネクションプーリングを使用しているため）
> > 　　- DBプライマリにアクセスしたいため、
> > 　　　負荷分散は行わない（load_balance_mode = off）
> >
> > 【検証内容】
> > 　1トランザクション内で大量SQL（SELECT文やINSERT文）を実行する
> > 　アプリケーション(java)について、DB直接接続の場合と
> > 　Pgpool接続の場合で、処理時間を測定。
> >
> > 　DB直接接続：全体処理時間00:00:30秒（1SQLあたり約3ミリ秒）
> > 　Pgpool接続：全体処理時間00:02:58秒（1SQLあたり約19ミリ秒）
> > 　　　⇒pgpool接続の方が1SQLあたり約16ミリ秒遅い
> >
> > 【ご相談事項】
> > 　・pgpoolを経由すると、1SQLあたりの処理時間が多く（約6倍）
> > 　　かかっているのですが、これは妥当なものなのでしょうか？
> > 　・Pgpoolで負荷分散とコネクションプールを行わない前提で、
> > 　　Pgpoolのチューニングにより性能改善策などは考えられますでしょうか？
> >
> > 何かお気づきの点や設定の勘所等をご教示いただければ幸いです。
> > よろしくお願いいたします。
> >
> >
> > _______________________________________________
> > pgpool-general-jp mailing list
> > pgpool-general-jp ＠ sraoss.jp
> > http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp