[pgcluster: 867] Re: connect 時の動作について
Suzuki Fumihito
suzukif ＠ nttdata-hokkaido.co.jp
2006年 3月 23日 (木) 23:44:36 JST
三谷さん
鈴木です。いつもすばやいご返答に感謝いたします。
> > このアプリケーションはSELECT文の発行だけなので、ある一つの
> > クラスタDBにconnectを発行した際に、レプリケーションサーバや
> > 他のクラスタDBに対して通信をしないようにするようなよい方法は
> > ないでしょうか。
>
> ロードバランサのconnection poolを有効にしてみてはどうでしょうか．
>
> > なお、アプリケーションはphpを使っています。connectの際にreadonly
> > みたいな指定ができ、そのときにはクラスタDB単独で応答を返す
> > ようなことができればいいのですが。
> んー，connect時にレプリケーションはしていないのですけど．．．
> SELECT以外に何かクエリーを投げているのではないでしょうか．
> レプリケーションサーバのログで何が投げられているか分かると思います．
> 確認してみてください．
>
標記件について、いろいろ試してみたところ、最初の質問が勘違いで、
どうも、ブラウザからphpのファイルにアクセスするときに、レプリケーション
サーバが反応していました。特にDB操作は行っていないアプリケーション
（<? echo "hello"; ?>と書いただけ）なのですが、レプリケーションサーバ
が動作しているようでした。そのときのログを添付します。
アプリケーションでDBの操作を行わない場合でもレプリケーションサーバ
との通信が発生することがあるのでしょうか？なお、上記アプリケーション
をphpのcliにて実行したときは、レプリケーションとの通信は行われて
おらず、Apacheを使ったときに通信が行われているようでした。
こちらの環境は以下です。
Apache2.0.46
PHP 4.4.0
PostgreSQL8.0.6 + 1.3.1rc3
なにか、お気づきの点があれば、ご教授下さい。よろしくお願いいたします。
======================================================================
pgreplicateデバッグログ
※ 192.168.0.1がレプリケーションサーバで、他のサーバはクラスタDBです。
======================================================================
2006-03-23 23:26:01 [27721] DEBUG:cmdSts=T
2006-03-23 23:26:01 [27721] DEBUG:cmdType=B
2006-03-23 23:26:01 [27721] DEBUG:rlog=0
2006-03-23 23:26:01 [27721] DEBUG:port=5432
2006-03-23 23:26:01 [27721] DEBUG:pid=13647
2006-03-23 23:26:01 [27721] DEBUG:from_host=192.168.0.1
2006-03-23 23:26:01 [27721] DEBUG:dbName=postgres
2006-03-23 23:26:01 [27721] DEBUG:userName=postgres
2006-03-23 23:26:01 [27721] DEBUG:recieve sec=1143123961
2006-03-23 23:26:01 [27721] DEBUG:recieve usec=841321
2006-03-23 23:26:01 [27721] DEBUG:query_size=5
2006-03-23 23:26:01 [27721] DEBUG:request_id=298
2006-03-23 23:26:01 [27721] DEBUG:replicate_id=0
2006-03-23 23:26:01 [27721] DEBUG:query=BEGIN
2006-03-23 23:26:01 [27721] DEBUG:sem_lock [1] req
2006-03-23 23:26:01 [27721] DEBUG:sem_lock [1] got it
2006-03-23 23:26:01 [27721] DEBUG:PGRreplicate_packet_send():checking host db1.mydomain for creating threads
2006-03-23 23:26:01 [27721] DEBUG:[0] is same host
2006-03-23 23:26:01 [27721] DEBUG:PGRreplicate_packet_send():checking host db2.mydomain for creating threads
2006-03-23 23:26:01 [27721] DEBUG:[1] is not same host
2006-03-23 23:26:01 [27721] DEBUG:pgr_createConn():PQsetdbLogin host[192.168.2.1] port[5432] db[postgres] user[postgres]
2006-03-23 23:26:02 [27721] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2006-03-23 23:26:02 [27721] DEBUG:start thread_send_cluster()
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1143123961,841321,213034,1,2) )
2006-03-23 23:26:02 [27721] DEBUG:PGRreplicate_packet_send():checking host db3.mydomain for creating threads
2006-03-23 23:26:02 [27721] DEBUG:[2] is not same host
2006-03-23 23:26:02 [27721] DEBUG:pgr_createConn():PQsetdbLogin host[192.168.3.1] port[5432] db[postgres] user[postgres]
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,2,0,1) )
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():execute query(BEGIN)
2006-03-23 23:26:02 [27721] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2006-03-23 23:26:02 [27721] DEBUG:start thread_send_cluster()
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1143123961,841321,213034,1,2) )
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():PQexec returns :BEGIN
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():pthread_exit[1]
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,2,0,1) )
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():execute query(BEGIN)
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():PQexec returns :BEGIN
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():pthread_exit[2]
2006-03-23 23:26:02 [27721] DEBUG:read_answer():QUERY DONE
2006-03-23 23:26:02 [27721] DEBUG:end thread_send_source()
2006-03-23 23:26:02 [27721] DEBUG:sem_unlock[1]
2006-03-23 23:26:02 [27721] DEBUG:PGRdo_replicate():PGRreplicate_packet_send returns 0
2006-03-23 23:26:02 [27721] DEBUG:cmdSts=T
2006-03-23 23:26:02 [27721] DEBUG:cmdType=R
2006-03-23 23:26:02 [27721] DEBUG:rlog=0
2006-03-23 23:26:02 [27721] DEBUG:port=5432
2006-03-23 23:26:02 [27721] DEBUG:pid=13647
2006-03-23 23:26:02 [27721] DEBUG:from_host=192.168.0.1
2006-03-23 23:26:02 [27721] DEBUG:dbName=postgres
2006-03-23 23:26:02 [27721] DEBUG:userName=postgres
2006-03-23 23:26:02 [27721] DEBUG:recieve sec=1143123962
2006-03-23 23:26:02 [27721] DEBUG:recieve usec=555451
2006-03-23 23:26:02 [27721] DEBUG:query_size=8
2006-03-23 23:26:02 [27721] DEBUG:request_id=299
2006-03-23 23:26:02 [27721] DEBUG:replicate_id=0
2006-03-23 23:26:02 [27721] DEBUG:query=ROLLBACK
2006-03-23 23:26:02 [27721] DEBUG:sem_lock [1] req
2006-03-23 23:26:02 [27721] DEBUG:sem_lock [1] got it
2006-03-23 23:26:02 [27721] DEBUG:PGRreplicate_packet_send():checking host db1.mydomain for creating threads
2006-03-23 23:26:02 [27721] DEBUG:[0] is same host
2006-03-23 23:26:02 [27721] DEBUG:PGRreplicate_packet_send():checking host db2.mydomain for creating threads
2006-03-23 23:26:02 [27721] DEBUG:[1] is not same host
2006-03-23 23:26:02 [27721] DEBUG:start thread_send_cluster()
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,3,0,1) )
2006-03-23 23:26:02 [27721] DEBUG:PGRreplicate_packet_send():checking host db3.mydomain for creating threads
2006-03-23 23:26:02 [27721] DEBUG:[2] is not same host
2006-03-23 23:26:02 [27721] DEBUG:start thread_send_cluster()
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,3,0,1) )
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():execute query(ROLLBACK)
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():execute query(ROLLBACK)
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():PQexec returns :ROLLBACK
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():pthread_exit[1]
2006-03-23 23:26:02 [27721] DEBUG:send_replicate_packet_to_server():PQexec returns :ROLLBACK
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-03-23 23:26:02 [27721] DEBUG:thread_send_cluster():pthread_exit[2]
2006-03-23 23:26:02 [27721] DEBUG:read_answer():QUERY DONE
2006-03-23 23:26:02 [27721] DEBUG:end thread_send_source()
2006-03-23 23:26:02 [27721] DEBUG:sem_unlock[1]
2006-03-23 23:26:02 [27721] DEBUG:PGRdo_replicate():PGRreplicate_packet_send returns 0
---
鈴木 文仁 [Suzuki Fumihito]
suzukif ＠ nttdata-hokkaido.co.jp
pgcluster メーリングリストの案内