v14-0005-Add-effective_io_readahead_window-setting.patch

From a72c51302f3ca71ab7524bcead0179a6304a677b Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.munro@gmail.com>
Date: Sun, 31 Mar 2024 05:48:20 +1300
Subject: [PATCH v14 5/7] Add effective_io_readahead_window setting.

In a few places we estimate whether the kernel will consider a pattern
of reads to be sequential, for readahead purposes.  If we think so, we
don't issue POSIX_FADV_WILLNEED advice, because we know that it's better
to get out of the kernel's way.

Linux uses a sliding window to detect sequential access, defaulting to
128kB (see blockdev --report).  This setting allows the user to tell
PostgreSQL that size, so we can try to model kernel behavior more
accurately.  Several other known operating systems seem to use a strict
next-block-only detection, so for other systems we just use 0 for now
(it must be exactly sequential), until we have specific information
about other OSes.  It's not a very critical question, because most other
systems either lack or ignore POSIX_FADV_WILLNEED anyway.

XXX Is this a good idea?
XXX This doesn't support units like '128kB' in ALTER TABLE, to be
like the GUC.

Suggested-by: Tomas Vondra <tomas.vondra@enterprisedb.com
Discussion: https://postgr.es/m/f603ac51-a7ff-496a-99c1-76673635692e%40enterprisedb.com
---
 doc/src/sgml/config.sgml                      | 22 +++++++++++++++++++
 doc/src/sgml/ref/alter_tablespace.sgml        |  6 +++--
 src/backend/access/common/reloptions.c        | 10 +++++++++
 src/backend/storage/aio/read_stream.c         | 14 ++++++++++--
 src/backend/storage/buffer/bufmgr.c           |  7 ++++++
 src/backend/utils/cache/spccache.c            | 14 ++++++++++++
 src/backend/utils/misc/guc_tables.c           | 14 ++++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/bin/psql/tab-complete.c                   |  2 +-
 src/include/commands/tablespace.h             |  1 +
 src/include/storage/bufmgr.h                  |  7 ++++++
 src/include/utils/spccache.h                  |  1 +
 12 files changed, 94 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 241a6079688..fe718b8228e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2734,6 +2734,28 @@ include_dir 'conf.d'
        </listitem>
       </varlistentry>
 
+      <varlistentry id="guc-effective-io-readahead-window" xreflabel="effective_io_readahead_window">
+       <term><varname>effective_io_readahead_window</varname> (<type>integer</type>)
+       <indexterm>
+        <primary><varname>effective_io_readahead_window</varname> configuration parameter</primary>
+       </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Sets the size of the window that <productname>PostgreSQL</productname>
+         expects the OS to use to detect sequential reads.  This is used to
+         make decisions about when to issue hints to the kernel on future
+         random reads.  Does not apply when direct I/O is used.
+        </para>
+        <para>
+         The default is 128kB on Linux, and 0 otherwise.  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>
+
       <varlistentry id="guc-max-worker-processes" xreflabel="max_worker_processes">
        <term><varname>max_worker_processes</varname> (<type>integer</type>)
        <indexterm>
diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml
index faf0c6e7fbc..25dbbea66ac 100644
--- a/doc/src/sgml/ref/alter_tablespace.sgml
+++ b/doc/src/sgml/ref/alter_tablespace.sgml
@@ -85,7 +85,8 @@ ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="par
       A tablespace parameter to be set or reset.  Currently, the only
       available parameters are <varname>seq_page_cost</varname>,
       <varname>random_page_cost</varname>, <varname>effective_io_concurrency</varname>,
-      <varname>maintenance_io_concurrency</varname> and <varname>io_combine_limit</varname>.
+      <varname>maintenance_io_concurrency</varname>,
+      <varname>effective_io_readahead_window</varname> and <varname>io_combine_limit</varname>.
       Setting these values for a particular tablespace will override the
       planner's usual estimate of the cost of reading pages from tables in
       that tablespace, and the executor's prefetching and I/O sizing behavior, as established
@@ -94,7 +95,8 @@ ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="par
       <xref linkend="guc-random-page-cost"/>,
       <xref linkend="guc-effective-io-concurrency"/>,
       <xref linkend="guc-maintenance-io-concurrency"/>),
-      <xref linkend="guc-io-combine-limit"/>).  This may be useful if
+      <xref linkend="guc-io-combine-limit"/>),
+      <xref linkend="guc-effective-io-readahead-window"/>).  This may be useful if
       one tablespace is located on a disk which is faster or slower than the
       remainder of the I/O subsystem.
      </para>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 573bf1e8606..8ee22723db9 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -381,6 +381,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 1, MAX_IO_COMBINE_LIMIT
 	},
+	{
+		{
+			"effective_io_readahead_window",
+			"Size of the window used by the OS to detect sequential buffered access.",
+			RELOPT_KIND_TABLESPACE,
+			ShareUpdateExclusiveLock
+		},
+		-1, 1, PG_INT16_MAX,
+	},
 	{
 		{
 			"parallel_workers",
@@ -2102,6 +2111,7 @@ tablespace_reloptions(Datum reloptions, bool validate)
 		{"effective_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_concurrency)},
 		{"maintenance_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, maintenance_io_concurrency)},
 		{"io_combine_limit", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, io_combine_limit)},
+		{"effective_io_readahead_window", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_readahead_window)},
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate,
diff --git a/src/backend/storage/aio/read_stream.c b/src/backend/storage/aio/read_stream.c
index 8dd19ed3390..a32177b148c 100644
--- a/src/backend/storage/aio/read_stream.c
+++ b/src/backend/storage/aio/read_stream.c
@@ -115,6 +115,7 @@ struct ReadStream
 	int16		pinned_buffers;
 	int16		distance;
 	int16		io_combine_limit;
+	int16		effective_io_readahead_window;
 	bool		advice_enabled;
 
 	/*
@@ -256,11 +257,15 @@ read_stream_start_pending_read(ReadStream *stream, bool suppress_advice)
 
 	/*
 	 * If advice hasn't been suppressed, this system supports it, and this
-	 * isn't a strictly sequential pattern, then we'll issue advice.
+	 * isn't sequential according to the effective_io_readahead_window
+	 * setting, (which should ideally tell us how the OS detects sequential
+	 * buffered access), then we'll issue advice.
 	 */
 	if (!suppress_advice &&
 		stream->advice_enabled &&
-		stream->pending_read_blocknum != stream->seq_blocknum)
+		!(stream->pending_read_blocknum >= stream->seq_blocknum &&
+		  stream->pending_read_blocknum <= (stream->seq_blocknum +
+											stream->effective_io_readahead_window)))
 		flags = READ_BUFFERS_ISSUE_ADVICE;
 	else
 		flags = 0;
@@ -422,6 +427,7 @@ read_stream_begin_relation(int flags,
 	int16		max_ios;
 	int16		my_io_combine_limit;
 	uint32		max_pinned_buffers;
+	int16		my_effective_io_readahead_window;
 	Oid			tablespace_id;
 
 	/* Make sure our bmr's smgr and persistent are populated. */
@@ -449,6 +455,7 @@ read_stream_begin_relation(int flags,
 		 */
 		max_ios = effective_io_concurrency;
 		my_io_combine_limit = io_combine_limit;
+		my_effective_io_readahead_window = effective_io_readahead_window;
 	}
 	else
 	{
@@ -457,6 +464,8 @@ read_stream_begin_relation(int flags,
 		else
 			max_ios = get_tablespace_io_concurrency(tablespace_id);
 		my_io_combine_limit = get_tablespace_io_combine_limit(tablespace_id);
+		my_effective_io_readahead_window =
+			get_tablespace_io_readahead_window(tablespace_id);
 	}
 	max_ios = Min(max_ios, PG_INT16_MAX);
 
@@ -535,6 +544,7 @@ read_stream_begin_relation(int flags,
 	stream->per_buffer_data_size = per_buffer_data_size;
 	stream->max_pinned_buffers = max_pinned_buffers;
 	stream->queue_size = queue_size;
+	stream->effective_io_readahead_window = my_effective_io_readahead_window;
 	stream->callback = callback;
 	stream->callback_private_data = callback_private_data;
 
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 70b19238b78..8960fb02a01 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -164,6 +164,13 @@ int			maintenance_io_concurrency = DEFAULT_MAINTENANCE_IO_CONCURRENCY;
  */
 int			io_combine_limit = DEFAULT_IO_COMBINE_LIMIT;
 
+/*
+ * In order to detect sequential access in the same way as the OS would, we
+ * need to know the size of the window it uses.  Overridden by the tablespace
+ * setting of the same name.
+ */
+int			effective_io_readahead_window = DEFAULT_EFFECTIVE_IO_READAHEAD_WINDOW;
+
 /*
  * GUC variables about triggering kernel writeback for buffers written; OS
  * dependent defaults are set via the GUC mechanism.
diff --git a/src/backend/utils/cache/spccache.c b/src/backend/utils/cache/spccache.c
index 97664824a4d..b29d7abda67 100644
--- a/src/backend/utils/cache/spccache.c
+++ b/src/backend/utils/cache/spccache.c
@@ -249,3 +249,17 @@ get_tablespace_io_combine_limit(Oid spcid)
 	else
 		return spc->opts->io_combine_limit;
 }
+
+/*
+ * get_tablespace_io_readahead_window
+ */
+int
+get_tablespace_io_readahead_window(Oid spcid)
+{
+	TableSpaceCacheEntry *spc = get_tablespace(spcid);
+
+	if (!spc->opts || spc->opts->effective_io_readahead_window < 0)
+		return effective_io_readahead_window;
+	else
+		return spc->opts->effective_io_readahead_window;
+}
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c12784cbec8..ab5e5f50df9 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3143,6 +3143,20 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"effective_io_readahead_window",
+			PGC_USERSET,
+			RESOURCES_ASYNCHRONOUS,
+			gettext_noop("Size of the window the OS uses to detect sequential buffered file access."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&effective_io_readahead_window,
+		DEFAULT_EFFECTIVE_IO_READAHEAD_WINDOW,
+		0, PG_INT16_MAX,
+		NULL, NULL, NULL
+	},
+
 	{
 		{"backend_flush_after", PGC_USERSET, RESOURCES_ASYNCHRONOUS,
 			gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index baecde28410..41598104b32 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -204,6 +204,7 @@
 #effective_io_concurrency = 1		# 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)
+#effective_io_readahead_window = 16	# 1-32767; expected system readahead window
 #max_worker_processes = 8		# (change requires restart)
 #max_parallel_workers_per_gather = 2	# limited by max_parallel_workers
 #max_parallel_maintenance_workers = 2	# limited by max_parallel_workers
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a4c746cf63a..e5e3b8a0366 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2634,7 +2634,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("ALTER", "TABLESPACE", MatchAny, "SET|RESET", "("))
 		COMPLETE_WITH("seq_page_cost", "random_page_cost",
 					  "effective_io_concurrency", "maintenance_io_concurrency",
-					  "io_combine_limit");
+					  "io_combine_limit", "effective_io_readahead_window");
 
 	/* ALTER TEXT SEARCH */
 	else if (Matches("ALTER", "TEXT", "SEARCH"))
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index c187283a6dd..94cd1a29985 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -44,6 +44,7 @@ typedef struct TableSpaceOpts
 	int			effective_io_concurrency;
 	int			maintenance_io_concurrency;
 	int			io_combine_limit;
+	int			effective_io_readahead_window;
 } TableSpaceOpts;
 
 extern Oid	CreateTableSpace(CreateTableSpaceStmt *stmt);
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 241f68c45e1..d131a0fb9f4 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -138,6 +138,13 @@ extern PGDLLIMPORT int maintenance_io_concurrency;
 #define DEFAULT_IO_COMBINE_LIMIT Min(MAX_IO_COMBINE_LIMIT, (128 * 1024) / BLCKSZ)
 extern PGDLLIMPORT int io_combine_limit;
 
+#ifdef __linux__
+#define DEFAULT_EFFECTIVE_IO_READAHEAD_WINDOW ((128 * 1024) / BLCKSZ)
+#else
+#define DEFAULT_EFFECTIVE_IO_READAHEAD_WINDOW 0
+#endif
+extern PGDLLIMPORT int effective_io_readahead_window;
+
 extern PGDLLIMPORT int checkpoint_flush_after;
 extern PGDLLIMPORT int backend_flush_after;
 extern PGDLLIMPORT int bgwriter_flush_after;
diff --git a/src/include/utils/spccache.h b/src/include/utils/spccache.h
index f8a19764a8f..a9d163400f7 100644
--- a/src/include/utils/spccache.h
+++ b/src/include/utils/spccache.h
@@ -18,5 +18,6 @@ extern void get_tablespace_page_costs(Oid spcid, float8 *spc_random_page_cost,
 extern int	get_tablespace_io_concurrency(Oid spcid);
 extern int	get_tablespace_maintenance_io_concurrency(Oid spcid);
 extern int	get_tablespace_io_combine_limit(Oid spcid);
+extern int	get_tablespace_io_readahead_window(Oid spcid);
 
 #endif							/* SPCCACHE_H */
-- 
2.44.0