[pgpool-general-jp: 1228] Re: スキーマ名を指定する必要がある場合のオンメモリクエリキャッシュ機能の挙動について

Tatsuo Ishii ishii @ sraoss.co.jp
2013年 11月 10日 (日) 22:13:04 JST


石井です。

FAQ
http://www.pgpool.net/mediawiki/index.php/FAQ#When_I_use_schema_qualified_table_names.2C_pgpool-II_does_not_invalidate_on_memory_query_cache_and_I_got_outdated_data._Why.3F
にもありますが、たぶん pgpool-regclass をインストールしていないのではないでしょうか?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> お世話になります。
> 神谷と申します。
> 
> オンメモリクエリキャッシュ機能を有効にしている状態で、
>  スキーマ名.テーブル名
> のテーブルを更新してもキャッシュを読み込んでしまいます。
> スキーマ名を指定しない場合は問題なく動作しております。
> 
> 設定ミスかバグだと思っております。
> 設定ミスでしたらミスの箇所をご教示頂けますと幸いです。
> 
> 以下に各種情報を記載させて頂きます。
> 
> [環境]
> PostgreSQL9.3.1
> pgpool-II-3.3.1
> 
> [pgpoolのオンメモリクエリキャッシュ機能の設定]
> memory_cache_enabled = on
> memqcache_method = 'shmem'
> memqcache_memcached_host = 'localhost'
> memqcache_memcached_port = 11211
> memqcache_total_size = 67108864
> memqcache_max_num_cache = 1000000
> memqcache_expire = 0
> memqcache_auto_cache_invalidation = on
> memqcache_maxcache = 409600
> memqcache_cache_block_size = 1048576
> memqcache_oiddir = '/home/ap2/oiddir'
> white_memqcache_table_list = ''
> black_memqcache_table_list = ''
> 
> [現象を確認したテーブル]
> # \d bookstore.users
>                                          テーブル "bookstore.users"
>          列          |           型           |                            修飾語
> ---------------------+------------------------+--------------------------------------------------------------
>  id                  | integer                | not null default nextval('bookstore.users_id_seq'::regclass)
>  username            | character varying(50)  | not null
>  password            | character varying(255) | not null
>  group               | integer                | not null default 1
>  email               | character varying(255) | not null default ''::character varying
>  last_login          | character varying(50)  | not null
>  login_hash          | character varying(255) | not null
>  profile_fields      | text                   | not null default ''::text
>  created_at          | integer                | not null default 0
>  updated_at          | integer                | not null default 0
>  password_miss_count | integer                | not null default 0
> 
> [現象を確認したクエリ]
> # SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
>  password_miss_count
> ---------------------
>                    1
> 
> ap2=# SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
>  password_miss_count
> ---------------------
>                    1
> 
> ap2=# UPDATE bookstore.users SET password_miss_count = 0 WHERE username = 'テストユーザー' ;
> UPDATE 1
> 
> ap2=# SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
>  password_miss_count
> ---------------------
>                    1
> 
> ap2=# SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
>  password_miss_count
> ---------------------
>                    1
> 
> [現象を確認したクエリを実行したときのログ]
> 2013-11-10 19:57:32 DEBUG: pid 30188: statement2: UPDATE bookstore.users SET password_miss_count = 0 WHERE username = 'テストユーザー' ;
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_set_query_in_progress: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: extended:0 query:SELECT oid FROM pg_class WHERE relname = 'bookstore.users'
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: kind: T
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: row description received
> 2013-11-10 19:57:32 DEBUG: pid 30188: num_fileds: 1
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: Command complete received
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: kind: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_query: Ready for query
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: Query: BEGIN
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: len:a000000
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: transaction state: T
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_unset_writing_transaction: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: Query: BEGIN
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: len:a000000
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: transaction state: T
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_unset_writing_transaction: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_unset_skip_reading_from_backends: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: kind: C from 0 th backend
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: kind: C from 1 th backend
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
> 2013-11-10 19:57:32 DEBUG: pid 30188: ProcessBackendResponse: kind from backend: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_set_command_success: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: kind: Z from 0 th backend
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: kind: Z from 1 th backend
> 2013-11-10 19:57:32 DEBUG: pid 30188: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
> 2013-11-10 19:57:32 DEBUG: pid 30188: ProcessBackendResponse: kind from backend: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: Query: COMMIT
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: len:b000000
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: Query: COMMIT
> 2013-11-10 19:57:32 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:57:32 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:57:32 DEBUG: pid 30188: len:b000000
> 2013-11-10 19:57:32 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_read_message_length: slot: 0 length: 5
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_read_message_length: slot: 1 length: 5
> 2013-11-10 19:57:32 DEBUG: pid 30188: ReadyForQuery: transaction state:T
> 2013-11-10 19:57:32 DEBUG: pid 30188: ReadyForQuery: transaction state:I
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_unset_query_in_progress: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: pool_unset_query_in_progress: done
> 2013-11-10 19:57:32 DEBUG: pid 30188: ProcessBackendResponse: Ready For Query
> 2013-11-10 19:57:36 DEBUG: pid 30188: ProcessFrontendResponse: kind from frontend Q(51)
> 2013-11-10 19:57:36 DEBUG: pid 30188: pool_unset_doing_extended_query_message: done
> 2013-11-10 19:57:36 DEBUG: pid 30188: statement2: SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
> 2013-11-10 19:57:36 DEBUG: pid 30188: encode_key: username testdb
> 2013-11-10 19:57:36 DEBUG: pid 30188: encode_key: database_name testdb
> 2013-11-10 19:57:36 DEBUG: pid 30188: encode_key: query SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
> 2013-11-10 19:57:36 DEBUG: pid 30188: encode_key: `testdbSELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;testdb' -> `df22c47532a03d5740cc31b64d683915'
> 2013-11-10 19:57:36 DEBUG: pid 30188: pool_fetch_cache: search key ==df22c47532a03d5740cc31b64d683915==
> 2013-11-10 19:57:36 DEBUG: pid 30188: pool_fetch_cache: query=SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ; len:71
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_cached_messages: T len: 44
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_message: kind=T, len=44, data=0xa93fd5
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_cached_messages: D len: 11
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_message: kind=D, len=11, data=0xa94002
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_cached_messages: C len: 13
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_message: kind=C, len=13, data=0xa9400e
> 2013-11-10 19:57:36 DEBUG: pid 30188: send_message: kind=Z, len=5, data=0x7fff4b4be45f
> 2013-11-10 19:57:36 DEBUG: pid 30188: pool_fetch_from_memory_cache: a query result found in the query cache, SELECT password_miss_count FROM bookstore.users WHERE username = 'テストユーザー' ;
> 
> 
> [(参考)同じDBでpublicスキーマのテーブルを更新したときのログ]
> 2013-11-10 19:56:19 DEBUG: pid 30188: statement2: UPDATE myuser SET password_miss_count = 0 WHERE uid = 'テストユーザー' ;
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_set_query_in_progress: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_extract_table_oids: table:"myuser" oid:16447
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: Query: BEGIN
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: len:a000000
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: transaction state: T
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_unset_writing_transaction: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: Query: BEGIN
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: len:a000000
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: transaction state: T
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_unset_writing_transaction: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_unset_skip_reading_from_backends: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: kind: C from 0 th backend
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: kind: C from 1 th backend
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
> 2013-11-10 19:56:19 DEBUG: pid 30188: ProcessBackendResponse: kind from backend: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_set_command_success: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: kind: Z from 0 th backend
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: kind: Z from 1 th backend
> 2013-11-10 19:56:19 DEBUG: pid 30188: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
> 2013-11-10 19:56:19 DEBUG: pid 30188: ProcessBackendResponse: kind from backend: Z
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: Query: COMMIT
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 1 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: len:b000000
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: Query: COMMIT
> 2013-11-10 19:56:19 DEBUG: pid 30188: wait_for_query_response: waiting for backend 0 completing the query
> 2013-11-10 19:56:19 DEBUG: pid 30188: detect_error: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: C
> 2013-11-10 19:56:19 DEBUG: pid 30188: len:b000000
> 2013-11-10 19:56:19 DEBUG: pid 30188: do_command: kind: Z
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_read_message_length: slot: 0 length: 5
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_read_message_length: slot: 1 length: 5
> 2013-11-10 19:56:19 DEBUG: pid 30188: ReadyForQuery: transaction state:T
> 2013-11-10 19:56:19 DEBUG: pid 30188: ReadyForQuery: transaction state:I
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_invalidate_query_cache: dboid 16385
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_invalidate_query_cache: deleting cacheid:0 itemid:0
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_delete_item_shmem_cache: cacheid:0 itemid:0
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_reset_memqcache_buffer: discard: 0xa93430
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_discard_query_cache_array: num_caches: 0
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_reset_memqcache_buffer: create: 0xa93430
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_reset_memqcache_buffer: discard temp buffer of 0xa95630 (UPDATE myuser SET password_miss_count = 0 WHERE uid = 'テストユーザー' 
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_unset_query_in_progress: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: pool_unset_query_in_progress: done
> 2013-11-10 19:56:19 DEBUG: pid 30188: ProcessBackendResponse: Ready For Query
> 
> よろしくお願いいたします。
> 
> _______________________________________________
> pgpool-general-jp mailing list
> pgpool-general-jp @ sraoss.jp
> http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp


pgpool-general-jp メーリングリストの案内