[pgpool-general: 8532] Re: Cache configuration

[Avi Raboah]

Thank you for the clarification.

One more question, is it possible that a query result will store in more
than one entry?

Thanks,

Avi

On Tue, 3 Jan 2023 at 10:43 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230103/868b6cdd/attachment.htm>