From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Dan Harris <fbsd@drivefaster.net>
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: Background vacuum
Date: Thu, 10 May 2007 17:10:56 -0700
Message-ID: <4643B490.2040704@cheapcomplexdevices.com> (raw)
In-Reply-To: <4642A811.9010703@drivefaster.net>
References: <4642A1B3.6030009@swissembedded.com>
<4642A811.9010703@drivefaster.net>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rm_pg@cheapcomplexdevices.com, fbsd@drivefaster.net
Subject: Re: Background vacuum
In-Reply-To: <4643B490.2040704@cheapcomplexdevices.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox