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