[pgpool-general-jp: 272] Re: pgpool-II パラレルモード使用下で特定の処理を行うと停止する
Yoshiharu Mori
y-mori @ sraoss.co.jp
2007年 11月 5日 (月) 13:34:07 JST
浅野様
盛です。
> 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
読んではいけないファイルディスクリプタからデータを読み込んでいるようです。
以下のパッチをpool_process_query.cに当てるといかがでしょうか?
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgpool/pgpool-II/pool_process_query.c.diff?r1=1.23.2.24&r2=1.23.2.25&f=u
> お世話になります。浅野と申します。
>
>
> 当方にて制作している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 mailing list
> pgpool-general-jp @ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp
>
--
SRA OSS, Inc. 日本支社
Yoshiharu Mori <y-mori @ sraoss.co.jp>
http://www.sraoss.co.jp/
pgpool-general-jp メーリングリストの案内