[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'> </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'> </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> </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'> </td>\n";
$list .= " <td valign='top'>$body</td>\n";
$list .= " <td>$main_image</td>\n";
$list .= " <td width='30' rowspan='2'> </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 メーリングリストの案内