Re: Priorities for users or queries?

The problem with using simple OS priority settings is you leave
yourself wide open to priority inversion.

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

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)