[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Active sessions does not terminated due to statement_timeout
From: Magnus Hagander <magnus () hagander ! net>
Date: 2024-03-26 14:43:19
Message-ID: CABUevEx0LbynTLU1z3yEsG8rCQnKqDMNjtHXvBCHXicZsb9_Jg () mail ! gmail ! com
[Download RAW message or body]
On Tue, Mar 26, 2024 at 3:19 PM Ц <pfunk@mail.ru> wrote:
> Greetings!
> I've faced with strange behavior when I see a lot of active sessions
> started hours ago while statement_timeout = '30min'.
> All of them are fetching from cursors.
>
> Typical session looks like:
> backend_start | 2024-03-26 14:34:20.552594+03
> xact_start | 2024-03-26 14:34:54.974628+03
> query_start | 2024-03-26 14:35:02.024133+03
> state_change | 2024-03-26 14:35:02.024134+03
> wait_event_type | Client
> wait_event | ClientWrite
> state | active
> backend_xid | 23240392
> backend_xmin | 23226474
> query | fetch all from "<unnamed portal 20>"
> backend_type | client backend
>
>
> They are accumulating up to tens by the end of the day with all negative
> impacts on performance.
> Initially I thought that clients already died but due to network issues
> database considers them to be alive. So I set tcp_keepalive GUCs to nonzero
> values. Without success.
> Then I checked connections from the app server side and found them in
> ESTABLISHED state.
> It's certainly an application fault and it should not hold cursor
> forever...but
>
> Is the any GUC parameters to fight with such «clients »?
>
>
I wonder if this might be the bug I saw in
https://www.postgresql.org/message-id/CABUevExBm_va9+iW0kgVuZbrLDUZ8VnL2wo2ig7jqqdGsy8ZKQ@mail.gmail.com
-- basically that there's some path when we're in ClientWrite that it
doesn't check for interrupts properly. I've unfortunately not had time to
dig into that one anymore.
What version of PostgreSQL and what platform are you on?
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Tue, Mar 26, 2024 at 3:19 PM Ц <<a \
href="mailto:pfunk@mail.ru">pfunk@mail.ru</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"> <div><div>Greetings!</div><div>I've faced with \
strange behavior when I see a lot of active sessions started hours ago while \
statement_timeout = '30min'.</div><div><div><div>All of them are fetching \
from cursors.</div><div> </div><div>Typical session looks \
like:</div><div>backend_start | 2024-03-26 \
14:34:20.552594+03</div><div>xact_start | 2024-03-26 \
14:34:54.974628+03</div><div>query_start | 2024-03-26 \
14:35:02.024133+03</div><div>state_change | 2024-03-26 \
14:35:02.024134+03</div><div>wait_event_type | Client</div><div>wait_event \
| ClientWrite</div><div>state | \
active</div><div>backend_xid | 23240392</div><div>backend_xmin | \
23226474</div><div>query | fetch all from \
"<unnamed portal 20>"</div><div>backend_type | client \
backend</div><div> </div><div> </div><div>They are accumulating up to tens by the \
end of the day with all negative impacts on performance.</div><div>Initially I \
thought that clients already died but due to network issues database considers them \
to be alive. So I set tcp_keepalive GUCs to nonzero values. Without \
success.</div><div>Then I checked connections from the app server side and found them \
in ESTABLISHED state.</div></div></div><div>It's certainly an application fault \
and it should not hold cursor forever...but</div><div> </div><div>Is the any GUC \
parameters to fight with such «clients \
»?</div><div><br></div></div></blockquote><div><br></div><div>I wonder if this might \
be the bug I saw in <a \
href="https://www.postgresql.org/message-id/CABUevExBm_va9+iW0kgVuZbrLDUZ8VnL2wo2ig7jq \
qdGsy8ZKQ@mail.gmail.com">https://www.postgresql.org/message-id/CABUevExBm_va9+iW0kgVuZbrLDUZ8VnL2wo2ig7jqqdGsy8ZKQ@mail.gmail.com</a> \
-- basically that there's some path when we're in ClientWrite that it \
doesn't check for interrupts properly. I've unfortunately not had time to dig \
into that one anymore.</div><div><br></div><div>What version of PostgreSQL and what \
platform are you on? </div></div><div><br></div><span \
class="gmail_signature_prefix">-- </span><br><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><div> Magnus Hagander<br> Me: <a \
href="http://www.hagander.net/" target="_blank">https://www.hagander.net/</a><br> \
Work: <a href="http://www.redpill-linpro.com/" \
target="_blank">https://www.redpill-linpro.com/</a></div></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic