Search Postgresql Archives

Re: Which process is actually doing the WAL writes/calls XLogFlush?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux