($INBOX_DIR/description missing)
help / color / mirror / Atom feedRe: Pre-allocating WAL files
17+ messages / 5 participants
[nested] [flat]
* Re: Pre-allocating WAL files
@ 2022-03-01 14:40 Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Justin Pryzby @ 2022-03-01 14:40 UTC (permalink / raw)
To: Maxim Orlov <[email protected]>; +Cc: Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Thu, Dec 30, 2021 at 02:51:10PM +0300, Maxim Orlov wrote:
> I did check the patch too and found it to be ok. Check and check-world are
> passed.
FYI: this is currently failing in cfbot on linux.
https://cirrus-ci.com/task/4934371210690560
https://api.cirrus-ci.com/v1/artifact/task/4934371210690560/log/src/test/regress/regression.diffs
DROP TABLESPACE regress_tblspace_renamed;
+ERROR: tablespace "regress_tblspace_renamed" is not empty
--
Justin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
@ 2022-03-01 15:07 ` Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2022-03-01 15:07 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Tue, Mar 01, 2022 at 08:40:44AM -0600, Justin Pryzby wrote:
> FYI: this is currently failing in cfbot on linux.
>
> https://cirrus-ci.com/task/4934371210690560
> https://api.cirrus-ci.com/v1/artifact/task/4934371210690560/log/src/test/regress/regression.diffs
>
> DROP TABLESPACE regress_tblspace_renamed;
> +ERROR: tablespace "regress_tblspace_renamed" is not empty
I believe this is due to an existing bug. This patch set seems to
influence the timing to make it more likely. I'm tracking the fix here:
https://commitfest.postgresql.org/37/3544/
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2022-03-17 23:12 ` Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2022-03-17 23:12 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
It seems unlikely that this will be committed for v15, so I've adjusted the
commitfest entry to v16 and moved it to the next commitfest.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2022-04-08 20:30 ` Nathan Bossart <[email protected]>
2022-07-14 18:34 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Nathan Bossart @ 2022-04-08 20:30 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Thu, Mar 17, 2022 at 04:12:12PM -0700, Nathan Bossart wrote:
> It seems unlikely that this will be committed for v15, so I've adjusted the
> commitfest entry to v16 and moved it to the next commitfest.
rebased
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v8-0001-Move-WAL-segment-creation-logic-to-its-own-functi.patch (7.4K, ../../20220408203003.GA1630183@nathanxps13/2-v8-0001-Move-WAL-segment-creation-logic-to-its-own-functi.patch)
download | inline diff:
From 3781795f9b4e448df6bdd24d5cd7c0743b5e2944 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Wed, 10 Nov 2021 18:35:14 +0000
Subject: [PATCH v8 1/2] Move WAL segment creation logic to its own function.
---
src/backend/access/transam/xlog.c | 103 +--------------------------
src/backend/storage/file/fd.c | 114 ++++++++++++++++++++++++++++++
src/include/storage/fd.h | 1 +
3 files changed, 116 insertions(+), 102 deletions(-)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index a7814d4019..87d71e2008 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2918,11 +2918,9 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
bool *added, char *path)
{
char tmppath[MAXPGPATH];
- PGAlignedXLogBlock zbuffer;
XLogSegNo installed_segno;
XLogSegNo max_segno;
int fd;
- int save_errno;
Assert(logtli != 0);
@@ -2952,106 +2950,7 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
elog(DEBUG2, "creating and filling new WAL file");
snprintf(tmppath, MAXPGPATH, XLOGDIR "/xlogtemp.%d", (int) getpid());
-
- unlink(tmppath);
-
- /* do not use get_sync_bit() here --- want to fsync only at end of fill */
- fd = BasicOpenFile(tmppath, O_RDWR | O_CREAT | O_EXCL | PG_BINARY);
- if (fd < 0)
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not create file \"%s\": %m", tmppath)));
-
- memset(zbuffer.data, 0, XLOG_BLCKSZ);
-
- pgstat_report_wait_start(WAIT_EVENT_WAL_INIT_WRITE);
- save_errno = 0;
- if (wal_init_zero)
- {
- struct iovec iov[PG_IOV_MAX];
- int blocks;
-
- /*
- * Zero-fill the file. With this setting, we do this the hard way to
- * ensure that all the file space has really been allocated. On
- * platforms that allow "holes" in files, just seeking to the end
- * doesn't allocate intermediate space. This way, we know that we
- * have all the space and (after the fsync below) that all the
- * indirect blocks are down on disk. Therefore, fdatasync(2) or
- * O_DSYNC will be sufficient to sync future writes to the log file.
- */
-
- /* Prepare to write out a lot of copies of our zero buffer at once. */
- for (int i = 0; i < lengthof(iov); ++i)
- {
- iov[i].iov_base = zbuffer.data;
- iov[i].iov_len = XLOG_BLCKSZ;
- }
-
- /* Loop, writing as many blocks as we can for each system call. */
- blocks = wal_segment_size / XLOG_BLCKSZ;
- for (int i = 0; i < blocks;)
- {
- int iovcnt = Min(blocks - i, lengthof(iov));
- off_t offset = i * XLOG_BLCKSZ;
-
- if (pg_pwritev_with_retry(fd, iov, iovcnt, offset) < 0)
- {
- save_errno = errno;
- break;
- }
-
- i += iovcnt;
- }
- }
- else
- {
- /*
- * Otherwise, seeking to the end and writing a solitary byte is
- * enough.
- */
- errno = 0;
- if (pg_pwrite(fd, zbuffer.data, 1, wal_segment_size - 1) != 1)
- {
- /* if write didn't set errno, assume no disk space */
- save_errno = errno ? errno : ENOSPC;
- }
- }
- pgstat_report_wait_end();
-
- if (save_errno)
- {
- /*
- * If we fail to make the file, delete it to release disk space
- */
- unlink(tmppath);
-
- close(fd);
-
- errno = save_errno;
-
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not write to file \"%s\": %m", tmppath)));
- }
-
- pgstat_report_wait_start(WAIT_EVENT_WAL_INIT_SYNC);
- if (pg_fsync(fd) != 0)
- {
- int save_errno = errno;
-
- close(fd);
- errno = save_errno;
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not fsync file \"%s\": %m", tmppath)));
- }
- pgstat_report_wait_end();
-
- if (close(fd) != 0)
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not close file \"%s\": %m", tmppath)));
+ CreateEmptyWalSegment(tmppath);
/*
* Now move the segment into place with its final name. Cope with
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 14b77f2861..4efc46460e 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -3891,3 +3891,117 @@ pg_pwritev_with_retry(int fd, const struct iovec *iov, int iovcnt, off_t offset)
return sum;
}
+
+/*
+ * CreateEmptyWalSegment
+ *
+ * Create a new file that can be used as a new WAL segment. The caller is
+ * responsible for installing the new file in pg_wal.
+ */
+void
+CreateEmptyWalSegment(const char *path)
+{
+ PGAlignedXLogBlock zbuffer;
+ int fd;
+ int save_errno;
+
+ unlink(path);
+
+ /* do not use get_sync_bit() here --- want to fsync only at end of fill */
+ fd = BasicOpenFile(path, O_RDWR | O_CREAT | O_EXCL | PG_BINARY);
+ if (fd < 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not create file \"%s\": %m", path)));
+
+ memset(zbuffer.data, 0, XLOG_BLCKSZ);
+
+ pgstat_report_wait_start(WAIT_EVENT_WAL_INIT_WRITE);
+ save_errno = 0;
+ if (wal_init_zero)
+ {
+ struct iovec iov[PG_IOV_MAX];
+ int blocks;
+
+ /*
+ * Zero-fill the file. With this setting, we do this the hard way to
+ * ensure that all the file space has really been allocated. On
+ * platforms that allow "holes" in files, just seeking to the end
+ * doesn't allocate intermediate space. This way, we know that we
+ * have all the space and (after the fsync below) that all the
+ * indirect blocks are down on disk. Therefore, fdatasync(2) or
+ * O_DSYNC will be sufficient to sync future writes to the log file.
+ */
+
+ /* Prepare to write out a lot of copies of our zero buffer at once. */
+ for (int i = 0; i < lengthof(iov); ++i)
+ {
+ iov[i].iov_base = zbuffer.data;
+ iov[i].iov_len = XLOG_BLCKSZ;
+ }
+
+ /* Loop, writing as many blocks as we can for each system call. */
+ blocks = wal_segment_size / XLOG_BLCKSZ;
+ for (int i = 0; i < blocks;)
+ {
+ int iovcnt = Min(blocks - i, lengthof(iov));
+ off_t offset = i * XLOG_BLCKSZ;
+
+ if (pg_pwritev_with_retry(fd, iov, iovcnt, offset) < 0)
+ {
+ save_errno = errno;
+ break;
+ }
+
+ i += iovcnt;
+ }
+ }
+ else
+ {
+ /*
+ * Otherwise, seeking to the end and writing a solitary byte is
+ * enough.
+ */
+ errno = 0;
+ if (pg_pwrite(fd, zbuffer.data, 1, wal_segment_size - 1) != 1)
+ {
+ /* if write didn't set errno, assume no disk space */
+ save_errno = errno ? errno : ENOSPC;
+ }
+ }
+ pgstat_report_wait_end();
+
+ if (save_errno)
+ {
+ /*
+ * If we fail to make the file, delete it to release disk space
+ */
+ unlink(path);
+
+ close(fd);
+
+ errno = save_errno;
+
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write to file \"%s\": %m", path)));
+ }
+
+ pgstat_report_wait_start(WAIT_EVENT_WAL_INIT_SYNC);
+ if (pg_fsync(fd) != 0)
+ {
+ int save_errno = errno;
+
+ close(fd);
+ errno = save_errno;
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not fsync file \"%s\": %m", path)));
+ }
+ pgstat_report_wait_end();
+
+ if (close(fd) != 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not close file \"%s\": %m", path)));
+}
diff --git a/src/include/storage/fd.h b/src/include/storage/fd.h
index 69549b000f..6bb9e3525b 100644
--- a/src/include/storage/fd.h
+++ b/src/include/storage/fd.h
@@ -190,6 +190,7 @@ extern int durable_unlink(const char *fname, int loglevel);
extern int durable_rename_excl(const char *oldfile, const char *newfile, int loglevel);
extern void SyncDataDirectory(void);
extern int data_sync_elevel(int elevel);
+extern void CreateEmptyWalSegment(const char *path);
/* Filename components */
#define PG_TEMP_FILES_DIR "pgsql_tmp"
--
2.25.1
[text/x-diff] v8-0002-WAL-segment-pre-allocation.patch (24.1K, ../../20220408203003.GA1630183@nathanxps13/3-v8-0002-WAL-segment-pre-allocation.patch)
download | inline diff:
From 6423b98e2f367623791288f1ce2b0d98887eb605 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Fri, 8 Apr 2022 13:27:31 -0700
Subject: [PATCH v8 2/2] WAL segment pre-allocation.
---
doc/src/sgml/config.sgml | 23 ++
src/backend/access/transam/xlog.c | 60 ++++-
src/backend/postmaster/checkpointer.c | 234 ++++++++++++++++++
src/backend/replication/basebackup.c | 6 +-
src/backend/storage/file/fd.c | 22 +-
src/backend/storage/lmgr/lwlocknames.txt | 1 +
src/backend/utils/misc/guc.c | 11 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/initdb/initdb.c | 1 +
src/bin/pg_basebackup/bbstreamer_file.c | 3 +-
src/bin/pg_basebackup/pg_basebackup.c | 13 +
src/bin/pg_basebackup/t/010_pg_basebackup.pl | 4 +-
src/bin/pg_resetwal/pg_resetwal.c | 48 ++++
src/include/postmaster/bgwriter.h | 5 +
src/include/storage/fd.h | 2 +-
15 files changed, 414 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6e3e27bed7..ae55038b6c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3219,6 +3219,29 @@ include_dir 'conf.d'
</listitem>
</varlistentry>
+ <varlistentry id="guc-wal-preallocate-max-size" xreflabel="wal_preallocate_max_size">
+ <term><varname>wal_preallocate_max_size</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>wal_preallocate_max_size</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The maximum amount of WAL to pre-allocate for use when a new WAL segment
+ must be initialized. Setting this parameter to a size less than the WAL
+ segment size disables this behavior. The default value is 64 megabytes
+ (<literal>64MB</literal>). This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+
+ <para>
+ WAL pre-allocation may improve performance in scenarios where new WAL
+ segments must be created (e.g., during checkpoints when WAL segments
+ cannot be recycled).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-wal-buffers" xreflabel="wal_buffers">
<term><varname>wal_buffers</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 87d71e2008..f7fc1c2fb4 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2921,6 +2921,8 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
XLogSegNo installed_segno;
XLogSegNo max_segno;
int fd;
+ int prealloc_segs;
+ bool found_prealloc = false;
Assert(logtli != 0);
@@ -2942,15 +2944,45 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
return fd;
/*
- * Initialize an empty (all zeroes) segment. NOTE: it is possible that
- * another process is doing the same thing. If so, we will end up
- * pre-creating an extra log segment. That seems OK, and better than
- * holding the lock throughout this lengthy process.
+ * Try to use a pre-allocated segment, if one exists. If none are
+ * available, we fall back to creating a new segment on our own.
+ *
+ * Note that we still look for a pre-allocated segment even if the pre-
+ * allocation functionality is disabled via the GUCs. This ensures that any
+ * pre-allocated segments left over after turning off the pre-allocation
+ * functionality are still eligible for use.
*/
- elog(DEBUG2, "creating and filling new WAL file");
+ LWLockAcquire(WALPreallocationLock, LW_EXCLUSIVE);
+ prealloc_segs = GetNumPreallocatedWalSegs();
+ if (prealloc_segs > 0)
+ {
+ elog(DEBUG2, "using pre-allocated WAL file");
- snprintf(tmppath, MAXPGPATH, XLOGDIR "/xlogtemp.%d", (int) getpid());
- CreateEmptyWalSegment(tmppath);
+ found_prealloc = true;
+ prealloc_segs--;
+ SetNumPreallocatedWalSegs(prealloc_segs);
+ snprintf(tmppath, MAXPGPATH, "%s/preallocated_segments/xlogtemp.%d",
+ XLOGDIR, prealloc_segs);
+ }
+ else
+ {
+ /*
+ * We're not using a pre-allocated segment, so there's no need to keep
+ * holding the WALPreallocationLock.
+ */
+ LWLockRelease(WALPreallocationLock);
+
+ /*
+ * Initialize an empty (all zeroes) segment. NOTE: it is possible that
+ * another process is doing the same thing. If so, we will end up
+ * pre-creating an extra log segment. That seems OK, and better than
+ * holding the lock throughout this lengthy process.
+ */
+ elog(DEBUG2, "creating and filling new WAL file");
+
+ snprintf(tmppath, MAXPGPATH, XLOGDIR "/xlogtemp.%d", (int) getpid());
+ CreateEmptyWalSegment(tmppath, ERROR);
+ }
/*
* Now move the segment into place with its final name. Cope with
@@ -2973,7 +3005,7 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
logtli))
{
*added = true;
- elog(DEBUG2, "done creating and filling new WAL file");
+ elog(DEBUG2, "done installing new WAL file");
}
else
{
@@ -2986,6 +3018,18 @@ XLogFileInitInternal(XLogSegNo logsegno, TimeLineID logtli,
elog(DEBUG2, "abandoned new WAL file");
}
+ /*
+ * If we are using a pre-allocated segment, we've been holding onto the
+ * WALPreallocationLock all this time so that the checkpointer process can't
+ * overwrite the file before we've installed it.
+ *
+ * While we're at it, also nudge the checkpointer process so that it pre-
+ * allocates new segments if possible.
+ */
+ if (found_prealloc)
+ LWLockRelease(WALPreallocationLock);
+ RequestWalPreallocation();
+
return -1;
}
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index c937c39f50..78ff4d14b2 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -128,6 +128,9 @@ typedef struct
uint32 num_backend_writes; /* counts user backend buffer writes */
uint32 num_backend_fsync; /* counts user backend fsync calls */
+ bool wal_prealloc_requested; /* protected by ckpt_lck */
+ int num_prealloc_segs; /* protected by WALPreallocationLock */
+
int num_requests; /* current # of requests */
int max_requests; /* allocated array size */
CheckpointerRequest requests[FLEXIBLE_ARRAY_MEMBER];
@@ -144,6 +147,7 @@ static CheckpointerShmemStruct *CheckpointerShmem;
int CheckPointTimeout = 300;
int CheckPointWarning = 30;
double CheckPointCompletionTarget = 0.9;
+int wal_prealloc_max_size_mb = 0;
/*
* Private state
@@ -166,6 +170,10 @@ static bool IsCheckpointOnSchedule(double progress);
static bool ImmediateCheckpointRequested(void);
static bool CompactCheckpointerRequestQueue(void);
static void UpdateSharedMemoryConfig(void);
+static void ScanForExistingPreallocatedSegments(void);
+static bool InstallPreallocatedWalSeg(const char *path);
+static void DoWalPreAllocation(void);
+static int GetMaxPreallocatedWalSegs(void);
/* Signal handlers */
static void ReqCheckpointHandler(SIGNAL_ARGS);
@@ -340,6 +348,11 @@ CheckpointerMain(void)
*/
ProcGlobal->checkpointerLatch = &MyProc->procLatch;
+ /*
+ * Look for any leftover pre-allocated segments we can use.
+ */
+ ScanForExistingPreallocatedSegments();
+
/*
* Loop forever
*/
@@ -360,6 +373,11 @@ CheckpointerMain(void)
AbsorbSyncRequests();
HandleCheckpointerInterrupts();
+ /*
+ * First do WAL pre-allocation.
+ */
+ DoWalPreAllocation();
+
/*
* Detect a pending checkpoint request by checking whether the flags
* word in shared memory is nonzero. We shouldn't need to acquire the
@@ -513,6 +531,12 @@ CheckpointerMain(void)
if (((volatile CheckpointerShmemStruct *) CheckpointerShmem)->ckpt_flags)
continue;
+ /*
+ * Also skip sleeping if WAL pre-allocation has been requested.
+ */
+ if (((volatile CheckpointerShmemStruct *) CheckpointerShmem)->wal_prealloc_requested)
+ continue;
+
/*
* Sleep until we are signaled or it's time for another checkpoint or
* xlog file switch.
@@ -692,6 +716,8 @@ ImmediateCheckpointRequested(void)
*
* 'progress' is an estimate of how much of the work has been done, as a
* fraction between 0.0 meaning none, and 1.0 meaning all done.
+ *
+ * This function also takes care of handling any WAL pre-allocation requests.
*/
void
CheckpointWriteDelay(int flags, double progress)
@@ -702,6 +728,15 @@ CheckpointWriteDelay(int flags, double progress)
if (!AmCheckpointerProcess())
return;
+ /*
+ * WAL pre-allocation has nothing to do with throttling BufferSync()'s write
+ * rate. However, since this function is called frequently during
+ * checkpoints, it is a convenient place to handle any pending WAL pre-
+ * allocation requests.
+ */
+ if (((volatile CheckpointerShmemStruct *) CheckpointerShmem)->wal_prealloc_requested)
+ DoWalPreAllocation();
+
/*
* Perform the usual duties and take a nap, unless we're behind schedule,
* in which case we just try to catch up as quickly as possible.
@@ -834,6 +869,203 @@ IsCheckpointOnSchedule(double progress)
return true;
}
+/*
+ * GetNumPreallocatedWalSegs
+ *
+ * Returns the number of pre-allocated WAL segments in the preallocated_segments
+ * directory. Callers are expected to hold the WALPreallocationLock.
+ */
+int
+GetNumPreallocatedWalSegs(void)
+{
+ Assert(LWLockHeldByMe(WALPreallocationLock));
+ return CheckpointerShmem->num_prealloc_segs;
+}
+
+/*
+ * SetNumPreallocatedWalSegs
+ *
+ * Sets the number of pre-allocated WAL segments in the preallocated_segments
+ * directory. Callers are expected to hold the WALPreallocationLock
+ * exclusively.
+ */
+void
+SetNumPreallocatedWalSegs(int i)
+{
+ Assert(LWLockHeldByMeInMode(WALPreallocationLock, LW_EXCLUSIVE));
+ CheckpointerShmem->num_prealloc_segs = i;
+}
+
+/*
+ * ScanForExistingPreallocatedSegments
+ *
+ * This function searches through pg_wal/preallocated_segments for any segments
+ * that were left over and sets the tracking variable in shared memory
+ * accordingly.
+ */
+static void
+ScanForExistingPreallocatedSegments(void)
+{
+ int i = 0;
+
+ /*
+ * fsync the preallocated_segments directory in case any renames have yet to
+ * be flushed to disk.
+ */
+ fsync_fname_ext(XLOGDIR "/preallocated_segments", true, false, FATAL);
+
+ /*
+ * Gather all the preallocated segments we can find.
+ */
+ while (true)
+ {
+ FILE *fd;
+ char path[MAXPGPATH];
+
+ snprintf(path, MAXPGPATH, "%s/preallocated_segments/xlogtemp.%d",
+ XLOGDIR, i);
+
+ fd = AllocateFile(path, "r");
+ if (fd != NULL)
+ {
+ FreeFile(fd);
+ i++;
+ }
+ else
+ {
+ if (errno != ENOENT)
+ ereport(FATAL,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\": %m", path)));
+ break;
+ }
+ }
+
+ LWLockAcquire(WALPreallocationLock, LW_EXCLUSIVE);
+ SetNumPreallocatedWalSegs(i);
+ LWLockRelease(WALPreallocationLock);
+
+ elog(DEBUG2, "found %d preallocated segments during startup", i);
+}
+
+/*
+ * InstallPreallocatedWalSeg
+ *
+ * Renames the file at "path" to the next open pre-allocated segment slot and
+ * bumps up num_prealloc_segs. If there is a problem, a WARNING is emitted, we
+ * attempt to delete the file, and false is returned. Otherwise, true is
+ * returned.
+ */
+static bool
+InstallPreallocatedWalSeg(const char *path)
+{
+ char newpath[MAXPGPATH];
+ int rc;
+
+ LWLockAcquire(WALPreallocationLock, LW_EXCLUSIVE);
+
+ snprintf(newpath, MAXPGPATH, "%s/preallocated_segments/xlogtemp.%d",
+ XLOGDIR, CheckpointerShmem->num_prealloc_segs);
+
+ rc = durable_rename(path, newpath, DEBUG1);
+ if (rc == 0)
+ CheckpointerShmem->num_prealloc_segs++;
+ else
+ {
+ ereport(WARNING,
+ (errcode_for_file_access(),
+ errmsg("file \"%s\" could not be renamed to \"%s\": %m",
+ path, newpath)));
+
+ (void) durable_unlink(path, DEBUG1);
+ (void) durable_unlink(newpath, DEBUG1);
+ }
+
+ LWLockRelease(WALPreallocationLock);
+
+ return (rc == 0);
+}
+
+/*
+ * DoWalPreAllocation
+ *
+ * Tries to allocate up to wal_preallocate_max_size worth of WAL.
+ */
+static void
+DoWalPreAllocation(void)
+{
+ int segs_to_prealloc;
+
+ /*
+ * Reset the request flag.
+ */
+ SpinLockAcquire(&CheckpointerShmem->ckpt_lck);
+ CheckpointerShmem->wal_prealloc_requested = false;
+ SpinLockRelease(&CheckpointerShmem->ckpt_lck);
+
+ /*
+ * Determine how many segments to pre-allocate.
+ */
+ LWLockAcquire(WALPreallocationLock, LW_SHARED);
+ segs_to_prealloc = GetMaxPreallocatedWalSegs() - GetNumPreallocatedWalSegs();
+ LWLockRelease(WALPreallocationLock);
+
+ /*
+ * Do the pre-allocation.
+ */
+ for (int i = 0; i < segs_to_prealloc; i++)
+ {
+ char tmppath[MAXPGPATH];
+
+ snprintf(tmppath, MAXPGPATH, "%s/preallocated_segments/xlogtemp", XLOGDIR);
+
+ if (!CreateEmptyWalSegment(tmppath, WARNING) ||
+ !InstallPreallocatedWalSeg(tmppath))
+ {
+ elog(DEBUG2, "failed to pre-allocate WAL segment");
+ return;
+ }
+ else
+ elog(DEBUG2, "pre-allocated WAL segment");
+
+ /* Check for barrier events. */
+ if (ProcSignalBarrierPending)
+ ProcessProcSignalBarrier();
+
+ if (ShutdownRequestPending)
+ return;
+ }
+}
+
+/*
+ * GetMaxPreallocatedWalSegs
+ *
+ * Returns the maximum number of pre-allocated WAL segments to create based on
+ * the current value of wal_preallocate_max_size.
+ */
+static int
+GetMaxPreallocatedWalSegs(void)
+{
+ return wal_prealloc_max_size_mb / (wal_segment_size / (1024 * 1024));
+}
+
+/*
+ * RequestWalPreallocation
+ *
+ * Requests that more segments be pre-allocated for future use.
+ */
+void
+RequestWalPreallocation(void)
+{
+ SpinLockAcquire(&CheckpointerShmem->ckpt_lck);
+ CheckpointerShmem->wal_prealloc_requested = true;
+ SpinLockRelease(&CheckpointerShmem->ckpt_lck);
+
+ if (ProcGlobal->checkpointerLatch &&
+ GetMaxPreallocatedWalSegs() > 0)
+ SetLatch(ProcGlobal->checkpointerLatch);
+}
+
/* --------------------------------
* signal handler routines
@@ -907,6 +1139,8 @@ CheckpointerShmemInit(void)
CheckpointerShmem->max_requests = NBuffers;
ConditionVariableInit(&CheckpointerShmem->start_cv);
ConditionVariableInit(&CheckpointerShmem->done_cv);
+ CheckpointerShmem->wal_prealloc_requested = false;
+ CheckpointerShmem->num_prealloc_segs = 0;
}
}
diff --git a/src/backend/replication/basebackup.c b/src/backend/replication/basebackup.c
index 27e4152446..2be9d199e3 100644
--- a/src/backend/replication/basebackup.c
+++ b/src/backend/replication/basebackup.c
@@ -1304,11 +1304,13 @@ sendDir(bbsink *sink, const char *path, int basepathlen, bool sizeonly,
&statbuf, sizeonly);
/*
- * Also send archive_status directory (by hackishly reusing
- * statbuf from above ...).
+ * Also send archive_status and preallocated_segments (by hackishly
+ * reusing statbuf from above ...).
*/
size += _tarWriteHeader(sink, "./pg_wal/archive_status", NULL,
&statbuf, sizeonly);
+ size += _tarWriteHeader(sink, "./pg_wal/preallocated_segments", NULL,
+ &statbuf, sizeonly);
continue; /* don't recurse into pg_wal */
}
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 4efc46460e..05ab0b4c66 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -3898,8 +3898,8 @@ pg_pwritev_with_retry(int fd, const struct iovec *iov, int iovcnt, off_t offset)
* Create a new file that can be used as a new WAL segment. The caller is
* responsible for installing the new file in pg_wal.
*/
-void
-CreateEmptyWalSegment(const char *path)
+bool
+CreateEmptyWalSegment(const char *path, int elevel)
{
PGAlignedXLogBlock zbuffer;
int fd;
@@ -3910,9 +3910,12 @@ CreateEmptyWalSegment(const char *path)
/* do not use get_sync_bit() here --- want to fsync only at end of fill */
fd = BasicOpenFile(path, O_RDWR | O_CREAT | O_EXCL | PG_BINARY);
if (fd < 0)
- ereport(ERROR,
+ {
+ ereport(elevel,
(errcode_for_file_access(),
errmsg("could not create file \"%s\": %m", path)));
+ return false;
+ }
memset(zbuffer.data, 0, XLOG_BLCKSZ);
@@ -3982,9 +3985,10 @@ CreateEmptyWalSegment(const char *path)
errno = save_errno;
- ereport(ERROR,
+ ereport(elevel,
(errcode_for_file_access(),
errmsg("could not write to file \"%s\": %m", path)));
+ return false;
}
pgstat_report_wait_start(WAIT_EVENT_WAL_INIT_SYNC);
@@ -3994,14 +3998,20 @@ CreateEmptyWalSegment(const char *path)
close(fd);
errno = save_errno;
- ereport(ERROR,
+ ereport(elevel,
(errcode_for_file_access(),
errmsg("could not fsync file \"%s\": %m", path)));
+ return false;
}
pgstat_report_wait_end();
if (close(fd) != 0)
- ereport(ERROR,
+ {
+ ereport(elevel,
(errcode_for_file_access(),
errmsg("could not close file \"%s\": %m", path)));
+ return false;
+ }
+
+ return true;
}
diff --git a/src/backend/storage/lmgr/lwlocknames.txt b/src/backend/storage/lmgr/lwlocknames.txt
index 6c7cf6c295..212bda8e7d 100644
--- a/src/backend/storage/lmgr/lwlocknames.txt
+++ b/src/backend/storage/lmgr/lwlocknames.txt
@@ -53,3 +53,4 @@ XactTruncationLock 44
# 45 was XactTruncationLock until removal of BackendRandomLock
WrapLimitsVacuumLock 46
NotifyQueueTailLock 47
+WALPreallocationLock 48
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 22b5571a70..0b496ddc9b 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2918,6 +2918,17 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"wal_preallocate_max_size", PGC_SIGHUP, WAL_SETTINGS,
+ gettext_noop("Sets the maximum amount of WAL to pre-allocate."),
+ NULL,
+ GUC_UNIT_MB
+ },
+ &wal_prealloc_max_size_mb,
+ 64, 0, 102400,
+ NULL, NULL, NULL
+ },
+
{
{"wal_buffers", PGC_POSTMASTER, WAL_SETTINGS,
gettext_noop("Sets the number of disk-page buffers in shared memory for WAL."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 94270eb0ec..8238679d25 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -223,6 +223,7 @@
# off, pglz, lz4, zstd, or on
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
+#wal_preallocate_max_size = 64MB # amount of WAL to pre-allocate
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ab826da650..0867bd30af 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -212,6 +212,7 @@ static char *extra_options = "";
static const char *const subdirs[] = {
"global",
"pg_wal/archive_status",
+ "pg_wal/preallocated_segments",
"pg_commit_ts",
"pg_dynshmem",
"pg_notify",
diff --git a/src/bin/pg_basebackup/bbstreamer_file.c b/src/bin/pg_basebackup/bbstreamer_file.c
index 393e9f340c..3b932b80a6 100644
--- a/src/bin/pg_basebackup/bbstreamer_file.c
+++ b/src/bin/pg_basebackup/bbstreamer_file.c
@@ -293,7 +293,8 @@ extract_directory(const char *filename, mode_t mode)
*/
if (!((pg_str_endswith(filename, "/pg_wal") ||
pg_str_endswith(filename, "/pg_xlog") ||
- pg_str_endswith(filename, "/archive_status")) &&
+ pg_str_endswith(filename, "/archive_status") ||
+ pg_str_endswith(filename, "/preallocated_segments")) &&
errno == EEXIST))
pg_fatal("could not create directory \"%s\": %m",
filename);
diff --git a/src/bin/pg_basebackup/pg_basebackup.c b/src/bin/pg_basebackup/pg_basebackup.c
index 65dcfff0a0..b9d990cf7c 100644
--- a/src/bin/pg_basebackup/pg_basebackup.c
+++ b/src/bin/pg_basebackup/pg_basebackup.c
@@ -683,6 +683,19 @@ StartLogStreamer(char *startpos, uint32 timeline, char *sysidentifier,
if (pg_mkdir_p(statusdir, pg_dir_create_mode) != 0 && errno != EEXIST)
pg_fatal("could not create directory \"%s\": %m", statusdir);
+
+ /*
+ * Also create pg_wal/preallocated_segments if necessary.
+ */
+ if (PQserverVersion(conn) >= 150000)
+ {
+ char prealloc_dir[MAXPGPATH];
+
+ snprintf(prealloc_dir, sizeof(prealloc_dir), "%s/pg_wal/preallocated_segments",
+ basedir);
+ if (pg_mkdir_p(prealloc_dir, pg_dir_create_mode) != 0 && errno != EEXIST)
+ pg_fatal("could not create directory \"%s\": %m", prealloc_dir);
+ }
}
/*
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index 7309ebddea..b3baee781c 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -204,10 +204,10 @@ SKIP:
"check backup dir permissions");
}
-# Only archive_status directory should be copied in pg_wal/.
+# Only archive_status and preallocated_segments directories should be copied in pg_wal/.
is_deeply(
[ sort(slurp_dir("$tempdir/backup/pg_wal/")) ],
- [ sort qw(. .. archive_status) ],
+ [ sort qw(. .. archive_status preallocated_segments) ],
'no WAL files copied');
# Contents of these directories should not be copied.
diff --git a/src/bin/pg_resetwal/pg_resetwal.c b/src/bin/pg_resetwal/pg_resetwal.c
index d4772a2965..dfae77fb78 100644
--- a/src/bin/pg_resetwal/pg_resetwal.c
+++ b/src/bin/pg_resetwal/pg_resetwal.c
@@ -85,6 +85,7 @@ static void RewriteControlFile(void);
static void FindEndOfXLOG(void);
static void KillExistingXLOG(void);
static void KillExistingArchiveStatus(void);
+static void KillExistingPreallocatedSegments(void);
static void WriteEmptyXLOG(void);
static void usage(void);
@@ -488,6 +489,7 @@ main(int argc, char *argv[])
RewriteControlFile();
KillExistingXLOG();
KillExistingArchiveStatus();
+ KillExistingPreallocatedSegments();
WriteEmptyXLOG();
printf(_("Write-ahead log reset\n"));
@@ -1037,6 +1039,52 @@ KillExistingArchiveStatus(void)
}
+/*
+ * Remove existing preallocated segments
+ */
+static void
+KillExistingPreallocatedSegments(void)
+{
+#define PREALLOCSEGDIR XLOGDIR "/preallocated_segments"
+
+ DIR *xldir;
+ struct dirent *xlde;
+ char path[MAXPGPATH + sizeof(PREALLOCSEGDIR)];
+
+ xldir = opendir(PREALLOCSEGDIR);
+ if (xldir == NULL)
+ {
+ pg_log_error("could not open directory \"%s\": %m", PREALLOCSEGDIR);
+ exit(1);
+ }
+
+ while (errno = 0, (xlde = readdir(xldir)) != NULL)
+ {
+ if (strncmp(xlde->d_name, "xlogtemp", strlen("xlogtemp")) == 0)
+ {
+ snprintf(path, sizeof(path), "%s/%s", PREALLOCSEGDIR, xlde->d_name);
+ if (unlink(path) < 0)
+ {
+ pg_log_error("could not delete file \"%s\": %m", path);
+ exit(1);
+ }
+ }
+ }
+
+ if (errno)
+ {
+ pg_log_error("could not read directory \"%s\": %m", PREALLOCSEGDIR);
+ exit(1);
+ }
+
+ if (closedir(xldir))
+ {
+ pg_log_error("could not close directory \"%s\": %m", PREALLOCSEGDIR);
+ exit(1);
+ }
+}
+
+
/*
* Write an empty XLOG file, containing only the checkpoint record
* already set up in ControlFile.
diff --git a/src/include/postmaster/bgwriter.h b/src/include/postmaster/bgwriter.h
index 2511ef451e..9f284fa76b 100644
--- a/src/include/postmaster/bgwriter.h
+++ b/src/include/postmaster/bgwriter.h
@@ -26,6 +26,7 @@ extern PGDLLIMPORT int BgWriterDelay;
extern PGDLLIMPORT int CheckPointTimeout;
extern PGDLLIMPORT int CheckPointWarning;
extern PGDLLIMPORT double CheckPointCompletionTarget;
+extern PGDLLIMPORT int wal_prealloc_max_size_mb;
extern void BackgroundWriterMain(void) pg_attribute_noreturn();
extern void CheckpointerMain(void) pg_attribute_noreturn();
@@ -42,4 +43,8 @@ extern void CheckpointerShmemInit(void);
extern bool FirstCallSinceLastCheckpoint(void);
+extern int GetNumPreallocatedWalSegs(void);
+extern void SetNumPreallocatedWalSegs(int i);
+extern void RequestWalPreallocation(void);
+
#endif /* _BGWRITER_H */
diff --git a/src/include/storage/fd.h b/src/include/storage/fd.h
index 6bb9e3525b..bfc32a9b39 100644
--- a/src/include/storage/fd.h
+++ b/src/include/storage/fd.h
@@ -190,7 +190,7 @@ extern int durable_unlink(const char *fname, int loglevel);
extern int durable_rename_excl(const char *oldfile, const char *newfile, int loglevel);
extern void SyncDataDirectory(void);
extern int data_sync_elevel(int elevel);
-extern void CreateEmptyWalSegment(const char *path);
+extern bool CreateEmptyWalSegment(const char *path, int elevel);
/* Filename components */
#define PG_TEMP_FILES_DIR "pgsql_tmp"
--
2.25.1
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2022-07-14 18:34 ` Nathan Bossart <[email protected]>
2022-07-25 16:24 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2022-07-14 18:34 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Fri, Apr 08, 2022 at 01:30:03PM -0700, Nathan Bossart wrote:
> On Thu, Mar 17, 2022 at 04:12:12PM -0700, Nathan Bossart wrote:
>> It seems unlikely that this will be committed for v15, so I've adjusted the
>> commitfest entry to v16 and moved it to the next commitfest.
>
> rebased
It's now been over a year since I first posted a patch in this thread, and
I still sense very little interest for this feature. I intend to mark it
as Withdrawn at the end of this commitfest.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-07-14 18:34 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2022-07-25 16:24 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Nathan Bossart @ 2022-07-25 16:24 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Thu, Jul 14, 2022 at 11:34:07AM -0700, Nathan Bossart wrote:
> It's now been over a year since I first posted a patch in this thread, and
> I still sense very little interest for this feature. I intend to mark it
> as Withdrawn at the end of this commitfest.
Done.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2025-01-21 03:31 ` Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Andy Fan @ 2025-01-21 03:31 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
Hi Nathan,
Come from [0] and thanks for working on this. Here are some design
review/question after my first going through the patch.
1. walwriter vs checkpointer? I prefer to walwriter for now because..
a. checkpointer is hard to do it in a timely manner either because
checkpoint itself may take a long time or the checkpoint_timeout
is much bigger than commit_delay. but walwriter could do this timely.
I think this is an important consideration for this feature.
b. We want walwriter to run with low latency to flush out async
commits. This is true, but preallocating a wal doesn't increase the
latency too much. After all, even user uses the aysnc commit, the walfile
allocating is done by walwriter already in our current implementation.
2. How many xlogfile should be preallocated by checkpointer/walwriter
once. In your patch it is controled by wal-preallocate-max-size. How
about just preallocate *the next one* xlogfile for the simplification
purpose?
3. Why is the purpose of preallocated_segments directory? what in my
mind is we just prellocate the normal filename so that XLogWrite could
open it directly. This is same as what wal_recycle does and we can reuse
the same strategy to clean up them if they are not needed anymore.
So the poc in my mind for this feature is:
- keep track the latested reallocated (by wal_recycle or preallocated)
logfile in XLogCtl.
- logwriter check current wal insert_pos and prellocate the *next one*
walfile if it doesn't preallocated yet.
- we need to handle race condition carefully between wal_recycle, user
backend and preallocation.
[0] https://www.postgresql.org/message-id/Z46BwCNAEjLyW85Z%40nathan
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
@ 2025-01-21 15:52 ` Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2025-01-21 15:52 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Tue, Jan 21, 2025 at 03:31:27AM +0000, Andy Fan wrote:
> Come from [0] and thanks for working on this. Here are some design
> review/question after my first going through the patch.
Thanks for taking a look.
> 1. walwriter vs checkpointer? I prefer to walwriter for now because..
>
> a. checkpointer is hard to do it in a timely manner either because
> checkpoint itself may take a long time or the checkpoint_timeout
> is much bigger than commit_delay. but walwriter could do this timely.
> I think this is an important consideration for this feature.
>
> b. We want walwriter to run with low latency to flush out async
> commits. This is true, but preallocating a wal doesn't increase the
> latency too much. After all, even user uses the aysnc commit, the walfile
> allocating is done by walwriter already in our current implementation.
I attempted to deal with this by having pre-allocation requests set the
checkpointer's latch and performing the pre-allocation within the
checkpointer's main loop and during write delays. However, checkpointing
does a number of other things that could just as easily delay
pre-allocation, so it's probably worth considering the WAL writer.
> 2. How many xlogfile should be preallocated by checkpointer/walwriter
> once. In your patch it is controled by wal-preallocate-max-size. How
> about just preallocate *the next one* xlogfile for the simplification
> purpose?
We could probably start with something like that. IIRC it was difficult to
create workloads where you'd need more than 1-2 at a time, provided
whatever is pre-allocating refills the pool quickly.
> 3. Why is the purpose of preallocated_segments directory? what in my
> mind is we just prellocate the normal filename so that XLogWrite could
> open it directly. This is same as what wal_recycle does and we can reuse
> the same strategy to clean up them if they are not needed anymore.
The purpose is to limit the use of pre-allocated segments to only
situations where WAL recycling is not sufficient. Basically, if writing a
record would require a new segment to be created, we can quickly pull a
pre-allocated one instead of creating it ourselves. Besides simplifying
matters, this prevents a lot of unnecessary pre-allocation, since many
workloads will almost never need anything beyond the recycled segments.
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2025-01-21 16:13 ` Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2025-01-21 16:13 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; Andres Freund <[email protected]>; [email protected]
On Tue, Jan 21, 2025 at 09:52:51AM -0600, Nathan Bossart wrote:
> On Tue, Jan 21, 2025 at 03:31:27AM +0000, Andy Fan wrote:
>> 3. Why is the purpose of preallocated_segments directory? what in my
>> mind is we just prellocate the normal filename so that XLogWrite could
>> open it directly. This is same as what wal_recycle does and we can reuse
>> the same strategy to clean up them if they are not needed anymore.
>
> The purpose is to limit the use of pre-allocated segments to only
> situations where WAL recycling is not sufficient. Basically, if writing a
> record would require a new segment to be created, we can quickly pull a
> pre-allocated one instead of creating it ourselves. Besides simplifying
> matters, this prevents a lot of unnecessary pre-allocation, since many
> workloads will almost never need anything beyond the recycled segments.
That being said, it would be nice to avoid the fsync() overhead to move a
pre-allocated WAL into place. My first instinct is that would be
substantially more complicated and may not actually improve matters all
that much, but I agree that it's worth exploring.
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2025-01-21 16:23 ` Andres Freund <[email protected]>
2025-01-22 01:14 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-22 15:50 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Andres Freund @ 2025-01-21 16:23 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Andy Fan <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
Hi,
On 2025-01-21 10:13:14 -0600, Nathan Bossart wrote:
> On Tue, Jan 21, 2025 at 09:52:51AM -0600, Nathan Bossart wrote:
> > On Tue, Jan 21, 2025 at 03:31:27AM +0000, Andy Fan wrote:
> >> 3. Why is the purpose of preallocated_segments directory? what in my
> >> mind is we just prellocate the normal filename so that XLogWrite could
> >> open it directly. This is same as what wal_recycle does and we can reuse
> >> the same strategy to clean up them if they are not needed anymore.
> >
> > The purpose is to limit the use of pre-allocated segments to only
> > situations where WAL recycling is not sufficient. Basically, if writing a
> > record would require a new segment to be created, we can quickly pull a
> > pre-allocated one instead of creating it ourselves. Besides simplifying
> > matters, this prevents a lot of unnecessary pre-allocation, since many
> > workloads will almost never need anything beyond the recycled segments.
I don't really understand that argument - we should be able to predict rather
precisely whether we need to preallocate or not. We have the recent WAL "fill
rate", we know the end of the WAL and we can easily track how far ahead of the
current point we have allocated. Why preallocate when we have a large reserve
of "future" segments? Why preallocate in a separate directory when we have no
future segments?
> That being said, it would be nice to avoid the fsync() overhead to move a
> pre-allocated WAL into place. My first instinct is that would be
> substantially more complicated and may not actually improve matters all
> that much, but I agree that it's worth exploring.
FWIW, I've seen the fsyncs around recycling being a rather substantial
bottleneck. To the point of the main benefit of larger segments being the
reduction in number of fsyncs at the end of a checkpoint. I think we should
be able to make the fsyncs a lot more efficient by batching them, first rename
a bunch of files, then fsync them and the directory. The current pattern
bascially requires a separate filesystem jouranl flush for each WAL segment.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
@ 2025-01-22 01:14 ` Andy Fan <[email protected]>
2025-01-22 15:56 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-22 16:21 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
1 sibling, 2 replies; 17+ messages in thread
From: Andy Fan @ 2025-01-22 01:14 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
Andres Freund <[email protected]> writes:
Hi,
> FWIW, I've seen the fsyncs around recycling being a rather substantial
> bottleneck. To the point of the main benefit of larger segments being the
> reduction in number of fsyncs at the end of a checkpoint. I think we should
> be able to make the fsyncs a lot more efficient by batching them, first rename
> a bunch of files, then fsync them and the directory. The current pattern
> bascially requires a separate filesystem jouranl flush for each WAL segment.
For education purpose, how to fsync files in batch? 'man fsync' tells me
user can only fsync one file each time.
int fsync(int fd);
The fsync manual seems not saying fsync on a directory would fsync all
the files under that directory.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
2025-01-22 01:14 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
@ 2025-01-22 15:56 ` Nathan Bossart <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Nathan Bossart @ 2025-01-22 15:56 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Andres Freund <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
On Wed, Jan 22, 2025 at 01:14:22AM +0000, Andy Fan wrote:
> Andres Freund <[email protected]> writes:
>> FWIW, I've seen the fsyncs around recycling being a rather substantial
>> bottleneck. To the point of the main benefit of larger segments being the
>> reduction in number of fsyncs at the end of a checkpoint. I think we should
>> be able to make the fsyncs a lot more efficient by batching them, first rename
>> a bunch of files, then fsync them and the directory. The current pattern
>> bascially requires a separate filesystem jouranl flush for each WAL segment.
>
> For education purpose, how to fsync files in batch? 'man fsync' tells me
> user can only fsync one file each time.
>
> int fsync(int fd);
>
> The fsync manual seems not saying fsync on a directory would fsync all
> the files under that directory.
I think Andres means that we should wait until the end of recycling to
fsync() the directory so that we aren't flushing it for every single
recycled segment. This sort of batching approach could also work well with
pre_sync_fname(), so that by the time we actually call fsync() on the
files, it has very little to do.
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
2025-01-22 01:14 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
@ 2025-01-22 16:21 ` Andres Freund <[email protected]>
2025-01-22 17:43 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Andres Freund @ 2025-01-22 16:21 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Nathan Bossart <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
Hi,
On 2025-01-22 01:14:22 +0000, Andy Fan wrote:
> Andres Freund <[email protected]> writes:
> > FWIW, I've seen the fsyncs around recycling being a rather substantial
> > bottleneck. To the point of the main benefit of larger segments being the
> > reduction in number of fsyncs at the end of a checkpoint. I think we should
> > be able to make the fsyncs a lot more efficient by batching them, first rename
> > a bunch of files, then fsync them and the directory. The current pattern
> > bascially requires a separate filesystem jouranl flush for each WAL segment.
>
> For education purpose, how to fsync files in batch? 'man fsync' tells me
> user can only fsync one file each time.
>
> int fsync(int fd);
>
> The fsync manual seems not saying fsync on a directory would fsync all
> the files under that directory.
Right now we do something that essentially boils down to
// recycle WAL file oldname1
fsync(open(oldname1));
rename(oldname1, newname1);
fsync(open(newname1));
fsync(open("pg_wal"));
// recycle WAL file oldname2
fsync(open(oldname2));
rename(oldname2, newname2);
fsync(open(newname2));
fsync(open("pg_wal"));
...
// recycle WAL file oldnameN
fsync(open(oldnameN));
rename(oldnameN, newnameN);
fsync(open(newnameN));
fsync(open("pg_wal"));
...
Most of the time the fsync on oldname won't have to do any IO (because
presumably we'll have flushed it before), but the rename obviously requires a
metadata update and thus the fsync will have work to do (whether it's the
fsync on newname or the directory will differ between filesystems).
This pattern basically forces the filesystem to do at least one journal flush
for every single WAL segment. I.e. each recycled segment will have at least
the latency of a single synchronous durable write IO.
But if we instead change it to something like this:
fsync(open(oldname1));
fsync(open(oldname2));
..
fsync(open(oldnameN));
rename(oldname1, newname1);
rename(oldname2, newname2);
..
rename(oldnameN, newnameN);
fsync(open(newname1));
fsync(open(newname2));
..
fsync(open(newnameN));
fsync(open("pg_wal"));
Most filesystems will be able to combine many of the the journal flushes
triggered by the renames into much bigger journal flushes. That means the
overall time for recycling is much lower than the earlier one, since there are
far fewer synchronous durable writes.
Here's a rough approximation of the effect using shell commands:
andres@awork3:/srv/dev/renamet$ rm -f test.*; N=1000; time (for i in $(seq 1 $N); do echo test > test.$i.old; done;sync; for i in $(seq 1 $N); do mv test.$i.old test.$i.new; sync; done;)
real 0m7.218s
user 0m0.431s
sys 0m4.892s
andres@awork3:/srv/dev/renamet$ rm -f test.*; N=1000; time (for i in $(seq 1 $N); do echo test > test.$i.old; done;sync; for i in $(seq 1 $N); do mv test.$i.old test.$i.new; done; sync)
real 0m2.678s
user 0m0.282s
sys 0m2.402s
The only difference between the two versions is that the latter can combine
the journal flushes, due to the sync happening outside of the loop.
This is a somewhat poor approximation of how this would work in postgres,
including likely exaggerating the gain (I think sync flushes the filesystem
superblock too), but it does show the principle.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
2025-01-22 01:14 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-22 16:21 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
@ 2025-01-22 17:43 ` Nathan Bossart <[email protected]>
2025-01-22 18:00 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Nathan Bossart @ 2025-01-22 17:43 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andy Fan <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
On Wed, Jan 22, 2025 at 11:21:03AM -0500, Andres Freund wrote:
> fsync(open(oldname1));
> fsync(open(oldname2));
> ..
> fsync(open(oldnameN));
>
> rename(oldname1, newname1);
> rename(oldname2, newname2);
> ..
> rename(oldnameN, newnameN);
>
> fsync(open(newname1));
> fsync(open(newname2));
> ..
> fsync(open(newnameN));
>
> fsync(open("pg_wal"));
What is the purpose of syncing the file before the rename?
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
2025-01-22 01:14 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-22 16:21 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
2025-01-22 17:43 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
@ 2025-01-22 18:00 ` Andres Freund <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Andres Freund @ 2025-01-22 18:00 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Andy Fan <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
Hi,
On 2025-01-22 11:43:20 -0600, Nathan Bossart wrote:
> On Wed, Jan 22, 2025 at 11:21:03AM -0500, Andres Freund wrote:
> > fsync(open(oldname1));
> > fsync(open(oldname2));
> > ..
> > fsync(open(oldnameN));
> >
> > rename(oldname1, newname1);
> > rename(oldname2, newname2);
> > ..
> > rename(oldnameN, newnameN);
> >
> > fsync(open(newname1));
> > fsync(open(newname2));
> > ..
> > fsync(open(newnameN));
> >
> > fsync(open("pg_wal"));
>
> What is the purpose of syncing the file before the rename?
It's from the general durable_rename() code. The reason it's there that it's
required for "atomically replace a file" use case. Imagine the following:
create_and_fill("somefile.tmp");
rename("somefile.tmp", "somefile");
fsync("somefile.tmp");
fsync(".");
If you crash (OS/HW level) in the wrong moment (between rename() taking effect
in-memory and the fsyncs), you might end up with "somefile" pointing to the
*new* file, because the rename took affect, but the new file's content not
having reached disk yet. I.e. "somefile" will be empty. Whether that's
possible depends on filesystem semantics (e.g. on ext4 it's possible with
data=writeback, I think it's always possible on xfs).
In contrast to that, if you fsync("somefile.tmp") before the rename, a crash
between rename() and the later fsyncs will have "somefile" either pointing to
the *old and valid contents* or the *new and valid contents*, without a chance
for an empty file.
However, for the case of WAL recycling, we shouldn't need fsync() before the
rename, because we ought to already have done so when creating
(c.f. XLogFileInitInternal() or when recycling it last time.
I suspect the theoretically superfluous fsync() won't have a meaningful
performance impact most of the time though, because
a) There shouldn't be any dirty data for the file, obviously we need to have
flushed the WAL past the recycled segment
b) Except for the first to-be-recycled segment, we just fsynced after the last
rename, so there won't be any filesystem journal data that needs to be
flushed
I'm not entirely sure about a) though - depending on mount options it's
possible that the fsync() will flush file modification times when using
wal_sync_method=fdatasync. But even if that's possibly reachable, I doubt
it'll be common, due to a checkpoint having to complete between the WAL flush
and recycling. Could be worth experimenting with.
Greetings,
Andres
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Pre-allocating WAL files
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Re: Pre-allocating WAL files Andy Fan <[email protected]>
2025-01-21 15:52 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Re: Pre-allocating WAL files Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Re: Pre-allocating WAL files Andres Freund <[email protected]>
@ 2025-01-22 15:50 ` Nathan Bossart <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Nathan Bossart @ 2025-01-22 15:50 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Andy Fan <[email protected]>; Justin Pryzby <[email protected]>; Maxim Orlov <[email protected]>; Pavel Borisov <[email protected]>; Bossart, Nathan <[email protected]>; Maxim Orlov <[email protected]>; [email protected]
On Tue, Jan 21, 2025 at 11:23:06AM -0500, Andres Freund wrote:
> On 2025-01-21 10:13:14 -0600, Nathan Bossart wrote:
>> On Tue, Jan 21, 2025 at 09:52:51AM -0600, Nathan Bossart wrote:
>> > On Tue, Jan 21, 2025 at 03:31:27AM +0000, Andy Fan wrote:
>> >> 3. Why is the purpose of preallocated_segments directory? what in my
>> >> mind is we just prellocate the normal filename so that XLogWrite could
>> >> open it directly. This is same as what wal_recycle does and we can reuse
>> >> the same strategy to clean up them if they are not needed anymore.
>> >
>> > The purpose is to limit the use of pre-allocated segments to only
>> > situations where WAL recycling is not sufficient. Basically, if writing a
>> > record would require a new segment to be created, we can quickly pull a
>> > pre-allocated one instead of creating it ourselves. Besides simplifying
>> > matters, this prevents a lot of unnecessary pre-allocation, since many
>> > workloads will almost never need anything beyond the recycled segments.
>
> I don't really understand that argument - we should be able to predict rather
> precisely whether we need to preallocate or not. We have the recent WAL "fill
> rate", we know the end of the WAL and we can easily track how far ahead of the
> current point we have allocated. Why preallocate when we have a large reserve
> of "future" segments? Why preallocate in a separate directory when we have no
> future segments?
If we can indeed reliably predict whether we need pre-allocation, then
sure, let's just create future segments directly in pg_wal. I'm not sure
we could reliably predict whether WAL will be recycled in time, so we might
pre-allocate a bit more than necessary, but that's not too terrible. My
"pooling" approach was intended to keep the pre-allocation to a minimum
(IME you really only need a couple at any given time) and to avoid the
guesswork involved in predicting.
>> That being said, it would be nice to avoid the fsync() overhead to move a
>> pre-allocated WAL into place. My first instinct is that would be
>> substantially more complicated and may not actually improve matters all
>> that much, but I agree that it's worth exploring.
>
> FWIW, I've seen the fsyncs around recycling being a rather substantial
> bottleneck. To the point of the main benefit of larger segments being the
> reduction in number of fsyncs at the end of a checkpoint. I think we should
> be able to make the fsyncs a lot more efficient by batching them, first rename
> a bunch of files, then fsync them and the directory. The current pattern
> bascially requires a separate filesystem jouranl flush for each WAL segment.
+1, these kinds of fsync() patterns should be fixed.
--
nathan
^ permalink raw reply [nested|flat] 17+ messages in thread
* [PATCH v5] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)
This adds the new auth_delay.max_milliseconds GUC. If set (its default is 0),
auth_delay adds exponential backoff with this GUC's value as maximum delay.
The exponential backoff is tracked per remote host and doubled for every failed
login attempt (i.e., wrong password, not just missing pg_hba line or database)
and reset to auth_delay.milliseconds after a successful authentication or when
no authentication attempts have been made for 5*max_milliseconds from that
host.
Authors: Michael Banck, based on an earlier patch by ζδΉη
Reviewed-by: Abhijit Menon-Sen, Tomas Vondra
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
contrib/auth_delay/auth_delay.c | 216 ++++++++++++++++++++++++++++++-
doc/src/sgml/auth-delay.sgml | 31 ++++-
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 244 insertions(+), 4 deletions(-)
diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index ff0e1fd461..5fb123d133 100644
--- a/contrib/auth_delay/auth_delay.c
+++ b/contrib/auth_delay/auth_delay.c
@@ -14,24 +14,50 @@
#include <limits.h>
#include "libpq/auth.h"
+#include "miscadmin.h"
#include "port.h"
+#include "storage/dsm_registry.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.h"
+#include "storage/shmem.h"
#include "utils/guc.h"
#include "utils/timestamp.h"
PG_MODULE_MAGIC;
+#define MAX_CONN_RECORDS 100
+
/* GUC Variables */
static int auth_delay_milliseconds = 0;
+static int auth_delay_max_milliseconds = 0;
/* Original Hook */
static ClientAuthentication_hook_type original_client_auth_hook = NULL;
+typedef struct AuthConnRecord
+{
+ char remote_host[NI_MAXHOST];
+ double sleep_time; /* in milliseconds */
+ TimestampTz last_failed_auth;
+} AuthConnRecord;
+
+static shmem_startup_hook_type shmem_startup_next = NULL;
+static AuthConnRecord *acr_array = NULL;
+
+static AuthConnRecord *auth_delay_find_acr_for_host(char *remote_host);
+static AuthConnRecord *auth_delay_find_free_acr(void);
+static double auth_delay_increase_delay_after_failed_conn_auth(Port *port);
+static void auth_delay_cleanup_conn_record(Port *port);
+static void auth_delay_expire_conn_records(Port *port);
+
/*
* Check authentication
*/
static void
auth_delay_checks(Port *port, int status)
{
+ double delay = auth_delay_milliseconds;
+
/*
* Any other plugins which use ClientAuthentication_hook.
*/
@@ -39,20 +65,190 @@ auth_delay_checks(Port *port, int status)
original_client_auth_hook(port, status);
/*
- * Inject a short delay if authentication failed.
+ * We handle both STATUS_ERROR and STATUS_OK - the third option
+ * (STATUS_EOF) is disregarded.
+ *
+ * In case of STATUS_ERROR we inject a short delay, optionally with
+ * exponential backoff.
+ */
+ if (status == STATUS_ERROR)
+ {
+ if (auth_delay_max_milliseconds > 0)
+ {
+ /*
+ * Delay by 2^n seconds after each authentication failure from a
+ * particular host, where n is the number of consecutive
+ * authentication failures.
+ */
+ delay = auth_delay_increase_delay_after_failed_conn_auth(port);
+
+ /*
+ * Clamp delay to a maximum of auth_delay_max_milliseconds.
+ */
+ delay = Min(delay, auth_delay_max_milliseconds);
+ }
+
+ if (delay > 0)
+ {
+ elog(DEBUG1, "Authentication delayed for %g seconds due to auth_delay", delay / 1000.0);
+ pg_usleep(1000L * (long) delay);
+ }
+
+ /*
+ * Expire delays from other hosts after auth_delay_max_milliseconds *
+ * 5.
+ */
+ auth_delay_expire_conn_records(port);
+ }
+
+ /*
+ * Remove host-specific delay if authentication succeeded.
+ */
+ if (status == STATUS_OK)
+ auth_delay_cleanup_conn_record(port);
+}
+
+static double
+auth_delay_increase_delay_after_failed_conn_auth(Port *port)
+{
+ AuthConnRecord *acr = NULL;
+
+ acr = auth_delay_find_acr_for_host(port->remote_host);
+
+ if (!acr)
+ {
+ acr = auth_delay_find_free_acr();
+
+ if (!acr)
+ {
+ /*
+ * No free space, MAX_CONN_RECORDS reached. Wait for the
+ * configured maximum amount.
+ */
+ elog(LOG, "auth_delay: host connection list full, waiting maximum amount");
+ return auth_delay_max_milliseconds;
+ }
+ strcpy(acr->remote_host, port->remote_host);
+ }
+ if (acr->sleep_time == 0)
+ acr->sleep_time = (double) auth_delay_milliseconds;
+ else
+ acr->sleep_time *= 2;
+
+ /*
+ * Set current timestamp for later expiry.
*/
- if (status != STATUS_OK)
+ acr->last_failed_auth = GetCurrentTimestamp();
+
+ return acr->sleep_time;
+}
+
+static AuthConnRecord *
+auth_delay_find_acr_for_host(char *remote_host)
+{
+ int i;
+
+ for (i = 0; i < MAX_CONN_RECORDS; i++)
+ {
+ if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+ return &acr_array[i];
+ }
+
+ return NULL;
+}
+
+static AuthConnRecord *
+auth_delay_find_free_acr(void)
+{
+ int i;
+
+ for (i = 0; i < MAX_CONN_RECORDS; i++)
+ {
+ if (!acr_array[i].remote_host[0])
+ return &acr_array[i];
+ }
+
+ return 0;
+}
+
+static void
+auth_delay_cleanup_conn_record(Port *port)
+{
+ AuthConnRecord *acr = NULL;
+
+ acr = auth_delay_find_acr_for_host(port->remote_host);
+ if (acr == NULL)
+ return;
+
+ port->remote_host[0] = '\0';
+
+ acr->sleep_time = 0.0;
+ acr->last_failed_auth = 0.0;
+}
+
+static void
+auth_delay_expire_conn_records(Port *port)
+{
+ int i;
+ TimestampTz now = GetCurrentTimestamp();
+
+ for (i = 0; i < MAX_CONN_RECORDS; i++)
{
- pg_usleep(1000L * auth_delay_milliseconds);
+ /*
+ * Do not expire the host from which the current authentication
+ * failure originated.
+ */
+ if (strcmp(acr_array[i].remote_host, port->remote_host) == 0)
+ continue;
+
+ if (acr_array[i].last_failed_auth > 0 && (long) ((now - acr_array[i].last_failed_auth) / 1000) > 5 * auth_delay_max_milliseconds)
+ {
+ acr_array[i].remote_host[0] = '\0';
+ acr_array[i].sleep_time = 0.0;
+ acr_array[i].last_failed_auth = 0.0;
+ }
}
}
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_init_state(void *ptr)
+{
+ Size shm_size;
+ AuthConnRecord *array = (AuthConnRecord *) ptr;
+
+ shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+
+ memset(array, 0, shm_size);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+ bool found;
+ Size shm_size;
+
+ if (shmem_startup_next)
+ shmem_startup_next();
+
+ shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+ acr_array = GetNamedDSMSegment("auth_delay", shm_size, auth_delay_init_state, &found);
+}
+
/*
* Module Load Callback
*/
void
_PG_init(void)
{
+ if (!process_shared_preload_libraries_in_progress)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("auth_delay must be loaded via shared_preload_libraries")));
+
/* Define custom GUC variables */
DefineCustomIntVariable("auth_delay.milliseconds",
"Milliseconds to delay before reporting authentication failure",
@@ -66,9 +262,23 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomIntVariable("auth_delay.max_milliseconds",
+ "Maximum delay for exponential backoff",
+ NULL,
+ &auth_delay_max_milliseconds,
+ 0,
+ 0, INT_MAX / 1000,
+ PGC_SIGHUP,
+ GUC_UNIT_MS,
+ NULL, NULL, NULL);
+
MarkGUCPrefixReserved("auth_delay");
/* Install Hooks */
original_client_auth_hook = ClientAuthentication_hook;
ClientAuthentication_hook = auth_delay_checks;
+
+ /* Set up shared memory */
+ shmem_startup_next = shmem_startup_hook;
+ shmem_startup_hook = auth_delay_shmem_startup;
}
diff --git a/doc/src/sgml/auth-delay.sgml b/doc/src/sgml/auth-delay.sgml
index 0571f2a99d..e3c182cd45 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,19 @@
connection slots.
</para>
+ <para>
+ It is optionally possible to let <filename>auth_delay</filename> wait longer
+ on each successive authentication failure if the configuration parameter
+ <varname>auth_delay.max_milliseconds</varname> is set. In this case,
+ <filename>auth_delay</filename> will start with a delay of
+ <varname>auth_delay.milliseconds</varname> and double the delay after each
+ consecutive authentication failure from a particular host, up to the given
+ <varname>auth_delay.max_milliseconds</varname>. If the host authenticates
+ successfully or after a timeout of five times
+ <varname>auth_delay.max_milliseconds</varname>, the delay is reset to
+ <varname>auth_delay.milliseconds</varname>.
+ </para>
+
<para>
In order to function, this module must be loaded via
<xref linkend="guc-shared-preload-libraries"/> in <filename>postgresql.conf</filename>.
@@ -39,6 +52,21 @@
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <varname>auth_delay.max_milliseconds</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>auth_delay.max_milliseconds</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The maximum delay in milliseconds, implying exponential backoff between
+ each successive failed attempt. The default is 0, meaning exponential
+ backoff is not active.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<para>
@@ -50,7 +78,8 @@
# postgresql.conf
shared_preload_libraries = 'auth_delay'
-auth_delay.milliseconds = '500'
+auth_delay.milliseconds = '125'
+auth_delay.max_milliseconds = '20000'
</programlisting>
</sect2>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 95ae7845d8..dd9fb9e530 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -166,6 +166,7 @@ AttrMap
AttrMissing
AttrNumber
AttributeOpts
+AuthConnRecord
AuthRequest
AuthToken
AutoPrewarmSharedState
--
2.39.2
--hoZxPH4CaxYzWscb--
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2025-01-22 18:00 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-01 14:40 Re: Pre-allocating WAL files Justin Pryzby <[email protected]>
2022-03-01 15:07 ` Nathan Bossart <[email protected]>
2022-03-17 23:12 ` Nathan Bossart <[email protected]>
2022-04-08 20:30 ` Nathan Bossart <[email protected]>
2022-07-14 18:34 ` Nathan Bossart <[email protected]>
2022-07-25 16:24 ` Nathan Bossart <[email protected]>
2025-01-21 03:31 ` Andy Fan <[email protected]>
2025-01-21 15:52 ` Nathan Bossart <[email protected]>
2025-01-21 16:13 ` Nathan Bossart <[email protected]>
2025-01-21 16:23 ` Andres Freund <[email protected]>
2025-01-22 01:14 ` Andy Fan <[email protected]>
2025-01-22 15:56 ` Nathan Bossart <[email protected]>
2025-01-22 16:21 ` Andres Freund <[email protected]>
2025-01-22 17:43 ` Nathan Bossart <[email protected]>
2025-01-22 18:00 ` Andres Freund <[email protected]>
2025-01-22 15:50 ` Nathan Bossart <[email protected]>
2023-12-27 14:55 [PATCH v5] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox