pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBackground vacuum
11+ messages / 6 participants
[nested] [flat]
* Background vacuum
@ 2007-05-10 04:38 Daniel Haensse <daniel.haensse@swissembedded.com>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Haensse @ 2007-05-10 04:38 UTC (permalink / raw)
To: pgsql-performance
Dear list,
I'm running postgres on a tomcat server. The vacuum is run every hour
(cronjob) which leads to a performance drop of the tomcat applications.
I played around with renice command and I think it is possible to reduce
this effect which a renice. The problem is how can I figure out the PID
of the postmaster performing the vacuum(automated)? Has anybody a nice
solution to change process priority? A shell script, maybe even for java?
best regards
Dani
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-10 05:05 Dan Harris <fbsd@drivefaster.net>
parent: Daniel Haensse <daniel.haensse@swissembedded.com>
0 siblings, 1 reply; 11+ messages in thread
From: Dan Harris @ 2007-05-10 05:05 UTC (permalink / raw)
To: pgsql-performance
Daniel Haensse wrote:
> Dear list,
>
> I'm running postgres on a tomcat server. The vacuum is run every hour
> (cronjob) which leads to a performance drop of the tomcat applications.
> I played around with renice command and I think it is possible to reduce
> this effect which a renice. The problem is how can I figure out the PID
> of the postmaster performing the vacuum(automated)? Has anybody a nice
> solution to change process priority? A shell script, maybe even for java?
>
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.
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.
-Dan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-11 00:10 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Dan Harris <fbsd@drivefaster.net>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Mayer @ 2007-05-11 00:10 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; Ron Mayer <rm_pg@cheapcomplexdevices.com>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-17 13:55 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrew Sullivan @ 2007-05-17 13:55 UTC (permalink / raw)
To: pgsql-performance
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@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-18 01:44 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Mayer @ 2007-05-18 01:44 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; Ron Mayer <rm_pg@cheapcomplexdevices.com>
Andrew Sullivan wrote:
> 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 paper referenced below gives a better explanation than I can.
Their conclusion was that on many real-life workloads (including
TPC-C and TPC-H like workloads) on many databases (including DB2
and postgresql) the benefits vastly outweighed the disadvantages.
> 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.
Sure. And in the paper they discussed the effect and found that
if you do have an OS scheduler than supports priority inheritance
the benefits are even bigger than those without it. But even
for OS's and scheduler combinations without it the benefits
were very significant.
>
>> 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.
Not necessarily. From the wide range of conditions the paper tested
I'd say it's more like quicksort - you need to be sure you avoid
theoretical pathological conditions that noone (that I can find)
has encountered in practice.
If you do know of such a workload, I (and imagine the authors
of that paper) would be quite interested.
Since they showed that the benefits are very real for both
TPC-C and TPC-H like workloads I think the burden of proof
is now more on the people warning of the (so far theoretical)
drawbacks.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-18 02:50 Greg Smith <gsmith@gregsmith.com>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Smith @ 2007-05-18 02:50 UTC (permalink / raw)
To: pgsql-performance
Ah, glad this came up again 'cause a problem here caused my original reply
to bounce.
On Thu, 10 May 2007, Ron Mayer wrote:
> 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
I spent a fair amount of time analyzing that paper recently, and found it
hard to draw any strong current conclusions from it. Locking and related
scalability issues are much better now than in the PG 7.3 they tested.
For example, from the paper:
"We find almost all lightweight locking in PostgreSQL fucntions to
serialize the I/O buffer pool and WAL activity...as a result, we attribute
all the lightweight lock waiting time for the above-listed locks to I/O."
Well, sure, if you classify those as I/O waits it's no surprise you can
darn near directly control them via CPU scheduling; I question the current
relevancy of this historical observation about the old code. I think it's
much easier to get into an honest I/O bound situation now with a TPC-C
like workload (they kind of cheated on that part too which is a whole
'nother discussion), especially with the even faster speeds of modern
processors, and then you're in a situation where CPU scheduling is not so
effective for indirectly controlling I/O prioritization.
Count me on the side that agrees adjusting the vacuuming parameters is the
more straightforward way to cope with this problem.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-18 03:09 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Mayer @ 2007-05-18 03:09 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>
Greg Smith wrote:
>
> Count me on the side that agrees adjusting the vacuuming parameters is
> the more straightforward way to cope with this problem.
Agreed for vacuum; but it still seems interesting to me that
across databases and workloads high priority transactions
tended to get through faster than low priority ones. Is there
any reason to believe that the drawbacks of priority inversion
outweigh the benefits of setting priorities?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-18 03:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-05-18 03:22 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> Greg Smith wrote:
>> Count me on the side that agrees adjusting the vacuuming parameters is
>> the more straightforward way to cope with this problem.
> Agreed for vacuum; but it still seems interesting to me that
> across databases and workloads high priority transactions
> tended to get through faster than low priority ones. Is there
> any reason to believe that the drawbacks of priority inversion
> outweigh the benefits of setting priorities?
Well, it's unclear, and anecdotal evidence is unlikely to convince
anybody. I had put some stock in the CMU paper, but if it's based
on PG 7.3 then you've got to **seriously** question its relevance
to the current code.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-18 19:21 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Ron Mayer @ 2007-05-18 19:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> Greg Smith wrote:
>>> Count me on the side that agrees adjusting the vacuuming parameters is
>>> the more straightforward way to cope with this problem.
>
>> Agreed for vacuum; but it still seems interesting to me that
>> across databases and workloads high priority transactions
>> tended to get through faster than low priority ones. Is there
>> any reason to believe that the drawbacks of priority inversion
>> outweigh the benefits of setting priorities?
>
> Well, it's unclear, and anecdotal evidence is unlikely to convince
> anybody. I had put some stock in the CMU paper, but if it's based
> on PG 7.3 then you've got to **seriously** question its relevance
> to the current code.
I was thinking the paper's results might apply more generally
to RDBMS-like applications since they did test 3 of them with
different locking behavior and different bottlenecks.
But true, I should stop bringing up 7.3 examples.
Anecdotally ;-) I've found renice-ing reports to help; especially
in the (probably not too uncommon case) where slow running
batch reporting queries hit different tables than interactive
reporting queries. I guess that's why I keep defending
priorities as a useful technique. It seems even more useful
considering the existence of schedulers that have priority
inheritance features.
I'll admit there's still the theoretical possibility that
it's a foot-gun so I don't mind people having to write
their own stored procedure to enable it - but I'd be
surprised if anyone could find a real world case where
priorities would do more harm than good.
Though, yeah, it'd be easy to construct an artificial
case that'd demonstrate priority inversion (i.e. have
a low priority process that takes a lock and sits
and spins on some CPU-intensive stored procedure
without doing any I/O).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-19 05:27 Greg Smith <gsmith@gregsmith.com>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Smith @ 2007-05-19 05:27 UTC (permalink / raw)
To: pgsql-performance
On Fri, 18 May 2007, Ron Mayer wrote:
> Anecdotally ;-) I've found renice-ing reports to help
Let's break this down into individual parts:
1) Is there enough CPU-intensive activity in some database tasks that they
can be usefully be controlled by tools like nice? Sure.
2) Is it so likely that you'll fall victim to a priority inversion problem
that you shouldn't ever consider that technique? No.
3) Does the I/O scheduler in modern OSes deal with a lot more things than
just the CPU? You bet.
4) Is vacuuming a challenging I/O demand? Quite.
Add all this up, and that fact that you're satisfied with how nice has
worked successfully for you doesn't have to conflict with an opinion that
it's not the best approach for controlling vacuuming. I just wouldn't
extrapolate your experience too far here.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Background vacuum
@ 2007-05-19 22:38 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 11+ messages in thread
From: Ron Mayer @ 2007-05-19 22:38 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>
Greg Smith wrote:
>
> Let's break this down into individual parts:
Great summary.
> 4) Is vacuuming a challenging I/O demand? Quite.
>
> Add all this up, and that fact that you're satisfied with how nice has
> worked successfully for you doesn't have to conflict with an opinion
> that it's not the best approach for controlling vacuuming. I just
> wouldn't extrapolate your experience too far here.
I wasn't claiming it's a the best approach for vacuuming.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-05-19 22:38 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-10 04:38 Background vacuum Daniel Haensse <daniel.haensse@swissembedded.com>
2007-05-10 05:05 ` Dan Harris <fbsd@drivefaster.net>
2007-05-11 00:10 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-05-17 13:55 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-05-18 01:44 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-05-18 02:50 ` Greg Smith <gsmith@gregsmith.com>
2007-05-18 03:09 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-05-18 03:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-18 19:21 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-05-19 05:27 ` Greg Smith <gsmith@gregsmith.com>
2007-05-19 22:38 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
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