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.