Re: Background vacuum

On Thu, May 10, 2007 at 05:10:56PM -0700, Ron Mayer wrote:
> One way is to write astored procedure that sets it's own priority.
> An example is here:
> http://weblog.bignerdranch.com/?p=11

Do you have evidence to show this will actually work consistently?
The problem with doing this is that if your process is holding a lock
that prevents some other process from doing something, then your
lowered priority actually causes that _other_ process to go slower
too. This is part of the reason people object to the suggestion that
renicing a single back end will help anything.

> 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)).

It is likely that in _some_ cases, you can get this benefit, because
you don't have contention issues. The explanation for the good lock
performance by Postgres on the TPC-C tests they were using is
PostgreSQL's MVCC: Postgres locks less. The problem comes when you
have contention, and in that case, CPU scheduling will really hurt.

This means that, to use CPU scheduling safely, you have to be really
sure that you know what the other transactions are doing.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier