Re: RES: Priority to a mission critical transaction

[Brian Hurt <bhurt(at)janestcapital(dot)com>]

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