[pgpool-general-jp: 267] pgpool-II パラレルモード使用下で特定の処理を行うと停止する

asano asano @ engm.com
2007年 11月 2日 (金) 22:25:22 JST


お世話になります。浅野と申します。


当方にて制作しているWEBアプリケーションで問題が発生しております。

pgpool-II (パラレルクエリモード) 使用下で、
特定のPHPプログラムファイルを実行すると
pgpool-IIが停止してしまうようです。

どなたか同じ状況が発生した方が見えましたら、
対処法をご教示頂ければ幸いです。

宜しくお願い申し上げます。


[状況]
    ・トピックを作成し、そのそれぞれのトピックについてコメントを書き込める
     (スレッド式掲示板に近いと思います)

    ・ページを閲覧に行くと延々と「読み込み中」の状態が続き、
     ページが表示できない。

    ・pgpool-IIとPostgreSQLを再起動させると数回の閲覧は可能だが、
     更新を繰り返したり、サイト内の別のページを閲覧した後に見に来ると停止状態になる。

    ・サイト内に他に同じような処理をさせるPHPプログラムがあるが、そちらでは
      この現象は発生しない。

    ・pgpool-II、PostgreSQLのログの両者共それらしい出力は見当たらない模様。
     というより、「読み込み中」状態になるとログに何も出力されてこない。

        tail -f -n20 /usr/local/pgsql/data.1/pg_log/postgresql.log
        tail -f -n20 /usr/local/pgpool/log/pgpool.log

     上記コマンドで確認したが、何も出力されない。
     さらに待っているとpostgresqlのログにautovacuumの処理は出力されてくる。

    ・pgpool-IIを停止させて、単体のPostgreSQLで動かしたところ、この現象は発生しなかった。

    ・pgpool-IIのバージョン1.3でも1.2でも停止状態になった。


-----------------------------------------------------------
データベースは実機一台にポートで分けて三つを起動させている状態。
[PostgreSQL起動時コマンド]
PGPORT=5432 pg_ctl -D /usr/local/pgsql/data start
PGPORT=5433 pg_ctl -D /usr/local/pgsql/data.1 start
PGPORT=5434 pg_ctl -D /usr/local/pgsql/data.2 start

[pgpool起動時コマンド]
/usr/local/pgpool/bin/pgpool -n -d >& /usr/local/pgpool/log/pgpool.log 2>&1
&

-----------------------------------------------------------
[ pgpool-II設定ファイル pgpool.conf ]

listen_addresses = 'localhost'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 32
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
logdir = '/tmp'
replication_mode = false
replication_strict = true
replication_timeout = 5000
load_balance_mode = false
replication_stop_on_mismatch = false
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
print_timestamp = true
master_slave_mode = false
connection_cache = true
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
insert_lock = false
ignore_leading_white_space = false
log_statement = false
log_connections = false
log_hostname = false
parallel_mode = true
enable_query_cache = false
pgpool2_hostname = 'localhost'
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'postgres'
system_db_password = ''

backend_hostname0 = ''
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = ''
backend_port1 = 5433
backend_weight1 = 1

backend_hostname2 = ''
backend_port2 = 5434
backend_weight2 = 1
enable_pool_hba = false

-----------------------------------------------------------
【 関連するテーブル 】

[テーブル名:c_commu_topic]
 c_commu_topic_id  | integer                     | not null
 c_commu_id        | integer                     | not null default 0
 name              | text                        | not null
 r_datetime        | timestamp without time zone | not null default now()
 r_date            | date                        | not null default
('now'::text)::date
 c_member_id       | integer                     | not null default 0
 open_date         | date                        | not null default
('now'::text)::date
 open_date_comment | character varying(100)      | not null
 open_pref_id      | integer                     | not null default 0
 open_pref_comment | character varying(100)      | not null
 invite_period     | date                        | not null default
('now'::text)::date
 event_flag        | integer                     | not null default 0


[テーブル名:c_commu_topic_comment]
 c_commu_topic_comment_id | integer                     | not null
 c_commu_id               | integer                     | not null default 0
 c_member_id              | integer                     | not null default 0
 body                     | text                        | not null
 r_datetime               | timestamp without time zone | not null default
now()
 r_date                   | date                        | not null default
('now'::text)::date
 number                   | integer                     | not null default 0
 c_commu_topic_id         | integer                     | not null default 0
 image_filename1          | character varying(200)      | not null
 image_filename2          | character varying(200)      | not null
 image_filename3          | character varying(200)      | not null
 image_width1             | smallint                    | not null default 0
 image_height1            | smallint                    | not null default 0
 image_width2             | smallint                    | not null default 0
 image_height2            | smallint                    | not null default 0
 image_width3             | smallint                    | not null default 0
 image_height3            | smallint                    | not null default 0


-----------------------------------------------------------
【分割ルール設定】 (pgpool_catalog.dist_def )

[c_commu_topic テーブル用]
insert into pgpool_catalog.dist_def values(
'topic_main_db',
'public',
'c_commu_topic',
'c_commu_topic_id',
ARRAY['c_commu_topic_id','c_commu_id','name','r_datetime','r_date','c_member_id','open_date','open_date_comment','open_pref_id','open_pref_comment','invite_period','event_flag'],
ARRAY['INTEGER','INTEGER','TEXT','TIMESTAMP','DATE','INTEGER','DATE','CHARACTER
VARYING(100)','INTEGER','CHARACTER VARYING(100)','DATE','INTEGER'],
'pgpool_catalog.dist_def_c_commu_topic')


[c_commu_topic_comment テーブル用]
insert into pgpool_catalog.dist_def values(
'topic_main_db',
'public',
'c_commu_topic_comment',
'c_commu_topic_comment_id',
ARRAY['c_commu_topic_comment_id','c_commu_id','c_member_id','body','r_datetime','r_date','number','c_commu_topic_id','image_filename1','image_filename2','image_filename3','image_width1','image_height1','image_width2','image_height2','image_width3','image_height3'],
ARRAY['INTEGER','INTEGER','INTEGER','TEXT','TIMESTAMP','DATE','INTEGER','INTEGER','CHARACTER
VARYING(200)','CHARACTER VARYING(200)','CHARACTER
VARYING(200)','SMALLINT','SMALLINT','SMALLINT','SMALLINT','SMALLINT','SMALLINT'],
'pgpool_catalog.dist_def_c_commu_topic_comment')

-----------------------------------------------------------
【分割ルールファンクション】

[c_commu_topic テーブル用]
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_c_commu_topic(val
ANYELEMENT) RETURNS INTEGER AS $$
SELECT CASE WHEN $1 > 0 then 1
END $$ LANGUAGE SQL;

[c_commu_topic_comment テーブル用]
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_c_commu_topic_comment(val
ANYELEMENT) RETURNS INTEGER AS $$
SELECT CASE WHEN $1 > 0 then 1
END $$ LANGUAGE SQL;

-----------------------------------------------------------
【 pgpool 停止した直前のログ 】 (※ユーザ名は■■■で置き換えてます。 )

2007-11-02 21:33:59 DEBUG: pid 26766: 0 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26766: 1 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26766: 2 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
0 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
1 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
2 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: kind from backend:
C
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
0 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
1 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_backend: read kind from
2 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: kind from backend:
Z
2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 0
length: 5
2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 1
length: 5
2007-11-02 21:33:59 DEBUG: pid 26766: pool_read_message_length: slot: 2
length: 5
2007-11-02 21:33:59 DEBUG: pid 26766: ReadyForQuery: message length: 5
2007-11-02 21:33:59 DEBUG: pid 26766: ReadyForQuery: transaction state: I
2007-11-02 21:33:59 DEBUG: pid 26766: pool_connection_pool_timer: set close
time 1194006839
2007-11-02 21:33:59 DEBUG: pid 26863: Protocol Major: 3 Minor: 0 database:
topic_main_db user: ■■■
2007-11-02 21:33:59 DEBUG: pid 26863: pool_send_auth_ok: send pid 26893 to
frontend
2007-11-02 21:33:59 DEBUG: pid 26863: select_load_balancing_node: selected
backend id is 0
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26863: read kind from frontend Q(51)
2007-11-02 21:33:59 DEBUG: pid 26863: statement2: SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
='6'")
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
from 0 th backend T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
from 1 th backend T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy kind from
backend: T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_one_backend: read kind
from 2 th backend T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: dummy kind from
backend: T
2007-11-02 21:33:59 DEBUG: pid 26863: pool_parallel_exec: kind from backend:
C
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
Z
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 0
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 1
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 2
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: message length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: transaction state: I
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26863: read kind from frontend X(58)
2007-11-02 21:33:59 DEBUG: pid 26863: statement2:  RESET ALL
2007-11-02 21:33:59 DEBUG: pid 26863: pool_rewrite_stmt: XXX rule 743
2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 0 completing the
query
2007-11-02 21:33:59 DEBUG: pid 26837: pool_rewrite_stmt: XXX message_code 7
2007-11-02 21:33:59 DEBUG: pid 26837: pool_rewrite_stmt: XXX rule 704
2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 1 completing the
query
2007-11-02 21:33:59 DEBUG: pid 26837: read kind from frontend Q(51)
2007-11-02 21:33:59 DEBUG: pid 26837: statement2: select * from
c_commu_topic_comment where c_commu_topic_id='3'
2007-11-02 21:33:59 DEBUG: pid 26837: can pool_parallel_exec  SELECT "*"
FROM "c_commu_topic_comment" WHERE  ("c_commu_topic_id"='3' )
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
from 0 th backend T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
from 2 th backend T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy kind from
backend: T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 2 completing the
query
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_one_backend: read kind
from 1 th backend T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: dummy kind from
backend: T
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26837: pool_parallel_exec: kind from backend:
C
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
0 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
1 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26837: read_kind_from_backend: read kind from
2 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: kind from backend:
Z
2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 0
length: 5
2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 1
length: 5
2007-11-02 21:33:59 DEBUG: pid 26837: pool_read_message_length: slot: 2
length: 5
2007-11-02 21:33:59 DEBUG: pid 26837: ReadyForQuery: message length: 5
2007-11-02 21:33:59 DEBUG: pid 26837: ReadyForQuery: transaction state: I
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
C
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
Z
2007-11-02 21:33:59 DEBUG: pid 26837: pool_process_query: num_fds: 13
2007-11-02 21:33:59 DEBUG: pid 26837: read kind from frontend Q(51)
2007-11-02 21:33:59 DEBUG: pid 26837: statement2: select * from
c_commu_topic_comment where c_commu_topic_id='3' order by
c_commu_topic_comment_id asc limit 1
2007-11-02 21:33:59 DEBUG: pid 26837: OneNode_do_command: Query:  SELECT *
FROM dblink('host=localhost port=9999 dbname=topic_main_db ','SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
=''3''")') AS c_commu_topic_comment(c_commu_topic_comment_id
INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
c_commu_topic_comment_id  LIMIT 1
2007-11-02 21:33:59 DEBUG: pid 26766: I am 26766 accept fd 6
2007-11-02 21:33:59 DEBUG: pid 26766: Protocol Major: 1234 Minor: 5679
database:  user:
2007-11-02 21:33:59 DEBUG: pid 26766: SSLRequest: sent N; retry startup
2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 1 completing the
query
2007-11-02 21:33:59 DEBUG: pid 26863: waiting for backend 2 completing the
query
2007-11-02 21:33:59 DEBUG: pid 26766: Protocol Major: 3 Minor: 0 database:
topic_main_db user: ■■■
2007-11-02 21:33:59 DEBUG: pid 26766: pool_send_auth_ok: send pid 26902 to
frontend
2007-11-02 21:33:59 DEBUG: pid 26766: select_load_balancing_node: selected
backend id is 1
2007-11-02 21:33:59 DEBUG: pid 26766: pool_process_query: num_fds: 10
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
S
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 0 length: 21
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 1 length: 21
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 2 length: 21
2007-11-02 21:33:59 DEBUG: pid 26863: 0 th backend: name: is_superuser
value: off
2007-11-02 21:33:59 DEBUG: pid 26863: 1 th backend: name: is_superuser
value: off
2007-11-02 21:33:59 DEBUG: pid 26863: 2 th backend: name: is_superuser
value: off
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend S NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
S
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 0 length: 32
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 1 length: 32
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length2: master
slot: 2 length: 32
2007-11-02 21:33:59 DEBUG: pid 26863: 0 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26863: 1 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26863: 2 th backend: name:
session_authorization value: ■■■
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend C NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
C
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
0 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
1 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: read_kind_from_backend: read kind from
2 th backend Z NUM_BACKENDS: 3
2007-11-02 21:33:59 DEBUG: pid 26863: pool_process_query: kind from backend:
Z
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 0
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 1
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: pool_read_message_length: slot: 2
length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: message length: 5
2007-11-02 21:33:59 DEBUG: pid 26863: ReadyForQuery: transaction state: I
2007-11-02 21:33:59 DEBUG: pid 26863: pool_connection_pool_timer: set close
time 1194006839
2007-11-02 21:33:59 DEBUG: pid 26766: read kind from frontend Q(51)
2007-11-02 21:33:59 DEBUG: pid 26766: statement2: SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
='3'")
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_query: num_fds: 10
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
from 1 th backend T
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
T
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: kind from backend:
D
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
from 2 th backend T
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy kind from
backend: T
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy from
backend: C
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_query: num_fds: 10
2007-11-02 21:33:59 DEBUG: pid 26766: read_kind_from_one_backend: read kind
from 1 th backend Z
2007-11-02 21:33:59 DEBUG: pid 26766: pool_parallel_exec: dummy kind from
backend: Z



-----------------------------------------------------------
【 PostgreSQL 停止した直前のログ 】(※ユーザ名は■■■で置き換えてます。 )


2007-11-02 21:33:58 JST SETLOG:  00000: duration: 0.479 ms
2007-11-02 21:33:58 JST SETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SET SESSION
AUTHORIZATION DEFAULT
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:58 JST SETLOG:  00000: duration: 0.476 ms
2007-11-02 21:33:58 JST SETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT *  FROM
dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
=''4''")') AS c_commu_topic_comment(c_commu_topic_comment_id
INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
c_commu_topic_comment_id  LIMIT 1
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:58 JST idleLOG:  00000: statement: select * from
c_commu_topic_comment WHERE c_commu_topic_id ='4'
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 10.318 ms
2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 72.010 ms
2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT "*" FROM
"c_commu_topic_comment" WHERE  ("c_commu_topic_id"='5' )
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:58 JST SELECTLOG:  00000: duration: 1.122 ms
2007-11-02 21:33:58 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST idleLOG:  00000: statement:  RESET ALL
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:58 JST RESETLOG:  00000: duration: 0.355 ms
2007-11-02 21:33:58 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:58 JST idleLOG:  00000: statement:  SELECT *  FROM
dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
=''5''")') AS c_commu_topic_comment(c_commu_topic_comment_id
INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
c_commu_topic_comment_id  LIMIT 1
2007-11-02 21:33:58 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
AUTHORIZATION DEFAULT
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.525 ms
2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
c_commu_topic_comment WHERE c_commu_topic_id ='5'
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.098 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 58.556 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT "*" FROM
"c_commu_topic_comment" WHERE  ("c_commu_topic_id"='6' )
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.120 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  RESET ALL
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST RESETLOG:  00000: duration: 0.383 ms
2007-11-02 21:33:59 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT *  FROM
dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
=''6''")') AS c_commu_topic_comment(c_commu_topic_comment_id
INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
c_commu_topic_comment_id  LIMIT 1
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
AUTHORIZATION DEFAULT
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.508 ms
2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
c_commu_topic_comment WHERE c_commu_topic_id ='6'
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.117 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 43.969 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  RESET ALL
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST RESETLOG:  00000: duration: 0.371 ms
2007-11-02 21:33:59 JST RESETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT "*" FROM
"c_commu_topic_comment" WHERE  ("c_commu_topic_id"='3' )
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.123 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SET SESSION
AUTHORIZATION DEFAULT
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SETLOG:  00000: duration: 0.448 ms
2007-11-02 21:33:59 JST SETLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:33:59 JST idleLOG:  00000: statement:  SELECT *  FROM
dblink('host=localhost dbname=topic_main_db port=9999 user=■■■','SELECT
pool_parallel("select * from c_commu_topic_comment WHERE c_commu_topic_id
=''3''")') AS c_commu_topic_comment(c_commu_topic_comment_id
INTEGER,c_commu_id INTEGER,c_member_id INTEGER,body TEXT,r_datetime
TIMESTAMP,r_date DATE,number INTEGER,c_commu_topic_id
INTEGER,image_filename1 CHARACTER VARYING(200),image_filename2 CHARACTER
VARYING(200),image_filename3 CHARACTER VARYING(200),image_width1
SMALLINT,image_height1 SMALLINT,image_width2 SMALLINT,image_height2
SMALLINT,image_width3 SMALLINT,image_height3 SMALLINT) ORDER BY
c_commu_topic_comment_id  LIMIT 1
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST idleLOG:  00000: statement: select * from
c_commu_topic_comment WHERE c_commu_topic_id ='3'
2007-11-02 21:33:59 JST idleLOCATION:  log_after_parse, postgres.c:605
2007-11-02 21:33:59 JST SELECTLOG:  00000: duration: 1.110 ms
2007-11-02 21:33:59 JST SELECTLOCATION:  exec_simple_query, postgres.c:1090
2007-11-02 21:34:40 JST LOG:  00000: autovacuum: processing database
"template1"
2007-11-02 21:34:40 JST LOCATION:  AutoVacMain, autovacuum.c:412

-----------------------------------------------------------
【 PHPプログラム 】
<?php

$c_member_id=19;

$commu_id = 3;

$resource_id = pg_connect("host=localhost port=9999 dbname=topic_main_db
user=topic_main_user password=");

if(!$resource_id) {
 print "<br><br>[ --CONNECTION ERROR-- ]<br><br>\n\n";
 exit;
}

//今日の日付
$r_datetime = date("Y-m-d H:i:s");

$page_define=20;

$show_page=1;
$line = $show_page * $page_define - $page_define;
$line1 = $line + 1;
$line2 = $line + $page_define;

//トピックを検索
$sql3 = "select * from c_commu_topic where c_commu_id = '$commu_id' order by
r_datetime desc limit $page_define offset $line";
$res_sql3 = pg_query($resource_id,"$sql3");
$list = "";
while($arycol3 = pg_fetch_array($res_sql3)){
 //そのトピックの書き込みの合計を取得
 $sql = "select * from c_commu_topic_comment where
c_commu_topic_id='$arycol3[c_commu_topic_id]'";
 $res_sql = pg_query($resource_id,"$sql");
 $total = pg_num_rows($res_sql);
 $total = $total -1;
 //そのトピックの最新の書き込み
 $sql4 = "select * from c_commu_topic_comment where
c_commu_topic_id='$arycol3[c_commu_topic_id]' order by
c_commu_topic_comment_id asc limit 1";
 $res_sql4 = pg_query($resource_id,"$sql4");
 $arycol4 = pg_fetch_array($res_sql4);
 $body = mb_strimwidth($arycol4['body'],0,300,"…",'EUC-JP');
 $filename1 = $arycol4['image_filename1'];
 $filename2 = $arycol4['image_filename2'];
 $filename3 = $arycol4['image_filename3'];
 $width1 = $arycol4['image_width1'];
 $height1 = $arycol4['image_height1'];
 $width2 = $arycol4['image_width2'];
 $height2 = $arycol4['image_height2'];
 $width3 = $arycol4['image_width3'];
 $height3 = $arycol4['image_height3'];


if($filename1 != ""){
 $main_image = "<img src='image.php?image_id=$filename1' alt=''
width='$base_width1' height='$base_height1' border='0' />";
}else{
 $main_image = "<img src='../common_img/spacer.gif' alt='' width='1'
height='1' border='0'>";
}

      $list .= "<tr>\n";
      $list .= "  <td bgcolor='#CCCCCC'><table width='100%'  border='0'
cellspacing='0' cellpadding='5'>\n";
      $list .= "    <tr>\n";
      $list .= "      <td width='30'>&nbsp;</td>\n";
      $list .= "      <td width='120'>$arycol3[r_datetime]</td>\n";
      $list .= "      <td><a
href='topic_form.php?commu_id=$commu_id&topic_id=$arycol3[c_commu_topic_id]'>$arycol3[name]</a></td>\n";
      $list .= "      <td width='30'>&nbsp;</td>\n";
   $list .= "    </tr>\n";
      $list .= "  </table></td>\n";
      $list .= "</tr>\n";
      $list .= "<tr>\n";
      $list .= "  <td bgcolor='#FFFFFF'><table width='100%'  border='0'
cellspacing='0' cellpadding='0'>\n";
      $list .= "    <tr>\n";
      $list .= "      <td>&nbsp;</td>\n";
      $list .= "    </tr>\n";
   $list .= "  </table>\n";
  $list .= "<table width='100%'  border='0' cellspacing='0'
cellpadding='5'>\n";
  $list .= "  <tr>\n";
  $list .= "  <td width='30' rowspan='2'>&nbsp;</td>\n";
  $list .= "  <td valign='top'>$body</td>\n";
  $list .= "  <td>$main_image</td>\n";
  $list .= "  <td width='30' rowspan='2'>&nbsp;</td>\n";
  $list .= "  </tr>\n";
  $list .= "  <tr>\n";
  $list .= "  <td colspan='2' align='right'>\n";
  $list .= " <a
href='topic_form.php?commu_id=$commu_id&topic_id=$arycol3[c_commu_topic_id]#write'
class='font4'>書き込み($total)</a>";
  $list .= "</td>\n";
  $list .= " </tr>\n";
  $list .= "</table>\n";
      $list .= "   </td>\n";
      $list .= "</tr>\n";
}

if($list == ""){
      $list .= "<tr>\n";
      $list .= "  <td bgcolor='#FFFFFF'>\n";
      $list .= "    <tr>\n";
      $list .= "      <td align='center'>記事がありません</td>\n";
      $list .= "    </tr>\n";
      $list .= "  </td>\n";
      $list .= "</tr>\n";
}

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=euc-jp">
<title>タイトル</title>
</head>

<body bgcolor="#FFFFFF" leftmargin="0" topmargin="0">
<table>
<?=$list ?>
</table>
</body>
</html>

-----------------------------------------------------------
OS:      Cent OS 4.4
Apache:    httpd-2.0.52
PHP:       4.4.5
pgpool-II:     1.3
PostgreSQL  8.1.4

------------------
エニグマ   浅野

asano @ engm.com




pgpool-general-jp メーリングリストの案内