Make NUM_XLOGINSERT_LOCKS configurable

Started by Nonameabout 2 years ago9 messages
#1Noname
1111hqshj@sina.com

Dear all,

I recently used benchmarksql to evaluate the performance of postgresql. I achieved nearly 20% improvement
with NUM_XLOGINSERT_LOCKS changed from 8 to 16 under some cases of high concurrency. I wonder whether
it is feasible to make NUM_XLOGINSERT_LOCKS a configuration parameter, so that users can get easier to optimize
their postgresql performance through this setting.

Thanks,
Qingsong

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Noname (#1)
Re: Make NUM_XLOGINSERT_LOCKS configurable

<1111hqshj@sina.com> writes:

I recently used benchmarksql to evaluate the performance of postgresql. I achieved nearly 20% improvement
with NUM_XLOGINSERT_LOCKS changed from 8 to 16 under some cases of high concurrency. I wonder whether
it is feasible to make NUM_XLOGINSERT_LOCKS a configuration parameter, so that users can get easier to optimize
their postgresql performance through this setting.

Making it an actual GUC would carry nontrivial costs, not least that
there are hot code paths that do "foo % NUM_XLOGINSERT_LOCKS" which
would go from a mask operation to a full integer divide. We are
unlikely to consider that on the basis of an unsupported assertion
that there's a performance gain under unspecified conditions.

Even with data to justify a change, I think it'd make a lot more sense
to just raise the constant value.

regards, tom lane

#3Michael Paquier
michael@paquier.xyz
In reply to: Tom Lane (#2)
Re: Make NUM_XLOGINSERT_LOCKS configurable

On Tue, Jan 09, 2024 at 09:38:17PM -0500, Tom Lane wrote:

Making it an actual GUC would carry nontrivial costs, not least that
there are hot code paths that do "foo % NUM_XLOGINSERT_LOCKS" which
would go from a mask operation to a full integer divide. We are
unlikely to consider that on the basis of an unsupported assertion
that there's a performance gain under unspecified conditions.

Even with data to justify a change, I think it'd make a lot more sense
to just raise the constant value.

This suggestion has showed up more than once in the past, and WAL
insertion is a path that can become so hot under some workloads that
changing it to a GUC would not be wise from the point of view of
performance. Redesigning all that to not require a set of LWLocks
into something more scalable would lead to better result, whatever
this design may be.
--
Michael

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Paquier (#3)
Re: Make NUM_XLOGINSERT_LOCKS configurable

Michael Paquier <michael@paquier.xyz> writes:

This suggestion has showed up more than once in the past, and WAL
insertion is a path that can become so hot under some workloads that
changing it to a GUC would not be wise from the point of view of
performance. Redesigning all that to not require a set of LWLocks
into something more scalable would lead to better result, whatever
this design may be.

Maybe. I bet just bumping up the constant by 2X or 4X or so would get
most of the win for far less work; it's not like adding a few more
LWLocks is expensive. But we need some evidence about what to set it to.

regards, tom lane

#5Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Tom Lane (#4)
Re: Make NUM_XLOGINSERT_LOCKS configurable

On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Paquier <michael@paquier.xyz> writes:

This suggestion has showed up more than once in the past, and WAL
insertion is a path that can become so hot under some workloads that
changing it to a GUC would not be wise from the point of view of
performance. Redesigning all that to not require a set of LWLocks
into something more scalable would lead to better result, whatever
this design may be.

Maybe. I bet just bumping up the constant by 2X or 4X or so would get
most of the win for far less work; it's not like adding a few more
LWLocks is expensive. But we need some evidence about what to set it to.

I previously made an attempt to improve WAL insertion performance with
varying NUM_XLOGINSERT_LOCKS. IIRC, we will lose what we get by
increasing insertion locks (reduction in WAL insertion lock
acquisition time) to the CPU overhead of flushing the WAL in
WaitXLogInsertionsToFinish as referred to by the following comment.
Unfortunately, I've lost the test results, I'll run them up again and
come back.

/*
* Number of WAL insertion locks to use. A higher value allows more insertions
* to happen concurrently, but adds some CPU overhead to flushing the WAL,
* which needs to iterate all the locks.
*/
#define NUM_XLOGINSERT_LOCKS 8

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bharath Rupireddy (#5)
Re: Make NUM_XLOGINSERT_LOCKS configurable

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:

On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Maybe. I bet just bumping up the constant by 2X or 4X or so would get
most of the win for far less work; it's not like adding a few more
LWLocks is expensive. But we need some evidence about what to set it to.

I previously made an attempt to improve WAL insertion performance with
varying NUM_XLOGINSERT_LOCKS. IIRC, we will lose what we get by
increasing insertion locks (reduction in WAL insertion lock
acquisition time) to the CPU overhead of flushing the WAL in
WaitXLogInsertionsToFinish as referred to by the following comment.

Very interesting --- this is at variance with what the OP said, so
we definitely need details about the test conditions in both cases.

Unfortunately, I've lost the test results, I'll run them up again and
come back.

Please.

regards, tom lane

#7Bharath Rupireddy
bharath.rupireddyforpostgres@gmail.com
In reply to: Tom Lane (#6)
2 attachment(s)
Re: Make NUM_XLOGINSERT_LOCKS configurable

On Wed, Jan 10, 2024 at 11:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:

On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Maybe. I bet just bumping up the constant by 2X or 4X or so would get
most of the win for far less work; it's not like adding a few more
LWLocks is expensive. But we need some evidence about what to set it to.

I previously made an attempt to improve WAL insertion performance with
varying NUM_XLOGINSERT_LOCKS. IIRC, we will lose what we get by
increasing insertion locks (reduction in WAL insertion lock
acquisition time) to the CPU overhead of flushing the WAL in
WaitXLogInsertionsToFinish as referred to by the following comment.

Very interesting --- this is at variance with what the OP said, so
we definitely need details about the test conditions in both cases.

Unfortunately, I've lost the test results, I'll run them up again and
come back.

Please.

Okay, I'm back with some testing.

Test case:
./pgbench --initialize --scale=100 --username=ubuntu postgres
./pgbench --progress=10 --client=64 --time=300 --builtin=tpcb-like
--username=ubuntu postgres

Setup:
./configure --prefix=$PWD/inst/ CFLAGS="-ggdb3 -O3" > install.log &&
make -j 8 install > install.log 2>&1 &

shared_buffers = '8GB'
max_wal_size = '32GB'
track_wal_io_timing = on

Stats measured:
I've used the attached patch to measure WAL Insert Lock Acquire Time
(wal_insert_lock_acquire_time) and WAL Wait for In-progress Inserts
to Finish Time (wal_wait_for_insert_to_finish_time).

Results with varying NUM_XLOGINSERT_LOCKS (note that we can't allow it
be more than MAX_SIMUL_LWLOCKS):
Locks TPS WAL Insert Lock Acquire Time in Milliseconds WAL
Wait for In-progress Inserts to Finish Time in Milliseconds
8 18669 12532 8775
16 18076 10641 13491
32 18034 6635 13997
64 17582 3937 14718
128 17782 4563 20145

Also, check the attached graph. Clearly there's an increase in the
time spent in waiting for in-progress insertions to finish in
WaitXLogInsertionsToFinish from 8.7 seconds to 20 seconds. Whereas,
the time spent to acquire WAL insertion locks decreased from 12.5
seconds to 4.5 seconds. Overall, this hasn't resulted any improvement
in TPS, in fact observed slight reduction.

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Attachments:

v1-0001-WAL-insertion-stats.patchapplication/octet-stream; name=v1-0001-WAL-insertion-stats.patchDownload
From 8765c04adb73d2c7057a5310e3670ec1fefdc0a5 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 12 Jan 2024 06:20:47 +0000
Subject: [PATCH v1] WAL insertion stats

---
 doc/src/sgml/monitoring.sgml            | 33 +++++++++++++++++++++
 src/backend/access/transam/xlog.c       | 39 +++++++++++++++++++++++++
 src/backend/catalog/system_views.sql    |  5 +++-
 src/backend/utils/activity/pgstat_wal.c |  7 +++--
 src/backend/utils/adt/pgstatfuncs.c     | 15 ++++++++--
 src/include/catalog/pg_proc.dat         |  6 ++--
 src/include/pgstat.h                    |  6 ++++
 src/test/regress/expected/rules.out     |  7 +++--
 8 files changed, 107 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b804eb8b5e..61f077b246 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3181,6 +3181,39 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        Time at which these statistics were last reset
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wal_insert_lock_acquire</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times WAL insertion lock was acquired
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wal_insert_lock_acquire_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Total amount of time spent acquiring WAL insertion lock via
+       <function>LWLockAcquire</function> request, in milliseconds
+       (if <varname>track_wal_io_timing</varname> is enabled, otherwise
+       zero).
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wal_wait_for_insert_to_finish_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Total amount of time spent waiting for WAL insertion to finish,
+       in milliseconds (if <varname>track_wal_io_timing</varname> is enabled,
+       otherwise zero).
+      </para></entry>
+     </row>
+
      </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 478377c4a2..b808025fe0 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1364,6 +1364,7 @@ static void
 WALInsertLockAcquire(void)
 {
 	bool		immed;
+	instr_time	start;
 
 	/*
 	 * It doesn't matter which of the WAL insertion locks we acquire, so try
@@ -1382,6 +1383,12 @@ WALInsertLockAcquire(void)
 		lockToTry = MyProc->pgprocno % NUM_XLOGINSERT_LOCKS;
 	MyLockNo = lockToTry;
 
+	/* Measure WAL insertion lock acquire time. */
+	if (track_wal_io_timing)
+		INSTR_TIME_SET_CURRENT(start);
+	else
+		INSTR_TIME_SET_ZERO(start);
+
 	/*
 	 * The insertingAt value is initially set to 0, as we don't know our
 	 * insert location yet.
@@ -1399,6 +1406,20 @@ WALInsertLockAcquire(void)
 		 */
 		lockToTry = (lockToTry + 1) % NUM_XLOGINSERT_LOCKS;
 	}
+
+	/*
+	 * Increment acquire time and number of times WAL insertion lock is
+	 * acquired.
+	 */
+	if (track_wal_io_timing)
+	{
+		instr_time	end;
+
+		INSTR_TIME_SET_CURRENT(end);
+		INSTR_TIME_ACCUM_DIFF(PendingWalStats.wal_insert_lock_acquire_time, end, start);
+	}
+
+	PendingWalStats.wal_insert_lock_acquire++;
 }
 
 /*
@@ -1501,6 +1522,7 @@ WaitXLogInsertionsToFinish(XLogRecPtr upto)
 	XLogRecPtr	finishedUpto;
 	XLogCtlInsert *Insert = &XLogCtl->Insert;
 	int			i;
+	instr_time	start;
 
 	if (MyProc == NULL)
 		elog(PANIC, "cannot wait without a PGPROC structure");
@@ -1537,6 +1559,13 @@ WaitXLogInsertionsToFinish(XLogRecPtr upto)
 	 * out for any insertion that's still in progress.
 	 */
 	finishedUpto = reservedUpto;
+
+	/* Measure wait for WAL insert to finish time. */
+	if (track_wal_io_timing)
+		INSTR_TIME_SET_CURRENT(start);
+	else
+		INSTR_TIME_SET_ZERO(start);
+
 	for (i = 0; i < NUM_XLOGINSERT_LOCKS; i++)
 	{
 		XLogRecPtr	insertingat = InvalidXLogRecPtr;
@@ -1584,6 +1613,16 @@ WaitXLogInsertionsToFinish(XLogRecPtr upto)
 		if (insertingat != InvalidXLogRecPtr && insertingat < finishedUpto)
 			finishedUpto = insertingat;
 	}
+
+	/* Increment wait for WAL insert to finish time. */
+	if (track_wal_io_timing)
+	{
+		instr_time	end;
+
+		INSTR_TIME_SET_CURRENT(end);
+		INSTR_TIME_ACCUM_DIFF(PendingWalStats.wal_wait_for_insert_to_finish_time, end, start);
+	}
+
 	return finishedUpto;
 }
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e43e36f5ac..1acc21a9cd 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1181,7 +1181,10 @@ CREATE VIEW pg_stat_wal AS
         w.wal_sync,
         w.wal_write_time,
         w.wal_sync_time,
-        w.stats_reset
+        w.stats_reset,
+        w.wal_insert_lock_acquire,
+        w.wal_insert_lock_acquire_time,
+        w.wal_wait_for_insert_to_finish_time
     FROM pg_stat_get_wal() w;
 
 CREATE VIEW pg_stat_progress_analyze AS
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 1a3c0e1a66..c6b596fcb1 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -119,6 +119,9 @@ pgstat_flush_wal(bool nowait)
 	WALSTAT_ACC(wal_sync, PendingWalStats);
 	WALSTAT_ACC_INSTR_TIME(wal_write_time);
 	WALSTAT_ACC_INSTR_TIME(wal_sync_time);
+	WALSTAT_ACC(wal_insert_lock_acquire, PendingWalStats);
+	WALSTAT_ACC_INSTR_TIME(wal_insert_lock_acquire_time);
+	WALSTAT_ACC_INSTR_TIME(wal_wait_for_insert_to_finish_time);
 #undef WALSTAT_ACC_INSTR_TIME
 #undef WALSTAT_ACC
 
@@ -158,9 +161,7 @@ pgstat_init_wal(void)
 bool
 pgstat_have_pending_wal(void)
 {
-	return pgWalUsage.wal_records != prevWalUsage.wal_records ||
-		PendingWalStats.wal_write != 0 ||
-		PendingWalStats.wal_sync != 0;
+	return true;
 }
 
 void
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 30a2063505..b320e71c2a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1470,7 +1470,7 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_wal(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_WAL_COLS	9
+#define PG_STAT_GET_WAL_COLS	12
 	TupleDesc	tupdesc;
 	Datum		values[PG_STAT_GET_WAL_COLS] = {0};
 	bool		nulls[PG_STAT_GET_WAL_COLS] = {0};
@@ -1497,7 +1497,12 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
 					   FLOAT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 9, "stats_reset",
 					   TIMESTAMPTZOID, -1, 0);
-
+	TupleDescInitEntry(tupdesc, (AttrNumber) 10, "wal_insert_lock_acquire",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 11, "wal_insert_lock_acquire_time",
+					   FLOAT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 12, "wal_wait_for_insert_to_finish_time",
+					   FLOAT8OID, -1, 0);
 	BlessTupleDesc(tupdesc);
 
 	/* Get statistics about WAL activity */
@@ -1524,6 +1529,12 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
 
 	values[8] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp);
 
+	values[9] = Int64GetDatum(wal_stats->wal_insert_lock_acquire);
+
+	/* Convert counters from microsec to millisec for display */
+	values[10] = Float8GetDatum(((double) wal_stats->wal_insert_lock_acquire_time) / 1000.0);
+	values[11] = Float8GetDatum(((double) wal_stats->wal_wait_for_insert_to_finish_time) / 1000.0);
+
 	/* Returns the record as Datum */
 	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
 }
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58811a6530..a8d562114a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5785,9 +5785,9 @@
 { oid => '1136', descr => 'statistics: information about WAL activity',
   proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{int8,int8,numeric,int8,int8,int8,float8,float8,timestamptz}',
-  proargmodes => '{o,o,o,o,o,o,o,o,o}',
-  proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,wal_write,wal_sync,wal_write_time,wal_sync_time,stats_reset}',
+  proallargtypes => '{int8,int8,numeric,int8,int8,int8,float8,float8,timestamptz,int8,float8,float8}',
+  proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,wal_write,wal_sync,wal_write_time,wal_sync_time,stats_reset,wal_insert_lock_acquire,wal_insert_lock_acquire_time,wal_wait_for_insert_to_finish_time}',
   prosrc => 'pg_stat_get_wal' },
 { oid => '6248', descr => 'statistics: information about WAL prefetching',
   proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 2136239710..ef3179376c 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -439,6 +439,9 @@ typedef struct PgStat_WalStats
 	PgStat_Counter wal_write_time;
 	PgStat_Counter wal_sync_time;
 	TimestampTz stat_reset_timestamp;
+	PgStat_Counter wal_insert_lock_acquire;
+	PgStat_Counter wal_insert_lock_acquire_time;
+	PgStat_Counter wal_wait_for_insert_to_finish_time;
 } PgStat_WalStats;
 
 /*
@@ -454,6 +457,9 @@ typedef struct PgStat_PendingWalStats
 	PgStat_Counter wal_sync;
 	instr_time	wal_write_time;
 	instr_time	wal_sync_time;
+	PgStat_Counter	wal_insert_lock_acquire;
+	instr_time	wal_insert_lock_acquire_time;
+	instr_time	wal_wait_for_insert_to_finish_time;
 } PgStat_PendingWalStats;
 
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index d878a971df..46274f8da9 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2233,8 +2233,11 @@ pg_stat_wal| SELECT wal_records,
     wal_sync,
     wal_write_time,
     wal_sync_time,
-    stats_reset
-   FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset);
+    stats_reset,
+    wal_insert_lock_acquire,
+    wal_insert_lock_acquire_time,
+    wal_wait_for_insert_to_finish_time
+   FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset, wal_insert_lock_acquire, wal_insert_lock_acquire_time, wal_wait_for_insert_to_finish_time);
 pg_stat_wal_receiver| SELECT pid,
     status,
     receive_start_lsn,
-- 
2.34.1

image.pngimage/png; name=image.pngDownload
�PNG


IHDR�!�`s� IDATx^�}p�y�$�$J%!� JeW��H�����bGciF'MO�4(��$E�i���H3:
u��u&i$
����DnjV2�b)�%�^��K"%J|����w���w��=���={��"p�9�����w�=�����k�m,// �@ ������^���:-� �@�^|�E0� �@�@��F� ���#
�@d�8��$@0r �@@�3M ��#�@d�8��$@%����)���Q�n�~����1*|�_��H7�7'�ih��S�k�&�;�_U?M@ �
���p��g;��E��}xh�z&���xx��D���L�qj�n����9+9 �u�@t.�{�Am`����B/�������U>wz�8�@���{����^o��;4
��DA4@ ���@'����i�g�9��^�`
 �	e��X���X�P��3�&��B���O��;����Xhccq�	��N�,�>�����&���;D�R`�����p�ugM��8���g)L�>}��9"�IF��ka�H�iR�%�|&��$�39�xv���g�
��|//�*>jd}�58A���(��%�������?�&8*R�(�<<E�|FE*��gr��p���-@��!N�����-���8��gr�!��1L�?u�E����n|��h��|B�E��1.�2���8��fj-�i*3����p���'�0uH��!m|����q��:��
J�	�N��dB�x�*������������|��
=���ej��)����!�� ��~��K���v��
J&����[@����CI�����>�cV���- �S�\�A�)
o���g���6(�'X�:��	!�q<��3n����2�/�7o-prS���Or���(����C.��$�`Q�d�K&�����"�x��Z|��L<��g<��N�[�������q���L
��L����$��k�K��dB�{���<��y`)���3:Va%!��1L�?u�E����n|��h��|B�E��1.�2���8��fj-�i*3����p���'�0uH��!m|����q��:��
J�	�N��dB�x�*������������|��
=���ej��)����!�� ��~��K���v��
J&����[@����CI�����>�cV���- �S�\�A�)
o���g���6(�'X�:��	!�q<��3n����2�/�7o-prS���Or���(����C.��$�`Q�d�K&�����"�x��Z|��L<��g<��N�[�������q���L
��L����$��k�K��dB�{���<��y`)���3:Va%!��1L�?u�E����n|��h��|*	��x�M9�v���Hw�����a�<�|�5H��Z?���������#Q����dB�
�4��������dA����%���!�w��-t����sg�aK��]�N��l'���Z��Q�D?uz�Y�;N��#�M�2I>w4>��Sn
I&�I  N��H����!���?LW�^-hii����?S��3��aZ���a:Fe�a�{��T��n/;
��((���d����4������x��{aa��2��|��xF5���3�s����H7�5��Ch�\�A�	�!_��@���4��4�mN������t��)Z]]����
������jHg���c�������3m���)�Y�2��@ �b^\\�.^�h�����MMM��}(Q�I+���?����������/�BX��P��wd�FR�5�i�}��14�B>Y\��?Oo��6���[���9^7���V��u+����e�`^�u��A����2=J�q�S`���������bV\�c��L��N	TD��fh|f�@�~>�b���b����]�v��7�h�����-'��������|y��}���nR2o���8��� |wE
{<I��Q��"��H���d����4����\|��>��3�����-�`Y`���S!���N��$��8�X�N�q�%��`�Il$����\[[�s�����g�����WlYt�g��m����$�������dB��G���B6��3�U[}��7���,�.\���f����m���
�J2o!�5����Wi�O^�s�iG�f���;�}�3_��!�Y��v�#��@���y��g�"��������|���{�n����m��gs�g-tk����?�����E~3��l�u�7���7SF��N�<��yb������=YY[]��,��")�w�dCC��~���=�����<�vQ|������#�j�	����l������9Z��]���\��=Y�g���O�|FE*�z�U0��_������������?&p�V��������jY`y52���A����(�g��mccc�(	C���+U�W���C/�^�����rC������o�N?���b
I��_N���g�L �X�[	|�������d�7hE��Y[\^�������W������_���ti��njl���Z^^/�y��-�����;w��?���jG��e?���J�����q�"����YO'�QIq/����/�H���q�����d���������=��y��x'��G��=���\���r����w�TXbAf1n������]-t�
�v�$���	�DHr���[&�8U|MRq&A���q��ER��m�ER�y�����g������	X����;��=���w����kn�q-�]����^���O}���W<��w�Y�|���[��/��d��f����e�������HrH��I����8����n>��#9�")������CZ7�*>qY�qz�����P�y��%�;�D//\����	���L�6l4�F�F,������sQ<2�gW�g,���������N�a�XP)�	����4Q���!
��8��IJ�")�Xu��dTP2V�qB�%��h���/��?�=ZZ.=���>t�.�}���V����o��+>65n�{���?��U�{��E���\��z{kSYy�]������c���i�=w��Q�7�g��Y�8U��Z��'��_����"�MRi�#�G��8_9MC�H��S�H�����tu-xT�^�:A8nj�6�\�t����i,���'��������{�J��]<J�"�=^YX����`oQ�O��{����}�`��fka^����6???_����������58��!���g�f��'���z����#XAGk�mk\���W��=�����tym+-����Z+]Yw���G�������u����g�~P�t$���[p$
�*$�Q#�{��>��'��_]-�k� ?��Ke�p�K2���7�VG����q�U�a�
��m�n���<{���Y�Bo/m&������&��:|��m]����������w�g�WJ�s�{1���r��>8.r��L���h:�8��_���+?i��p���������m�8w�l����;�_�O��g\�������_���K��v���.X�^����i������u'�n��Z���m;o�8=}����������j�q�>@�U�0��dB^���@�d�|�F��������[����6�wI�s��(P������1�S	I>!�y�����	a�����=�\1�OwEr�7I%�H��8U��Sq�A��89��[@���h��S�({�z�$���q�6������YX����	��L���i��8����1i>�]���xX9���my�����yH����$�g������n��:��
�Oux��Hf����wo[�g�[l����|�\C�O�����&�&��8Mb#�/q�d���Z�������m�<����q��!
[�39����n��:��
������7G�[�;�YxyX��|���?$����(U+�	�j 5C�&���?�Q��Yl�9[�'�����4������.�ZZ�L-j2�85i�^��|n�����������;v�(�q���!����5�u�$�����y
���<8���$6������n�6#`k�����MF�mp)�m���+�'�<�kz$�5O��L�My��uJ]��i����utt�����a*WA�*CftI>x�N�$���H7�����x�M�v��[89LC�g�o�i����U�����>}��9�xn�I&�I� Ns��^,���l���^��}�#��\�|���O�gr���8����D^^����N-����9����N[����#�M��;*��G�)7�p�gC����^�x�\����e����J�@j��=�����v��l��j|&GVA�=�z��s/�
�H�������*�����
��R~��������{�=z���m�u{�A����nD����S-������`�tG��-�����'�"���_HC,G���p����=��8p�jo���K���~�w��^n��!���2���QQ�4��Dn �D�WY�O
���4��9�M��Ma��8���D`ee��y���a`~���������b�Voyy�:���)�����7�-�; ���h`kI�x�����0a���#y�iJ��������
������[n�{�a��r%�����s��;������X+N[&�E�&����$�z�^x5M��,�
[������zr�\C2�M�;(N���^,?���v���������l�B�����I�����I�=nd��5��t��>+<md���
�'�s�����Q��GE*���w�>�����P�;W�,��5������{�i���4k��O/6�3i��U_�O6� o ��q�dQPT/%?�������C����-����=��o���y����m���+��n/��j<H��v������#�q�B�qP3��$�`sy�L2!����������������#����N�q�W	���]��\�R|��Wt���e�9X.���x��hYp�B�����-�MMM�*�}��Em��rY��&�3M�����,����	�]���7n[��i�W����`;��k�����f#���8���b����qo��5h�X���T�)�l6v�gr�!��1�m!�0.Y�?Z���^�`�u���[kQP\�&����wo�����\���K�c.d&0����K,I�	����a������;��sj��z�$m�f�������]tI{��j-_j�X�~��%O������8u���-���prcY��s�D�����<�y���L��������/}�\�<Vj[	|KM,��g,��*A��c��C=T���k�����[o���I-�1!��XdB���U��83�8�4� E�$��+�����S�7*�����TmF2!t�������96��9\��|&G�Ce�f���~�����M�U�q����]|M��s�S2�+H�	N�z~� ���������B#�g;%"E8j6�8kB���3Wt�t|���fp
��/�M��eZ�x�Zv���S���k�/�_�����>FqwnB�+�ot�i4=���Oe��� �'�
�,���	Z_z����o�f�����*��_|?��O&zw�dB�t& N��H��L��I�gr6 �U0��?~��_�VQ|���7�N�
|=����E�G�>7��gn���(��Sn
I�	�����hc�r`�~��H	���O���?o?N����6(���J��L	����)�R3�39��*^���i��/�h��O����?���-��������_S����,�q �u�h�
�i:<�:P4��$��*<��:K�&�HKK��6���o�~#���5���
o���#��/���G>B����e�dBhsR�!��D�L�O����
�L"��+/�
]��_�����i���D�{�GK���w|��h�-���7��>�����w�q�w�}���X@�k�3sc�3s
�:>����1I>!�	�e^|d����f[ij��Z��[�������}������dB$�C{U���L
��L���8�L)89�t��FW��{��--m���u�h�6���;� �5�f�	�i\�@4��$�`MDo\�HW�'�[/���[o�[/<Oo\�����b��dB��3�-��A���O��H�
�L���Hi�/�������2��[�9r$3/��9CSSS��a�>��W�ym�^%�����������i���*h�_|������S-��J�	��?/�:y�$---������Y���MW��������h�_,.�J��dB$�Kw]���l���l���:�L�(89��~��tc��]��+�X�{�Y1m=����}���c_��[���2?6tFV�F��)�:#+J�	�@9?�����������{�VX��0����]��k9����O��o���F�	�m&�6(�0>��A��39����=_���g^�vl�^�j�]y���m����
�?����H3��4�
��O
 dB�OpB����s�<�C�<]����.��L��~?�F�	Q��4�G�i�-����8��gr�x�N�$���H7��_89LC�g���i����U��Q��b����ag~�z$����|����)���3*R�('�����x�qj<L���\^8I�Cs�3�O�T(3:B���sW�#p����&���|twwSkkk/�"�=��Z�Vk���C2!"�BA���)6>S;���gr������s/�
�H�������:>w�F	5m���x�#�h�7\P9jm�����v���{��_i���O�9R�|��e~YI>!�1���/�v���~�3�Q���Mm��i������~�4��c�g�b�%���	�H5�)kfF�gf��4>��Zw���0f�?��at��Ejoo�������i�H�/��=��~�.����-���
�q�zSd�	p���ag~�9_��mll��i�:�O����G��}��??f���;�������]5��5!�UI>�5�{M-����N�:E�/_}�����w\zh���Z|�����H��L��ImI&~R�t�G�:�������H�� ���y��<�u
N?q4;�Gc�/:zG��t}�4a��r2��q�-}�-=�5�:I��S����#��\��L|�@�&�����C�,H�� �&A���{_���O}�v���*(��-�7lI[�;l��o'z�t���hL2�c�#Vq�A��a��	�b�J�	�A����������EZ^^������
���o�:��_���/�:��������%��i�R�=�i�}��14��$��*L���&���{444��{��gOf9�����������>f?3�����L|�pB�&�����C�,H�	��4i/,8�y��;w��>�Y��	�/��l
Is����E���^��7	#�i�}��14��$��*L?��Cv��{�����/��&���0�����-��y{x:��d���
�4��������dA�Op��MW��+����L===��HPB\�����#��[��+�7�����L|�pA�&�����C�,H�	����o�I�?��������'<H��v[f9�����'�]����w�������$?3�F�&�����C�,H�	��4�OMM���
555��h��}��G������|����p���k��)	G��4��4��.H�	�HTZ��$DEo��OS�C�Z)%��0�lqJ���m��>��-J�	�dN��JBx{��0��iY?yX������E����Z#�35�SiH�Op*�mD5!�1%^���;��������|B�c���������!���N�!��
��5"�'85�57!xC���-.�����Z�����2n���N��L��Z�i!�N;�|B���Pk+I�{��������[���qB�4N��C���f`|f�`��|B���2�#!x����?�+���N� IDAT����CQ�?�3�<?�#N)t�E�:�������H�	��TJ�t&����	��b��1����3�,���6���P���������|B�k�o��	&�[>� m���2Y5-��t�4���>�����XS�O�����I2!����e�=O�b���ye�����/������ZU=l�S�!�N?��B�I4�>�4����H�	N�N�%�
���k]��_WM�w��o�-��O�O��u���M��m�h���U���3�*�D�&�����K,I�	6�aE$"�~�����[�y�4������#��������+F^<�8�9�hq*fxq�i8A��I�	V$����	Q+>�����"��j`����^w�e��p��=���f���g���@�O�N�R�%�QB�������_-��������yc~�V�#�8���Zq�"fvy�i6?��I�	Ve����	%<�_��"m,9��������I������=���bl��Y���k���3	z����q��#I>!�I����dBD
i����K�+��|�zD���~�K������%���7i���k��=�n��6����X��#-�2!��x$)�8��g^]�i'I<�������J&����b�����?�{�#K����mba:�gR�gR��>��#�7�|B����A}���
���{��7m���u���m��c�����(�[%�1�8�!�8��[|��M�$�� ��4�7FSnd�4:�M<��pr��&�8�t
�D��jC����v��i:r�H���-'�i��C�+�����_��,�,�,��7�Jw~�+-�2k������R~����\���:Z��K'ixh�z&���X��S��u���
�v ��"�M"�����M��j����<w��������X��3*b���c4@#����X��|���8
Jf���pq���oY���z��+1����?b�X����a�w�`������O�=�����E��r��	����3Tm
Y����=������E��L�������R�j����UI>5�7��Ps�,z���UD2!b9$T�'0?�������~A��~p����R�-w�f��Z�S(m27>3�@��|j`���>�94AG���6�Ic�4�X�4^>KMo<K[�z�����+7�=Z�u��s�����v"?��	���l�����]��t��7o�K��`�9+5r�W���W�t8�b���Y
�K�GM�|�S�������x!�u���������G�����<2?���g��&iq&A���������	Z_zOt�5U$u��m?��=RdY�����Fvox��|�;7���~0pT��Q.i�����s��^��4��?c�������Y��a�M��b��"�>�,�V�8���eq�B�;Q���6V�C��w���"]��#�����wYSE/j��v�|b���T�����H|>�����l3���9do�<��x�>{qW��;��7���}��D��0V�U��=+ab��`��,����e��������ho��'u�S�%��`%*�(,�fD�x�V��([B������a��]t\|X�]1fq��[u�(q�M�����/��z�;i�g���Hs%Ni��'N7��[}��@���q��B�HO����������������V~��(���z����OV��^� �����8'x�����u�Ea>�mX�=f��*[1�h�b�7��^ b�=�P��Jq���Zq�:7����T��Fy��`AF+��~s��;�����o���WS�1�a&���so.�����S������e�um��m��G��;�
���gFk��%"�W�k��{a����~���X�3WCg�������vW���W�g��Z�����n����x!�q��sU�
7Hae������N�tC��C��SA����h���M����j6�vY����=�8��!�*LRV2!	�v#�q����4��N�%,q��5����:<g�u���9n\�����W������n�������}"�NJ���y??�b$'8*��L����G������}-K���9���?��+�����r�r�j��H.����o��#��c��Y_)(�'8��"�&��86T��$z��r�����[4'��\��I� o��g^]I>!���]�#����x�g	l��TS|�"fvy���prS���Or���(����C.��$�`Q�d�K&�����"�x��Z|��L<��g<��� ��1L�?u�E����n|��h��|B�E��1.�2���8��fj-�i*3����pC89n�Z@�g
�����vH35>3�_{��|���.y��	!�}�gt��P|����>���X���'�0uH��!m|����q��:��
J�	�N��dB�x�*������������|���[���- �S�\�A�)
o���g���6(�'X�:��	!�q<��3n����2�/�7������$~��ko|j�4S��3S��7.�'z����7(���GoqF�*%�gX��#���UXIprS���Or���(����C.��$�`Q�d�K&�����"�x��Z|��L<��g<��� ��1L�?u�E����n|��h��|B�E��1.�2���8��fj-�i*3����pC89n�Z@�g
�����vH35>3�_{��|���.y��	!�}�gt��P|����>���X��4J�N������A���T�����t���:y,���#k�>�O��G���Y3G�N����L�S'��x�qj����AB��c���8���Rt�gt��P|&g����1�������*8
J�)�<?\E�|FA)?e�gr� ��1L�?u�E����n|��h��|B�E��1.�2���8��fj-�i*3����p���+~���h}}=9*��@�B�������>,�`��K!@  ��9l7;�GcSN����Yq�$@�L0J�3E� �Hp�`�) �.`�@��g:�@ F ��p��I �`�@��g:�@ P�ta�� ��$u��6�I"�KX�/N�js����tX�:[n�[������C�#��;��G�trx�&��[~c����I�q����5)�\����b���~�
]�/�Nc�����4MG����]���>�3A�����z���9F4`��'���{~g�\��|�0Mwct/���S^c����I!qM���{���p�'u
�b��:g�����T���S��!�84C����e�TB���d�������e��.�I�9O�<�7��N�:�&���5	�iv+4�I������"l�H����~�"=^��'���s�.�������|8�k��������*�t���T!��\� �
�]�R/��/��]���s�0����<�v�}�r�K
81�yB����yg�4�io�����5����$pv��e�Bm����M��.,�8���k�F���WO��*��c�	p��B����5)`��d
���p�-�);��E;��}[3���o���������z[�T>�=4�S����--$���+#��Q�?dvN�vV����:�&U���&�_�y)@��#\1gX�'��cva��<����GR�+N����1����}��)w�;,�|�'��1�������_\����Tl�1�@ ��X,@ ��.$a ��`�P ��. ���� �
@��BQ ����Bv�@( VE�@�B�I�@ ��X,@ ��.$a ��`�P ��. ���� �
@��BQ ����Bv�@( VE�@�B�I�@ ��X,@ ��.$��,���!�<�k���FG��D�M�_u
�D����x�������61a ���c�Y����t�<b:K�}3th��:����	��q�;�n	t7������s@ `
`a&����sg.��������6�n���o;Q&��DE �C,-���_����hqy�n��������Cw���X�F�[]]w����F% ��8`E�������_�+�R/��7�P���*pi8Hl!�����4�:���B���/��w�C��U���rC����hv����W
��+(�@�@e��~�-?��5��Z�Bcc���o��O�Un���k���h���h{8z���8���1G ��`!d��x�~�7��._)���6�o���*����::��$�pa �+�R��o�W���tq�a^��t�g�nQ1��@ P�@��I�{k����g�V�}�c�p�0�y@���#@���� �@���%��u���(E@@ � �y`	> �@�!�;J@�pX��@ Pw@���R�y@���#@���� �@���%��u���(E@@ � �y`	> �@�!��������/
N�S�������	�w>���2��;:B�mn��O�*�+�r������W4/"��%ZQJ������Q)������_��^���X6�7��ga��?�t*�����b.z�z����gn(> T.1_�����8:V�Jn�^�����u�n��ZblG��q������/fC�����	_g����M�T9cf�9F4��)����
�h6�q��B���	�c{��;�{�[8I����������� V�1�D<Z�Hz�@,����.�������N+
M�����-�j��b�"v����4
8=-�� �=D3e=��=�^����49�x�5X�]���>�����v:n����^��9K���
��\'�z{y]��o�������	)�h����W��h���qqoj������@/���Q�HuLm���k5�����q��bc�K��g~^��/���V��r����{=9W�<��C4VU	]��:f���mE����^���O`~X���n�]�eQ����v��4PF�Y�
����;?N������R�M;v�����i���J�=�C,���h�
D7p���]�^�BZ{���[c���H�=��ZB*���p�p�a����eC�*=`���^����X��|�����7��� oT�p�/�A�J�;��j�(�S����7>a887�@�	R&����=���Fa��������6�.f�[7���7$������1(?���T��&��������m��|*�i�mEN+��(b#V�n��h���E������/�j�n�xT9�Z���i��_+~S��/\n��(-��u��������9��������u����ykt��R���.�n\G*E���YC���H?�a���U(����K��zjE�<�
�b�A�(�N��g*�$���>|m��w]@P��>�@vn�u��<z��OAb\�;7ySn9s+����a�}�����`Y��8�a6������sq�+"w����5���b�R�h�K���6�~�T�b��sx�f�F�=��C���x��\"���(���T�U,�)\L��%o��s���&����o�m��J\�z�q��C������"���+�noq��J#2����=������0��{�$gD�><C������7q�O|T@���b�����:j��^*ka����y���9\���0�k`<�S!\��:�	�-���=��u�pqH��`�;L:d�����V��.mC��6JC��#eC�n����=`,Ur.��C����ZB�=��=`k&�7�P�S�\�#G<|�0E���y�:d��75����R����f���X0
�|�a���������JC�vj��?���?�U[���GSl�+���-�r��KM��W����~�Q~��W�����'6���.����f������.��U���V��]TK�X��]XV������R�;9�,�	\������=\��4
�e%���L�B9�*�(\�R�\d�a�'�<]�G������|SU\���N`~�o2K{s��w�O�Xs�����(���J@�6��%*�v�5���_�66��c��~o"{��xl%+��.�W ���,���X3�0�y ��|
 ��p��M ����_�)�@  �Y��6�@��G|��@�,�g�:�@�k�5� �@��h �y ��|
 ��p��M ����_�)�@  �Y��6�@��G���������<[�y��-d������~����
�j#����B�-n#���M�s��������M���9����N����z.������|/������Q��n`��Ly�Hy_�R�S��������s�y���-�����7/��Ng���8�l;��!�y���x{q`g�Z��ke����Z��N�!k`wg�*'H�6�C��cD����
��/L�O6�p���S-��JpDC �T���,�q�`���|�a���{!�&:��z|�7�HZ�}���@�NtNd���U�0gkkkt��9��]�v��-[b��Ml�����y�.�
[�F}3AB{�f�f�M���N|��^WT�(���;��P8�k���R���@� ���6a����6�bd�9I���)x/F4>�@�V�fr�q��_�M�����U���i+t�:z�^P�lp;A>uV��B}��������b��
R�f���\�K��8�lv�_k�}{6��L�����r��7��E;J\�^^9nNn����d��`����\���Z|���q���aq9s�����9�Y�]�8F�G�\c���p�������s��!�\'O<^�����5�t���s�8��'��S�9��+��<�8%c\	��
X������c�=F+++v+


�#?�#���^�s2�������+�mV�CUj���w����&���p���F�;����Py����4=6M����K��]����������/���MC��}3b��N�O�{ir�p�����/�^_�8D����J��Syf������!���1��c
_�r�Sg�������'�`��W����L���g(�5����o���������\�'����Z�'��(�c��c1W����Ms���\�S�<pc+&b�yhs�u���������A���vT�v�Ny�"�O=�=�������oll�b\����;����+)�����o2�N�(-���Lt�.��X�
��]��p��"W�����{/�[�q,�A3u���^>_����.���
(����wH�/�^����\��~gi������n4�����fX��~L���_��V���t�;�2J��J�����oP�����+/�9WB�,�%K=��m�)�0^��b���;j� ����L'*���Lvt�c��s�U��_7
W�[. ']1��2X��G}�8��XU���m��s����Jl��z�!�������!�,8n���B�f���s4�^D�6�z�yO��$.���=T���L:���b"{���U�7���&xN���~Y���v~����R�NM��[0�_��W�p�����RIP���
��#��4��He�<�8M�M�xnm�
�kX)]
��0X��W^y�fggiuu���M�6����K��s�b��p�w��}b�k6zJ�*N$�
�\�����Bn=M��v���aKW��D��ZtaraF�!��v|C��[svs���2�*��?4Lu�b4�g�q:��k��/���������C��������IDAT��F�TD��J=`�dB1��z��
�3J<A=���P�T��duvt����-�<(�{�O�T���{���}�t,�T�X��8X����%:u�Tq��m���9�<������04����((��c�E���x��=*�O�m�}��E����v�^�;�J�C��EX�����/�Q6�]�`��Y�^���n��Y�*�)�b-^��N�O���mA=��a�.��,�#�pm?^ZS���j���,`�����i�h�^`����Q-_�xw9
��L/�A��gq�P#�Pm��e��x�"������j�X�k�cD�{��8]��l�TM.C��s�.7����K������rR�Z�g�`A�^}�Uz��'����v+���� ���_�U�F�V��6n�77q���J p� ����t��3kx��������[�y)���z�@ ��X-�@ �	�& a ��
`�P ��& ���� �*@�U�BY ���kf�@� VAe�@hB�	H�@ ��X-�@ �	�& a ��
`�P ��& ���� �*���K/m�����CY �@ �?��@EJ@o�
IEND�B`�
#8Jim Nasby
jim.nasby@gmail.com
In reply to: Bharath Rupireddy (#7)
Re: Make NUM_XLOGINSERT_LOCKS configurable

On 1/12/24 12:32 AM, Bharath Rupireddy wrote:

Test case:
./pgbench --initialize --scale=100 --username=ubuntu postgres
./pgbench --progress=10 --client=64 --time=300 --builtin=tpcb-like
--username=ubuntu postgres

Setup:
./configure --prefix=$PWD/inst/ CFLAGS="-ggdb3 -O3" > install.log &&
make -j 8 install > install.log 2>&1 &

shared_buffers = '8GB'
max_wal_size = '32GB'
track_wal_io_timing = on

Stats measured:
I've used the attached patch to measure WAL Insert Lock Acquire Time
(wal_insert_lock_acquire_time) and WAL Wait for In-progress Inserts
to Finish Time (wal_wait_for_insert_to_finish_time).

Unfortunately this leaves the question of how frequently is
WaitXLogInsertionsToFinish() being called and by whom. One possibility
here is that wal_buffers is too small so backends are constantly having
to write WAL data to free up buffers.
--
Jim Nasby, Data Architect, Austin TX

#9Jakub Wartak
jakub.wartak@enterprisedb.com
In reply to: Bharath Rupireddy (#7)
Re: Make NUM_XLOGINSERT_LOCKS configurable

On Fri, Jan 12, 2024 at 7:33 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

On Wed, Jan 10, 2024 at 11:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> writes:

On Wed, Jan 10, 2024 at 10:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Maybe. I bet just bumping up the constant by 2X or 4X or so would get
most of the win for far less work; it's not like adding a few more
LWLocks is expensive. But we need some evidence about what to set it to.

I previously made an attempt to improve WAL insertion performance with
varying NUM_XLOGINSERT_LOCKS. IIRC, we will lose what we get by
increasing insertion locks (reduction in WAL insertion lock
acquisition time) to the CPU overhead of flushing the WAL in
WaitXLogInsertionsToFinish as referred to by the following comment.

Very interesting --- this is at variance with what the OP said, so
we definitely need details about the test conditions in both cases.

Unfortunately, I've lost the test results, I'll run them up again and
come back.

Please.

Okay, I'm back with some testing

[..]

Results with varying NUM_XLOGINSERT_LOCKS (note that we can't allow it
be more than MAX_SIMUL_LWLOCKS):
Locks TPS WAL Insert Lock Acquire Time in Milliseconds WAL
Wait for In-progress Inserts to Finish Time in Milliseconds
8 18669 12532 8775
16 18076 10641 13491
32 18034 6635 13997
64 17582 3937 14718
128 17782 4563 20145

Also, check the attached graph. Clearly there's an increase in the
time spent in waiting for in-progress insertions to finish in
WaitXLogInsertionsToFinish from 8.7 seconds to 20 seconds. Whereas,
the time spent to acquire WAL insertion locks decreased from 12.5
seconds to 4.5 seconds. Overall, this hasn't resulted any improvement
in TPS, in fact observed slight reduction.

Hi, I've hastily tested using Bharath's patches too as I was thinking
it would be a fast win due to contention, however it seems that (at
least on fast NVMEs?) increasing NUM_XLOGINSERT_LOCKS doesn't seem to
help.

With pgbench -P 5 -c 32 -j 32 -T 30 and
- 64vCPU Lsv2 (AMD EPYC), on single NVMe device (with ext4) that can
do 100k RW IOPS@8kB (with fio/libaio, 4jobs)
- shared_buffers = '8GB', max_wal_size = '32GB', track_wal_io_timing = on
- maxed out wal_buffers = '256MB'

tpcb-like with synchronous_commit=off
TPS wal_insert_lock_acquire_time wal_wait_for_insert_to_finish_time
8 30393 24087 128
32 31205 968 93

tpcb-like with synchronous_commit=on
TPS wal_insert_lock_acquire_time wal_wait_for_insert_to_finish_time
8 12031 8472 10722
32 11957 1188 12563

tpcb-like with synchronous_commit=on and pgbench -c 64 -j 64
TPS wal_insert_lock_acquire_time wal_wait_for_insert_to_finish_time
8 25010 90620 68318
32 25976 18569 85319
// same, Bharath said , it shifted from insert_lock to
waiting_for_insert to finish

insertonly (largeinserts) with synchronous_commit=off (still -c 32 -j 32)
TPS wal_insert_lock_acquire_time wal_wait_for_insert_to_finish_time
8 367 19142 83
32 393 875 68

insertonly (largeinserts) with synchronous_commit=on (still -c 32 -j 32)
TPS wal_insert_lock_acquire_time wal_wait_for_insert_to_finish_time
8 329 15950 125
32 310 2177 316

insertonly was := {
create sequence s1;
create table t (id bigint, t text) partition by hash (id);
create table t_h0 partition of t FOR VALUES WITH (modulus 8, remainder 0);
create table t_h1 partition of t FOR VALUES WITH (modulus 8, remainder 1);
create table t_h2 partition of t FOR VALUES WITH (modulus 8, remainder 2);
create table t_h3 partition of t FOR VALUES WITH (modulus 8, remainder 3);
create table t_h4 partition of t FOR VALUES WITH (modulus 8, remainder 4);
create table t_h5 partition of t FOR VALUES WITH (modulus 8, remainder 5);
create table t_h6 partition of t FOR VALUES WITH (modulus 8, remainder 6);
create table t_h7 partition of t FOR VALUES WITH (modulus 8, remainder 7);

and runtime pgb:
insert into t select nextval('s1'), repeat('A', 1000) from
generate_series(1, 1000);
}

it was truncated every time, DB was checkpointed, of course it was on master.
Without more details from Qingsong it is going to be hard to explain
the boost he witnessed.

-J.