Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7097C9FC28D for ; Fri, 23 Feb 2007 16:57:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 53767-05 for ; Fri, 23 Feb 2007 16:57:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sumo.dreamhost.com (sumo.dreamhost.com [66.33.216.29]) by postgresql.org (Postfix) with ESMTP id EDCD39FC27C for ; Fri, 23 Feb 2007 16:57:21 -0400 (AST) Received: from spunkymail-a2.g.dreamhost.com (sd-green-bigip-177.dreamhost.com [208.97.132.177]) by sumo.dreamhost.com (Postfix) with ESMTP id BBA10184818 for ; Fri, 23 Feb 2007 12:57:20 -0800 (PST) Received: from [138.23.204.204] (akami.cs.ucr.edu [138.23.204.204]) by spunkymail-a2.g.dreamhost.com (Postfix) with ESMTP id E22E0870C4; Fri, 23 Feb 2007 12:57:08 -0800 (PST) Message-ID: <45DF535A.6090008@araisoft.com> Date: Fri, 23 Feb 2007 12:49:30 -0800 From: Benjamin Arai Reply-To: benjamin@araisoft.com Organization: Araisoft User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: Ron Mayer Cc: Bruce Momjian , pgsql-general@postgresql.org Subject: Re: Priorities for users or queries? References: <45D65E08.7000306@cheapcomplexdevices.com> <200702170152.l1H1qax16645@momjian.us> <45DB81EA.9080401@cheapcomplexdevices.com> In-Reply-To: <45DB81EA.9080401@cheapcomplexdevices.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.572 tagged_above=0 required=5 tests=AWL, BAYES_40 X-Spam-Level: X-Archive-Number: 200702/1392 X-Sequence-Number: 109881 My problem with [1] is that even for 10 users the percentage of time spent in locks is very high. Can priorities scale? 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 > >