Re: Configuring PostgreSQL to minimize impact of checkpoints

The king of statistics in these cases, is probably vmstat. one can
drill down on specific things from there, but first you should send
some vmstat output.

Reducing cache -> reducing IO suggests to me the OS might be paging out
shared buffers. This is indicated by activity in the "si" and "so"
columns of vmstat. intentional disk activity by the
applciation(postgres) shows up in the "bi" and "bo" columns.

If you are having a "write storm" or bursty writes that's burying
performance, a scsi raid controler with writeback cache will greatly
improve the situation, but I do believe they run around $1-2k. If
it's write specific problem, the cache matters more than the striping,
except to say that write specfic perf problems should avoid raid5

please send the output of "vmstat 10" for about 10 minutes, spanning
good performance and bad performance.

On May 11, 2004, at 9:52 AM, jao(at)geophile(dot)com wrote:

> Quoting Rob Fielding <rob(at)dsvr(dot)net>:
>
>> Assuming you're running with optimal schema and index design (ie
>> you're
>> not doing extra work unnecessarily), and your backend has
>> better-then-default config options set-up (plenty of tips around
>> here),
>> then disk arrangement is critical to smoothing the ride.
>
> The schema and queries are extremely simple. I've been experimenting
> with config options. One possibility I'm looking into is whether
> shared_buffers is too high, at 12000. We have some preliminary evidence
> that setting it lower (1000) reduces the demand for IO bandwidth to
> a point where the spikes become almost tolerable.
>
>> First tip would to take your pg_xlog and put it on another disk (and
>> channel).
>
> That's on my list of things to try.
>
>> Next if you're running a journalled fs, get that journal off
>> onto another disk (and channel). Finally, get as many disks for the
>> data
>> store and spread the load across spindles.
>
> Dumb question: how do I spread the data across spindles? Do you have
> a pointer to something I could read?
>
> Jack Orenstein
>
> ----------------------------------------------------------------
> This message was sent using IMP, the Internet Messaging Program.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>