Thread: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
[GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
From
Daniel Westermann
Date:
Hi all,
as I did not find the answer in the documentation: Which background process is actually doing the writes/flushes to the WAL? In the docs (https://www.postgresql.org/docs/10/static/wal-configuration.html) it is explained which internal functions are responsible for this: XLogInsertRecord and XLogFlush but who does call them, especially the flush? Is it the process for the user connection itself then which calls this after commit?
Initially I thought it is the job of the wal_writer but according to this blog post (http://www.cybertec.at/postgresql-writer-and-wal-writer-processes-explained/) this seems not to be true, right? Why do I need the wal_writer at all then when synchronous_commit is set to something else than off?
Thanks for your help
Daniel
Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?
From
Adrian Klaver
Date:
On 06/23/2017 05:50 AM, Daniel Westermann wrote: > Hi all, > > as I did not find the answer in the documentation: Which background > process is actually doing the writes/flushes to the WAL? In the docs > (https://www.postgresql.org/docs/10/static/wal-configuration.html) it is > explained which internal functions are responsible for this: > XLogInsertRecord and XLogFlush but who does call them, especially the > flush? Is it the process for the user connection itself then which calls > this after commit? For all the details see here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/transam/README;h=4ae4715339e707ab5ed879a628aa96b73002ef43;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7 > > Initially I thought it is the job of the wal_writer but according to > this blog post > (http://www.cybertec.at/postgresql-writer-and-wal-writer-processes-explained/) > this seems not to be true, right? Why do I need the wal_writer at all > then when synchronous_commit is set to something else than off? See here: https://www.postgresql.org/docs/9.6/static/wal-async-commit.html > > Thanks for your help > Daniel -- Adrian Klaver adrian.klaver@aklaver.com
Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
From
Daniel Westermann
Date:
>On 06/23/2017 05:50 AM, Daniel Westermann wrote: >> Hi all, >> >> as I did not find the answer in the documentation: Which background >> process is actually doing the writes/flushes to the WAL? In the docs >> (https://www.postgresql.org/docs/10/static/wal-configuration.html) it is >> explained which internal functions are responsible for this: >> XLogInsertRecord and XLogFlush but who does call them, especially the >> flush? Is it the process for the user connection itself then which calls >> this after commit? > >For all the details see here: > >https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/transam/README;h=4ae4715339e707ab5ed879a628aa96b73002ef43;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7 > >> >> Initially I thought it is the job of the wal_writer but according to >> this blog post >> (http://www.cybertec.at/postgresql-writer-and-wal-writer-processes-explained/) >> this seems not to be true, right? Why do I need the wal_writer at all >> then when synchronous_commit is set to something else than off? > >See here: > >https://www.postgresql.org/docs/9.6/static/wal-async-commit.html
Thanks, Adrian
It is clear now for the asynchronous stuff and wal_writer.
But I still did not figure out (or I am just not able to understand it from the README linked above)
which process is actually doing the write to the wal when you have synchronous commit set to on. Can
someone put some light on this?
Thanks
Daniel
Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
From
"David G. Johnston"
Date:
On Tuesday, June 27, 2017, Daniel Westermann <daniel.westermann@dbi-services.com> wrote:
>On 06/23/2017 05:50 AM, Daniel Westermann wrote:But I still did not figure out (or I am just not able to understand it from the README linked above)
which process is actually doing the write to the wal when you have synchronous commit set to on. Can
someone put some light on this?
The one in which the "commit" command was executed. i.e., user session processes.
David J.
Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
From
Adrian Klaver
Date:
On 06/27/2017 11:19 AM, Daniel Westermann wrote: >>On 06/23/2017 05:50 AM, Daniel Westermann wrote: >>> Hi all, > > Thanks, Adrian > It is clear now for the asynchronous stuff and wal_writer. > But I still did not figure out (or I am just not able to understand it from the README linked above) > which process is actually doing the write to the wal when you have synchronous commit set to on. Can > someone put some light on this? ps axjf /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data \_ postgres: logger process \_ postgres: checkpointer process \_ postgres: writer process \_ postgres: wal writer process \_ postgres: autovacuum launcher process \_ postgres: stats collector process \_ postgres: bgworker: pglogical supervisor AFAIK the wal writer process. > > Thanks > Daniel > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?
From
"David G. Johnston"
Date:
On 06/27/2017 11:19 AM, Daniel Westermann wrote:
>
> Thanks, Adrian
> It is clear now for the asynchronous stuff and wal_writer.
> But I still did not figure out (or I am just not able to understand it from the README linked above)
> which process is actually doing the write to the wal when you have synchronous commit set to on. Can
> someone put some light on this?
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 the 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.
David J.
Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?
From
Daniel Westermann
Date:
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).
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
Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?
From
Adrian Klaver
Date:
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