[pgpool-general: 8351] Re: Jdbc driver harm cache hit ratio

Tatsuo Ishii ishii at sraoss.co.jp
Wed Aug 3 21:50:27 JST 2022

> Thanks for your email.
> So in case I am using extended query the query will be cached only when the
> sync message received?

Yes. Note that in extended query message, sending sync message at some
point is required. See the PostgreSQL manual. Anyway sending sync
message should be automatically done in the driver level and you don't
need to worry about it.

> If this is the case why I see so much difference in cache hit ratio between
> the drivers?

I think because psychopg2 uses simple query protocol as you said.

> If it’s not the case, do you know what can be done in order to decrease the
> gap?

No. I am not an expert of JDBC. However if you provide pgpool log with
log_client_messages and log_statement enabled while using JDBC, maybe
I can find some hints to fill the gap.

> Thanks,
> Avi
> On Wed, 3 Aug 2022 at 11:27 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> > Hi,
>> >
>> > I ran query load with two different drivers via pgpool with cache
>> enabled.
>> >
>> > 1. With psychopg2 which generates simple query and I got 91% cache hit
>> > ratio.
>> > 2. I ran the same load with jdbc which generates extended query and I got
>> > 0.03% cache hit ratio.
>> >
>> > Any thoughts? Am I missing some configuration maybe?
>> Please a section in the manual:
>> https://www.pgpool.net/docs/latest/en/html/runtime-in-memory-query-cache.html
>> As explained in the doc, JDBC by default operates in extended query
>> mode. In the mode the chance to create query cache tends to be smaller
>> than simple query because query cache will not be created until sync
>> message is sent from frontend.
>> Also you might want to inspect pgpool log with log_client_messages and
>> log_statement enabled to find the timing of sync message being sent
>> from frontend.
>> Best reagards,
>> --
>> Tatsuo Ishii
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp

More information about the pgpool-general mailing list