Re: Background vacuum

Dan Harris wrote:
> Daniel Haensse wrote:
>> Has anybody a nice
>> solution to change process priority? A shell script, maybe even for java?

One way is to write astored procedure that sets it's own priority.
An example is here:
http://weblog.bignerdranch.com/?p=11

> While this may technically work, I think it lacks a key point. 'nice' (
> at least the versions I'm familiar with ) do not adjust I/O priority.
> VACUUM is bogging things down because of the extra strain on I/O. CPU
> usage shouldn't really be much of a factor.

Actually, CPU priorities _are_ an effective way of indirectly scheduling
I/O priorities.

This paper studied both CPU and lock priorities on a variety
of databases including PostgreSQL.

http://www.cs.cmu.edu/~bianca/icde04.pdf

" By contrast, for PostgreSQL, lock scheduling is not as
effective as CPU scheduling (see Figure 4(c)).
...
The effectiveness of CPU-Prio for TPC-C on
PostgreSQL is surprising, given that I/O (I/O-related
lightweight locks) is its bottleneck. Due to CPU prioritization,
high-priority transactions are able to request I/O resources
before low-priority transactions can. As a result,
high-priority transactions wait fewer times (52% fewer) for
I/O, and when they do wait, they wait behind fewer transactions
(43% fewer). The fact that simple CPU prioritization
is able to improve performance so significantly suggests that
more complicated I/O scheduling is not always necessary.
...
For TPC-C on MVCC DBMS, and in particular PostgreSQL,
CPU scheduling is most effective, due to its ability
to indirectly schedule the I/O bottleneck.
...
For TPC-C running on PostgreSQL, the simplest CPU scheduling
policy (CPU-Prio) provides a factor of 2 improvement
for high-priority transactions, while adding priority inheritance
(CPU-Prio-Inherit) provides a factor of 6 improvement
while hardly penalizing low-priority transactions.
Preemption (P-CPU) provides no appreciable benefit
over CPU-Prio-Inherit
"

> Instead, I would recommend looking at vacuum_cost_delay and the related
> settings to make vacuum lower priority than the queries you care about.
> This should be a cleaner solution for you.

Yeah, that's still true.