Re: What exactly is postgres doing during INSERT/UPDATE ?

On Sat, Aug 29, 2009 at 6:26 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Fri, Aug 28, 2009 at 8:19 PM, Jeff Janes<jeff(dot)janes(at)gmail(dot)com> wrote:
> >> ---------- Forwarded message ----------
> >> From: Joseph S <jks(at)selectacast(dot)net>
> >> To: Greg Smith <gsmith(at)gregsmith(dot)com>, pgsql-performance(at)postgresql(dot)org
> >> Date: Fri, 28 Aug 2009 10:25:10 -0400
> >> Subject: Re: What exactly is postgres doing during INSERT/UPDATE ?
> >> Greg Smith wrote:
> >>
> >>> The main two things you can do to improve this on the database side:
> >>>
> >>> -Increase checkpoint_segments, which reduces how often updated data has
> >>> to be flushed to disk
> >>
> >> It fsync is turned off, does this matter so much?
> >
> > It still matters. The kernel is only willing to have so much dirty data
> > sitting in the disk cache. Once it reaches that limit, user processes
> doing
> > writes start blocking while the kernel flushes stuff on their behalf.
>
> it doesn't matter nearly as much though.

True, but it matters enough that it ought not be ignored. I've run into it
more than once, and I haven't been at this very long.

> if you are outrunning the
> o/s write cache with fsync off, then it's time to start looking at new
> hardware.

Or to start looking at tweaking the kernel VM settings. The kernel doesn't
always handle these situations as gracefully as it could, and might produce
a practical throughput that is much less than the theoretical one. But
reducing the frequency of checkpoints is easier than either of these, and
cheaper than buying new hardware. I don't see why the hardest and most
expensive option would be the first choice.

Jeff