Re: Priorities for users or queries?

Benjamin Arai wrote:
> My problem with [1] is that even for 10 users the percentage of time
> spent in locks is very high.

Really? In the paper referenced in the thread you quoted,
figure 1H shows TCP-C with PostgreSQL and shows that time
spent in locks with 10 users is extremely small (about 10%
of time in locks with 5 warehouses and near 0% at 30
warehouses).

This is in contrast with DB2 which shows about 80% time
in locks with 5 warehouses and ten clients. Perhaps you
were thinking DB2?

With TCP-W, neither PostgreSQL nor DB2 shows any significant
time spent in locks with 12 clients.

> Can priorities scale?

The PostgreSQL-priority-mechanisms paper referenced in this thread
used TPC-C using 500MB - 3GB databases with 10 warehouses and
from 1 to 300 Clients and TPC-W with 150MB and between 12
and 150 clients.

So I'd say yes, it scales to meet most needs.

>
> Benjamin
>
> 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
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>