Home Explore Blog CI



postgresql

12th chunk of `doc/src/sgml/wal.sgml`
81b57bf9bbe2264af5c4435cf6fdc6ec93c11ab0ecec0b5e0000000100000fa2
 about to exceed <xref linkend="guc-max-wal-size"/>
   However, because of limitations on when a restartpoint can be performed,
   <varname>max_wal_size</varname> is often exceeded during recovery,
   by up to one checkpoint cycle's worth of WAL.
   (<varname>max_wal_size</varname> is never a hard limit anyway, so you should
   always leave plenty of headroom to avoid running out of disk space.)
   The <structfield>restartpoints_done</structfield> counter in the
   <link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
   view counts the restartpoints that have really been performed.
  </para>

  <para>
   In some cases, when the WAL size on the primary increases quickly,
   for instance during massive <command>INSERT</command>,
   the <structfield>restartpoints_req</structfield> counter on the standby
   may demonstrate a peak growth.
   This occurs because requests to create a new restartpoint due to increased
   WAL consumption cannot be performed because the safe checkpoint record
   since the last restartpoint has not yet been replayed on the standby.
   This behavior is normal and does not lead to an increase in system resource
   consumption.
   Only the <structfield>restartpoints_done</structfield>
   counter among the restartpoint-related ones indicates that noticeable system
   resources have been spent.
  </para>

  <para>
   There are two commonly used internal <acronym>WAL</acronym> functions:
   <function>XLogInsertRecord</function> and <function>XLogFlush</function>.
   <function>XLogInsertRecord</function> is used to place a new record into
   the <acronym>WAL</acronym> buffers in shared memory. If there is no
   space for the new record, <function>XLogInsertRecord</function> will have
   to write (move to kernel cache) a few filled <acronym>WAL</acronym>
   buffers. This is undesirable because <function>XLogInsertRecord</function>
   is used on every database low level modification (for example, row
   insertion) at a time when an exclusive lock is held on affected
   data pages, so the operation needs to be as fast as possible.  What
   is worse, writing <acronym>WAL</acronym> buffers might also force the
   creation of a new WAL segment, which takes even more
   time. Normally, <acronym>WAL</acronym> buffers should be written
   and flushed by an <function>XLogFlush</function> request, which is
   made, for the most part, at transaction commit time to ensure that
   transaction records are flushed to permanent storage. On systems
   with high WAL output, <function>XLogFlush</function> requests might
   not occur often enough to prevent <function>XLogInsertRecord</function>
   from having to do writes.  On such systems
   one should increase the number of <acronym>WAL</acronym> buffers by
   modifying the <xref linkend="guc-wal-buffers"/> parameter.  When
   <xref linkend="guc-full-page-writes"/> is set and the system is very busy,
   setting <varname>wal_buffers</varname> higher will help smooth response times
   during the period immediately following each checkpoint.
  </para>

  <para>
   The <xref linkend="guc-commit-delay"/> parameter defines for how many
   microseconds a group commit leader process will sleep after acquiring a
   lock within <function>XLogFlush</function>, while group commit
   followers queue up behind the leader.  This delay allows other server
   processes to add their commit records to the WAL buffers so that all of
   them will be flushed by the leader's eventual sync operation.  No sleep
   will occur if <xref linkend="guc-fsync"/> is not enabled, or if fewer
   than <xref linkend="guc-commit-siblings"/> other sessions are currently
   in active transactions; this avoids sleeping when it's unlikely that
   any other session will commit soon.  Note that on some platforms, the
   resolution of a sleep request is ten milliseconds, so that any nonzero
   <varname>commit_delay</varname> setting between 1 and 10000
   microseconds would

Title: WAL Functions and Performance Optimization
Summary
The PostgreSQL WAL functions, XLogInsertRecord and XLogFlush, manage the writing and flushing of WAL buffers, and parameters like wal_buffers, commit_delay, and fsync can be adjusted to optimize performance, especially in systems with high WAL output or busy workloads, to prevent delays and improve response times.