Re: checkpoints/bgwriter tuning verification

On Thu, Oct 29, 2009 at 10:24 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> There are two ways that that writes can hang:
>
> 1) You've gotten to the point in the checkpoint cycle where it's calling
> fsync to flush everything out of the filesystem.  At this point you could
> potentially have a big chunk of data that needs to go out to disk, and any
> other client that needs to write something (or needs a resource locked by a
> writer) is stuck behind that.
>
> 2) The entire OS write cache is filled.  Once that happens, if a client
> tries to write something else, the OS will force it to nap until there's
> space again to hold that write.

I'm on FreeBSD. I've got my shared_buffers set to 1200MB on a box
with 20GB of RAM, and fast external RAID on a fibre channel.

Is that perhaps too low to keep the "hot" bits of my DB in memory? I
hear so much conflicting advice on how big to set shared_buffers...

I've tweaked the checkpoint target to 0.8 and segments to 64. Is 15
minutes reasonable for the timeout value?

I really don't think I'm saturating the disk bandwidth. I see spikes
of 18MB/s or more, but it usually hovers at under 3MB/s according to
iostat output.