From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: bttanakahbk <bttanakahbk@oss.nttdata.com>
Cc: Hamid Akhtar <hamid.akhtar@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Tharakan, Robins <tharar@amazon.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: track_planning causing performance regression
Date: Fri, 11 Sep 2020 23:04:41 +0900
Message-ID: <af5d442d-1abe-a3da-ee5f-437edc770fe7@oss.nttdata.com> (raw)
In-Reply-To: <a84734eb0dd137b11d7b84fc14ed530c@oss.nttdata.com>
References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com>
<42a13b4c-e60c-c6e7-3475-8eff8050bed4@oss.nttdata.com>
<CAH2-WzmYXtaeCtTEmU_37Eiu5AuRM3etinKFKo=posnLtcxe3g@mail.gmail.com>
<bc8125cb-264e-5a48-7d1f-17b84df831ed@oss.nttdata.com>
<CAFj8pRC9Jxa8r5i0TNBWLb8mzuaYzEoLq3QOvip0jVpHPOLbVA@mail.gmail.com>
<646e5cae-5e8b-feaa-43dc-014bf355bf08@oss.nttdata.com>
<CAFj8pRC-pfdxNhNGTiAACezYfXdpvKbv9n927rjuj9qkMPCFcA@mail.gmail.com>
<73b66e99-b4f3-9c4e-3b70-157ddbe81771@oss.nttdata.com>
<CAFj8pRDT4PDfj69W5TVy-ToWy6gWS+6d9HUxj7G_+F0Rjk2FFA@mail.gmail.com>
<cb6b6bc4-e853-f552-4934-8ef66dca5960@oss.nttdata.com>
<CANugjhsdRAJnGjbUeczo1Z82J1WD+jdoWrNvvxmEX4xgdkgZGw@mail.gmail.com>
<773565c1-6508-063a-8872-20c16d283ee8@oss.nttdata.com>
<CANugjhsQUMZjmHSToOBr-QF7D4v49DtTb3A-kW096niJtLQc0A@mail.gmail.com>
<94a5cda2-b814-d4a8-59d3-b5f00f889e71@oss.nttdata.com>
<bb780fff-c4b0-44aa-b821-cf2a12df41d1@oss.nttdata.com>
<a84734eb0dd137b11d7b84fc14ed530c@oss.nttdata.com>
On 2020/09/11 16:23, bttanakahbk wrote:
> Hi,
>
> On 2020-08-19 00:43, Fujii Masao wrote:
>>> Yes, I pushed the document_overhead_by_track_planning.patch, but this
>>> CF entry is for pgss_lwlock_v1.patch which replaces spinlocks with lwlocks
>>> in pg_stat_statements. The latter patch has not been committed yet.
>>> Probably attachding the different patches in the same thread would cause
>>> this confusing thing... Anyway, thanks for your comment!
>>
>> To avoid further confusion, I attached the rebased version of
>> the patch that was registered at CF. I'd appreciate it if
>> you review this version.
>
> I tested pgss_lwlock_v2.patch with 3 workloads. And I couldn't observe performance
> improvement in our environment and I'm afraid to say that even worser in some case.
> - Workload1: pgbench select-only mode
> - Workload2: pgbench custom scripts which run "SELECT 1;"
> - Workload3: pgbench custom scripts which run 1000 types of different simple queries
Thanks for running the benchmarks!
>
> - Workload1
> First we set the pg_stat_statements.track_planning to on/off and run the fully-cached pgbench
> select-only mode on pg14head which is installed in on-premises server(32CPU, 256GB mem).
> However in this enveronment we couldn't reproduce 45% performance drop due to s_lock conflict
> (Tharakan-san mentioned in his post on 2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com).
>
> - Workload2
> Then we adopted pgbench custom script "SELECT 1;" which supposed to increase the s_lock and
> make it easier to reproduce the issue. In this case around 10% of performance decrease
> which also shows slightly increase in s_lock (~10%). With this senario, despite a s_lock
> absence, the patch shows more than 50% performance degradation regardless of track_planning.
> And also we couldn't see performance improvement in this workload.
>
> pgbench:
> initialization: pgbench -i -s 100
> benchmarking : pgbench -j16 -c128 -T180 -r -n -f <script> -h <address> -U <user> -p <port> -d <db>
> # VACUUMed and pg_prewarmed manually before run the benchmark
> query：SELECT 1;
>> pgss_lwlock_v2.patch track_planning TPS decline rate s_lock CPU usage
>> - OFF 810509.4 standard 0.17% 98.8％（sys24.9%,user73.9%）
>> - ON 732823.1 -9.6% 1.94% 95.1％（sys22.8%,user72.3%）
>> + OFF 371035.0 -49.4% - 65.2％（sys20.6%,user44.6%）
>> + ON 193965.2 -47.7% - 41.8％（sys12.1%,user29.7%）
> # "-" is showing that s_lock was not reported from the perf.
Ok, so my proposed patch degrated the performance in this case :(
This means that replacing spinlock with lwlock in pgss is not proper
approach for the lock contention issue on pgss...
I proposed to split the spinlock for each pgss entry into two
to reduce the lock contention, upthread. One is for planner stats,
and the other is for executor stats. Is it worth working on
this approach as an alternative idea? Or does anyone have any better idea?
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, bttanakahbk@oss.nttdata.com, hamid.akhtar@gmail.com, pavel.stehule@gmail.com, pg@bowt.ie, rjuju123@gmail.com, tharar@amazon.com
Subject: Re: track_planning causing performance regression
In-Reply-To: <af5d442d-1abe-a3da-ee5f-437edc770fe7@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