v35-0001-Increase-default-effective_io_concurrency-to-16.patch

From 7b35b1144bddf202fb4d56a9b783751a0945ba0e Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 10 Mar 2025 17:17:38 -0400
Subject: [PATCH v35 1/5] Increase default effective_io_concurrency to 16

The default effective_io_concurrency has been 1 since it was introduced
in b7b8f0b6096d2ab6e. Referencing the associated discussion [1], it
seems 1 was chosen as a conservative value that seemed unlikely to cause
regressions.

Experimentation on high latency cloud storage as well as fast, local
nvme storage (see Discussion link) shows that even slightly higher values
improve query timings substantially. 1 actually performs worse than 0.
With effective_io_concurrency 1, we are not prefetching enough to avoid
I/O stalls, but we are issuing extra syscalls.

Moreover, when bitmap heap scan is converted to using the read stream
API, a prefetch distance of 1 will prevent read combining which is quite
detrimental to performance.

The new default is 16, which should be more appropriate in the general
case while still avoiding flooding low IOPs devices with I/O requests.

[1] https://www.postgresql.org/message-id/flat/FDDBA24E-FF4D-4654-BA75-692B3BA71B97%40enterprisedb.com

Discussion: https://postgr.es/m/CAAKRu_Z%2BJa-mwXebOoOERMMUMvJeRhzTjad4dSThxG0JLXESxw%40mail.gmail.com
---
 doc/src/sgml/config.sgml                      | 38 +++++++++----------
 src/backend/utils/misc/postgresql.conf.sample |  2 +-
 src/include/storage/bufmgr.h                  |  2 +-
 3 files changed, 19 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index d2fa5f7d1a9..8c4409fc8bf 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2577,36 +2577,32 @@ include_dir 'conf.d'
          Sets the number of concurrent disk I/O operations that
          <productname>PostgreSQL</productname> expects can be executed
          simultaneously.  Raising this value will increase the number of I/O
-         operations that any individual <productname>PostgreSQL</productname> session
-         attempts to initiate in parallel.  The allowed range is 1 to 1000,
-         or zero to disable issuance of asynchronous I/O requests. Currently,
-         this setting only affects bitmap heap scans.
+         operations that any individual <productname>PostgreSQL</productname>
+         session attempts to initiate in parallel.  The allowed range is
+         <literal>1</literal> to <literal>1000</literal>, or
+         <literal>0</literal> to disable issuance of asynchronous I/O requests.
+         The default is <literal>16</literal> on supported systems, otherwise
+         <literal>0</literal>. Currently, this setting only affects bitmap heap
+         scans.
         </para>
 
         <para>
-         For magnetic drives, a good starting point for this setting is the
-         number of separate
-         drives comprising a RAID 0 stripe or RAID 1 mirror being used for the
-         database.  (For RAID 5 the parity drive should not be counted.)
-         However, if the database is often busy with multiple queries issued in
-         concurrent sessions, lower values may be sufficient to keep the disk
-         array busy.  A value higher than needed to keep the disks busy will
-         only result in extra CPU overhead.
-         SSDs and other memory-based storage can often process many
-         concurrent requests, so the best value might be in the hundreds.
+         Higher values will have the most impact on higher latency storage
+         where queries otherwise experience noticeable I/O stalls and on
+         devices with high IOPs. Higher values than needed to satisfy the query
+         or keep the device busy can be expected to only introduce extra CPU
+         overhead.
         </para>
 
         <para>
-         Asynchronous I/O requires that the operating system supports issuing
-         read-ahead advice.  If there is no operating system support then
-         setting this parameter to anything but zero will result in an error.
+         On systems without prefetch advice support, attempting to configure
+         any value other than <literal>0</literal> will error out.
         </para>
 
         <para>
-         The default is 1 on supported systems, otherwise 0.  This value can
-         be overridden for tables in a particular tablespace by setting the
-         tablespace parameter of the same name (see
-         <xref linkend="sql-altertablespace"/>).
+         This value can be overridden for tables in a particular tablespace by
+         setting the tablespace parameter of the same name (see <xref
+         linkend="sql-altertablespace"/>).
         </para>
        </listitem>
       </varlistentry>
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 2d1de9c37bd..e2939079088 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -198,7 +198,7 @@
 # - I/O -
 
 #backend_flush_after = 0		# measured in pages, 0 disables
-#effective_io_concurrency = 1		# 1-1000; 0 disables prefetching
+#effective_io_concurrency = 16		# 1-1000; 0 disables prefetching
 #maintenance_io_concurrency = 10	# 1-1000; 0 disables prefetching
 #io_combine_limit = 128kB		# usually 1-32 blocks (depends on OS)
 
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 7c1e4316dde..b204e4731c1 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -152,7 +152,7 @@ extern PGDLLIMPORT bool track_io_timing;
 
 /* only applicable when prefetching is available */
 #ifdef USE_PREFETCH
-#define DEFAULT_EFFECTIVE_IO_CONCURRENCY 1
+#define DEFAULT_EFFECTIVE_IO_CONCURRENCY 16
 #define DEFAULT_MAINTENANCE_IO_CONCURRENCY 10
 #else
 #define DEFAULT_EFFECTIVE_IO_CONCURRENCY 0
-- 
2.34.1