pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: Priority to a mission critical transaction
17+ messages / 9 participants
[nested] [flat]
* RES: Priority to a mission critical transaction
@ 2006-11-28 19:01 Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
0 siblings, 2 replies; 17+ messages in thread
From: Carlos H. Reimer @ 2006-11-28 19:01 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-performance
Hi,
There is an article about "Lowering the priority of a PostgreSQL query"
(http://weblog.bignerdranch.com/?p=11) that explains how to use the
setpriority() to lower PostgreSQL processes.
I?m wondering how much effective it would be for i/o bound systems.
Will the setpriority() system call affect i/o queue too?
Reimer
> -----Mensagem original-----
> De: Jim C. Nasby [mailto:jim@nasby.net]
> Enviada em: domingo, 26 de novembro de 2006 22:52
> Para: Brad Nicholson
> Cc: carlos.reimer@opendb.com.br; pgsql-performance@postgresql.org
> Assunto: Re: [PERFORM] Priority to a mission critical transaction
>
>
> On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote:
> > On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote:
> > > Hi,
> > >
> > > We have an application that is mission critical, normally very fast,
> > > but when an I/O or CPU bound transaction appears, the mission critical
> > > application suffers. Is there a way go give some kind of priority to
> > > this kind of application?
> > > Reimer
> >
> >
> > Not that I'm aware of. Depending on what the problems transactions are,
> > setting up a replica on a separate machine and running those
> > transactions against the replica might be the solution.
>
> The BizGres project has been working on resource quotas, which might
> eventually evolve to what you're looking for.
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
@ 2006-11-28 19:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2006-11-28 19:07 UTC (permalink / raw)
To: carlos.reimer@opendb.com.br; +Cc: Jim C. Nasby <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> There is an article about "Lowering the priority of a PostgreSQL query"
> (http://weblog.bignerdranch.com/?p=11) that explains how to use the
> setpriority() to lower PostgreSQL processes.
> I?m wondering how much effective it would be for i/o bound systems.
That article isn't worth the electrons it's written on. Aside from the
I/O point, there's a little problem called "priority inversion". See
the archives for (many) past discussions of nice'ing backends.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-11-28 23:44 ` Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-30 04:29 ` Re: RES: Priority to a mission critical transaction Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 17+ messages in thread
From: Bruce Momjian @ 2006-11-28 23:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: carlos.reimer@opendb.com.br, "Jim C. Nasby" <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
Someone should ask them to remove the article.
---------------------------------------------------------------------------
Tom Lane wrote:
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > There is an article about "Lowering the priority of a PostgreSQL query"
> > (http://weblog.bignerdranch.com/?p=11) that explains how to use the
> > setpriority() to lower PostgreSQL processes.
>
> > I?m wondering how much effective it would be for i/o bound systems.
>
> That article isn't worth the electrons it's written on. Aside from the
> I/O point, there's a little problem called "priority inversion". See
> the archives for (many) past discussions of nice'ing backends.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
@ 2006-11-29 01:20 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ron Mayer @ 2006-11-29 01:20 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>
Before asking them to remove it, are we sure priority inversion
is really a problem?
I thought this paper: http://www.cs.cmu.edu/~bianca/icde04.pdf
did a pretty good job at studying priority inversion on RDBMs's
including PostgreSQL on various workloads (TCP-W and TCP-C) and
found that the benefits of setting priorities vastly outweighed
the penalties of priority inversion across all the databases and
all the workloads they tested.
Bruce Momjian wrote:
> Someone should ask them to remove the article.
>
> ---------------------------------------------------------------------------
>
> Tom Lane wrote:
>> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
>>> There is an article about "Lowering the priority of a PostgreSQL query"
>>> (http://weblog.bignerdranch.com/?p=11) that explains how to use the
>>> setpriority() to lower PostgreSQL processes.
>>> I?m wondering how much effective it would be for i/o bound systems.
>> That article isn't worth the electrons it's written on. Aside from the
>> I/O point, there's a little problem called "priority inversion". See
>> the archives for (many) past discussions of nice'ing backends.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2006-11-29 13:25 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Re: RES: Priority to a mission critical transaction Mark Lewis <mark.lewis@mir3.com>
0 siblings, 1 reply; 17+ messages in thread
From: Brian Hurt @ 2006-11-29 13:25 UTC (permalink / raw)
To: pgsql-performance
Ron Mayer wrote:
>Before asking them to remove it, are we sure priority inversion
>is really a problem?
>
>I thought this paper: http://www.cs.cmu.edu/~bianca/icde04.pdf
>did a pretty good job at studying priority inversion on RDBMs's
>including PostgreSQL on various workloads (TCP-W and TCP-C) and
>found that the benefits of setting priorities vastly outweighed
>the penalties of priority inversion across all the databases and
>all the workloads they tested.
>
>
>
I have the same question. I've done some embedded real-time
programming, so my innate reaction to priority inversions is that
they're evil. But, especially given priority inheritance, is there any
situation where priority inversion provides *worse* performance than
running everything at the same priority? I can easily come up with
situations where it devolves to that case- where all processes get
promoted to the same high priority. But I can't think of one where
using priorities makes things worse, and I can think of plenty where it
makes things better.
Brian
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
@ 2006-11-29 15:03 ` Mark Lewis <mark.lewis@mir3.com>
2006-11-29 15:17 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mark Lewis @ 2006-11-29 15:03 UTC (permalink / raw)
To: Brian Hurt <bhurt@janestcapital.com>; +Cc: pgsql-performance
On Wed, 2006-11-29 at 08:25 -0500, Brian Hurt wrote:
...
> I have the same question. I've done some embedded real-time
> programming, so my innate reaction to priority inversions is that
> they're evil. But, especially given priority inheritance, is there any
> situation where priority inversion provides *worse* performance than
> running everything at the same priority? I can easily come up with
> situations where it devolves to that case- where all processes get
> promoted to the same high priority. But I can't think of one where
> using priorities makes things worse, and I can think of plenty where it
> makes things better.
...
It can make things worse when there are at least 3 priority levels
involved. The canonical sequence looks as follows:
LOW: Aquire a lock
MED: Start a long-running batch job that hogs CPU
HIGH: Wait on lock held by LOW task
at this point, the HIGH task can't run until the LOW task releases its
lock. but the LOW task can't run to completion and release its lock
until the MED job completes.
(random musing): I wonder if PG could efficiently be made to temporarily
raise the priority of any task holding a lock that a high priority task
waits on. I guess that would just make it so that instead of HIGH tasks
being effectively reduced to LOW, then LOW tasks could be promoted to
HIGH.
-- Mark Lewis
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Re: RES: Priority to a mission critical transaction Mark Lewis <mark.lewis@mir3.com>
@ 2006-11-29 15:17 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 16:43 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 17+ messages in thread
From: Brian Hurt @ 2006-11-29 15:17 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: pgsql-performance
Mark Lewis wrote:
>On Wed, 2006-11-29 at 08:25 -0500, Brian Hurt wrote:
>...
>
>
>>I have the same question. I've done some embedded real-time
>>programming, so my innate reaction to priority inversions is that
>>they're evil. But, especially given priority inheritance, is there any
>>situation where priority inversion provides *worse* performance than
>>running everything at the same priority? I can easily come up with
>>situations where it devolves to that case- where all processes get
>>promoted to the same high priority. But I can't think of one where
>>using priorities makes things worse, and I can think of plenty where it
>>makes things better.
>>
>>
>...
>
>It can make things worse when there are at least 3 priority levels
>involved. The canonical sequence looks as follows:
>
>LOW: Aquire a lock
>MED: Start a long-running batch job that hogs CPU
>HIGH: Wait on lock held by LOW task
>
>at this point, the HIGH task can't run until the LOW task releases its
>lock. but the LOW task can't run to completion and release its lock
>until the MED job completes.
>
>
>
>(random musing): I wonder if PG could efficiently be made to temporarily
>raise the priority of any task holding a lock that a high priority task
>waits on. I guess that would just make it so that instead of HIGH tasks
>being effectively reduced to LOW, then LOW tasks could be promoted to
>HIGH.
>
>
>
I thought that was what priority inheritance did- once HIGH blocks on a
lock held by LOW, LOW gets it's priority raised to that of HIGH. Then
LOW takes precedence over MED. If LOW blocks on a lock held by MED when
it has the same priority of HIGH, MED gets it's priority raised to
HIGH. Note that now all three processes are running with HIGH priority-
but is this any different from the default case of running them as the
same priority? This is what I was talking about when I said I could
imagine priority inheritance "devolving" to the single priority case.
Of course, this is a little tricky to implement. I haven't looked at
how difficult it'd be within Postgres.
Brian
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Re: RES: Priority to a mission critical transaction Mark Lewis <mark.lewis@mir3.com>
2006-11-29 15:17 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
@ 2006-11-29 16:43 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 17:21 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ron Mayer @ 2006-11-29 16:43 UTC (permalink / raw)
To: Brian Hurt <bhurt@janestcapital.com>
Brian Hurt wrote:
> Mark Lewis wrote:
>> On Wed, 2006-11-29 at 08:25 -0500, Brian Hurt wrote:
>>
>>> I have the same question. I've done some embedded real-time
>>> programming, so my innate reaction to priority inversions is that
>>> they're evil. But, especially given priority inheritance, is there any
>>> situation where priority inversion provides *worse* performance than
>>> running everything at the same priority?
Yes, there are certainly cases where a single high priority
transaction will suffer far worse than it otherwise would have.
Apparently there are plenty of papers stating that priority inversion
is a major problem in RDBMs's for problems that require that specific
deadlines have to be met (such as in real time systems). However the
papers using the much weaker criteria of "most high priority things
finish faster than they would have otherwise, and the others aren't
hurt too bad" suggest that it's not as much of a problem. Two of
the articles referenced by the paper being discussed here apparently
go into these cases.
The question in my mind is whether overall the benefits outweigh
the penalties - in much the same way that qsort's can have O(n^2)
behavior but in practice outweigh the penalties of many alternatives.
>> It can make things worse when there are at least 3 priority levels
>> involved. The canonical sequence looks as follows:
>>
>> LOW: Aquire a lock
>> MED: Start a long-running batch job that hogs CPU
>> HIGH: Wait on lock held by LOW task
>>
>> at this point, the HIGH task can't run until the LOW task releases its
>> lock. but the LOW task can't run to completion and release its lock
>> until the MED job completes.
Don't many OS's dynamically tweak priorities such that processes
that don't use most of their timeslice (like LOW) get priority
boosts and those that do use a lot of CPU (like MED) get
penalized -- which may help protect against this particular
sequence if you don't set LOW and MED too far apart?
>> (random musing): I wonder if PG could efficiently be made to temporarily
>> raise the priority of any task holding a lock that a high priority task
>> waits on. ...
>
> I thought that was what priority inheritance did-
Yes, me too..
> Of course, this is a little tricky to implement. I haven't looked at
> how difficult it'd be within Postgres.
ISTM that it would be rather OS-dependent anyway. Different OS's
have different (or no) hooks - heck, even different 2.6.* linuxes
(pre 2.6.18 vs post) have different hooks for priority
inheritance - so I wouldn't really expect to see cpu scheduling
policy details like that merged with postgresql except maybe from
a patched version from a RTOS vendor.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Re: RES: Priority to a mission critical transaction Mark Lewis <mark.lewis@mir3.com>
2006-11-29 15:17 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 16:43 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2006-11-29 17:21 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 18:47 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 17+ messages in thread
From: Brian Hurt @ 2006-11-29 17:21 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: pgsql-performance
Ron Mayer wrote:
>Brian Hurt wrote:
>
>
>>Mark Lewis wrote:
>>
>>
>>>On Wed, 2006-11-29 at 08:25 -0500, Brian Hurt wrote:
>>>
>>>
>>>
>>>>I have the same question. I've done some embedded real-time
>>>>programming, so my innate reaction to priority inversions is that
>>>>they're evil. But, especially given priority inheritance, is there any
>>>>situation where priority inversion provides *worse* performance than
>>>>running everything at the same priority?
>>>>
>>>>
>
>Yes, there are certainly cases where a single high priority
>transaction will suffer far worse than it otherwise would have.
>
>
OK.
Although I'm tempted to make the issue more complex by throwing Software
Transactional Memory into the mix:
http://citeseer.ist.psu.edu/shavit95software.html
http://citeseer.ist.psu.edu/anderson95realtime.html
That second paper is interesting in that it says that STM solves the
priority inversion problem. Basically the higher priority process
forces the lower priority process to abort it's transaction and retry it.
Is it possible to recast Postgres' use of locks to use STM instead? How
would STM interact with Postgres' existing transactions? I don't know.
This would almost certainly require Postgres to write it's own locking,
with all the problems it entails (does the source currently use inline
assembly anywhere? I'd guess not.).
>Apparently there are plenty of papers stating that priority inversion
>is a major problem in RDBMs's for problems that require that specific
>deadlines have to be met (such as in real time systems).
>
It's definately a problem in realtime systems, not just realtime DBMS.
In this case, running everything at the same priority doesn't work and
isn't an option.
>The question in my mind is whether overall the benefits outweigh
>the penalties - in much the same way that qsort's can have O(n^2)
>behavior but in practice outweigh the penalties of many alternatives.
>
>
>
Also, carefull choice of pivot values, and switching to other sorting
methods like heapsort when you detect you're in a pathological case,
help. Make the common case fast and the pathological case not something
that causes the database to fall over.
Setting priorities would be a solution to a problem I haven't hit yet,
but can see myself needing to deal with. Which is why I'm interested in
this issue. If it's a case of "setting priorities can make things
better, and doesn't make things worse" is great. If it's a case of
"setting priorities can make things better, but occassionally makes
things much worse" is a problem.
>>Of course, this is a little tricky to implement. I haven't looked at
>>how difficult it'd be within Postgres.
>>
>>
>
>ISTM that it would be rather OS-dependent anyway. Different OS's
>have different (or no) hooks - heck, even different 2.6.* linuxes
>(pre 2.6.18 vs post) have different hooks for priority
>inheritance - so I wouldn't really expect to see cpu scheduling
>policy details like that merged with postgresql except maybe from
>a patched version from a RTOS vendor.
>
>
>
Hmm. I was thinking of Posix.4's setpriority() call.
Brian
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Re: RES: Priority to a mission critical transaction Mark Lewis <mark.lewis@mir3.com>
2006-11-29 15:17 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
2006-11-29 16:43 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 17:21 ` Re: RES: Priority to a mission critical transaction Brian Hurt <bhurt@janestcapital.com>
@ 2006-11-29 18:47 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ron Mayer @ 2006-11-29 18:47 UTC (permalink / raw)
To: Brian Hurt <bhurt@janestcapital.com>
Brian Hurt wrote:
> Ron Mayer wrote:
>> Brian Hurt wrote:
>>> Mark Lewis wrote:
>>>> On Wed, 2006-11-29 at 08:25 -0500, Brian Hurt wrote:
>>>>> But, especially given priority inheritance, is there any
>
> That second paper is interesting in that it says that STM solves the
> priority inversion problem. Basically the higher priority process
> forces the lower priority process to abort it's transaction and retry it.
>
> Is it possible to recast Postgres' use of locks to use STM instead?
If I read the CMU paper right (http://www.cs.cmu.edu/~bianca/icde04.pdf),
that's equivalent to what they call "preemptive abort scheduling"
and tested as well as priority inversion.
They did test this and compared it to priority inversion with
postgresql and found them about equivalent.
"Preemptive scheduling (P-LQ and P-CPU) attempts to eliminate the
wait excess for high-priority transactions by preempting low-priority
lock holders in the way of high-priority transactions. We find that
preemptive policies provide little benefit
...
TPC-C running on PostgreSQL ... Preemption (P-CPU) provides
no appreciable benefit over CPU-Prio-Inherit."
> Setting priorities would be a solution to a problem I haven't hit yet,
> but can see myself needing to deal with. Which is why I'm interested in
> this issue. If it's a case of "setting priorities can make things
> better, and doesn't make things worse" is great. If it's a case of
> "setting priorities can make things better, but occassionally makes
> things much worse" is a problem.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Re: RES: Priority to a mission critical transaction Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Re: RES: Priority to a mission critical transaction Bruce Momjian <bruce@momjian.us>
@ 2006-11-30 04:29 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 17+ messages in thread
From: Josh Berkus @ 2006-11-30 04:29 UTC (permalink / raw)
To: pgsql-performance; +Cc: Bruce Momjian <bruce@momjian.us>; Tom Lane <tgl@sss.pgh.pa.us>; carlos.reimer@opendb.com.br, "Jim C. Nasby" <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>
Bruce,
> Someone should ask them to remove the article.
"Someone".
Um, *who* taught for Big Nerd Ranch for several years, Bruce?
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
@ 2006-11-28 19:20 ` Andreas Kostyrka <andreas@kostyrka.org>
2006-11-28 19:45 ` Re: RES: Priority to a mission critical transaction Josh Berkus <josh@agliodbs.com>
2006-11-28 20:31 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
1 sibling, 2 replies; 17+ messages in thread
From: Andreas Kostyrka @ 2006-11-28 19:20 UTC (permalink / raw)
To: Carlos H. Reimer <carlos.reimer@opendb.com.br>; +Cc: Jim C. Nasby <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
* Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
> Hi,
>
> There is an article about "Lowering the priority of a PostgreSQL query"
> (http://weblog.bignerdranch.com/?p=11) that explains how to use the
> setpriority() to lower PostgreSQL processes.
>
> I?m wondering how much effective it would be for i/o bound systems.
>
> Will the setpriority() system call affect i/o queue too?
Nope, and in fact the article shows the way not to do it.
See http://en.wikipedia.org/wiki/Priority_inversion
Basically, lowering the priority of one backend in PostgreSQL can lead
to reduced performance of all, especially also the backends with
higher priorities.
(Think of priority inversion as a timed soft deadlock. It will
eventually resolve, because it's not a real deadlock, but it might
mean halting important stuff for quite some time.)
Taking the example above, consider the following processes and nice
values:
19x backends As nice = 0
1x backend B nice = 10 (doing maintenance work)
1x updatedb nice = 5 (running as a cronjob at night)
Now, it possible (the probability depends upon your specific
situation), where backend B grabs some internal lock that is needed,
and then it gets preempted by higher priority stuff. Well, the A
backends need that lock too, so they cannot run; instead we wait till
updatedb (which updates the locate search db, and goes through the
whole filesystem of the server) is finished.
Luckily most if not all of these processes are disc io bound, so they
get interrupted any way, and low priority processes don't starve.
Well, replace updatedb with something hogging the CPU, and rethink the
situation.
Andreas
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
@ 2006-11-28 19:45 ` Josh Berkus <josh@agliodbs.com>
2006-11-29 01:11 ` Re: RES: Priority to a mission critical transaction Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 17+ messages in thread
From: Josh Berkus @ 2006-11-28 19:45 UTC (permalink / raw)
To: pgsql-performance; +Cc: Andreas Kostyrka <andreas@kostyrka.org>; Carlos H. Reimer <carlos.reimer@opendb.com.br>; Jim C. Nasby <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>
All,
The Bizgres project is working on resource management for PostgreSQL. So far,
however, they have been able to come up with schemes that work for BI/DW at
the expense of OLTP. Becuase of O^N lock checking issues, resource
management for OLTP which doesn't greatly reduce overall performance seems a
near-impossible task.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
2006-11-28 19:45 ` Re: RES: Priority to a mission critical transaction Josh Berkus <josh@agliodbs.com>
@ 2006-11-29 01:11 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 17+ messages in thread
From: Mark Kirkwood @ 2006-11-29 01:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, Andreas Kostyrka <andreas@kostyrka.org>; Carlos H. Reimer <carlos.reimer@opendb.com.br>; Jim C. Nasby <jim@nasby.net>; Brad Nicholson <bnichols@ca.afilias.info>
Josh Berkus wrote:
> All,
>
> The Bizgres project is working on resource management for PostgreSQL. So far,
> however, they have been able to come up with schemes that work for BI/DW at
> the expense of OLTP. Becuase of O^N lock checking issues, resource
> management for OLTP which doesn't greatly reduce overall performance seems a
> near-impossible task.
>
Right - I guess it is probably more correct to say that the
implementation used in Bizgres is specifically targeted at BI/DW
workloads rather than OLTP.
At this point we have not measured its impact on concurrency in anything
other than a handwaving manner - e.g pgbench on an older SMP system
showed what looked like about a 10% hit. However the noise level for
pgbench is typically >10% so - a better benchmark on better hardware is
needed.
Cheers
Mark
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
@ 2006-11-28 20:31 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 01:27 ` Re: RES: Priority to a mission critical transaction Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 17+ messages in thread
From: Ron Mayer @ 2006-11-28 20:31 UTC (permalink / raw)
To: Andreas Kostyrka <andreas@kostyrka.org>; jim@nasby.net, Ron Mayer <rm_pg@cheapcomplexdevices.com>
Short summary:
* Papers studying priority inversion issues with
databases including PosgreSQL and realistic workloads
conclude setpriority() helps even in the presence of
priority inversion issues for TCP-C and TCP-W like
workloads.
* Avoiding priority inversion with priority inheritance
will further help some workloads (TCP-C) more than
others (TCP-W) but even without such schedulers
priority inversion does not cause as much harm
as the benefit you get from indirectly scheduling
I/O through setpriority() in any paper I've seen.
Andreas Kostyrka wrote:
> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>> Will the setpriority() system call affect i/o queue too?
>
> Nope, and in fact the article shows the way not to do it.
Actually *YES* setpriority() does have an indirect effect
on the I/O queue.
This paper: http://www.cs.cmu.edu/~bianca/icde04.pdf
studies setpriority() with non-trivial (TCP-W and TCP-C)
workloads on a variety of databases and shows that
that setpriority() is *extremely* effective for
PostgreSQL.
"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."
> See http://en.wikipedia.org/wiki/Priority_inversion
Priority Inversion is a well studied problem; and depends
on both the workload and the database. In particular,
TPC-W workloads have been studied on a variety of databases
including PostgreSQL. Again, from:
http://www.cs.cmu.edu/~bianca/icde04.pdf
They observe that avoiding priority inversion
issues by enabling priority inheritance with PostgreSQL
has a negligible effect on TCP-W like workloads, but
a significant improvement on TCP-C like workloads.
"Recall from Section 5.3 that CPU scheduling (CPUPrio)
is more effective than NP-LQ for TPC-W. Thus Figure 8
compares the policies CPU-Prio-Inherit to CPU-Prio for
the TPC-W workload on PostgreSQL.
We find that there is no improvement for CPU-Prio-
Inherit over CPU-Prio. This is to be expected given
the low data contention found in the TPC-W workload; priority
inversions can only occur during data contention. Results
for low-priority transactions are not shown, but as in
Figure 4, low-priority transactions are only negligibly
penalized on average."
Yes, theoretically priority inversion can have pathologically
bad effects (not unlike qsort), it affects some workloads more
than others.
But in particular, their paper concludes that
PostgreSQL with TCP-C and TCP-W like workloads
gain significant benefits and no drawbacks from
indirectly tuning I/O scheduling with setpriority().
If anyone has references to papers or studies that suggest that
priority inversion actually is a problem with RDBMS's - and
PostgreSQL on Linux in particular, I'd be very interested.
Otherwise it seems to me existing research points to
significant benefits with only theoretical drawbacks
in pathological cases.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
2006-11-28 20:31 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2006-11-29 01:27 ` Mark Kirkwood <markir@paradise.net.nz>
2006-11-29 10:55 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 17+ messages in thread
From: Mark Kirkwood @ 2006-11-29 01:27 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Andreas Kostyrka <andreas@kostyrka.org>; jim@nasby.net; pgsql-performance
Ron Mayer wrote:
> Short summary:
> * Papers studying priority inversion issues with
> databases including PosgreSQL and realistic workloads
> conclude setpriority() helps even in the presence of
> priority inversion issues for TCP-C and TCP-W like
> workloads.
> * Avoiding priority inversion with priority inheritance
> will further help some workloads (TCP-C) more than
> others (TCP-W) but even without such schedulers
> priority inversion does not cause as much harm
> as the benefit you get from indirectly scheduling
> I/O through setpriority() in any paper I've seen.
>
> Andreas Kostyrka wrote:
>> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>>> Will the setpriority() system call affect i/o queue too?
>> Nope, and in fact the article shows the way not to do it.
>
> Actually *YES* setpriority() does have an indirect effect
> on the I/O queue.
>
While I was at Greenplum a related point was made to me:
For a TPC-H/BI type workload on a well configured box the IO subsystem
can be fast enough so that CPU is the bottleneck for much of the time -
so being able to use setpriority() as a resource controller makes sense.
Also, with such a workload being mainly SELECT type queries, the dangers
connected with priority inversion are considerably reduced.
Cheers
Mark
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: RES: Priority to a mission critical transaction
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:20 ` Re: RES: Priority to a mission critical transaction Andreas Kostyrka <andreas@kostyrka.org>
2006-11-28 20:31 ` Re: RES: Priority to a mission critical transaction Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 01:27 ` Re: RES: Priority to a mission critical transaction Mark Kirkwood <markir@paradise.net.nz>
@ 2006-11-29 10:55 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ron Mayer @ 2006-11-29 10:55 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>
Mark Kirkwood wrote:
> Ron Mayer wrote:
>> Short summary:
>> * Papers studying priority inversion issues with
>> databases including PosgreSQL and realistic workloads
>> conclude setpriority() helps even in the presence of
>> priority inversion issues for TCP-C and TCP-W like
>> workloads.
>> * Avoiding priority inversion with priority inheritance
>> will further help some workloads (TCP-C) more than
>> others (TCP-W) but even without such schedulers
>> priority inversion does not cause as much harm
>> as the benefit you get from indirectly scheduling
>> I/O through setpriority() in any paper I've seen.
>>
>> Andreas Kostyrka wrote:
>>> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>>>> Will the setpriority() system call affect i/o queue too?
>>> Nope, and in fact the article shows the way not to do it.
>>
>> Actually *YES* setpriority() does have an indirect effect
>> on the I/O queue.
>>
>
> While I was at Greenplum a related point was made to me:
>
> For a TPC-H/BI type workload on a well configured box the IO subsystem
> can be fast enough so that CPU is the bottleneck for much of the time -
> so being able to use setpriority() as a resource controller makes sense.
Perhaps - but section 4 of the paper in question (pages 3 through 6
of the 12 pages at http://www.cs.cmu.edu/~bianca/icde04.pdf) go
through great lengths to identify the bottlenecks for each workload
and each RDBMS. Indeed for the TCP-W on PostgreSQL and DB2, CPU
was a bottleneck but no so for TCP-C - which had primarily I/O
contention on PostgreSQL and lock contention on DB2.
http://www.cs.cmu.edu/~bianca/icde04.pdf
"for TPC-C ... The main result shown in Figure 1 is that locks
are the bottleneck resource for both Shore and DB2 (rows 1 and
2), while I/O tends to be the bottleneck resource for PostgreSQL
(row 3). We now discuss these in more detail.
...
Thus, CPU is the bottleneck resource for TPC-W 1."
> Also, with such a workload being mainly SELECT type queries, the dangers
> connected with priority inversion are considerably reduced.
And indeed the TCP-W benchmark did not show further improvement
for high priority transactions with Priority Inheritance enabled
in the scheduler (which mitigates the priority inversion problem) -
but the TCP-C benchmark did show further improvement -- which agrees
with Mark's observation. However even with priority inversion
problems; the indirect benefits of setpriority() on I/O scheduling
outweighed the penalties of priority inversion in each of their
test cases.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2006-11-30 04:29 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-28 19:01 RES: Priority to a mission critical transaction Carlos H. Reimer <carlos.reimer@opendb.com.br>
2006-11-28 19:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-28 23:44 ` Bruce Momjian <bruce@momjian.us>
2006-11-29 01:20 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 13:25 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 15:03 ` Mark Lewis <mark.lewis@mir3.com>
2006-11-29 15:17 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 16:43 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 17:21 ` Brian Hurt <bhurt@janestcapital.com>
2006-11-29 18:47 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-30 04:29 ` Josh Berkus <josh@agliodbs.com>
2006-11-28 19:20 ` Andreas Kostyrka <andreas@kostyrka.org>
2006-11-28 19:45 ` Josh Berkus <josh@agliodbs.com>
2006-11-29 01:11 ` Mark Kirkwood <markir@paradise.net.nz>
2006-11-28 20:31 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-11-29 01:27 ` Mark Kirkwood <markir@paradise.net.nz>
2006-11-29 10:55 ` 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