[pgsql-jp: 36555] Re: JDBC+pgpool利用時の不具合(!?)について

[YAMADA Toshio]

山田です。
さっそくの修正版ありがとうございます。
結果から言いますとどうもダメなようでした。

Date: Wed, 14 Dec 2005 22:54:07 +0900 (JST)
From: Tatsuo Ishii <ishii ＠ sraoss.co.jp>
> ありがとうございます。やはりというか、
> 
> Parse
> Bind
> Describe
> Execute
> Sync
> 
> の順で実行されるのですね。しかも、Syncが行われるまではバックエンドから
> 応答が返らないと。

なるほど。。。
一方のPREPAREでない通常のステートメントは、
Q(51) 一発で実行されているのをデバッグ出力で
見させていただきました。

> は、Executeで応答が返ることになっているのですが...)。これで本当にうま
> くいくかどうか自信がないのですが、一応修正版を、
> 
> http://www2b.biglobe.ne.jp/~caco/tmp/pgpool-2.6.5.tar.gz
> 
> に置いたので、試してみていただけるでしょうか?

先日お話したまったくの同じ環境で、pgpoolのみ上記修正版に
入れ替えまして、テスト実行しました。

結果としては、２度試しましたが、一度目は即、二度目は双方の
サーバーが1000行程度インサートした後、止まってしまいました。

以前(修正前)は、pgpool.confで設定した
　replication_timeout = 50000
が効いているのか、停止後50秒程度で縮退運転に入りその後
pgpool自体は動いておりましたが、今回はまったく返ってきません
でした。5分程度ほっておきましたが、応答がないので、pgpoolを
落としました。

停止直前のpgpoolのデバッグ出力を下記に載せて置きます。
(health check の前の)
waiting for master completing the query
で止まっております。
なお、app1もapp2も(ログを見る限り)同じ状況でしたので、
片系のログを記載させていただきます。

それでは、よろしくお願いいたします。

■(8)pgpoolデバッグ出力(修正版)
---------------------------------------------------------------
2005-12-15 12:18:34 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from backend pending data C len: 17 po: 5
2005-12-15 12:18:34 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from backend pending data Z len: 5 po: 17
2005-12-15 12:18:34 DEBUG: pid 19218: pool_process_query: kind from backend: Z
2005-12-15 12:18:34 DEBUG: pid 19218: pool_read_message_length: lenghth: 5
2005-12-15 12:18:34 DEBUG: pid 19218: ReadyForQuery: message length: 5
2005-12-15 12:18:34 DEBUG: pid 19218: ReadyForQuery: transaction state: I
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend P(50)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend D(44)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:34 DEBUG: pid 19218: read kind from frontend S(53)
2005-12-15 12:18:34 DEBUG: pid 19218: waiting for master completing the query
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend 2
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: waiting for secondary for data ready
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data C len: 47 po: 5
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data 1 len: 36 po: 16
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 1
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data 2 len: 31 po: 21
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data n len: 26 po: 26
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: n
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data C len: 21 po: 31
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data Z len: 5 po: 47
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: Z
2005-12-15 12:18:35 DEBUG: pid 19218: pool_read_message_length: lenghth: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: message length: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: transaction state: T
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend P(50)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend D(44)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend S(53)
2005-12-15 12:18:35 DEBUG: pid 19218: waiting for master completing the query
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend 1
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: waiting for secondary for data ready
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 1
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data 2 len: 69 po: 5
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data T len: 64 po: 10
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: T
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data D len: 32 po: 42
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: D
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data C len: 17 po: 57
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data Z len: 5 po: 69
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: Z
2005-12-15 12:18:35 DEBUG: pid 19218: pool_read_message_length: lenghth: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: message length: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: transaction state: T
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend P(50)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend D(44)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend S(53)
2005-12-15 12:18:35 DEBUG: pid 19218: waiting for master completing the query
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend 1
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: waiting for secondary for data ready
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 1
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data 2 len: 31 po: 5
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data n len: 26 po: 10
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: n
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data C len: 21 po: 15
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data Z len: 5 po: 31
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: Z
2005-12-15 12:18:35 DEBUG: pid 19218: pool_read_message_length: lenghth: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: message length: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: transaction state: T
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend S(53)
2005-12-15 12:18:35 DEBUG: pid 19218: waiting for master completing the query
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend 2
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: waiting for secondary for data ready
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: 2
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data C len: 17 po: 5
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: C
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from backend pending data Z len: 5 po: 17
2005-12-15 12:18:35 DEBUG: pid 19218: pool_process_query: kind from backend: Z
2005-12-15 12:18:35 DEBUG: pid 19218: pool_read_message_length: lenghth: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: message length: 5
2005-12-15 12:18:35 DEBUG: pid 19218: ReadyForQuery: transaction state: I
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend P(50)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend B(42)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend D(44)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend E(45)
2005-12-15 12:18:35 DEBUG: pid 19218: read kind from frontend S(53)
2005-12-15 12:18:35 DEBUG: pid 19218: waiting for master completing the query
2005-12-15 12:18:37 LOG:   pid 19135: starting health checking
2005-12-15 12:19:07 LOG:   pid 19135: starting health checking
2005-12-15 12:19:37 LOG:   pid 19135: starting health checking
2005-12-15 12:20:07 LOG:   pid 19135: starting health checking
2005-12-15 12:20:37 LOG:   pid 19135: starting health checking
2005-12-15 12:21:07 LOG:   pid 19135: starting health checking
2005-12-15 12:21:37 LOG:   pid 19135: starting health checking
---------------------------------------------------------------

-- 
by YAMADA Toshio <toshi ＠ mx1.freemail.ne.jp>