Re: Priorities for users or queries?

Jim Nasby wrote:
> The problem with using simple OS priority settings is you leave yourself
> wide open to priority inversion.

Which is why you either
(a) note that papers studying priority inversion on RDBMS's
find that it's a non issue on many RDBMS workloads; and
(except for real-time databases) you tend to still get
at least partial benefits even in the face of priority
inversions.
or
(b) use a scheduler in your OS that supports priority
inheritance or other mechanisms to avoid priority
inversion problems.
If you want to use priority inheritance to avoid
the priority inversion settings it appears versions
of Linux, BSD, Windows, and Solaris at least give
you the ability to do so.

> There is already work being done on a queuing system; take a look at the
> bizgres archives.

Which is cool; but not quite the same as priorities.

It seems to me that Bizgres and/or PostgreSQL would not
want to re-implement OS features like schedulers.

> On Feb 20, 2007, at 5:19 PM, Ron Mayer wrote:
>
>> Bruce Momjian wrote:
>>> Hard to argue with that.
>>
>> Is it a strong enough argument to add a TODO?
>>
>>
>> I'm thinking some sort of TODO might be called for.
>>
>> Perhaps two TODOs?
>> * Use the OS's priority features to prioritize
>> backends (and document that it might work
>> better with OS's that support priority inheritance).
>> * Investigate if postgresql could develop an
>> additional priority mechanism instead of using
>> the OS's.
>>
>>> Ron Mayer wrote:
>>>> Magnus Hagander wrote: ...
>>>>> quite likely to suffer from priority inversion
>>>> ... CMU paper... tested PostgreSQL (and DB2) on TPC-C
>>>> and TPC-W ...found that...I/O scheduling through
>>>> CPU priorities is a big win for postgresql.
>>>>
>>>> http://www.cs.cmu.edu/~bianca/icde04.pdf
>>
>> Setting priorities seems a rather common request,
>> supposedly coming up every couple months [5].
>>
>> The paper referenced [1] suggests that even with
>> naive schedulers, use of CPU priorities is very
>> effective for CPU and I/O intensive PostgreSQL
>> workloads.
>>
>> If someone eventually finds a workload that does suffer
>> worse performance due to priority inversion,
>> (a) they could switch to an OS and scheduler
>> that supports priority inheritance;
>> (b) it'd be an interesting case for a paper
>> rebutting the CMU one; and
>> (c) they don't have to use priorities.
>>
>> If a user does find he wants priority inheritance it
>> seems Linux[1], BSD[2], some flavors of Windows[3],
>> and Solaris[4] all seem to be options; even though
>> I've only seen PostgreSQL specifically tested for
>> priority inversion problems with Linux (which did
>> not find problems but found additional benefit of
>> using priority inheritance).
>>
>>
>>
>>
>> [1] Linux with Priority inheritance showing benefits for
>> PostgreSQL
>> http://www.cs.cmu.edu/~bianca/icde04.pdf
>> [2] BSD priority inheritance work mentioned:
>> http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
>> [3] Windows priority inheritance stuff:
>> http://msdn2.microsoft.com/en-us/library/aa915356.aspx
>> [4] Solaris priority inheritance stuff
>> http://safari5.bvdep.com/0131482092/ch17lev1sec7
>> http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
>> [5] Tom suggests that priorities are a often requested feature.
>> http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>