Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9436E9FBABC for ; Tue, 20 Feb 2007 19:19:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 57344-06 for ; Tue, 20 Feb 2007 19:19:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 84AF79FB9A5 for ; Tue, 20 Feb 2007 19:19:13 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 4073830AF0; Wed, 21 Feb 2007 00:19:12 +0100 (MET) From: Ron Mayer X-Newsgroups: pgsql.general Subject: Re: Priorities for users or queries? Date: Tue, 20 Feb 2007 15:19:06 -0800 Organization: Hub.Org Networking Services Lines: 64 Message-ID: <45DB81EA.9080401@cheapcomplexdevices.com> References: <45D65E08.7000306@cheapcomplexdevices.com> <200702170152.l1H1qax16645@momjian.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Bruce Momjian User-Agent: Icedove 1.5.0.9 (X11/20061220) In-Reply-To: <200702170152.l1H1qax16645@momjian.us> To: pgsql-general@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/1085 X-Sequence-Number: 109575 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