MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Date: Fri, 11 Sep 2020 16:23:28 +0900
From: bttanakahbk <bttanakahbk@oss.nttdata.com>
To: Fujii Masao <masao.fujii@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
Subject: Re: track_planning causing performance regression
In-Reply-To: <bb780fff-c4b0-44aa-b821-cf2a12df41d1@oss.nttdata.com>
References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com>
 <CAH2-WznCT6rZYGYE_Zxf1oYDZVMyXqm+SWRx+R2b5-DX11pmpQ@mail.gmail.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>
Message-ID: <a84734eb0dd137b11d7b84fc14ed530c@oss.nttdata.com>
User-Agent: Roundcube Webmail/1.1.9
Precedence: bulk

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

- 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.

- Workload3
Next, there is concern that replacement of LWLock may reduce performance 
in some other workloads.
(Fujii-san mentioned in his post on 
42a13b4c-e60c-c6e7-3475-8eff8050bed4@oss.nttdata.com).
To clarify this, we prepared 1000 simple queries which is supposed to 
prevent the conflict of
s_lock and may expect to see the behavior without s_lock. In this case, 
no performance decline
was observed and also we couldn't see any additional memory consumption 
or cpu usage.

pgbench:
  initialization: pgbench -n -i -s 100 --partitions=1000 
--partition-method=range
  benchmarking  : command is same as (Workload1)
query: SELECT abalance FROM pgbench_accounts_xxxx WHERE aid = :aid + 
(10000 * :random_num - 10000);
>   pgss_lwlock_v2.patch  track_planning  TPS       decline rate   CPU 
> usage
>   -                     OFF             88329.1   standard       
> 82.1%（sys6.5%,user75.6%）
>   -                     ON              88015.3   -0.36%         
> 82.6%（sys6.5%,user76.1%）
>   +                     OFF             88177.5    0.18%         
> 82.2%（sys6.5%,user75.7%）
>   +                     ON              88079.1   -0.11%         
> 82.5%（sys6.5%,user76.0%）

(Environment)
machine:
  server/client - 32 CPUs / 256GB  # used same machine as server & client
postgres:
  version: v14 (6eee73e)
  configure: '--prefix=/usr/pgsql-14a' 'CFLAGS=-O2'
GUC param (changed from defaults):
  shared_preload_libraries = 'pg_stat_statements, pg_prewarm'
  autovacuum = off
  checkpoint = 120min
  max_connections=300
  listen_address='*'
  shared_buffers=64GB


Regards,

-- 
Hibiki Tanaka