[pgcluster: 933] Re: psql でクエリーが固まる
takay
takay.ml ＠ gmail.com
2006年 10月 24日 (火) 22:07:28 JST
お手数おかけします。
環境は2台のサーバ構成で、それぞれにクラスタ・レプリケーション・ロードバランサ
を起動しています。
設定ファイルとログを添付します（長文失礼します）。
＃長くなるのでコメント行は省いてます。
[/etc/hosts] --------------------------------------------------------------
127.0.0.1 localhost.localdomain localhost
192.168.1.11 sv1.local1.example.jp sv1
192.168.1.12 sv2.local1.example.jp sv2
192.168.2.111 cl_1.local2.example.jp rep_u.local2.example.jp lb_1.local2.example.jp cl_1 rep_u lb_1
192.168.2.112 cl_2.local2.example.jp rep_l.local2.example.jp lb_2.local2.example.jp cl_2 rep_l lb_2
[cluster.conf] -----------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> rep_u.local2.example.jp </Host_Name>
<Port> 18001 </Port>
<Recovery_Port> 18101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> rep_l.local2.example.jp </Host_Name>
<Port> 18001 </Port>
<Recovery_Port> 18101 </Recovery_Port>
</Replicate_Server_Info>
<Recovery_Port> 17101 </Recovery_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -2 </Rsync_Option>
<Rsync_Compress> yes </Rsync_Compress>
<Pg_Dump_Path> /usr/local/pgsql/bin/pg_dump </Pg_Dump_Path>
<When_Stand_Alone> read_only </When_Stand_Alone>
[pgreplicate.conf] -----------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cl_1.local2.example.jp </Host_Name>
<Port> 15432 </Port>
<Recovery_Port> 17101 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cl_2.local2.example.jp </Host_Name>
<Port> 15432 </Port>
<Recovery_Port> 17101 </Recovery_Port>
</Cluster_Server_Info>
<LoadBalance_Server_Info>
<Host_Name> lb_1.local2.example.jp </Host_Name>
<Recovery_Port> 16101 </Recovery_Port>
</LoadBalance_Server_Info>
<LoadBalance_Server_Info>
<Host_Name> lb_2.local2.example.jp </Host_Name>
<Recovery_Port> 16101 </Recovery_Port>
</LoadBalance_Server_Info>
■↓この部分は Server-2 のみの内容です。■
<Replicate_Server_Info>
<Host_Name> rep_u.local2.example.jp </Host_Name>
<Port> 18001 </Port>
<Recovery_Port> 18101 </Recovery_Port>
</Replicate_Server_Info>
■↑この部分は Server-2 のみの内容です。■
<Host_Name> rep_u.local2.example.jp </Host_Name>
■↑この部分は Server-2 では rep_l.local2.example.jp と設定してます。■
<Replication_Port> 18001 </Replication_Port>
<Recovery_Port> 18101 </Recovery_Port>
<RLOG_Port> 18301 </RLOG_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> yes </Use_Replication_Log>
[pglb.conf] -----------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cl_1.local2.example.jp </Host_Name>
<Port> 15432 </Port>
<Max_Connect> 32 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cl_2.local2.example.jp </Host_Name>
<Port> 15432 </Port>
<Max_Connect> 32 </Max_Connect>
</Cluster_Server_Info>
<Host_Name> lb_1.local2.example.jp </Host_Name>
■↑この部分は Server-2 では lb_2.local2.example.jp と設定してます。■
<Backend_Socket_Dir> /tmp </Backend_Socket_Dir>
<Receive_Port> 5432 </Receive_Port>
<Recovery_Port> 16101 </Recovery_Port>
<Max_Cluster_Num> 3 </Max_Cluster_Num>
<Use_Connection_Pooling> no </Use_Connection_Pooling>
[pg_hba.conf] -----------------------------------------------------------
local all all trust
# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
# Replication Server connections:
host all all 192.168.2.0 255.255.255.0 trust
host all all 192.168.1.0 255.255.255.0 trust
───────────────────────────────────
以下は前回お送りしたレプリケーションサーバのログの、前半部分です。
2006-10-24 21:53:08 [23958] DEBUG:/home/database/pgreplicate.log open ok
2006-10-24 21:53:08 [23958] DEBUG:PGR_Get_Conf_Data ok
2006-10-24 21:53:08 [23958] DEBUG:LoadBalanceTbl allocate ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():CascadeTbl shmget ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():CascadeTbl shmat ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():CascadeInf shmget ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():CascadeInf shmat ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():CommitLog shmget ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():Commit_Log_Tbl shmat ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():RLog Memory Allocation ok
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Host_Name,cl_1.local2.ubiqs.jp)
2006-10-24 21:53:08 [23958] DEBUG:registering hostname cl_1.local2.ubiqs.jp
2006-10-24 21:53:08 [23958] DEBUG:resolved name is 192.168.2.111
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Port,15432)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Recovery_Port,17101)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Host_Name,cl_2.local2.ubiqs.jp)
2006-10-24 21:53:08 [23958] DEBUG:registering hostname cl_2.local2.ubiqs.jp
2006-10-24 21:53:08 [23958] DEBUG:resolved name is 192.168.2.112
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Port,15432)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Recovery_Port,17101)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Host_Name,lb_1.local2.ubiqs.jp)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Recovery_Port,16101)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Host_Name,lb_2.local2.ubiqs.jp)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Recovery_Port,16101)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Host_Name,rep_u.local2.ubiqs.jp)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Replication_Port,18001)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Recovery_Port,18101)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(RLOG_Port,18301)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Response_Mode,normal)
2006-10-24 21:53:08 [23958] DEBUG:registering (key,value)=(Use_Replication_Log,yes)
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():HostTbl shmget ok
2006-10-24 21:53:08 [23958] DEBUG:PGRget_Conf_Data():HostTbl shmat ok
2006-10-24 21:53:08 [23958] DEBUG:Use Replication Log. Start PGR_RLog_Main()
2006-10-24 21:53:08 [23958] DEBUG:replicate_main():entering replicate_main
addr.sun_path[/home/database/.s.PGRLOG.18301]
Replicateion_Log->RLog_Sock_Path[/home/database/.s.PGRLOG.18301]
2006-10-24 21:53:08 [23958] DEBUG:replicate_main() 18001 port bind OK
2006-10-24 21:53:08 [23958] DEBUG:cmdSts=N
2006-10-24 21:53:08 [23958] DEBUG:rlog=0
2006-10-24 21:53:08 [23958] DEBUG:port=0
2006-10-24 21:53:08 [23958] DEBUG:pid=0
2006-10-24 21:53:08 [23958] DEBUG:from_host=192.168.2.111
2006-10-24 21:53:08 [23958] DEBUG:dbName=template1
2006-10-24 21:53:08 [23958] DEBUG:userName=postgres
2006-10-24 21:53:08 [23958] DEBUG:recieve sec=0
2006-10-24 21:53:08 [23958] DEBUG:recieve usec=0
2006-10-24 21:53:08 [23958] DEBUG:query_size=65
2006-10-24 21:53:08 [23958] DEBUG:request_id=0
2006-10-24 21:53:08 [23958] DEBUG:replicate_id=0
2006-10-24 21:53:08 [23958] DEBUG:recovery_status=0
2006-10-24 21:53:08 [23958] DEBUG:query=SELECT PGR_SYSTEM_COMMAND_FUNCTION(1,'192.168.2.111',18001,18101)
2006-10-24 21:53:08 [23958] DEBUG:PGRis_same_host():not same host
2006-10-24 21:53:08 [23958] DEBUG:pgr_createConn():PQsetdbLogin host[192.168.2.111] port[15432] db[template1] user[postgres]
2006-10-24 21:53:08 [23958] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2006-10-24 21:53:08 [23960] DEBUG:PGRrecovery_main():PGRrecovery_main bind port 18101
2006-10-24 21:53:08 [23963] DEBUG:start thread_send_cluster()
2006-10-24 21:53:08 [23963] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,1,0,1) )
2006-10-24 21:53:08 [23963] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-10-24 21:53:08 [23963] DEBUG:send_replicate_packet_to_server():PQexec send :SELECT PGR_SYSTEM_COMMAND_FUNCTION(1,'192.168.2.111',18001,18101)
2006-10-24 21:53:08 [23963] DEBUG:send_replicate_packet_to_server():PQexec returns :SYSTEM_COMMAND
2006-10-24 21:53:08 [23963] DEBUG:deleteTransactionTbl():
2006-10-24 21:53:08 [23963] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-10-24 21:53:08 [23963] DEBUG:deleteTransactionTbl():
2006-10-24 21:53:08 [23963] DEBUG:thread_send_cluster():pthread_exit[0]
2006-10-24 21:53:08 [23958] DEBUG:PGRis_same_host():not same host
2006-10-24 21:53:08 [23958] DEBUG:pgr_createConn():PQsetdbLogin host[192.168.2.112] port[15432] db[template1] user[postgres]
2006-10-24 21:53:08 [23958] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2006-10-24 21:53:08 [23964] DEBUG:start thread_send_cluster()
2006-10-24 21:53:08 [23964] DEBUG:send_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(8,1,0,1) )
2006-10-24 21:53:08 [23964] DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2006-10-24 21:53:08 [23964] DEBUG:send_replicate_packet_to_server():PQexec send :SELECT PGR_SYSTEM_COMMAND_FUNCTION(1,'192.168.2.111',18001,18101)
2006-10-24 21:53:08 [23964] DEBUG:send_replicate_packet_to_server():PQexec returns :SYSTEM_COMMAND
2006-10-24 21:53:08 [23964] DEBUG:deleteTransactionTbl():
2006-10-24 21:53:08 [23964] DEBUG:thread_send_cluster():return value from send_replicate_packet_to_server() is 0
2006-10-24 21:53:08 [23964] DEBUG:deleteTransactionTbl():
2006-10-24 21:53:08 [23964] DEBUG:thread_send_cluster():pthread_exit[1]
これ以降は前回添付したものがすべてです（削っていません）。
以上、よろしくお願いします。
---
takay.ml ＠ gmail.com
pgcluster メーリングリストの案内