Обсуждение: Time limit for a process to hold Content lock in Buffer Cache
Hi all, I was searching for an existing mechanism which ensures that a crashed process releases a content lock held on a page in buffer cache. Also, a similar mechanism for refcount of the page. I seem to have missed it. Do we have a mechanism,and if yes,where can I find it? Regards, Atri -- Regards, Atri l'apprenant
On Thursday, May 23, 2013 3:35 PM Atri Sharma wrote: > Hi all, > > I was searching for an existing mechanism which ensures that a crashed > process releases a content lock held on a page in buffer cache. Also, > a similar mechanism for refcount of the page. > > I seem to have missed it. Do we have a mechanism,and if yes,where can I > find it? On any process (backend, bgwriter,checkpointer, ..) crash, it reinitialize the shared memory which will clear any locks held. Please refer functions HandleChildCrash() and PostmasterStateMachine(). With Regards, Amit Kapila.
Sent from my iPad On 23-May-2013, at 16:27, Amit Kapila <amit.kapila@huawei.com> wrote: > On Thursday, May 23, 2013 3:35 PM Atri Sharma wrote: >> Hi all, >> >> I was searching for an existing mechanism which ensures that a crashed >> process releases a content lock held on a page in buffer cache. Also, >> a similar mechanism for refcount of the page. >> >> I seem to have missed it. Do we have a mechanism,and if yes,where can I >> find it? > > On any process (backend, bgwriter,checkpointer, ..) crash, it reinitialize > the shared memory which will clear any locks held. > Please refer functions HandleChildCrash() and PostmasterStateMachine(). > > Thanks a ton. On a different note, shouldn't we have a time out for a content lock in buffer cache? There is a limit to the time any saneprocess would want to hold a lock,especially when many tasks can be accomplish with only pinning the page. I am just musing here,but it seems that we could potentially analyse this.I may be completely wrong. Regards, Atri
Atri Sharma <atri.jiit@gmail.com> writes:
> On a different note, shouldn't we have a time out for a content lock
> in buffer cache?
No; the overhead of setting up and canceling such a timeout would
greatly outweigh any possible benefit.
Generally speaking, LWLocks are not meant to be used in situations where
the lock hold time might be long enough to justify worrying about
timeouts. If you need that kind of behavior, use a heavyweight lock.
regards, tom lane
On Thu, May 23, 2013 at 8:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Atri Sharma <atri.jiit@gmail.com> writes: >> On a different note, shouldn't we have a time out for a content lock >> in buffer cache? > > No; the overhead of setting up and canceling such a timeout would > greatly outweigh any possible benefit. > > Generally speaking, LWLocks are not meant to be used in situations where > the lock hold time might be long enough to justify worrying about > timeouts. If you need that kind of behavior, use a heavyweight lock. Right, the overheads,especially in case of interruptions would be high. I was musing over a possible condition where a rogue client gets the backend to process queries which take a *lot* of time(note, this is only in my head atm.I may be completely wrong here). Wouldnt something on the lines of a timeout help here? Regards, Atri -- Regards, Atri l'apprenant
Atri Sharma <atri.jiit@gmail.com> writes:
> I was musing over a possible condition where a rogue client gets the
> backend to process queries which take a *lot* of time(note, this is
> only in my head atm.I may be completely wrong here).
> Wouldnt something on the lines of a timeout help here?
You can already set statement_timeout for that. I don't think worrying
about it at the level of buffer content locks would be terribly helpful,
since those locks are generally held only for long enough to read or
write the page or to verify the visibility of rows on it. Even if the
client is rogue, it can't affect those timings too much.
regards, tom lane
On Thu, May 23, 2013 at 8:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Atri Sharma <atri.jiit@gmail.com> writes: >> I was musing over a possible condition where a rogue client gets the >> backend to process queries which take a *lot* of time(note, this is >> only in my head atm.I may be completely wrong here). > >> Wouldnt something on the lines of a timeout help here? > > You can already set statement_timeout for that. I don't think worrying > about it at the level of buffer content locks would be terribly helpful, > since those locks are generally held only for long enough to read or > write the page or to verify the visibility of rows on it. Even if the > client is rogue, it can't affect those timings too much. Right. I seem to be understanding this now. BTW, what is your opinion on a rogue client's damaging capabilities? Theoretically, what can a client which tries to stall the backend target? Regards, Atri -- Regards, Atri l'apprenant
Atri Sharma <atri.jiit@gmail.com> writes:
> BTW, what is your opinion on a rogue client's damaging capabilities?
> Theoretically, what can a client which tries to stall the backend
> target?
If you let an uncooperative user issue arbitrary SQL queries, he can
do any number of things to put server performance into the tank.
For instance, take out exclusive locks on all your tables and just
go to sleep (although I think this is limited by table permissions in
recent PG versions). Or start up an unconstrained join on some giant
tables. etc. etc. This isn't an area that people have felt deserved
adding a lot of overhead to control.
regards, tom lane
> For instance, take out exclusive locks on all your tables and just > go to sleep (although I think this is limited by table permissions in > recent PG versions). This is what I have been worried about. The locks(exclusive locks or content locks in buffers) can be held and stalled. Cant we do anything about it? This is why I contemplated the timeout part. Regards, Atri -- Regards, Atri l'apprenant
Atri Sharma <atri.jiit@gmail.com> writes:
>> For instance, take out exclusive locks on all your tables and just
>> go to sleep (although I think this is limited by table permissions in
>> recent PG versions).
> This is what I have been worried about. The locks(exclusive locks or
> content locks in buffers) can be held and stalled. Cant we do anything
> about it? This is why I contemplated the timeout part.
No, you're not following. These would be table-level heavyweight locks,
and there already are timeout mechanisms that work at that level.
regards, tom lane
> > If you let an uncooperative user issue arbitrary SQL queries, he can > do any number of things to put server performance into the tank. > For instance, take out exclusive locks on all your tables and just > go to sleep (although I think this is limited by table permissions in > recent PG versions). Or start up an unconstrained join on some giant > tables. etc. etc. This isn't an area that people have felt deserved > adding a lot of overhead to control. In such a case, would statement_timeout apply? If using statement_timeout, would the longest a client can stall server be limited to statement_timeout amount of time? -- Amit Langote
> No, you're not following. These would be table-level heavyweight locks, > and there already are timeout mechanisms that work at that level. > Oh,right.Sorry,I missed that. I will go and research heavyweight locks now. Thanks a ton! Regards, Atri -- Regards, Atri l'apprenant
On Thu, May 23, 2013 at 8:52 PM, Amit Langote <amitlangote09@gmail.com> wrote: >> >> If you let an uncooperative user issue arbitrary SQL queries, he can >> do any number of things to put server performance into the tank. >> For instance, take out exclusive locks on all your tables and just >> go to sleep (although I think this is limited by table permissions in >> recent PG versions). Or start up an unconstrained join on some giant >> tables. etc. etc. This isn't an area that people have felt deserved >> adding a lot of overhead to control. > > In such a case, would statement_timeout apply? If using > statement_timeout, would the longest a client can stall server be > limited to statement_timeout amount of time? > I am not sure, but does statement_timeout depend on *what* the query is doing internally(i.e. if it is holding lots of locks,pins etc)? Regards, Atri -- Regards, Atri l'apprenant
Atri Sharma <atri.jiit@gmail.com> writes:
> I am not sure, but does statement_timeout depend on *what* the query
> is doing internally(i.e. if it is holding lots of locks,pins etc)?
A little bit --- the timeout won't actually kill the query until the
next time control reaches a CHECK_FOR_INTERRUPTS macro that's not inside
a critical section. We've had issues in the past with particular code
paths that failed to include such a check in a long-running loop, and
there might still be some left. But by and large, it won't take very
long for the query to notice the interrupt.
regards, tom lane
> > A little bit --- the timeout won't actually kill the query until the > next time control reaches a CHECK_FOR_INTERRUPTS macro that's not inside > a critical section. We've had issues in the past with particular code > paths that failed to include such a check in a long-running loop, and > there might still be some left. But by and large, it won't take very > long for the query to notice the interrupt. Right.I believe this is part of the standard way in which we handle interrupts,right? Making sure that we cancel a query when the backend is in a state to do so,not when the interrupt actually comes in,right? Regards, Atri -- Regards, Atri l'apprenant
On Thu, May 23, 2013 at 10:43 AM, Atri Sharma <atri.jiit@gmail.com> wrote: >> >> A little bit --- the timeout won't actually kill the query until the >> next time control reaches a CHECK_FOR_INTERRUPTS macro that's not inside >> a critical section. We've had issues in the past with particular code >> paths that failed to include such a check in a long-running loop, and >> there might still be some left. But by and large, it won't take very >> long for the query to notice the interrupt. > > > Right.I believe this is part of the standard way in which we handle > interrupts,right? Making sure that we cancel a query when the backend > is in a state to do so,not when the interrupt actually comes in,right? yes. all non trivial parts of the code (in terms of time) must run the interrupt check. it basically just looks a the signal flag set by the signal handler. merlin
Atri Sharma <atri.jiit@gmail.com> writes:
> Right.I believe this is part of the standard way in which we handle
> interrupts,right? Making sure that we cancel a query when the backend
> is in a state to do so,not when the interrupt actually comes in,right?
Right, the actual signal handler will only kill the query immediately
if the backend is in a safe state (eg, while it's waiting for a
heavyweight lock). Otherwise it just sets a flag that's checked by
CHECK_FOR_INTERRUPTS. See StatementCancelHandler in postgres.c.
regards, tom lane
> Right, the actual signal handler will only kill the query immediately > if the backend is in a safe state (eg, while it's waiting for a > heavyweight lock). Otherwise it just sets a flag that's checked by > CHECK_FOR_INTERRUPTS. See StatementCancelHandler in postgres.c. Roger that, I will definitely have a look and ask if I have any questions. Thanks a ton! Regards, Atri -- Regards, Atri l'apprenant