Re: Analyse without locking?

Thanks for your explanations.

Tom Lane wrote:
> Richard Neill <rn214(at)cam(dot)ac(dot)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.

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