Re: Why is wal_writer_delay limited to 10s?

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Clemens Eisserer <linuxhippy(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Why is wal_writer_delay limited to 10s?
Date: 2013-12-27 21:50:03
Message-ID: 52BDF60B.2000908@archidevsys.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 28/12/13 02:01, Clemens Eisserer wrote:
> Hi,
>
> Just to be curious, why is wal_writer_delay limited to 10s?
> I am using postgresql in an embedded environment where every 10s
> sensor values are logged and even with "synchronous_commit = off" and
> wal_writer_delay=10000 this burns quite a lot of nand cycles. For me
> it wouldn't hurt loosing minutes of data - it is only important that
> the database is in a consistent state after power loss.
>
> Thanks, Clemens
>
> PS: It is really impressive how flexible and powerful postgresql is.
> I am using it on small TP-Link OpenWRT router (32m ram, 400mhz MIPS),
> on a raspberry pi as well as on larger servers for "real" database
> stuff with huge Hibernate-generated queries. Whenever/weherever I use
> postgresql, it does an excellent job and is rock-solid.
> Thanks a lot for this impressive piece of work :)
>
>
"BECAUSE NO ONE WOULD EVER WANT LESS THAN 10 SECONDS..." was probably an
implicit assumption?

The truth be told, I don't know! However, I am sure the real experts
will weigh in soon.

Probably the assumption is that the WAL is on spinning rust, using SSD's
might alter the timing equation. So it would be a good idea to tell us
what storage you are using, along with anything else you might think
could be relevant.

It will be interesting to see what the real reasons are.

I suspect that are valid technical reasons to suspect problems of using
too short an interval, and people played safe by making the minimum time
sufficiently greater to avoid such problems.

You have the source, so you could reduce the minimum and see what happens.

It may be that you need a different way of initiating it.

Cheers,
Gavin

Running it on a raspberry pi is impressive - but way back, I remember
IBM once recommending at least one megabyte for running their database
on a mainframe. All (not just the smart) phones now have more memory
than any of the mainframes I programmed in the early 1980's!

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joseph Kregloh 2013-12-27 21:56:53 Re: pg_upgrade & tablespaces
Previous Message Chris Curvey 2013-12-27 21:18:12 Re: ON_ERROR_EXIT and interactive mode (or, how to force interactive mode off)