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

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> We seem to be arguing both that making it larger would do absolutely
> nothing, and that making it larger would do something very bad.

The question to me is not so much whether it would do anything bad,
as whether it would do anything good. I think it's entirely unproven
that merely raising this limit would solve anything for Clemens'
use-case; it seems likely that additional behavioral changes would
be needed, and we've not seen what's under that rock.

What it definitely would do, however, is encourage people to raise
the setting, perhaps to larger values than are good for them. The
system is currently tuned on the assumption that wal_writer_delay
is a fractional second; we have no data on how it performs with
significantly larger settings.

Also, a closer look shows that the wal writer doesn't write anything
merely because it's woken up. Unless I'm misreading XLogBackgroundFlush,
it will write only (1) any completed full pages of WAL, or (2) data up
to an async commit. As for (1), writing out completed pages (which
are not going to get overwritten) doesn't seem like it should be a problem
for a flash drive. If it is a problem, maybe recompiling with a bigger
XLOG page size is indicated. As for (2), while I grant Clemens' claim
that he's willing to tolerate loss of async commits, it's a bit hard
to credit that he's willing to lose three hours worth of async commits.
Which is what he'd be risking with wal_writer_delay set to 1h.

There's also the existing "hibernate" mode, which has unclear interactions
with a desire to put off writes for such long intervals.

Basically, this proposal needs a lot more evidence than has been
provided, IMO.

regards, tom lane