Re: Max connections reached without max connections reached

> Ok it is possible, I've got a C extension up and running which hooks > ExecutorStart, then once for each TX ( I monitor the nesting depth like in > pg_stat_statements, and only attach at the top level) attaches a sub > transaction callback, tracking start subtransaction events and incrementing >

Re: Max connections reached without max connections reached

> > > I'm guessing this won't be back-patched? Is it possible to somehow read > this information from a C function? > > Ok it is possible, I've got a C extension up and running which hooks ExecutorStart, then once for each TX ( I monitor the nesting depth like in pg_stat_statements, and only attac

Re: Max connections reached without max connections reached

+1, I too like the idea. The patch doesn't seem to be doing any heavy > lifting, I think that much overhead should be acceptable. > I'm guessing this won't be back-patched? Is it possible to somehow read this information from a C function? - James -- The contents of this email are confidential

Re: Max connections reached without max connections reached

On Mon, Dec 6, 2021 at 6:11 AM James Sewell wrote: >> >> Agreed with both points. What about we add, subxid count and overflow >> status in LocalPgBackendStatus and through that, we can show in >> pg_stat_activity. That way we don't have to report it ever and >> whenever the user is running pg_s

Re: Max connections reached without max connections reached

> > Agreed with both points. What about we add, subxid count and overflow > status in LocalPgBackendStatus and through that, we can show in > pg_stat_activity. That way we don't have to report it ever and > whenever the user is running pg_stat_activity they can fetch it > directly from "proc->sub

Re: Max connections reached without max connections reached

On Sun, Dec 5, 2021 at 10:55 AM Dilip Kumar wrote: > > On Fri, Dec 3, 2021 at 9:02 PM Tom Lane wrote: > > > > Dilip Kumar writes: > > > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar wrote: > > >> I think there is no such view or anything which tells about which > > >> backend or transaction has mo

Re: Max connections reached without max connections reached

On Fri, Dec 3, 2021 at 9:02 PM Tom Lane wrote: > > Dilip Kumar writes: > > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar wrote: > >> I think there is no such view or anything which tells about which > >> backend or transaction has more than 64 sub transaction. But if we > >> are ready to modify th

Re: Max connections reached without max connections reached

> On Fri, Dec 03, 2021 at 10:32:03AM -0500, Tom Lane wrote: > Dilip Kumar writes: > > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar wrote: > >> I think there is no such view or anything which tells about which > >> backend or transaction has more than 64 sub transaction. But if we > >> are ready to

Re: Max connections reached without max connections reached

Dilip Kumar writes: > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar wrote: >> I think there is no such view or anything which tells about which >> backend or transaction has more than 64 sub transaction. But if we >> are ready to modify the code then we can LOG that information in >> GetNewTransact

Re: Max connections reached without max connections reached

On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar wrote: > I think there is no such view or anything which tells about which > backend or transaction has more than 64 sub transaction. But if we > are ready to modify the code then we can LOG that information in > GetNewTransactionId(), when first time w

Re: Max connections reached without max connections reached

> > I expect my 'vote' counts for naught, but I fully expect seeing these show > up in the logs would have helped me much more quickly have insight into > what was going on during times of very high concurrency and extreme > slowness with many processes showing as waiting on LwLocks. > Is there an

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 9:06 PM Dilip Kumar wrote: > IMHO, it is good to LOG such information if we are not already logging > this anywhere. > +1 I expect my 'vote' counts for naught, but I fully expect seeing these show up in the logs would have helped me much more quickly have insight into wha

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent wrote: > On 12/1/21 7:08 PM, Michael Lewis wrote: > > On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > >> Also be on the look-out for begin trans; begin trans; etc I read >> Kumar's report as nested transactions. If your gang is doing a transaction

Re: Max connections reached without max connections reached

On Thu, Dec 2, 2021 at 3:59 AM James Sewell wrote: > > >> Looking again into the back trace[1], it appeared that the backend is >> getting stuck while getting the subtransaction's parent information, >> and that can only happen if the snapshot is getting marked as >> overflowed. So it seems that

Re: Max connections reached without max connections reached

On 12/1/21 7:08 PM, Michael Lewis wrote: On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent > wrote: Also be on the look-out for begin trans; begin trans; etc  I read Kumar's report as nested transactions.  If your gang is doing a transaction per row, they need

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > Also be on the look-out for begin trans; begin trans; etc I read Kumar's > report as nested transactions. If your gang is doing a transaction per > row, they need a kick in the rear. Anyone not closing those needs a pink > slip. > I have see

Re: Max connections reached without max connections reached

On 12/1/21 3:29 PM, James Sewell wrote: Looking again into the back trace[1], it appeared that the backend is getting stuck while getting the subtransaction's parent information, and that can only happen if the snapshot is getting marked as overflowed.  So it seems that some of t

Re: Max connections reached without max connections reached

> Looking again into the back trace[1], it appeared that the backend is > getting stuck while getting the subtransaction's parent information, > and that can only happen if the snapshot is getting marked as > overflowed. So it seems that some of the scripts are using a lot of > sub-transaction (>

Re: Max connections reached without max connections reached

On Tue, Nov 30, 2021 at 5:16 AM James Sewell wrote: >> >> How did you verify that, maybe some process started IO and stuck >> there? Can we check pg_stat_activity that is there some process that >> shows in the wait event as SLRURead/SLRUWrite and not coming out of >> that state? Looking again in

Re: Max connections reached without max connections reached

> > How did you verify that, maybe some process started IO and stuck > there? Can we check pg_stat_activity that is there some process that > shows in the wait event as SLRURead/SLRUWrite and not coming out of > that state? There are no interesting entries in pg_stat_activity, and no wait events

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 10:53 AM James Sewell wrote: > > >> > Seems like some of the processes are taking a long time or stuck while >> > reading/writing SLRU pages, and due to that while creating a new >> > connection the backend process is not able to check the transaction >> > status (from pg_x

Re: Max connections reached without max connections reached

> > Seems like some of the processes are taking a long time or stuck while > > reading/writing SLRU pages, and due to that while creating a new > > connection the backend process is not able to check the transaction > > status (from pg_xlog) of the pg_class tuple and gets stuck/taking a > > long ti

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 9:50 AM Dilip Kumar wrote: > > Does that shed any light? > > Seems like some of the processes are taking a long time or stuck while > reading/writing SLRU pages, and due to that while creating a new > connection the backend process is not able to check the transaction > st

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 9:50 AM Dilip Kumar wrote: > > On Thu, Nov 25, 2021 at 8:58 AM James Sewell > wrote: > >> > >> The hypothesis I'm thinking of is that incoming sessions are being blocked > >> somewhere before they can acquire a ProcArray entry; if so, they'd not > >> show up in either pg_

Re: Max connections reached without max connections reached

On Thu, Nov 25, 2021 at 8:58 AM James Sewell wrote: >> >> The hypothesis I'm thinking of is that incoming sessions are being blocked >> somewhere before they can acquire a ProcArray entry; if so, they'd not >> show up in either pg_stat_activity or pg_locks. What we have to look for >> then is evi

Re: Max connections reached without max connections reached

> > The hypothesis I'm thinking of is that incoming sessions are being blocked > somewhere before they can acquire a ProcArray entry; if so, they'd not > show up in either pg_stat_activity or pg_locks. What we have to look for > then is evidence of somebody holding a strong lock on a shared relati

Re: Max connections reached without max connections reached

James Sewell writes: >> I'm also wondering a bit about whether they're being blocked on a lock, >> eg. due to something taking an exclusive lock on pg_authid or pg_database. >> pg_locks might be interesting to check. > postgres=# select * from pg_locks where not granted; The hypothesis I'm think

Re: Max connections reached without max connections reached

> > > > It's used to trigger ETL jobs. There are other bigger consumers of > > connections - the issue isn't the stream of jobs, that works fine under > > normal operation. The issue is that when jobs stay in "startup" and > > don't enter pg_stat_activity the system spirals downwards and no new > >

Re: Max connections reached without max connections reached

On 11/23/21 16:58, James Sewell wrote: > re: EnterpriseDB yes it is - I'm having this same discussion with them > in parallel What version of theirs? PostgreSQL 11.9 (EnterpriseDB Advanced Server 11.9.18) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat

Re: Max connections reached without max connections reached

> > re: EnterpriseDB yes it is - I'm having this same discussion with them > > in parallel > > What version of theirs? > PostgreSQL 11.9 (EnterpriseDB Advanced Server 11.9.18) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit > > > > re: rundeck, yes - but th

Re: Max connections reached without max connections reached

On 11/23/21 16:23, James Sewell wrote: The enterprisedb is one of their customized versions? rundeck_cluster below refers to https://digital.ai/technology/rundeck ? re: EnterpriseDB yes it is - I'm having this same discussion with them i

Re: Max connections reached without max connections reached

> The enterprisedb is one of their customized versions? > > rundeck_cluster below refers to https://digital.ai/technology/rundeck? > > re: EnterpriseDB yes it is - I'm having this same discussion with them in parallel re: rundeck, yes - but this is only one of many things connecting. it's not doing

Re: Max connections reached without max connections reached

On 11/23/21 15:53, James Sewell wrote: It's v odd as it happens *sometimes* - having said that it's happening right this moment: [enterprisedb@oprpgs001 edb-as-11]$ ps -ef|grep postgres:  | wc -l 517 [enterprisedb@oprpgs001 ~]$ ps -ef|grep postgres: | grep -i start | wc -l 480 The enterpr

Re: Max connections reached without max connections reached

> > > So I guess the question becomes why are they spending so much time in > the startup state. That should take mere milliseconds, unless the > clients are being slow to handle the authentication exchange? > > I'm also wondering a bit about whether they're being blocked on a lock, > eg. due to s

Re: Max connections reached without max connections reached

> > > Sorry, I could have been clearer - pg_stat_activity is what I'm looking > > at - I'm recording connection info from here every 15 seconds (from a > > superuser account so I don't get locked out). It never peaks above 300 > > (in fact when the incident happens no new connections can come in so

Re: Max connections reached without max connections reached

James Sewell writes: > If I measure from `ps -ef | grep postgres` and look at the connections > then I can see that with the startup connections I am hitting this limit. > So client processes which are listed to the OS as "startup" ARE counted > towards the 597 connections, but are NOT reported i

Re: Max connections reached without max connections reached

On 11/23/21 14:56, James Sewell wrote: Sorry, I could have been clearer - pg_stat_activity is what I'm looking at - I'm recording connection info from here every 15 seconds (from a superuser account so I don't get locked out). It never peaks above 300 (in fact when the incident happens no new

Re: Max connections reached without max connections reached

> > What are you looking at to claim the number of connections is under 600? > Maybe there's some disconnect between what you're measuring and what the > database thinks. > > A different line of thought is that ProcArray slots can be consumed by > things that aren't client connection processes, in

Re: Max connections reached without max connections reached

James Sewell writes: > The system handles a lot of connections - we have a max_connections of 600. > Most are long lived JDBC, but there are a lot of ETL / ad-hoc jobs etc. > Connections normally sit at 300ish, with 70 active at the most. The > machines have 32 CPU cores . PgBouncer is sadly not

Max connections reached without max connections reached

Hi, This is a bit of an odd one - I'm on PostgreSQL 11.9 with a single streaming replica. The system handles a lot of connections - we have a max_connections of 600. Most are long lived JDBC, but there are a lot of ETL / ad-hoc jobs etc. Connections normally sit at 300ish, with 70 active at the