Hi Yamaji,
On 31.07.2018 12:12, Yamaji, Ryo wrote:
-----Original Message-----
From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru]
Sent: Friday, January 12, 2018 9:53 PM
To: Thomas Munro <thomas.mu...@enterprisedb.com>; Stephen Frost
<sfr...@snowman.net>
Cc: Michael Paquier <michael.paqu...@gmail.com>; PostgreSQL mailing
lists <pgsql-hack...@postgresql.org>; Tsunakawa, Takayuki/綱川 貴之
<tsunakawa.ta...@jp.fujitsu.com>
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?
This patch will be included in next release of PgPro EE.
Concerning next commit fest - I am not sure.
At previous commitfest it was returned with feedback that it "has
received no review or comments since last May".
May be your review will help to change this situation.
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.
I agree it may be more useful to limit amount of memory used by prepare
queries, rather than number of prepared statements.
But it is just more difficult to calculate and maintain (I am not sure
that just looking at CacheMemoryContext is enough for it).
Also, if working set of queries (frequently repeated queries) doesn't
fir in memory, then autoprepare will be almost useless (because with
high probability
prepared query will be thrown away from the cache before it can be
reused). So limiting queries from "application side" seems to be more
practical.
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?
I will check it.
