> -----Original Message-----
> From: Konstantin Knizhnik [mailto:[email protected]]
> Sent: Friday, January 12, 2018 9:53 PM
> To: Thomas Munro <[email protected]>; Stephen Frost
> <[email protected]>
> Cc: Michael Paquier <[email protected]>; PostgreSQL mailing
> lists <[email protected]>; Tsunakawa, Takayuki/綱川 貴之
> <[email protected]>
> Subject: Re: [HACKERS] Cached plans and statement generalization
>
> Thank you very much for reporting the problem.
> Rebased version of the patch is attached.
Hi Konstantin.
I think that this patch excel very much. Because the customer of our
company has the demand that migrates from other DB to PostgreSQL, and
the problem to have to modify the application program to do prepare in
that case occurs. It is possible to solve it by the problem's using this
patch. I want to be helping this patch to be committed. Will you participate
in the following CF?
To review this patch, I verified it. The verified environment is
PostgreSQL 11beta2. It is necessary to add "executor/spi.h" and "jit/jit.h"
to postgres.c of the patch by the updating of PostgreSQL. Please rebase.
1. I confirmed the influence on the performance by having applied this patch.
The result showed the tendency similar to Konstantin.
-s:100 -c:8 -t: 10000 read-only
simple: 20251 TPS
prepare: 29502 TPS
simple(autoprepare): 28001 TPS
2. I confirmed the influence on the memory utilization by the length of query
that did
autoprepare. Short queries have 1 constant. Long queries have 100 constants.
This result was shown that preparing long query used the memory more.
before prepare：plan cache context: 1032 used
prepare 10 short query statement：plan cache context: 15664 used
prepare 10 long query statement：plan cache context: 558032 used
In this patch, the maximum number of query that can do prepare can be set to
autoprepare_limit.
However, is it good in this? I think that I can assume the scene in the
following.
- Application side user: To elicit the performance, they want to specify the
number of prepared
query.
- Operation side user: To prevent the memory from overflowing, they want to set
the maximum value
of the memory utilization.
Therefore, I propose to add the parameter to specify the maximum memory
utilization.
3. I confirmed the transition of the amount of the memory when it tried to
prepare query
of the number that exceeded the value specified for autoprepare_limit.
[autoprepare_limit=1 and execute 10 different queries]
plan cache context: 1032 used
plan cache context: 39832 used
plan cache context: 78552 used
plan cache context: 117272 used
plan cache context: 155952 used
plan cache context: 194632 used
plan cache context: 233312 used
plan cache context: 272032 used
plan cache context: 310712 used
plan cache context: 349392 used
plan cache context: 388072 used
I feel the doubt in an increase of the memory utilization when I execute a lot
of
query though cached query is one (autoprepare_limit=1).
This behavior is correct?
Best regards, Yamaji
