[pgpool-hackers: 4319] Re: minimal parser and multi-statement query

[Muhammad Usama]

Hi Ishii-San,

On Fri, Apr 14, 2023 at 10:08 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Hi Usama,
>
> I think you are wondering how the patch affects the performance. So I
> did some quick testing using pgbench. In summary, with this commit I
> see 3-4% performance degradation.
>
> pgbench -n -S -p 11000 -c 10 -j 5 -T 300 test
>
> without the patch the result is around 29,000 tps on my laptop while
> with it the result is around 28,000. 28000/29000 = 0.9655.
>
> What do you think?
>

Thanks for conducting the tests.
3-4 % seems quite a significant degradation. So one possible way would bet
retire the
minimal parser and always use the standard parser so that we can save the
extra scanning
needed for the detection of multi-statement queries.

It (removing minimal parser altogether) may be less of a performance
overhead than this. What is your opinion?

Thanks
Best regards
Muhammad Usama


> >>>>> I temporarily turns off the optimization and adds regression test for
> >>>>> that in master branch.
> >>>>>
> >>>>> Hopefully before releasing 4.5 we find a way to determine whether the
> >>>>> query is a multi-statement query or not in less expensive way. And if
> >>>>> it is not a multi-statement query, we can turn on the optimization.
> >>>>
> >>>> Maybe we use this in the PostgreSQL source code:
> >>>>
> >>>> src/fe_utils/psqlscan.c
> >>>
> >>> Attached is the patch to import psqlscan.c (and other necessary
> >>> files).
> >>
> >> Attached is the v2 patch.
> >>
> >> - Never use minimal parser if pgpool is in replication or snapshot
> >>   isolation mode. Before introducing psqlscan, we did that way. I just
> >>   forgot it.
> >>
> >> - unbreak "make dist".
> >
> > I have pushed the v2 patch to master branch.
> >
> https://url.avanan.click/v2/___https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=48da8715bf403965507eef0321c0ab10054ac71c___.YXAzOnBlcmNvbmE6YTpnOjhlZjg1YjBjMjIxOTFkNmIyNmQwZWM3YTBiMGU5OTRjOjY6NmMwMDpjMjdlMWNmMjM4OGNiY2VmZGQzMzZkZTBjYzBmZTZhMWNiMDdiNzVjODhiODRkNmQ2ODM3OWM5YjQzN2MxZDIxOnA6VA
> >
> > Although this could be regarded as a bug fix, I hesitate to back patch
> > to stable branches because the patch size is relatively
> > large. Comments/suggestions?
> >
> > Best reagards,
> > --
> > Tatsuo Ishii
> > SRA OSS LLC
> > English:
> https://url.avanan.click/v2/___http://www.sraoss.co.jp/index_en/___.YXAzOnBlcmNvbmE6YTpnOjhlZjg1YjBjMjIxOTFkNmIyNmQwZWM3YTBiMGU5OTRjOjY6NzllZjowZjU1MGYzNDYxMTkwNmY5YzUxYzEyOGM1YzlkNTE1MDU0ZGM0NDQxZjJjMTEzN2U0ZWYxYmZhNjY1NmY0YWUyOnA6VA
> > Japanese:
> https://url.avanan.click/v2/___http://www.sraoss.co.jp___.YXAzOnBlcmNvbmE6YTpnOjhlZjg1YjBjMjIxOTFkNmIyNmQwZWM3YTBiMGU5OTRjOjY6NzhmMzozNjM2M2E1MGFjMzJiMDQzMTQ2ZTk2ZjNjOTFlZGYwZmU2OWRhY2M1MjIzNjczZDViYWU5Mjk2YWI0YjQ5ZTNiOnA6VA
> > _______________________________________________
> > pgpool-hackers mailing list
> > pgpool-hackers at pgpool.net
> >
> https://url.avanan.click/v2/___http://www.pgpool.net/mailman/listinfo/pgpool-hackers___.YXAzOnBlcmNvbmE6YTpnOjhlZjg1YjBjMjIxOTFkNmIyNmQwZWM3YTBiMGU5OTRjOjY6MWM1ODo3YWRkNWNkZjEzZjY0MTI4ODhiMWQwMmNmMjk3ZmYxNTcxNzIyNWFkNWNjMWQxZWQ5ZWZmNmQ5ZWM2MDVhOTFkOnA6VA
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230414/88b5255e/attachment-0001.htm>