From: bttanakahbk <bttanakahbk@oss.nttdata.com>
To: Fujii Masao <masao.fujii@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 16:23:28 +0900
Message-ID: <a84734eb0dd137b11d7b84fc14ed530c@oss.nttdata.com> (raw)
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>
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
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: bttanakahbk@oss.nttdata.com, masao.fujii@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: <a84734eb0dd137b11d7b84fc14ed530c@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