Thanks for your explanations.
Tom Lane wrote:
Richard Neill <rn214@xxxxxxxxx> 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.
That makes sense. I think that 64 is sane - it means crash-recovery
takes less than 1 minute, yet we aren't seeing the warning that
checkpoints are too frequent.
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.
I see why it might not work well - priority inversion etc.
What I was trying to achieve is to say that vacuum can have all the
spare idle CPU/IO that's available, but must *immediately* back off when
something else needs the CPU/IO/Locks.
For example,
nice -n 20 yes > /dev/null
ionice -c 3 dd if=/dev/zero > tmp.del
will both get quite a lot of work done on a medium-loaded system (try
this on your own laptop), but have zero impact on other processes.
On the other hand, changing vacuum_cost_delay means that vacuum runs
slowly even if the CPU is otherwise idle; yet it still impacts on the
responsiveness of some queries.
Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
