[pgcluster: 547] Re: FreeBSD 5.2.1 + PGCluster 1.0.7 createdb 失敗
me477494 ＠ members.interq.or.jp
me477494 ＠ members.interq.or.jp
2004年 9月 18日 (土) 08:03:46 JST
幾田です。
お世話になっております。
三谷様。ご回答ありがとうございました。
また、返信が時間が掛かり誠に申し訳ございませんでした。
結果から申し上げますと
レプリケーションされるようになったのですが
createdb、connect、update、insert が
応答まで非常に遅く困っております。
connect 後、select は非常に早いのですが…。
================================================================================
早速ではございますが
ご返答頂いた際にご指示頂けた内容を
下記に記載致します。
※ドメイン部分は公開できませんので伏せております。
--<ロードバランサ>--------------------------------------------------------------
▼/etc/hosts
::1 localhost.xxxxxx.com localhost
127.0.0.1 localhost.xxxxxx.com localhost
192.168.0.21 lb1.xxxxxx.com lb1
192.168.0.21 lb1.xxxxxx.com.
192.168.0.11 web1.xxxxxx.com web1
192.168.0.12 web2.xxxxxx.com web2
192.168.0.31 cl1.xxxxxx.com cl1
192.168.0.32 cl2.xxxxxx.com cl2
192.168.0.33 cl3.xxxxxx.com cl3
192.168.0.41 rp1.xxxxxx.com rp1
192.168.0.42 rp2.xxxxxx.com rp2
▼/etc/rc.conf
hostname="lb1.xxxxxx.com"
ifconfig_em0="inet 192.168.0.21 netmask 255.255.255.0"
▼hostname コマンド実行結果
lb1.xxxxxx.com
▼ifconfig コマンド実行結果
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
options=1b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING>
inet 192.168.0.21 netmask 0xffffff00 broadcast 192.168.0.255
inet6 fe80::20d:60ff:fe83:8904%em0 prefixlen 64 scopeid 0x1
inet 10.0.0.40 netmask 0xffffff00 broadcast 10.0.0.255
ether 00:0d:60:83:89:04
media: Ethernet autoselect (100baseTX <full-duplex>)
status: active
--<クラスタDB１(マスタDB)>------------------------------------------------------
▼/etc/hosts
::1 localhost.xxxxxx.com localhost
127.0.0.1 localhost.xxxxxx.com localhost
192.168.0.31 cl1.xxxxxx.com cl1
192.168.0.31 cl1.xxxxxx.com.
192.168.0.11 web1.xxxxxx.com web1
192.168.0.12 web2.xxxxxx.com web2
192.168.0.21 lb1.xxxxxx.com lb1
192.168.0.32 cl2.xxxxxx.com cl2
192.168.0.33 cl3.xxxxxx.com cl3
192.168.0.41 rp1.xxxxxx.com rp1
192.168.0.42 rp2.xxxxxx.com rp2
▼/etc/rc.conf
hostname="cl1.xxxxxx.com"
ifconfig_em0="inet 192.168.0.31 netmask 255.255.255.0"
▼hostname コマンド実行結果
cl1.xxxxxx.com
▼ifconfig コマンド実行結果
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
options=1b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING>
inet 192.168.0.31 netmask 0xffffff00 broadcast 192.168.0.255
inet6 fe80::20d:60ff:fe83:24e3%em0 prefixlen 64 scopeid 0x1
ether 00:0d:60:83:24:e3
media: Ethernet autoselect (100baseTX <full-duplex>)
status: active
--<クラスタDB２>----------------------------------------------------------------
▼/etc/hosts
::1 localhost.xxxxxx.com localhost
127.0.0.1 localhost.xxxxxx.com localhost
192.168.0.32 cl2.xxxxxx.com cl2
192.168.0.32 cl2.xxxxxx.com.
192.168.0.11 web1.xxxxxx.com web1
192.168.0.12 web2.xxxxxx.com web2
192.168.0.21 lb1.xxxxxx.com lb1
192.168.0.31 cl1.xxxxxx.com cl1
192.168.0.33 cl3.xxxxxx.com cl3
192.168.0.41 rp1.xxxxxx.com rp1
192.168.0.42 rp2.xxxxxx.com rp2
▼/etc/rc.conf
hostname="cl2.xxxxxx.com"
ifconfig_em0="inet 192.168.0.32 netmask 255.255.255.0"
▼hostname コマンド実行結果
cl2.xxxxxx.com
▼ifconfig コマンド実行結果
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
options=1b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING>
inet 192.168.0.32 netmask 0xffffff00 broadcast 192.168.0.255
inet6 fe80::20d:60ff:fe83:8939%em0 prefixlen 64 scopeid 0x1
ether 00:0d:60:83:89:39
media: Ethernet autoselect (100baseTX <full-duplex>)
status: active
--<クラスタDB３>----------------------------------------------------------------
▼/etc/hosts
::1 localhost.xxxxxx.com localhost
127.0.0.1 localhost.xxxxxx.com localhost
192.168.0.33 cl3.xxxxxx.com cl3
192.168.0.33 cl3.xxxxxx.com.
192.168.0.11 web1.xxxxxx.com web1
192.168.0.12 web2.xxxxxx.com web2
192.168.0.21 lb1.xxxxxx.com lb1
192.168.0.31 cl1.xxxxxx.com cl1
192.168.0.32 cl2.xxxxxx.com cl2
192.168.0.41 rp1.xxxxxx.com rp1
192.168.0.42 rp2.xxxxxx.com rp2
▼/etc/rc.conf
hostname="cl3.xxxxxx.com"
ifconfig_em0="inet 192.168.0.33 netmask 255.255.255.0"
▼hostname コマンド実行結果
cl3.xxxxxx.com
▼ifconfig コマンド実行結果
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
options=1b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING>
inet 192.168.0.33 netmask 0xffffff00 broadcast 192.168.0.255
inet6 fe80::20d:60ff:fe83:8978%em0 prefixlen 64 scopeid 0x1
ether 00:0d:60:83:89:78
media: Ethernet autoselect (100baseTX <full-duplex>)
status: active
--<レプリケーションサーバ>------------------------------------------------------
▼/etc/hosts
::1 localhost.xxxxxx.com localhost
127.0.0.1 localhost.xxxxxx.com localhost
192.168.0.41 rp1.xxxxxx.com rp1
192.168.0.41 rp1.xxxxxx.com.
192.168.0.11 web1.xxxxxx.com web1
192.168.0.12 web2.xxxxxx.com web2
192.168.0.21 lb1.xxxxxx.com lb1
192.168.0.31 cl1.xxxxxx.com cl1
192.168.0.32 cl2.xxxxxx.com cl2
192.168.0.33 cl3.xxxxxx.com cl3
192.168.0.42 rp2.xxxxxx.com rp2
▼/etc/rc.conf
hostname="rp1.xxxxxx.com"
ifconfig_em0="inet 192.168.0.41 netmask 255.255.255.0"
▼hostname コマンド実行結果
rp1.xxxxxx.com
▼ifconfig コマンド実行結果
em0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
options=1b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING>
inet 192.168.0.41 netmask 0xffffff00 broadcast 192.168.0.255
inet6 fe80::20d:60ff:fe83:89a4%em0 prefixlen 64 scopeid 0x1
ether 00:0d:60:83:89:a4
media: Ethernet autoselect (100baseTX <full-duplex>)
status: active
▼デバックログ
/usr/local/pgsql/data/pgreplicate.sts open ok
DEBUG(init_server_tbl): PGR_Get_Conf_Data ok
DEBUG(init_server_tbl): LoadBalanceTbl allocate ok
DEBUG(init_server_tbl): CascadeTbl shmget ok
DEBUG(init_server_tbl): CascadeTbl shmat ok
DEBUG(init_server_tbl): CascadeInf shmget ok
DEBUG(init_server_tbl): CascadeInf shmat ok
DEBUG(init_server_tbl): CommitLog shmget ok
DEBUG(init_server_tbl): Commit_Log_Tbl shmat ok
DEBUG(init_server_tbl): Conf data read ok
DEBUG(init_server_tbl): HostTbl shmget ok
DEBUG(init_server_tbl): HostTbl shmat ok
DEBUG(write_log_file): LockWaitTbl shmget ok
DEBUG(write_log_file): LockWaitTbl shmat ok
DEBUG(replicate_main): replicate main 8777 port bind OK
DEBUG(PGRreplicate_packet_send): cmdSts=N
DEBUG(PGRreplicate_packet_send): cmdType=
DEBUG(PGRreplicate_packet_send): port=0
DEBUG(PGRreplicate_packet_send): pid=0
DEBUG(PGRreplicate_packet_send): except_host=
DEBUG(PGRreplicate_packet_send): from_host=rp1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=0
DEBUG(PGRreplicate_packet_send): recieve usec=0
DEBUG(PGRreplicate_packet_send): query_size=64
DEBUG(PGRreplicate_packet_send): query=SELECT PGR_SYSTEM_COMMAND_FUNCTION(1,'rp1.xxxxxx.com',8777,7778)
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): not same host:
DEBUG(is_need_response): sem_lock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl1.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl1.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl1.xxxxxx.com] port[5432] db[template1] user[postgres]
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
DEBUG(PGRrecovery_main): PGRrecovery_main bind port 7778
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): dbPersistLogin timeout
ERROR(pgr_createConn): New Transaction but pgr_createConn failed
ERROR(pgr_createConn): -1077944768 ＠ cl1.xxxxxx.com is not ready
ERROR(pgr_createConn): setTransactionTbl failed
DEBUG(pgr_createConn): sem_lock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(PGRis_same_host): not same host:
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl2.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl2.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl2.xxxxxx.com] port[5432] db[template1] user[postgres]
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): dbPersistLogin timeout
ERROR(pgr_createConn): New Transaction but pgr_createConn failed
ERROR(pgr_createConn): -1077944768 ＠ cl2.xxxxxx.com is not ready
ERROR(pgr_createConn): setTransactionTbl failed
DEBUG(pgr_createConn): sem_lock[3]
DEBUG(PGRsem_lock): sem_unlock[2]
DEBUG(PGRis_same_host): not same host:
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl3.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl3.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl3.xxxxxx.com] port[5432] db[template1] user[postgres]
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!
ERROR(pgr_createConn): dbPersistLogin timeout
ERROR(pgr_createConn): New Transaction but pgr_createConn failed
ERROR(pgr_createConn): -1077944768 ＠ cl3.xxxxxx.com is not ready
ERROR(pgr_createConn): setTransactionTbl failed
DEBUG(pgr_createConn): sem_lock[4]
DEBUG(PGRsem_lock): sem_unlock[3]
DEBUG(getTransactionTbl): sem_unlock[4]
DEBUG(PGRsem_unlock): PGRreplicate_packet_send end
DEBUG(PGRsem_unlock): wait replicate
DEBUG(PGRsem_unlock): wait replicate
DEBUG(PGRsem_unlock): wait replicate
DEBUG(PGRsem_unlock): wait replicate
DEBUG(PGRsem_unlock): wait replicate
※以下「DEBUG(PGRsem_unlock): wait replicate」の繰り返しです。
================================================================================
各種マニュアルで起動順番は…
１．レプリケーションサーバ
２．クラスタ
３．ロードバランサ
との事でしたが
「ERROR(pgr_createConn): PQsetdbLogin failed. close socket!!」
と表示されておりましたので
１．クラスタ
２．レプリケーションサーバ
３．ロードバランサ
の順で起動した所
「createdb」の実行が成功し
「pgsql」で作成したDBに接続でき
DDLやDMLの発行ができました。
クラスタから先に行動した際のデバックログは下記の通りです。
▼デバックログ
DEBUG(init_server_tbl): /usr/local/pgsql/data/pgreplicate.sts open ok
DEBUG(init_server_tbl): PGR_Get_Conf_Data ok
DEBUG(init_server_tbl): LoadBalanceTbl allocate ok
DEBUG(init_server_tbl): CascadeTbl shmget ok
DEBUG(init_server_tbl): CascadeTbl shmat ok
DEBUG(init_server_tbl): CascadeInf shmget ok
DEBUG(init_server_tbl): CascadeInf shmat ok
DEBUG(init_server_tbl): CommitLog shmget ok
DEBUG(init_server_tbl): Commit_Log_Tbl shmat ok
DEBUG(init_server_tbl): Conf data read ok
DEBUG(init_server_tbl): HostTbl shmget ok
DEBUG(init_server_tbl): HostTbl shmat ok
DEBUG(write_log_file): LockWaitTbl shmget ok
DEBUG(write_log_file): LockWaitTbl shmat ok
DEBUG(replicate_main): replicate main 8777 port bind OK
DEBUG(PGRreplicate_packet_send): cmdSts=N
DEBUG(PGRreplicate_packet_send): cmdType=
DEBUG(PGRreplicate_packet_send): port=0
DEBUG(PGRreplicate_packet_send): pid=0
DEBUG(PGRreplicate_packet_send): except_host=
DEBUG(PGRreplicate_packet_send): from_host=rp1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=0
DEBUG(PGRreplicate_packet_send): recieve usec=0
DEBUG(PGRreplicate_packet_send): query_size=64
DEBUG(PGRreplicate_packet_send): query=SELECT PGR_SYSTEM_COMMAND_FUNCTION(1,'rp1.xxxxxx.com',8777,7778)
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): not same host:
DEBUG(is_need_response): sem_lock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl1.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl1.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl1.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(PGRrecovery_main): PGRrecovery_main bind port 7778
================================================================================
PostgreSQL用に行った設定を下記に記載致します。
下記の設定で
ウェブサーバ2台＋DB(PostgreSQL)サーバ1台で
安定稼動しております。
※PGCluster が動作すれば、通常のPostgreSQL1台と入れ替える予定です。
▼ /usr/src/sys/i386/conf に追加
# PostgreSQL setting
options SYSVSHM
options SHMMAXPGS=40960
options SHMSEG=256
options SYSVSEM
options SEMMNI=256
options SEMMNS=512
options SEMMNU=256
options SEMMAP=256
▼ /etc/login.conf に追加
# postgres
postgres:\
:datasize-cur=256M:\
:maxproc-cur=256:\
:openfiles-cur=256:
================================================================================
「createdb」コマンドの実行や
「psql」でのconnect、「insert」文や「update」文の発行に
非常に時間が掛かり、実用に耐える状態ではございませんでした。
--<マスターDBで「createdb」実行時のログ>----------------------------------------
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl1.xxxxxx.com query:SELECT
PGR_SYSTEM_COMMAND_FUNCTION(1,'rp1.xxxxxx.com',8777,7778)
DEBUG(insertTransactionTbl): sem_lock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(getTransactionTbl): hit !! transaction tbl host cl1.xxxxxx.com db:template1 pid:0
DEBUG(PGRis_same_host): not same host:
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl2.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl2.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl2.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl2.xxxxxx.com query:SELECT
PGR_SYSTEM_COMMAND_FUNCTION(1,'rp1.xxxxxx.com',8777,7778)
DEBUG(insertTransactionTbl): sem_lock[3]
DEBUG(PGRsem_lock): sem_unlock[2]
DEBUG(getTransactionTbl): hit !! transaction tbl host cl2.xxxxxx.com db:template1 pid:0
DEBUG(PGRis_same_host): not same host:
DEBUG(PGRreplicate_packet_send_each_server): except:0@ host:5432 ＠ cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl3.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl3.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl3.xxxxxx.com] port[5432] db[template1] user[postgres]
--<マスターDBで「psql [DB名]」実行時のログ>-------------------------------------
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl3.xxxxxx.com query:SELECT
PGR_SYSTEM_COMMAND_FUNCTION(1,'rp1.xxxxxx.com',8777,7778)
DEBUG(insertTransactionTbl): sem_lock[4]
DEBUG(PGRsem_lock): sem_unlock[3]
DEBUG(getTransactionTbl): hit !! transaction tbl host cl3.xxxxxx.com db:template1 pid:0
DEBUG(getTransactionTbl): sem_unlock[4]
DEBUG(PGRsem_unlock): PGRreplicate_packet_send end
DEBUG(PGRsem_unlock): wait replicate
DEBUG(PGRsem_unlock): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=40788
DEBUG(PGRreplicate_packet_send): except_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=40811
DEBUG(PGRreplicate_packet_send): except_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(PGRreplicate_packet_send): recieve usec=423529
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(PGRreplicate_packet_send): recieve usec=423257
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): 5432 @ cl1.xxxxxx.com return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(PGRreturn_result): PGRreturn_result[]
DEBUG(PGRreturn_result): 128 send
DEBUG(PGRreturn_result): status of PGRreturn_result[0]
DEBUG(PGRreturn_result): sem_lock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl1.xxxxxx.com host:5432 ＠ cl2.xxxxxx.com
DEBUG(PGRis_same_host): 5432 @ cl1.xxxxxx.com return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(PGRreturn_result): PGRreturn_result[]
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl2.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl2.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(PGRreturn_result): 128 send
DEBUG(PGRreturn_result): status of PGRreturn_result[0]
DEBUG(PGRreturn_result): sem_lock[2]
DEBUG(pgr_createConn): PQsetdbLogin host[cl2.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=31335
DEBUG(PGRreplicate_packet_send): except_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(PGRreplicate_packet_send): recieve usec=426056
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=40862
DEBUG(PGRreplicate_packet_send): except_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=mc
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(PGRreplicate_packet_send): recieve usec=426551
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): 5432 @ cl1.xxxxxx.com return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=31994
DEBUG(PGRreplicate_packet_send): except_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(PGRreplicate_packet_send): recieve usec=427112
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): replicate_loop selected
DEBUG(replicate_loop): wait replicate
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=32037
DEBUG(PGRreplicate_packet_send): except_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095406464
DEBUG(PGRreplicate_packet_send): recieve usec=433993
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
=================================================================================
「create table」文は、下記を発行致しました。
----------------------------------------
CREATE TABLE test (
id INTEGER PRIMARY KEY,
add_date TIMESTAMP DEFAULT NOW(),
data VARCHAR(20)
);
----------------------------------------
実行後、下記メッセージが表示されたのですが…
----------------------------------------
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
CREATE TABLE
----------------------------------------
同内容の「NOTICE」が
何故二件出力されたのかが不明です。
通常の PostgreSQL では
一件しか出力されないと記憶しているのですが
記憶違いであれば申し訳ございません。
下記に、「create table」実行時のログを記載致しますが
一行目の「DEBUG(pgr_createConn): PQsetdbLogin ok!!」が
表示されるまで5分以上掛かりました。
--<マスターDBで「create table」文 実行時のログ>---------------------------------
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl2.xxxxxx.com query:SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1095406464,423529) )
DEBUG(insertTransactionTbl): PQexec send :SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): PQexec end
DEBUG(getTransactionTbl): hit !! transaction tbl host cl2.xxxxx.com db:template1 pid:40811
DEBUG(getTransactionTbl): sem_lock[3]
DEBUG(PGRsem_lock): sem_unlock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl1.xxxxxx.com host:5432 ＠ cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl3.xxxxxx.com host:5432 ＠ cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl1.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl1.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(PGRsend_replicate_packet_to_server): host(cl2.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl2.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(pgr_createConn): PQsetdbLogin host[cl1.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl1.xxxxxx.com host:5432 ＠ cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl3.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl3.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl3.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=40891
DEBUG(PGRreplicate_packet_send): except_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095407064
DEBUG(PGRreplicate_packet_send): recieve usec=435303
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): 5432 @ cl1.xxxxxx.com return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=31380
DEBUG(PGRreplicate_packet_send): except_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095407064
DEBUG(PGRreplicate_packet_send): recieve usec=436236
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=32061
DEBUG(PGRreplicate_packet_send): except_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095407064
DEBUG(PGRreplicate_packet_send): recieve usec=436855
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
=================================================================================
「insert」文は、下記を発行致しました。
----------------------------------------
INSERT INTO test ( id, data )
VALUES ( 0, 'test' );
----------------------------------------
「insert」文は、ロードバランサから
「psql -h lb1.xxxxxx.com -p 5432 [DB名]」で接続して発行しました。
※ロードバランサからの接続も非常に時間が掛かりました
--<ロードバランサで「insert」文 実行時のログ>------------------------------------
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): wait replicate
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl1.xxxxxx.com query:SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1095407064,436236) )
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl2.xxxxxx.com query:SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1095407064,435303) )
DEBUG(insertTransactionTbl): PQexec send :SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): PQexec end
DEBUG(getTransactionTbl): hit !! transaction tbl host cl2.xxxxxx.com db:template1 pid:40891
DEBUG(getTransactionTbl): sem_lock[3]
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:template1 port:5432 user:postgres host:cl3.xxxxxx.com query:SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1095406464,433993) )
DEBUG(insertTransactionTbl): PQexec send :SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): PQexec end
DEBUG(getTransactionTbl): hit !! transaction tbl host cl3.xxxxxx.com db:template1 pid:32037
DEBUG(getTransactionTbl): sem_lock[4]
DEBUG(PGRsem_lock): sem_unlock[3]
DEBUG(PGRsem_lock): sem_unlock[2]
DEBUG(getTransactionTbl): sem_unlock[4]
DEBUG(PGRsem_unlock): PGRreplicate_packet_send end
DEBUG(PGRsem_unlock): replicate_loop selected
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl1.xxxxxx.com host:5432 ＠ cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl3.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl3.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
ERROR(PGRread_packet): recv failed: (Connection reset by peer)
ERROR(PGRread_packet): session closed
DEBUG(pgr_createConn): PQsetdbLogin host[cl3.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(child_wait): replicate main: selected
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=31518
DEBUG(PGRreplicate_packet_send): except_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl3.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095410664
DEBUG(PGRreplicate_packet_send): recieve usec=493598
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): wait replicate
DEBUG(insertTransactionTbl): PQexec send :SET client_encoding = 'SJIS'
DEBUG(insertTransactionTbl): PQexec end
DEBUG(getTransactionTbl): hit !! transaction tbl host cl1.xxxxxx.com db:template1 pid:31380
DEBUG(getTransactionTbl): sem_lock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl2.xxxxxx.com host:5432 ＠ cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl1.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl1.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ cl3.xxxxxx.com host:5432 ＠ cl2.xxxxxx.com
DEBUG(pgr_createConn): PQsetdbLogin host[cl1.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:cl2.xxxxxx.com
DEBUG(PGRsend_replicate_packet_to_server): host(cl2.xxxxxx.com) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[cl2.xxxxxx.com] port[5432] db[template1] user[postgres]
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate main: selected
DEBUG(replicate_loop): wait replicate
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=32198
DEBUG(PGRreplicate_packet_send): except_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl2.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095410664
DEBUG(PGRreplicate_packet_send): recieve usec=829002
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(is_need_response): sem_lock[1]
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=28
DEBUG(PGRread_packet): read[28] query[SET client_encoding = 'SJIS']
DEBUG(PGRread_packet): query :: SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=T
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=41307
DEBUG(PGRreplicate_packet_send): except_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): from_host=cl1.xxxxxx.com
DEBUG(PGRreplicate_packet_send): dbName=template1
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1095410664
DEBUG(PGRreplicate_packet_send): recieve usec=829724
DEBUG(PGRreplicate_packet_send): query_size=28
DEBUG(PGRreplicate_packet_send): query=SET client_encoding = 'SJIS'
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): 5432 @ cl1.xxxxxx.com return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(replicate_loop): wait replicate
================================================================================
以上です。
誠にお手数かとは存じますが
お手隙の際にご解答頂ければ幸いです。
説明内容の不備や
環境設定の不備等がございましたら
お申し付け下さい。
では、何卒宜しくお願い申し上げます。
pgcluster メーリングリストの案内