[pgpool-general: 8531] Re: Cache configuration
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Jan 3 17:43:05 JST 2023
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
More information about the pgpool-general
mailing list