Re: RES: Priority to a mission critical transaction - Mailing list pgsql-performance

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.