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
2895b53b033c47ccb22972b589050...@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-8eff8050b...@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
