Subject: Re: [HACKERS] Cached plans and statement generalization
To: "Yamaji, Ryo" <yamaji.ryo@jp.fujitsu.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: 
 <CA+TgmoZAHc4wqL3HvVmTd87s=bobp_8_7R9H=B_kH6q4GcA=Yg@mail.gmail.com>
 <4bd78cd4-1e64-5294-2eb8-3d061b20288f@postgrespro.ru>
 <CA+TgmoaCAoSrU9TL8t5J3YP9xp1P-0f5vySy74Rd5kPKLNuqfQ@mail.gmail.com>
 <8eed9c23-19ba-5404-7a9e-0584b836b3f3@postgrespro.ru>
 <283dfb96-48c2-1a87-90ab-97ca87b237fe@postgrespro.ru>
 <CAEepm=2rvscv5kQppLsO6SGzEpKD6v+WB7ikO+uGniQXqQcRjQ@mail.gmail.com>
 <b542f0ee-02be-01cc-d8ee-af1a5a1718c3@postgrespro.ru>
 <e6ced412-75ee-b4a8-d61d-68fed2cfe8cc@postgrespro.ru>
 <CAB7nPqS+2ZRcZZGMJOAYON2h_GOL8G2QhjmbZT__pPuZ8feA0A@mail.gmail.com>
 <a47db8e0-16c0-32ef-60b1-6ee3ebac5f3e@postgrespro.ru>
 <20180106225118.GS2416@tamriel.snowman.net>
 <CAEepm=1MyFoM7VS+ed3BS3PWvSz_oU_3_uMESdQCozku1+K1SQ@mail.gmail.com>
 <20638bbb-2744-a4ba-116d-084e9c6520bf@postgrespro.ru>
 <9A6E5062D5D4DB458C80C2B2920BD71D5C2FA8@g01jpexmbkw23>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <e9cc0486-4a64-2fc8-3eb7-58d92f5a0604@postgrespro.ru>
Date: Wed, 1 Aug 2018 00:30:27 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <9A6E5062D5D4DB458C80C2B2920BD71D5C2FA8@g01jpexmbkw23>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Hi Yamaji,


On 31.07.2018 12:12, Yamaji, Ryo wrote:
>> -----Original Message-----
>> From: Konstantin Knizhnik [mailto:k.knizhnik@postgrespro.ru]
>> Sent: Friday, January 12, 2018 9:53 PM
>> To: Thomas Munro <thomas.munro@enterprisedb.com>; Stephen Frost
>> <sfrost@snowman.net>
>> Cc: Michael Paquier <michael.paquier@gmail.com>; PostgreSQL mailing
>> lists <pgsql-hackers@postgresql.org>; Tsunakawa, Takayuki/綱川 貴之
>> <tsunakawa.takay@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.