Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?

On 06/27/2017 11:47 PM, Daniel Westermann wrote:
>
>     AFAIK the wal writer process.
>
>
> ​>Um, no.  "Synchronous" means that the caller has to wait for the
> result to appear before it can move on.  "Asynchronous" means that >he
> caller can issue the instruction and immediately move on.  I guessing
> here but while usually the caller would have to provide >callback hook
> to get the answer in the future in this case the caller is assuming a
> positive result and doesn't listen for a response.  It is >for the
> asynchronous mode ​that wal_writer exists.  In synchronous mode it would
> be somewhat inefficient to hand-off/leave the work to >a separate
> process to perform while the main process remains idle - better to just
> have the main process do it.  Its not a total win since >the WAL file
> takes on the inherent contention.
>  >
>  >The linked readme (and I suspect much of the docs) was written under
> the assumption that the calling session performs all work not >otherwise
> explicitly designated as being handled by a separate process.  That is
> why you cannot find an affirmative answer to the >posed question - it is
> taken as something having been previously learned (or deduced in my case
> - the others links being illustrative >too).
>
>  >Now, I'm still just going off of human documentation and not the
> actual code - but my confidence level is quite high.
>
> Seems I am not the only one who is confused here. To summarize: When
> synchronous_commit is set to on it is the user session that does the
> write to the wal. When synchronous_commit is set to off (which means
> asynchronous commit) it is the job of the wal_writer to (batch) commit
> what needs to be commited since the last flush (can be configured with
> wal_writer_delay).

The wal_writer is always running regardless of the synchronous_commit
setting. What turning it off does is allow Postgres to return a
transaction completed signal before the WAL info is actually written to
the disk. This means there is a chance of data loss should the
machine/server crash between the time Postgres said the transaction was
completed and the time the WAL records for that transaction hit the disk:

https://www.postgresql.org/docs/9.6/static/wal-async-commit.html

"If the database crashes during the risk window between an asynchronous
commit and the writing of the transaction's WAL records, then changes
made during that transaction will be lost. The duration of the risk
window is limited because a background process (the "WAL writer")
flushes unwritten WAL records to disk every wal_writer_delay
milliseconds. The actual maximum duration of the risk window is three
times wal_writer_delay because the WAL writer is designed to favor
writing whole pages at a time during busy periods."

This might help:

https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-WAL-WRITER-DELAY

Now there are caveats. This does not apply to UNLOGGED tables:

https://www.postgresql.org/docs/9.6/static/sql-createtable.html:
"
UNLOGGED

     If specified, the table is created as an unlogged table. Data
written to unlogged tables is not written to the write-ahead log (see
Chapter 30), which makes them considerably faster than ordinary tables.
However, they are not crash-safe: an unlogged table is automatically
truncated after a crash or unclean shutdown. The contents of an unlogged
table are also not replicated to standby servers. Any indexes created on
an unlogged table are automatically unlogged as well.
"

Also synchronous_commit has different behavior if
synchronous_standby_names is non-empty:

https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT

The important thing to know from all of the above is Postgres writes to
WAL before it writes to the data files(UNLOGGED tables excepted).


>
> Maybe it is worth to enhance the documentation for this, at least for
> synchronous_commit=true? The asynchronous behavior is well documented
> here: https://www.postgresql.org/docs/current/static/wal-async-commit.html.
>
> Again, thanks David and Adrian for your help
> Kind Regards
> Daniel
>


--
Adrian Klaver
adrian.klaver@aklaver.com