Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush?
От | Adrian Klaver |
---|---|
Тема | Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush? |
Дата | |
Msg-id | 53265138-db70-32db-7de6-b668275c4eb9@aklaver.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] Which process is actually doing the WAL writes/callsXLogFlush? (Daniel Westermann <daniel.westermann@dbi-services.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: