[pgpool-hackers: 2845] Re: [pgpool-committers: 4826] pgpool: Deal with DROP DATABASE command.

Tatsuo Ishii ishii at sraoss.co.jp
Fri Jun 29 11:29:17 JST 2018


It turned out that the bottle neck was the lock acquisition. Since
there's no lock API provided by memcached, I simulated it by creating
an object in memcached. If someone else already created the object,
pgpool child sleep 100 ms and retries. If I decrease the sleep time to
as small as 1 micro sec, the TPS increased from 100 to 9600. However,
current master branch performs 16000 TPS. So still performance is too
low.  Moreover, I constantly get error from memcached saying that
creating object is too large. In this implementation, I have key =
db_oid/table_oid, data = array of query cache hash. The array could
become huge.

So I tried to change the implementation so that key =
db_oid/table_oid/sequence_number, data = single_query_cache_hash. With
this, I didn't have the error any more. But the TPS is as low as 6000.

Moreover, memcached seems to feel free purge the data. (I checked it
by using memccat). It's fine that the query result cache is
gone. Since in that case we can retrieve fresh data from PostgreSQL
anyway.  However, losing cache invalidating data will lead to cache
consistency problem. So this is not acceptable.

In summary, my challenge to implement cross-pgpool query result cache
seems going into a dead end: less performance and loss of cache
consistency.

Probably time to abandon the attempt.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I have tested this using pgbench -S and the result was disappointing.
> I guess the bottle neck is, large data updating, which is necessary to
> maintain query cache hash vs. table oid list. I will work on improving
> this, but if I still get poor performance, maybe I should abandon
> this.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Deal with DROP DATABASE command.
>> 
>> Now query cache with memcached works with the command.
>> 
>> Branch
>> ------
>> query_cache
>> 
>> Details
>> -------
>> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=f71a67fef4d0c8324a3560c798947fce470cdd36
>> 
>> Modified Files
>> --------------
>> src/include/query_cache/pool_memqcache.h |   2 -
>> src/query_cache/pool_oid_map.c           | 128 ++++++++++++++++++++++++++-----
>> 2 files changed, 107 insertions(+), 23 deletions(-)
>> 
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers


More information about the pgpool-hackers mailing list