From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Tharakan, Robins <tharar@amazon.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: track_planning causing performance regression
Date: Mon, 29 Jun 2020 18:56:02 +0900
Message-ID: <3edbb799-965f-b779-300f-27db343f7ca9@oss.nttdata.com> (raw)
In-Reply-To: <CAOBaU_aoz-x0iJfOHCZdXtegiSNG813-Ct1XrxA6hZrQ8boapQ@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: masao.fujii@oss.nttdata.com, rjuju123@gmail.com, tharar@amazon.com
Subject: Re: track_planning causing performance regression
In-Reply-To: <3edbb799-965f-b779-300f-27db343f7ca9@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox