Richard Neill <rn214@cam.ac.uk> writes:
> Now, I understand that increasing checkpoint_segments is generally a
> good thing (subject to some limit), but doesn't that just mean that
> instead of say a 1 second outage every minute, it's a 10 second outage
> every 10 minutes?
In recent PG versions you can spread the checkpoint I/O out over a
period of time, so it shouldn't be an "outage" at all, just background
load. Other things being equal, a longer checkpoint cycle is better
since it improves the odds of being able to coalesce multiple changes
to the same page into a single write. The limiting factor is your
threshold of pain on how much WAL-replay work would be needed to recover
after a crash.
> Is it possible (or even sensible) to do a manual vacuum analyze with
> nice/ionice?
There's no support for that in PG. You could try manually renice'ing
the backend that's running your VACUUM but I'm not sure how well it
would work; there are a number of reasons why it might be
counterproductive. Fooling with the vacuum_cost_delay parameters is the
recommended way to make a vacuum run slower and use less of the machine.
regards, tom lane