Provided by: pgpool2_4.3.7-1ubuntu4_amd64 
NAME
SHOW_POOL_CACHE - displays cache storage statistics
SYNOPSIS
SHOW POOL_CACHE
DESCRIPTION
SHOW POOL_CACHE displays in memory query cache statistics if in memory query cache is
enabled. Here is an example session: See Table 26, “Items displayed in show pool_cache”
for a description of each item.
test=# \x
\x
Expanded display is on.
test=# show pool_cache;
show pool_cache;
-[ RECORD 1 ]---------------+---------
num_cache_hits | 891703
num_selects | 99995
cache_hit_ratio | 0.90
num_hash_entries | 131072
used_hash_entries | 99992
num_cache_entries | 99992
used_cache_entries_size | 12482600
free_cache_entries_size | 54626264
fragment_cache_entries_size | 0
Note
If the cache storage is memcached, values for all columns except num_cache_hits,
num_selects and cache_hit_ratio show 0.
Table 26. Items displayed in show pool_cache
┌────────────────────────────┬──────────────────────────────────────────────┐
│Name │ Description │
├────────────────────────────┼──────────────────────────────────────────────┤
│num_cache_hits │ The number of hits against the │
│ │ query cache. │
├────────────────────────────┼──────────────────────────────────────────────┤
│num_selects │ The number of SELECT that did │
│ │ not hit against the query cache. │
├────────────────────────────┼──────────────────────────────────────────────┤
│cache_hit_ratio │ The cache hit ratio. │
│ │ Calculated as │
│ │ num_cache_hits/(num_cache_hits+num_selects). │
├────────────────────────────┼──────────────────────────────────────────────┤
│num_hash_entries │ The number of entries in the hash table used │
│ │ to manage │
│ │ the cache. In order to manage large │
│ │ number of │
│ │ cache Pgpool-II uses the hash table. │
│ │ The number of hash entries is │
│ │ automatically adjusted to the │
│ │ nearest power of two greater │
│ │ than memqcache_max_num_cache. For │
│ │ example, 100,000, which is the │
│ │ default │
│ │ for memqcache_max_num_cache is │
│ │ adjusted to │
│ │ 131,072 (2 to the 17th power). │
├────────────────────────────┼──────────────────────────────────────────────┤
│used_hash_entries │ The number of used hash entries. If the │
│ │ value │
│ │ approaches num_hash_entries, it is │
│ │ recommended to increase │
│ │ num_hash_entries. │
│ │ Even if all the hash table entries │
│ │ are used, no error is │
│ │ raised. However, performance suffers │
│ │ because hash table │
│ │ entries and caches are reused to │
│ │ register new cache entries. │
├────────────────────────────┼──────────────────────────────────────────────┤
│num_cache_entries │ The number of cache entries already used. │
│ │ In the current │
│ │ implementation the number should be │
│ │ identical │
│ │ to used_hash_entries. │
├────────────────────────────┼──────────────────────────────────────────────┤
│free_cache_entries_size │ The size in bytes of the unsed cache. As │
│ │ this value approaches │
│ │ 0, it removes the registered cache │
│ │ and registers a new cache, │
│ │ which does not cause an error, but │
│ │ reduces performance. │
│ │ Consider to increase │
│ │ memqcache_total_size. │
├────────────────────────────┼──────────────────────────────────────────────┤
│fragment_cache_entries_size │ The size in bytes of the fragmented cache. │
│ │ When a registered │
│ │ cache is evicted, the space becomes │
│ │ fragmented until the next │
│ │ time that block is reused. │
│ │ Pgpool-II writes cache in fixed-size │
│ │ blocks specified by │
│ │ memqcache_cache_block_size. When a │
│ │ registered cache is evicted, the space │
│ │ becomes fragmented until the next time │
│ │ that block is reused. │
│ │ fragment_cache_entries_size displays │
│ │ the total size of such fragmented │
│ │ regions. │
└────────────────────────────┴──────────────────────────────────────────────┘