I've configured pgpool2 (v3.4.3) with postgresql (v9.5) to cache queries like this:
memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_total_size = 67108864
memqcache_max_num_cache = 10000000
memqcache_expire = 600
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
I can confirm that the queries are actually being cached.
The problem is that the cache would not invalidate even after updates, inserts and deletes on the same table. That is what happens:
- Select a row from table - cache is not present - actual select is performed.
- Select the same row - row is retrieved from cache.
- Update the same row - success.
- Select the same row - row is retrieved from cache.
Cache invalidation works as expected in public schema but not in other schemas. I've read this in FAQ:
When I use schema qualified table names, pgpool-II does not invalidate on memory query cache and I got outdated data. Why? It seems you did not install "pgpool_regclass" function. Without the function, pgpool-II ignores the schema name pat of the schema qualified table name and the cache invalidation fails.
I've installed both pgpool_adm, pgpool_recovery and pgpool_regclass extensions both on the application and postgres databases but cache is still not invalidating.
