Re: COMMIT NOWAIT Performance Option

On Mon, 2007-02-26 at 21:20 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
>
> > The interesting point is you can have a huge data grinding app, yet with
> > other tables alongside that hold more important data. In that scenario,
> > 90% of the data would be COMMIT NOWAIT, whilst the small important data
> > is safe.
>
> Does this means that the regular COMMIT is slower because it has to
> force more data to disk? I imagine that this isn't the case, because
> it's not the write itself that's slow; rather, it's the wait until the
> fsync on WAL is reported complete. However, did you measure this?

No, I've not measured that aspect specifically. But the overall effect
depends upon your hardware. (Laptops work great :-)

A COMMIT will write all of WAL, no matter how much that is. This is no
different from now, where a normal COMMIT executing while another
backend is doing a work may have to write more than just its own WAL.

We actually consider this a good thing: piggyback writes go out of their
way to ensure we write as much WAL as possible in one go.

There is no doubt a point where the fsync delay has been set wrong *and*
there are so few normal COMMITs that they do become slower. But that
slower isn't the same as "starved", just the result of having to do the
work of others. The WALwriter will be doing the heavy lifting, if set
correctly.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com