From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Julien Rouhaud <rjuju123@gmail.com>
To: 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 17:55:28 +0900
Message-ID: <d4a15deb-8a85-b78c-b447-bc5df7c4a926@oss.nttdata.com> (raw)
In-Reply-To: <CAOBaU_ZNAjSQEdWK9ERsv6zqhdRqB_C0dVv97BrKETV4Rv7gig@mail.gmail.com>
References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com>
<CAOBaU_ZNAjSQEdWK9ERsv6zqhdRqB_C0dVv97BrKETV4Rv7gig@mail.gmail.com>
On 2020/06/29 16:05, Julien Rouhaud wrote:
> Hi,
>
> On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins <tharar@amazon.com> wrote:
>>
>> During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows
Thanks for the benchmark!
>> ~45% performance drop [2] at high DB connection counts (when compared with v12.3)
That's bad :(
>>
>> Disabling pg_stat_statements.track_planning (which is 'On' by default)
>> brings the TPS numbers up to v12.3 levels.
>>
>> The inflection point (in this test-case) is 128 Connections, beyond which the
>> TPS numbers are consistently low. Looking at the mailing list [1], this issue
>> didn't surface earlier possibly since the regression is trivial at low connection counts.
>>
>> It would be great if this could be optimized further, or track_planning
>> disabled (by default) so as to not trip users upgrading from v12 with pg_stat_statement
>> enabled (but otherwise not particularly interested in track_planning).
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.
>> These are some details around the above test:
>>
>> pgbench: scale - 100 / threads - 16
>> test-duration - 30s each
>> server - 96 vCPUs / 768GB - r5.24xl (AWS EC2 instance)
>> client - 72 vCPUs / 144GB - c5.18xl (AWS EC2 instance) (co-located with the DB server - Same AZ)
>> v12 - REL_12_STABLE (v12.3)
>> v13Beta1 - REL_13_STABLE (v13Beta1)
>> max_connections = 10000
>> shared_preload_libraries = 'pg_stat_statements'
>> shared_buffers 128MB
>
> I can't reproduce this on my laptop, but I can certainly believe that
> running the same 3 queries using more connections than available cores
> will lead to extra overhead.
>
> I disagree with the conclusion though. It seems to me that if you
> really have this workload that consists in these few queries and want
> to get better performance, you'll anyway use a connection pooler
> and/or use prepared statements, which will make this overhead
> disappear entirely, and will also yield an even bigger performance
> improvement. A quick test using pgbench -M prepared, with
> track_planning enabled, with still way too many connections already
> shows a 25% improvement over the -M simple without track_planning.
I understand your point. But IMO the default setting basically should
be safer value, i.e., off at least until the problem disappears.
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: <d4a15deb-8a85-b78c-b447-bc5df7c4a926@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