Subject: Re: track_planning causing performance regression
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: "Tharakan, Robins" <tharar@amazon.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com>
 <CAOBaU_ZNAjSQEdWK9ERsv6zqhdRqB_C0dVv97BrKETV4Rv7gig@mail.gmail.com>
 <d4a15deb-8a85-b78c-b447-bc5df7c4a926@oss.nttdata.com>
 <CAOBaU_awkSmyBxUXZLfH0DrogqeXd6W8TBmLpF7f--RrxPRy9A@mail.gmail.com>
 <49f15257-2fb8-fd7a-5163-4517e3565e3c@oss.nttdata.com>
 <CAOBaU_aoz-x0iJfOHCZdXtegiSNG813-Ct1XrxA6hZrQ8boapQ@mail.gmail.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <3edbb799-965f-b779-300f-27db343f7ca9@oss.nttdata.com>
Date: Mon, 29 Jun 2020 18:56:02 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CAOBaU_aoz-x0iJfOHCZdXtegiSNG813-Ct1XrxA6hZrQ8boapQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 2020/06/29 18:53, Julien Rouhaud wrote:
> On Mon, Jun 29, 2020 at 11:38 AM Fujii Masao
> <masao.fujii@oss.nttdata.com> wrote:
>>
>>>> Your benchmark result seems to suggest that the cause of the problem is
>>>> the contention of per-query spinlock in pgss_store(). Right?
>>>> This lock contention is likely to happen when multiple sessions run
>>>> the same queries.
>>>>
>>>> One idea to reduce that lock contention is to separate per-query spinlock
>>>> into two; one is for planning, and the other is for execution. pgss_store()
>>>> determines which lock to use based on the given "kind" argument.
>>>> To make this idea work, also every pgss counters like shared_blks_hit
>>>> need to be separated into two, i.e., for planning and execution.
>>>
>>> This can probably remove some overhead, but won't it eventually hit
>>> the same issue when multiple connections try to plan the same query,
>>> given the number of different queries and very low execution runtime?
>>
>> Yes. But maybe we can expect that the idea would improve
>> the performance to the near same level as v12?
> 
> A POC patch should be easy to do and see how much it solves this
> problem.  However I'm not able to reproduce the issue, and IMHO unless
> we specifically want to be able to distinguish planner-time counters
> from execution-time counters, I'd prefer to disable track_planning by
> default than going this way, so that users with a sane usage won't
> have to suffer from a memory increase.

Agreed. +1 to change that default to off.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION