[pgpool-hackers: 2839] Proposal: allow to share query cache by multiple Pgpool-II instances

[Tatsuo Ishii]

Currently, Pgpool-II does not share query cache data among Pgpool-II
servers. If the query cache is on shared memory, without doubt this is
impossible or at least vary hard. However if we use memcached for the
query cache storage, we can already share the cache among Pgpool-II
servers today. The only problem is cache validation. Since Pgpool-II
relies on table oid data stored in the local files, Pgpool-II server
which does not have the file will not invalidate query cache even if
DML is sent from clients and this leads to cache inconsistency state.

To solve the problem, I propose to store the data used for cache
invalidation in the memcached server, rather than local files when
using memcached for the cache storage.

The data used for invalidating query cache in the memcached will be:

Key: database oid (32 bits)+table oid (32 bits)
Data: array of cache key (32 bytes) which use the table in the SELECT query.

Memcached allows up to 1MB of data for each cache entry, so we could
have up to 32768 cache entries for a table specified by the data above.

In addition to above, we need to have index data when drop database
command gets executed. The for the data will be:

Key: database oid (32 bits)
Data: array of table oids (32 bits)

BTW before implementing this, I would like to split the one giant
query related source file (src/query_cache/pool_memqcache.c, about 5k
lines) into multiple smaller files to make implementations and future
maintenance works easier.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp