Re: BUG #9161: wal_writer_delay is limited to 10s

On 2014-02-14 23:03:44 +0100, Clemens Eisserer wrote:
> Hi Tom,
>
> Thanks for taking a look at my bug report.
>
> > But if you're generating WAL, it's going to get written anyway
> > whenever a transaction commits.
>
> As far as I understood, this is only the case with
> synchronous_commit=on, which is the reason why I turned synchrous
> commit off.

The WAL writer will be woken up anyway if a transaction commits
(c.f. XLogSetAsyncXactLSN()).

> > You could forestall that with fsync=off,
> > perhaps, but if you do that then the WAL writer won't fsync either, so
> > it shouldn't matter how often it wakes up.  Checkpoints will force WAL
> > output more often than once per hour by default, too.  So I'm wondering
> > exactly what combination of other settings you envision using this with,
> > and what's the workload of the database server.
>
> Because the system should be crash-resistent, my goal would be to have
> postgresql issue fsyncs only every few minutes instead of every 10s,
> by running the wal writer only every now and then (I know there is
> only very little data in the WAL anyway).
>
> For now I have a single insert transaction every 10s with
> synchronous_commit=off, however because the wal writer weaks up every
> 10s data are immediatly written to the SD card, dramatically reducing
> its lifespan.

The WAL writer shouldn't write anything if there's nothing to
write. Maybe you have checkpoint_timeout set to something low and it's
performing checkpoints regularly?

You might also rethink the quality of the media you're using...

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services