Re: track_planning causing performance regression - Mailing list pgsql-hackers

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