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

[Tatsuo Ishii]

Even with memcached, Pgpool-II takes lock because updating tables will
access "oid files" (containing table oids. The file are used for cache
invalidation).

> Hi,
> 
> Thanks for the fast response.
> 
> Do you think that if i’ll used memcached instead of shmem the performance
> would be better?
> Probably memcached using a better locking mechanism.
> 
> Thanks,
> 
> Avi.
> On Tue, 13 Sep 2022 at 7:18 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi,
>> >
>> > I ran 100 connections concurrently with pooling and query cache enabled,
>> > and I saw a poor performance which indicting  on a very big query waits.
>> >
>> > I found in code that there is a locking mechanism when fetching from
>> shmem.
>> >
>> > Could you please share why you make a critical section when pgpool trying
>> > to read from cache?
>>
>> Because concurrent modifications to the tables could delete query
>> cache, which will confuse process that are reading the cache.
>>
>> 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).
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS LLC
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>