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

On Fri, Feb 14, 2014 at 7:50 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > 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...

Indeed, from my experience, SD cards are terribly unreliable because
they have terrible write leveling (if at all). You're better off using
a USB stick if you have a USB port. It may cost you a bit more power,
but it will both last a lot more, and perform better.