[Pgpool-hackers] patch "Caching query results in pgpool-II"
Tatsuo Ishii
ishii at sraoss.co.jp
Mon Jun 27 23:35:35 UTC 2011
I think the reason why you do not see data in the cache is you did not
cache it. Here is the debug output when I run "select * from t1".
2011-06-28 08:14:06 DEBUG: pid 370: Don't regist buf because a SELECT statement includes non IMMUTABLE function
The message comes from set_cache_on_memcached().
if (!is_select_non_immutable_flag)
{
pool_debug("Don't regist buf because a SELECT statement includes non IMMUTABLE function");
return;
}
If the meaning of the flag "is_select_non_immutable_flag" is "true if
the SELECT statement includes non IMMUTABLE function", the apparently
the test is wrong.
Did you turn on -d while testing your code? If you do so, probably you
will notice lots of funny things and those are valuable info for
debugging. For example, when I run create table, I see those:
2011-06-28 08:32:32 DEBUG: pid 2497: Don't regist buf because a SELECT statement includes non IMMUTABLE function
2011-06-28 08:32:41 DEBUG: pid 2497: memqcache_register: saving buf
2011-06-28 08:32:41 DEBUG: pid 2497: add_buf: len=1, total=1 bufsize=10241
2011-06-28 08:32:41 DEBUG: pid 2497: add_buf: len=4, total=5 bufsize=10245
2011-06-28 08:32:41 DEBUG: pid 2497: add_buf: len=13, total=18 bufsize=10258
I can see your code is trying to cache data even when the query is
CREATE TABLE. This suggests there's someting wrong in your code.
BTW there are suggestions regarding coding style:
1) Add the function name to the debug message. This will make debugging lot easier. i.e.
pool_debug("set_cache_on_memcached: Don't regist buf because a SELECT statement includes non IMMUTABLE function");
2) Don't use those comments. That's not our style and gives no extra
information.
* @param char kind
* @param POOL_CONNECTION frontend
* @param int oid
* @param char *data
* @param int data_len
3) Add more comments. You are beginner of C, and sometimes it's very
hard to guess what you want to from just looking at the code.
2011-06-28 08:14:06 DEBUG: pid 370: I am 370 accept fd 6
2011-06-28 08:14:06 DEBUG: pid 370: read_startup_packet: application_name: psql
2011-06-28 08:14:06 DEBUG: pid 370: Protocol Major: 3 Minor: 0 database: test user: t-ishii
2011-06-28 08:14:06 DEBUG: pid 370: new_connection: connecting 0 backend
2011-06-28 08:14:06 DEBUG: pid 370: pool_ssl: SSL requested but SSL support is not available
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length: slot: 0 length: 8
2011-06-28 08:14:06 DEBUG: pid 370: pool_do_auth: auth kind:0
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 26
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: application_name value: psql
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 25
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: client_encoding value: UTF8
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 23
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: DateStyle value: ISO, MDY
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 25
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: integer_datetimes value: on
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 27
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: IntervalStyle value: postgres
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 20
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: is_superuser value: on
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 25
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: server_encoding value: UTF8
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 25
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: server_version value: 9.0.3
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 34
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: session_authorization value: t-ishii
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 36
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: standard_conforming_strings value: off
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length2: master slot: 0 length: 19
2011-06-28 08:14:06 DEBUG: pid 370: 0 th backend: name: TimeZone value: Japan
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length: slot: 0 length: 12
2011-06-28 08:14:06 DEBUG: pid 370: pool_do_auth: cp->info[i]:0x7f8bb0281000 pid:377
2011-06-28 08:14:06 DEBUG: pid 370: pool_send_auth_ok: send pid 377 to frontend
2011-06-28 08:14:06 DEBUG: pid 370: select_load_balancing_node: selected backend id is 0
2011-06-28 08:14:06 DEBUG: pid 370: selected load balancing node: 0
2011-06-28 08:14:06 DEBUG: pid 370: pool_unset_query_in_progress: done
2011-06-28 08:14:06 DEBUG: pid 370: pool_unset_skip_reading_from_backends: done
2011-06-28 08:14:06 DEBUG: pid 370: pool_unset_ignore_till_sync: done
2011-06-28 08:14:06 DEBUG: pid 370: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 1
2011-06-28 08:14:06 DEBUG: pid 370: ProcessBackendResponse: kind from backend: Z
2011-06-28 08:14:06 DEBUG: pid 370: Don't regist buf because a SELECT statement includes non IMMUTABLE function
2011-06-28 08:14:06 DEBUG: pid 370: pool_read_message_length: slot: 0 length: 5
2011-06-28 08:14:16 DEBUG: pid 370: ProcessFrontendResponse: kind from frontend Q(51)
2011-06-28 08:14:16 DEBUG: pid 370: pool_unset_doing_extended_query_message: done
2011-06-28 08:14:16 DEBUG: pid 370: statement2: select * from t1;
2011-06-28 08:14:16 DEBUG: pid 370: pool_has_non_immutable_function_call: 0
2011-06-28 08:14:17 DEBUG: pid 370: get_cache_on_memcached: CACHE NOT FOUND
2011-06-28 08:14:17 DEBUG: pid 370: pool_memqcache_lookup: memqcache not found
2011-06-28 08:14:17 DEBUG: pid 370: pool_set_query_in_progress: done
2011-06-28 08:14:17 DEBUG: pid 370: wait_for_query_response: waiting for backend 0 completing the query
2011-06-28 08:14:17 DEBUG: pid 370: read_kind_from_backend: read kind from 0 th backend T NUM_BACKENDS: 1
2011-06-28 08:14:17 DEBUG: pid 370: ProcessBackendResponse: kind from backend: T
2011-06-28 08:14:17 DEBUG: pid 370: memqcache_register: saving buf
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=1, total=1 bufsize=10241
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=4, total=5 bufsize=10245
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=22, total=27 bufsize=10267
2011-06-28 08:14:17 ERROR: pid 370: Could not open file
2011-06-28 08:14:17 DEBUG: pid 370: read_kind_from_backend: read kind from 0 th backend D NUM_BACKENDS: 1
2011-06-28 08:14:17 DEBUG: pid 370: ProcessBackendResponse: kind from backend: D
2011-06-28 08:14:17 DEBUG: pid 370: memqcache_register: saving buf
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=1, total=28 bufsize=10268
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=4, total=32 bufsize=10272
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=7, total=39 bufsize=10279
2011-06-28 08:14:17 DEBUG: pid 370: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 1
2011-06-28 08:14:17 DEBUG: pid 370: ProcessBackendResponse: kind from backend: C
2011-06-28 08:14:17 DEBUG: pid 370: memqcache_register: saving buf
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=1, total=40 bufsize=10280
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=4, total=44 bufsize=10284
2011-06-28 08:14:17 DEBUG: pid 370: add_buf: len=9, total=53 bufsize=10293
2011-06-28 08:14:17 DEBUG: pid 370: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 1
2011-06-28 08:14:17 DEBUG: pid 370: ProcessBackendResponse: kind from backend: Z
2011-06-28 08:14:17 DEBUG: pid 370: Don't regist buf because a SELECT statement includes non IMMUTABLE function
2011-06-28 08:14:17 DEBUG: pid 370: pool_read_message_length: slot: 0 length: 5
2011-06-28 08:14:17 DEBUG: pid 370: pool_unset_query_in_progress: done
2011-06-28 08:14:17 DEBUG: pid 370: pool_unset_query_in_progress: done
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Hello
>
> I have implemented memcached's query cache function.
> I am sending the patch file.
>
> It can be compile, but no complete, so all function I am going to implement
> is not included
> because I have some questions and I am at a loss what to do about them now.
> It's so nice to give me some advices.
>
> patch file detail.
>
> [Modify files]
> - Makefile.am
> - Makefile.in
> - pgpool.conf.sample
> - pool_config.h
> - pool_config.l
> - pool_config.c
> - pool_proto_modules.c
> - pool_process_query.c
> - pool_select_walker.c
> - pool_select_walker.h
>
> [New File]
> - pool_memqcache.c
> - pool_memqcache.h
>
> You need to install Memcached or libmemcached(C client library) because
> memcached is used for caching query.
> You have to edit Makefile before "make".
> The way to edit it is the following.
>
> - Makefile
> CFLAGS = -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations
> -I/usr/local/include -lmemcached
>
>
> What bothers me are the following.
>
> * First, I don't understand the way to edit configure.in for each external
> software(Memcached).
> Although I appended "AC_ARG_WITH", "AC_SUBST" in configure.in, there is a
> warning message
> when I execute "./configure --with-memcached=/usr/local/include".
>
> [configure.in]
> AC_ARG_WITH(memcached,
> [--with-memcache=DIR : site library files for Memcached in DIR],
> [
> case "$withval" in
> "" | y | ye | yes | n | no)
> AC_MSG_ERROR([*** You must supply an argument to the --with-memcache
> option.])
> ;;
> esac
> MEMCACHED_DIR="$withval"
> ])
>
> AC_SUBST(MEMCACHED_DIR)
>
>
> bash-3.2$ ./configure --with-memcached=/usr/local/include/
> configure: WARNING: unrecognized options: --with-memcached
> ......
>
>
> * Next, I don't know about the way to fetch SELECT results in DataRow packet
> or tableoid in RowDescription packet.
> I try to GDB debug, but I cannot see in DataRow packet.
> There are functions of getting SELECT results or tableoid in
> memqcache_register(pool_memqcache.c).
> I thought that this code execute as I intended, but cannot get SELECT
> results or tableoid.
> What should I do owing to correct them.
>
> ---
> best regards
> Masanori YAMAZAKI
>
>
>
>
>
> 2011年6月20日0:14 Masanori Yamazaki <m.yamazaki23 at gmail.com>:
>
>> pool_memcached.h is included in pool_process_query.c
>> I forgot it.
>>
>>
>>
>> > I got compile errors after applying your patches.
>> > Forgot to include pool_memqcache.h?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> gcc -DHAVE_CONFIG_H -DDEFAULT_CONFIGDIR=\"/usr/local/etc\" -I.
>> -D_GNU_SOURCE -I /usr/local/pgsql/include -g -O2 -Wall
>> -Wmissing-prototypes -Wmissing-declarations -MT pool_process_query.o -MD -MP
>> -MF .deps/pool_process_query.Tpo -c -o pool_process_query.o
>> pool_process_query.c
>>
>> pool_process_query.c:55:28: error: pool_memqcache.h: No such file or
>> directory
>> pool_process_query.c: In function 'pool_process_query':
>> pool_process_query.c:112: warning: implicit declaration of function
>> 'init_buf'
>> pool_process_query.c: In function 'SimpleForwardToFrontend':
>> pool_process_query.c:1173: warning: implicit declaration of function
>> 'memqcache_register'
>> make[2]: *** [pool_process_query.o] Error 1
>> make[2]: Leaving directory `/home/t-ishii/work/pgfoundry/GSoC/pgpool-II'
>> make[1]: *** [all-recursive] Error 1
>> make[1]: Leaving directory `/home/t-ishii/work/pgfoundry/GSoC/pgpool-II'
>> make: *** [all] Error 2
>>
>>
>>
>> 2011年6月19日23:44 Masanori Yamazaki <m.yamazaki23 at gmail.com>:
>>
>> > So where is new document patch?
>>>
>>> I'm sending new document patch.
>>> I described the way to install memcached or libmemcached(c client library)
>>> for memcached users.
>>>
>>>
>>> regards
>>> Masanori YAMAZAKI
>>>
>>>
>>>
>>> 2011/6/19 Tatsuo Ishii <ishii at sraoss.co.jp>
>>>
>>>> Forgot to attach patches.
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese: http://www.sraoss.co.jp
>>>>
>>>> >> 2011/6/14 Tatsuo Ishii <ishii at sraoss.co.jp>
>>>> >>
>>>> >>> Please explain what is implemented and what is not implemented in
>>>> this
>>>> >>> patch.
>>>> >>>
>>>> >>
>>>> >> What is implemented in this patch are as below.
>>>> >> - connect to memcached.
>>>> >> - set RowDescription or DataRow on memcached.
>>>> >> - get RowDescription or DataRow on memcached by using key of
>>>> md5(SELECT
>>>> >> convert).
>>>> >> - delete cache on memcached by using key of md5(SELECT convert). only
>>>> >> module.
>>>> >> - fetch and parse the fellowing data in pgpool.conf.
>>>> >>
>>>> >> memory_cache_enabled
>>>> >> memqcache_method
>>>> >> memqcache_memcached_host
>>>> >> memqcache_memcached_port
>>>> >> memqcache_total_size
>>>> >> memqcache_expire
>>>> >> memqcache_maxcache
>>>> >> memqcache_cache_block_size
>>>> >>
>>>> >> - add data to buffer before set data on memcached.
>>>> >> - get buffer in order to set it on memcached
>>>> >> - initialize buffer
>>>> >>
>>>> >> I am implemented only in Simple Query case.
>>>> >>
>>>> >>
>>>> >> What is not implemented in this patch are as below.
>>>> >> - shmem cache.
>>>> >> structure of managing table oids.
>>>> >> the space management map.
>>>> >> on memory query cache structure.
>>>> >>
>>>> >> - implementation in Extended Query case.
>>>> >>
>>>> >> - cache validation.
>>>> >> The result of SELECT using none IMMUTABLE functions is not cached.
>>>> >
>>>> > I have made a patch to implement a function which checks if a SELECT
>>>> > statement includes non IMMUTABLE function call.
>>>> >
>>>> > extern bool pool_has_non_immutable_function_call(Node *node);
>>>> >
>>>> > See attached patches.
>>>> >
>>>> > TODO:
>>>> >
>>>> > Some statements including:
>>>> >
>>>> > SELECT current_timestamp;
>>>> >
>>>> > is not detected by the function even if it uses IMMUTABLE function
>>>> > because it is translated to a non function form. Obviously we should
>>>> > not cache the result of current_timestamp. What should we do? Probably
>>>> > we have to have a heuristics to detect the particular form?
>>>> >
>>>> >> - remove cache
>>>> >> If cache is full, the least recently used cache is removed.
>>>> >> If a table is dropped or modified, related cache data is deleted.
>>>> >> If a table definition is modified(ALTER TABLE), related cache data is
>>>> >> deleted.
>>>> >> If a schema or database is dropped, related cache data is deleted.
>>>> >>
>>>> >>
>>>> >> This week I am mainly implementing the shmem cache.
>>>> >>
>>>> >> Regards
>>>> >> Masanori YAMAZAKI
>>>> >>
>>>> >>
>>>> >>
>>>> >>
>>>> >>> --
>>>> >>> Tatsuo Ishii
>>>> >>> SRA OSS, Inc. Japan
>>>> >>> English: http://www.sraoss.co.jp/index_en.php
>>>> >>> Japanese: http://www.sraoss.co.jp
>>>> >>>
>>>> >>> > Hello, hackers
>>>> >>> >
>>>> >>> > I have implemented a function caching query on memcached.
>>>> >>> > I am sending the patch file and it's so nice to give me some
>>>> opinions.
>>>> >>> >
>>>> >>> > Main program which I implemented is as below.
>>>> >>> > - Search for cache data on memcached by using md5 which converted
>>>> SELECT
>>>> >>> > into.
>>>> >>> > - Set data on memcached. The key is md5 which converted SELECT
>>>> into.
>>>> >>> > The value is RowDescription, DataRow from Backend.
>>>> >>> >
>>>> >>> > [Modify files]
>>>> >>> > - pool.h
>>>> >>> > - child.c
>>>> >>> > - pool_config.l
>>>> >>> > - pool_config.h
>>>> >>> > - pool_config.c
>>>> >>> > - pool_system.c
>>>> >>> > - pool_proto_modules.c
>>>> >>> > - pool_process_query.c
>>>> >>> >
>>>> >>> > [New File]
>>>> >>> > - pool_memqcache.c
>>>> >>> >
>>>> >>> >
>>>> >>> > --
>>>> >>> > Regards
>>>> >>> > Masanori YAMAZAKI
>>>> >>>
>>>>
>>>
>>>
>>>
>>> --
>>> best regards
>>> Masanori YAMAZAKI
>>> email: m.yamazaki23 at gmail.com
>>>
>>>
>>
More information about the Pgpool-hackers
mailing list