Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CA65E9FB4D6 for ; Fri, 16 Feb 2007 18:42:38 -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 21184-08 for ; Fri, 16 Feb 2007 18:42:34 -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 0B40B9FB39B for ; Fri, 16 Feb 2007 18:42:34 -0400 (AST) Received: from spunkymail-a10.g.dreamhost.com (sd-green-bigip-177.dreamhost.com [208.97.132.177]) by sumo.dreamhost.com (Postfix) with ESMTP id 2A81617F0FC for ; Fri, 16 Feb 2007 14:42:34 -0800 (PST) Received: from [138.23.204.204] (akami.cs.ucr.edu [138.23.204.204]) by spunkymail-a10.g.dreamhost.com (Postfix) with ESMTP id 2B7F9161836; Fri, 16 Feb 2007 14:42:31 -0800 (PST) Message-ID: <45D63192.6020707@araisoft.com> Date: Fri, 16 Feb 2007 14:34:58 -0800 From: Benjamin Arai Reply-To: benjamin@araisoft.com Organization: Araisoft User-Agent: Thunderbird 2.0b2 (Windows/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> <45D62ABE.8010609@araisoft.com> <45D631C7.3010101@Yahoo.com> In-Reply-To: <45D631C7.3010101@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=0.886 tagged_above=0 required=5 tests=AWL, BAYES_40 X-Spam-Level: X-Archive-Number: 200702/911 X-Sequence-Number: 109401 Hi Jan, That is true but it really only solves the case for readers/writers. In the long run I feel there should be some mechanism to determine the priority of a query either on a user or query basis. This would lend PostgreSQL to a whole new industry that is currently only filled with commercial solutions (i.e. Oracle, DB2). Would this be difficult to implement? Maybe a summer of code person could do it. Benjamin Arai Jan Wieck wrote: > On 2/16/2007 5:05 PM, Benjamin Arai wrote: >> Fair enough, thanks for the clarification. > > What you can do to throttle things in a reasonable manner would > require that your application knows which transaction requires > updating when it begins it. If that is the case, you can setup > multiple connection pools with pgpool, one for reading having many > physical connections, each shared for just a few clients, another > having few physical connections shared by all writers. That way you > will have a limited number of writers active at the same time. > > > Jan > > >> >> 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 >>>>> >>>>> >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org/ > >