Re: Priorities for users or queries?

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(at)postgresql(dot)org
>>>>>>> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Benjamin
>>>>>>> Arai
>>>>>>> Sent: Saturday, February 10, 2007 6:56 PM
>>>>>>> To: pgsql-general(at)postgresql(dot)org; pgsql-admin(at)postgresql(dot)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/

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #