[pgpool-general: 8531] Re: Cache configuration

[Tatsuo Ishii]

Hi,

> Hi,
> 
> I’m a bit confused with the shared memory cache size configuration.
> 
> Can you explain the connections between the following configurations:
> 
> 1. Memqcache_total_size
> 2. Memqcache_max_num_cache
> 3. Memqcache_block_size
> 
> I am not sure how the those configuration works with a correlation to
> entries.
> 
> Let say I want blocksize to 2MB
> And I want 1M entries
> How should I configure the proxy in a correlation to the total size?

That depends on the average query result cache size. You can consult
"show pool_cache" command to know the average cache size. For example,
if you have following "show pool_cache" result:

num_cache_hits              | 0
num_selects                 | 20
cache_hit_ratio             | 0.00
num_hash_entries            | 1048576
used_hash_entries           | 22
num_cache_entries           | 22
used_cache_entries_size     | 2937
free_cache_entries_size     | 67105927
fragment_cache_entries_size | 0

the average cache size = used_cache_entries_size/num_cache_entries = 2937/22 = 133.5 (Bytes).
Thus you will need memqcache_total_size = 133.5 * 1M = 139984896 Bytes = 133.5MB.

> And can you explain more about the calculation for the num entries with the
> multiplication of 48.

Each cache has "cache header" and "cache body". The cache header is 48
bytes long describing the cache body content, and includes md5 hash
query string. When a query is issued, pgpool searches the cache using
the md5 hash calculated using the query string. See
src/include/query_cache/pool_memqcache.h for more details.

BTW, while checking the source code to verify the calculation, I
realized that "48" is not actually correct. We also have a hash table
to manage the cache entries. For this we need 12 bytes more for each
cache entry. So the correct calculation constant is 64, not 48. I will
fix the docs soon.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp