Re: Priorities for users or queries? - Mailing list 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/
>
>