[pgcluster: 324] Re: クラスタサーバ上でのクエリについて
KIKAWADA Mitsukuni/黄川田光国
mitsukuni ＠ unixmagic.net
2004年 6月 2日 (水) 11:54:16 JST
きかわだです。
solaris上で1.0.7rc1を使っていて、次のように問題があります。
ホストは次の構成で動かしています。
LB
|
+-------+---+----------+
クラスタ１ クラスタ２ クラスタ３
+-----+-----+----------+
|
レプリケータ
loadbarancer上でpsqlを動かしています。
LB上からpsqlでデータベースにインサートした場合のログです。。
bash-2.05$ psql -h localhost testdb
Welcome to psql 7.3.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testdb=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
field1 | integer | not null default nextval('public.test_field1_seq'::text)
field2 | text | default ''
Indexes: test_pkey primary key btree (field1)
testdb=# INSERT INTO testdb (field2) values('test val5');
WARNING: This query is not permitted when all replication servers fell down
On Wed, 02 Jun 2004 10:21:34 +0900
"KIKAWADA Mitsukuni/黄川田光国" <mitsukuni ＠ unixmagic.net> wrote:
続けて、コネクトされたロードバランサ側のログです。
DEBUG(PGRscan_cluster): 1 ClusterDB can be used
DEBUG(PGRscan_cluster): wt001112-local [5432],useFlag->2 max->32 use_num->0
DEBUG(PGRdo_child): I am 13847
DEBUG(do_accept): I am 13847 accept fd 4
DEBUG(pool_read): Protocol Version: 00020000
DEBUG(pool_read): Protocol Major: 2 Minor:0
DEBUG(pool_read): database: testdb
DEBUG(pool_read): user: postgres
DEBUG(pool_read): major: 2
DEBUG(pool_get_cp): pool_get_cp not found
DEBUG(pool_create_cp): [wt001110] [wt001112-local] is not same
DEBUG(PGRcheck_recovery): load balance wait
DEBUG(PGRsem_unlock): pool_open call
DEBUG(pool_read): read kind from backend Z
DEBUG(pool_read): read kind from frontend Q(51)
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:begin; select getdatabaseencoding(); commit: readlen: 44
DEBUG(mystrlen): strlength: 44
DEBUG(mystrlen): pool_read_string: string: :begin; select getdatabaseencoding(); commit:
DEBUG(mystrlen): pool_read_string: final read 44 string: :begin; select getdatabaseencoding(); commit:
DEBUG(mystrlen): pool_read_string: total result 44 string: :begin; select getdatabaseencoding(); commit:
DEBUG(pool_read): read kind from backend C
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:BEGIN: readlen: 75
DEBUG(mystrlen): strlength: 6
DEBUG(mystrlen): pending data saved: :Pblank: length: 69 return data: :BEGIN: length: 6
DEBUG(pool_read): read kind from backend pending data P len: 68 po: 1
DEBUG(mystrlen): cp->len(68) cp->po(1)
DEBUG(mystrlen): strlength(6) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data T len: 61 po: 8
DEBUG(mystrlen): cp->len(59) cp->po(10)
DEBUG(mystrlen): strlength(20) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data D len: 28 po: 41
DEBUG(pool_write): AsciiRow: len:6 data: EUC_J
DEBUG(pool_read): read kind from backend pending data C len: 16 po: 53
DEBUG(mystrlen): cp->len(16) cp->po(53)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data C len: 8 po: 61
DEBUG(mystrlen): cp->len(8) cp->po(61)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data Z len: 0 po: 69
DEBUG(pool_read): read kind from frontend Q(51)
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'; COMMIT: readlen: 82
DEBUG(mystrlen): strlength: 82
DEBUG(mystrlen): pool_read_string: string: :BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'; COMMIT:
DEBUG(mystrlen): pool_read_string: final read 82 string: :BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'; COMMIT:
DEBUG(mystrlen): pool_read_string: total result 82 string: :BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'; COMMIT:
DEBUG(pool_read): read kind from backend C
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:BEGIN: readlen: 59
DEBUG(mystrlen): strlength: 6
DEBUG(mystrlen): pending data saved: :Pblank: length: 53 return data: :BEGIN: length: 6
DEBUG(pool_read): read kind from backend pending data P len: 52 po: 1
DEBUG(mystrlen): cp->len(52) cp->po(1)
DEBUG(mystrlen): strlength(6) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data T len: 45 po: 8
DEBUG(mystrlen): cp->len(43) cp->po(10)
DEBUG(mystrlen): strlength(9) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data D len: 23 po: 30
DEBUG(pool_write): AsciiRow: len:1 data:
DEBUG(pool_read): read kind from backend pending data C len: 16 po: 37
DEBUG(mystrlen): cp->len(16) cp->po(37)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data C len: 8 po: 45
DEBUG(mystrlen): cp->len(8) cp->po(45)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data Z len: 0 po: 53
DEBUG(pool_read): read kind from frontend Q(51)
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;: readlen: 531
DEBUG(mystrlen): strlength: 531
DEBUG(mystrlen): pool_read_string: string: :SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;:
DEBUG(mystrlen): pool_read_string: final read 531 string: :SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;:
DEBUG(mystrlen): pool_read_string: total result 531 string: :SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;:
DEBUG(pool_read): read kind from backend P
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:blank: readlen: 177
DEBUG(mystrlen): strlength: 6
DEBUG(mystrlen): pending data saved: :T: length: 171 return data: :blank: length: 6
DEBUG(pool_read): read kind from backend pending data T len: 170 po: 1
DEBUG(mystrlen): cp->len(168) cp->po(3)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(mystrlen): cp->len(151) cp->po(20)
DEBUG(mystrlen): strlength(5) bufsz(1024)
DEBUG(mystrlen): cp->len(136) cp->po(35)
DEBUG(mystrlen): strlength(5) bufsz(1024)
DEBUG(mystrlen): cp->len(121) cp->po(50)
DEBUG(mystrlen): strlength(6) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data D len: 104 po: 67
DEBUG(pool_write): AsciiRow: len:6 data: publi
DEBUG(pool_write): AsciiRow: len:4 data: tes
DEBUG(pool_write): AsciiRow: len:5 data: tabl
DEBUG(pool_write): AsciiRow: len:8 data: postgre
DEBUG(pool_read): read kind from backend pending data D len: 63 po: 108
DEBUG(pool_write): AsciiRow: len:6 data: publi
DEBUG(pool_write): AsciiRow: len:15 data: test_field1_se
DEBUG(pool_write): AsciiRow: len:8 data: sequenc
DEBUG(pool_write): AsciiRow: len:8 data: postgre
DEBUG(pool_read): read kind from backend pending data C len: 8 po: 163
DEBUG(mystrlen): cp->len(8) cp->po(163)
DEBUG(mystrlen): strlength(7) bufsz(1024)
DEBUG(pool_read): read kind from backend pending data Z len: 0 po: 171
DEBUG(pool_read): read kind from frontend Q(51)
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:INSERT INTO testdb (field2) values('test val5');: readlen: 49
DEBUG(mystrlen): strlength: 49
DEBUG(mystrlen): pool_read_string: string: :INSERT INTO testdb (field2) values('test val5');:
DEBUG(mystrlen): pool_read_string: final read 49 string: :INSERT INTO testdb (field2) values('test val5');:
DEBUG(mystrlen): pool_read_string: total result 49 string: :INSERT INTO testdb (field2) values('test val5');:
DEBUG(pool_read): read kind from backend N
DEBUG(pool_read_string): pool_read_string: readsize: 1024 readp: 0
DEBUG(pool_read_string): pool_read_string: buf:WARNING: This query is not permitted when all replication servers fell down
: readlen: 79
DEBUG(mystrlen): strlength: 79
DEBUG(mystrlen): pool_read_string: string: :WARNING: This query is not permitted when all replication servers fell down
:
DEBUG(mystrlen): pool_read_string: final read 79 string: :WARNING: This query is not permitted when all replication servers fell down
:
DEBUG(mystrlen): pool_read_string: total result 79 string: :WARNING: This query is not permitted when all replication servers fell down
:
DEBUG(pool_read): read kind from backend Z
以上、参考になりますでしょうか？
solaris上でテストして欲しいぞ！という要望があれば、
こちらでガンガン作業を進めます。
よろしく御願いします。
> きかわだです。
>
> On Tue, 01 Jun 2004 16:41:53 +0900
> mitani <mitani ＠ sraw.co.jp> wrote:
>
> > > きかわだです。
> > こんにちは．
> > きかわださん，ということは環境はSolarisですね(^^)．
>
> そゆことです(笑)
> solaris 9を使用して動作検証を行っております。
>
>
> > > bash-2.05$ createdb -EEUC_JP testdb
> > > ERROR: CREATE DATABASE: source database "template1" is being accessed by other users
> > > (これ以後タイムアウト待ち)
> > createdbかdropdbをされた後，template1へのセッション削除がうまく行ってい
> > ないようです．
> > 今すぐは見れないのですが，時間のあるときに確認してみます．
>
> これが解消しないことにはクラスタ構成が組めているかどうか
> 確認出来ないので、是非ともよろしく御願いします。
>
> --
> 黄川田光国 / KIKAWADA Mitsukuni <mitsukuni ＠ unixmagic.net>
>
--
黄川田光国 / KIKAWADA Mitsukuni <mitsukuni ＠ unixmagic.net>
pgcluster メーリングリストの案内