On Sat, Aug 29, 2009 at 6:26 AM, Merlin Moncure
<mmoncure@gmail.com> wrote:
On Fri, Aug 28, 2009 at 8:19 PM, Jeff Janes<
jeff.janes@gmail.com> wrote:
>> ---------- Forwarded message ----------
>> From: Joseph S <
jks@selectacast.net>
>> To: Greg Smith <
gsmith@gregsmith.com>,
pgsql-performance@postgresql.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.
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