[pgpool-general: 8406] Re: Query cache in shared memory bed performance

[Tatsuo Ishii]

> Ideally the process reading query cache should only grab a shared
> lock, which will not block other reading process. Unfortunately we do
> not have shared lock facility (we only have exclusive lock).

I have created a proof of concept patch to implement the share lock
using flock(2). Attached is a graph showing the benchmark result. The
Y axis is the number of queries executed within 1 second. The X axis
is the number of concurrent session.

As you can see new implementation (enable query cache (flock,
exclusive lock for update), green line) outperforms existing
implementation using exclusive lock only (red line) as number of
concurrent users increases. Note that this is somewhat extreme case
because the cache hit ratio is 100% (i.e. exactly same query is used
during the benchmark). So the difference will not be that big in the
wild.

The actual query used was:
SELECT repeat('abcdefg', 500);

The blue line is the case when query cache is disabled just for
reference.

The benchmark was performed on a Ubuntu 20 box with 16GB mem, 6 physical
cores, 12 vcores.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: bench.png
Type: image/png
Size: 51126 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220915/07046a9a/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: query-cache.diff
Type: text/x-patch
Size: 7543 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220915/07046a9a/attachment-0001.bin>