Home Explore Blog CI



postgresql

14th chunk of `doc/src/sgml/wal.sgml`
308f3792d57825fe94eb503d46b26a291521c3764bf5535b0000000100000fa4
 sessions that reach the point where they need to
   flush their commit records during the window in which the previous
   flush operation (if any) is occurring.  At higher client counts a
   <quote>gangway effect</quote> tends to occur, so that the effects of group
   commit become significant even when <varname>commit_delay</varname> is
   zero, and thus explicitly setting <varname>commit_delay</varname> tends
   to help less.  Setting <varname>commit_delay</varname> can only help
   when (1) there are some concurrently committing transactions, and (2)
   throughput is limited to some degree by commit rate; but with high
   rotational latency this setting can be effective in increasing
   transaction throughput with as few as two clients (that is, a single
   committing client with one sibling transaction).
  </para>

  <para>
   The <xref linkend="guc-wal-sync-method"/> parameter determines how
   <productname>PostgreSQL</productname> will ask the kernel to force
   <acronym>WAL</acronym> updates out to disk.
   All the options should be the same in terms of reliability, with
   the exception of <literal>fsync_writethrough</literal>, which can sometimes
   force a flush of the disk cache even when other options do not do so.
   However, it's quite platform-specific which one will be the fastest.
   You can test the speeds of different options using the <xref
   linkend="pgtestfsync"/> program.
   Note that this parameter is irrelevant if <varname>fsync</varname>
   has been turned off.
  </para>

  <para>
   Enabling the <xref linkend="guc-wal-debug"/> configuration parameter
   (provided that <productname>PostgreSQL</productname> has been
   compiled with support for it) will result in each
   <function>XLogInsertRecord</function> and <function>XLogFlush</function>
   <acronym>WAL</acronym> call being logged to the server log. This
   option might be replaced by a more general mechanism in the future.
  </para>

  <para>
   There are two internal functions to write WAL data to disk:
   <function>XLogWrite</function> and <function>issue_xlog_fsync</function>.
   When <xref linkend="guc-track-wal-io-timing"/> is enabled, the total
   amounts of time <function>XLogWrite</function> writes and
   <function>issue_xlog_fsync</function> syncs WAL data to disk are counted as
   <varname>write_time</varname> and <varname>fsync_time</varname> in
   <xref linkend="pg-stat-io-view"/> for the <varname>object</varname>
   <literal>wal</literal>, respectively.
   <function>XLogWrite</function> is normally called by
   <function>XLogInsertRecord</function> (when there is no space for the new
   record in WAL buffers), <function>XLogFlush</function> and the WAL writer,
   to write WAL buffers to disk and call <function>issue_xlog_fsync</function>.
   <function>issue_xlog_fsync</function> is normally called by
   <function>XLogWrite</function> to sync WAL files to disk.
   If <varname>wal_sync_method</varname> is either
   <literal>open_datasync</literal> or <literal>open_sync</literal>,
   a write operation in <function>XLogWrite</function> guarantees to sync written
   WAL data to disk and <function>issue_xlog_fsync</function> does nothing.
   If <varname>wal_sync_method</varname> is either <literal>fdatasync</literal>,
   <literal>fsync</literal>, or <literal>fsync_writethrough</literal>,
   the write operation moves WAL buffers to kernel cache and
   <function>issue_xlog_fsync</function> syncs them to disk. Regardless
   of the setting of <varname>track_wal_io_timing</varname>, the number
   of times <function>XLogWrite</function> writes and
   <function>issue_xlog_fsync</function> syncs WAL data to disk are also
   counted as <varname>writes</varname> and <varname>fsyncs</varname>
   in <structname>pg_stat_io</structname> for the <varname>object</varname>
   <literal>wal</literal>, respectively.
  </para>

  <para>
   The <xref linkend="guc-recovery-prefetch"/> parameter can be used to reduce
   I/O wait times during recovery by instructing

Title: WAL Configuration and Optimization
Summary
The configuration parameters for Write-Ahead Logging (WAL) in PostgreSQL, such as commit_delay, wal_sync_method, and track_wal_io_timing, can be adjusted to optimize performance, reliability, and disk I/O, and tools like pg_test_fsync can be used to measure the effectiveness of these settings, and other parameters like recovery_prefetch can be used to reduce I/O wait times during recovery.