Subject: Re: track_planning causing performance regression
To: bttanakahbk <bttanakahbk@oss.nttdata.com>
Cc: Hamid Akhtar <hamid.akhtar@gmail.com>,
 Pavel Stehule <pavel.stehule@gmail.com>, Peter Geoghegan <pg@bowt.ie>,
 Julien Rouhaud <rjuju123@gmail.com>, "Tharakan, Robins" <tharar@amazon.com>,
 pgsql-hackers@postgresql.org
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>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <af5d442d-1abe-a3da-ee5f-437edc770fe7@oss.nttdata.com>
Date: Fri, 11 Sep 2020 23:04:41 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.12.0
MIME-Version: 1.0
In-Reply-To: <a84734eb0dd137b11d7b84fc14ed530c@oss.nttdata.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk


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