[pgpool-general: 8404] Re: Extended query and cache

[Avi Raboah]

Thank you for the clarification.

On Thu, 15 Sep 2022 at 3:47 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Hi,
> > I am not sure I understood what you mean when you mentioned “close
> message”
> >
> > But i’ll try to ask you in a different way.
> >
> > Let look on the following example:
> >
> > 1. Select * from users where id = $1; using extended query. —> miss
> >
> > 2. Now in case i’ll run the same query i’ll get it back from cache.
> >
> > So I am understanding why the parse message should be pass to the backend
> > and not read the result from cache due to that Parse message didn’t
> > contains the query params. That’s make sense.
> >
> > But in Bind message the packet already contains the query params. So why
> > you can’t read the result from cache here and we need to wait to the
> > execute message?
>
> Bind message does not return the "result". It just returns it succeeded
> (bind complete message) or failed (error response). The actual result
> (which is called "portal") is only in PostgreSQL's memory. So "caching
> result of bind message" is almost meaningless.
>
> > One more question is why we need to send the bind message anyway to the
> > backend in case we have already the result of the execute message in
> cache?
>
> Think about this scenario:
>
> 1. pgpool receives a bind message but finds that the corresponding
> query cache exists. So pgpool does not send the bind message to
> backend.
>
> 2. In other session the table used in the query has been modified and
> the query is gone.
>
> 3. pgpool receives an execute message and tries to extract the query
> cache, but it has already gone. However pgpool cannot send the execute
> message to backend because the result of bind message (portal) does
> not exist in the backend.
>
> > I am asking the above questions because I am looking in the rds
> performance
> > insights.
> > And I can see that in case I am sending a lot of queries concurrently
> when
> > I have cache enabled I can see a very big wait because of a lot of
> > connections in idle ClientRead wait event.
>
> I am not familiar with performance insights and cannot comment on it.
>
> > And I am asking myself why the db needs to know about queries we already
> > have in cache?
> >
> > Hope it was clear.
> >
> >
> > Thanks a lot,
> >
> > Avi
> >
> >
> >
> > On Wed, 14 Sep 2022 at 2:42 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> >> > Hi,
> >> >
> >> > In case query already cached, why in extended query mode the db knows
> >> about
> >> > that query?
> >> > Because in that case pgpool should return the result from the cache in
> >> > order to save db resources.
> >> > But I found that although we have the query in cache pgpool still send
> >> > parse and bind request to the db.
> >> >
> >> > Please share your thoughts 🙏
> >>
> >> Yes, Pgpool-II only caches the result of execute message. The main
> >> reason is, to not return stale cache.  After receiving a close message
> >> for the statement or the portal that is bound to the execute message,
> >> the cache for the execute message should not be returned. When
> >> Pgpool-II receives close messages, they remove the internal record of
> >> previpusly received statement or portal. When an execute message
> >> arrives, Pgpool-II checks whether the record for the execute message
> >> exists. If does not, the request fails. This strategy is simple but
> >> works well.
> >>
> >> I think the saving by caching parse message is not small for
> >> especially complex queries. But in this case users already reuse the
> >> prepared statement anyway.
> >>
> >> I think the saving of bind message is usually small because it does
> >> not involve planning in most cases.
> >>
> >> Best reagards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS LLC
> >> English: http://www.sraoss.co.jp/index_en/
> >> Japanese:http://www.sraoss.co.jp
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220915/0f349d91/attachment.htm>