Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 841039FBD3D for ; Fri, 16 Feb 2007 18:06:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 13832-01 for ; Fri, 16 Feb 2007 18:05:57 -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 36EA59FBB05 for ; Fri, 16 Feb 2007 18:05:57 -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 72A2417B9FF for ; Fri, 16 Feb 2007 14:05:53 -0800 (PST) Received: from benjamin-arais-computer.local (wg05.ucr.edu [138.23.2.34]) by spunkymail-a2.g.dreamhost.com (Postfix) with ESMTP id F0616870EF; Fri, 16 Feb 2007 14:05:50 -0800 (PST) Message-ID: <45D62ABE.8010609@araisoft.com> Date: Fri, 16 Feb 2007 14:05:50 -0800 From: Benjamin Arai Organization: Araisoft User-Agent: Thunderbird 2.0b2 (Macintosh/20070116) MIME-Version: 1.0 To: Jan Wieck Cc: Magnus Hagander , Adam Rich , pgsql-general@postgresql.org Subject: Re: Priorities for users or queries? References: <000001c74da2$a9a4a990$6400a8c0@dualcore> <45CEE33B.8020303@hagander.net> <45CF5A19.1010101@araisoft.com> <45D625B4.5040008@Yahoo.com> <45D62878.60406@araisoft.com> <45D629E1.5030504@Yahoo.com> In-Reply-To: <45D629E1.5030504@Yahoo.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=2.105 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO, RCVD_IN_BL_SPAMCOP_NET, RCVD_IN_SORBS_WEB X-Spam-Level: ** X-Archive-Number: 200702/908 X-Sequence-Number: 109398 Fair enough, thanks for the clarification. Benjamin Jan Wieck wrote: > On 2/16/2007 4:56 PM, Benjamin Arai wrote: >> Hi Jan, >> >> That makes sense. Does that mean that a low-priority "road-block" >> can cause a deadlock or just an very long one lock? > > It doesn't cause any deadlock by itself. Although the longer one holds > one lock, before attempting to acquire another, the higher the risk > someone else grabs that and tries visa versa. So if there is a risk of > deadlocks due to the access pattern of your application, then slowing > down the updating processes will increase the risk of it to happen. > > > Jan > >> >> Benjamin >> >> Jan Wieck wrote: >>> On 2/11/2007 1:02 PM, Benjamin Arai wrote: >>>> Hi Magnus, >>>> >>>> Think this can be avoided as long the the queries executed on the >>>> lower priority process never lock anything important. In my case, >>>> I would alway be doing inserts with the lower priority process, so >>>> inversion should never occur. On the other hand if some lock occur >>>> somewhere else specific to Postgres then there may be an issue. >>>> Are there any other tables locked by the the Postgres process other >>>> than those locks explicitly set by the query? >>> >>> If you assume that the logical row level locks, placed by such low >>> priority "road-block", would be the important thing to watch out >>> for, you are quite wrong. Although Postgres appears to avoid >>> blocking readers by concurrent updates using MVCC, this isn't >>> entirely true. The moment one updating backend needs to scribble >>> around in any heap or index block, it needs an exclusive lock on >>> that block until it is done with that. It will not hold that block >>> level lock until the end of its transaction, but it needs to hold it >>> until the block is in a consistent state again. That means that the >>> lower the priority of those updating processes, the more exclusively >>> locked shared buffers you will have in the system, with the locking >>> processes currently not getting the CPU because of their low priority. >>> >>> >>> Jan >>> >>>> >>>> Benjamin >>>> >>>> Magnus Hagander wrote: >>>>> Most likely, you do not want to do this. You *can* do it, but you are >>>>> quite likely to suffer from priority inversion >>>>> (http://en.wikipedia.org/wiki/Priority_inversion) >>>>> >>>>> //Magnus >>>>> >>>>> >>>>> Adam Rich wrote: >>>>> >>>>>> There is a function pg_backend_pid() that will return the PID for >>>>>> the current session. You could call this from your updating app >>>>>> to get a pid to feed to the NICE command. >>>>>> >>>>>> >>>>>> -----Original Message----- >>>>>> From: pgsql-general-owner@postgresql.org >>>>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin >>>>>> Arai >>>>>> Sent: Saturday, February 10, 2007 6:56 PM >>>>>> To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org >>>>>> Subject: [GENERAL] Priorities for users or queries? >>>>>> >>>>>> >>>>>> Hi, >>>>>> >>>>>> Is there a way to give priorities to queries or users? Something >>>>>> similar to NICE in Linux. My goal is to give the updating >>>>>> (backend) application a very low priority and give the web >>>>>> application a high priority to avoid disturbing the user experience. >>>>>> >>>>>> Thanks in advance! >>>>>> >>>>>> Benjamin >>>>>> >>>>>> >>>>>> ---------------------------(end of >>>>>> broadcast)--------------------------- >>>>>> TIP 4: Have you searched our list archives? >>>>>> >>>>>> http://archives.postgresql.org/ >>>>>> >>>>>> >>>>>> ---------------------------(end of >>>>>> broadcast)--------------------------- >>>>>> TIP 3: Have you checked our extensive FAQ? >>>>>> >>>>>> http://www.postgresql.org/docs/faq >>>>>> >>>>> >>>>> >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 3: Have you checked our extensive FAQ? >>>> >>>> http://www.postgresql.org/docs/faq >>> >>> > >