Re: Priorities for users or queries?
От | Benjamin Arai |
---|---|
Тема | Re: Priorities for users or queries? |
Дата | |
Msg-id | 45D63192.6020707@araisoft.com обсуждение исходный текст |
Ответ на | Re: Priorities for users or queries? (Jan Wieck <JanWieck@Yahoo.com>) |
Список | pgsql-general |
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/ > >
В списке pgsql-general по дате отправления: