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

[Tatsuo Ishii]

> Hi Pgpool-II developers,
> 
>>> 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.
> 
> Attached is the v1 of the patch. The difference from the patch posted
> at pgpool-general is, adding on_proc_exit so that the lock file is
> unlinked upon the pgpool main process exits. If there's no objection,
> I will add this to the master branch.

Done. Thanks.
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp