[pgpool-general: 4581] Exclude 'SELECT some_function()' from query cache

[Abel Martín]

Hi,

We've been successfully running pgpool-II in our organization for a
year and a half. After some tuning we were able to reach a rather
stable setup for our day-to-day operations in our production
environment.

We're currently running version 3.4.2 from PostgreSQL RPM repos for
CentOS 6. We recently decided to give shared memory query cache a try
and are consistently getting a cache hit ratio of 0.63 since then,
which is OK. This is our current cache status:

----------------------------------
 PGpool cache status
----------------------------------
num_cache_hits              | 12114302
num_selects                 | 7229941
cache_hit_ratio             | 0.63
num_hash_entries            | 8388608
used_hash_entries           | 1624
num_cache_entries           | 1624
used_cache_entries_size     | 523990852
free_cache_entries_size     | 10335033
fragment_cache_entries_size | 447107

I read that query cache starts paying at 0.7 hit ratio, but that might
be a bit conservative, so we decided to stick to query cache for the
time being.

After some analysis we discovered that a great amount of these hits
are due to this query:

   SELECT postgis_version();

We'd like to exclude this query from the cache, but the config
parameter black_memqcache_table_list doesn't seem to be useful in this
case, since the data is not queried from a table. Our goal is to see
how many hits return actual data from the tables of our databases.

Regards,
Abel Martin