public inbox for [email protected]help / color / mirror / Atom feed
Vacuum full connection exhaustion 8+ messages / 5 participants [nested] [flat]
* Vacuum full connection exhaustion @ 2024-08-07 17:34 Costa Alexoglou <[email protected]> 0 siblings, 3 replies; 8+ messages in thread From: Costa Alexoglou @ 2024-08-07 17:34 UTC (permalink / raw) To: [email protected] Hey folks, I noticed something weird, and not sure if this is the expected behaviour or not in PostgreSQL. So I am running Benchbase (a benchmark framework) with 50 terminals (50 concurrent connections). There are 2-3 additional connections, one for a postgres-exporter container for example. So far so good, and with a `max_connections` at 100 there is no problem. What happens is that if I execute manually `VACUUM FULL` the connections are exhausted. Also tried this with 150 `max_connections` to see if it just “doubles” the current connections, but as it turned out, it still exhausted all the connections until it reached `max_connections`. This was cross-checked, as the postgres-exporter could not connect, and I manually was not allowed to connect with `psql`. Is this expected or is this a bug? postgres-exporter logs: ``` sql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: sorry, too many clients already ``` ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-08 10:22 Ron Johnson <[email protected]> parent: Costa Alexoglou <[email protected]> 2 siblings, 0 replies; 8+ messages in thread From: Ron Johnson @ 2024-08-08 10:22 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Thu, Aug 8, 2024 at 5:18 AM Costa Alexoglou <[email protected]> wrote: > Hey folks, > > I noticed something weird, and not sure if this is the expected behaviour > or not in PostgreSQL. > > So I am running Benchbase (a benchmark framework) with 50 terminals (50 > concurrent connections). > There are 2-3 additional connections, one for a postgres-exporter > container for example. > > So far so good, and with a `max_connections` at 100 there is no problem. > What happens is that if I execute manually `VACUUM FULL` > Off-topic, but... *WHY?? *It almost certainly does not do what you think it does. Especially if it's just "VACUUM FULL;" the connections are exhausted. > Connect to the relevant database and run this query. Don't disconnect, and keep running it over and over again as you run the "VACUUM FULL;". That'll tell you exactly what happens. select pid ,datname as db ,application_name as app_name ,case when client_hostname is not null then client_hostname else client_addr::text end AS client_name ,usename ,to_char((EXTRACT(epoch FROM now() - backend_start))/60.0, '99,999.00') as backend_min ,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS.MS') as "Query Start" ,to_char((EXTRACT(epoch FROM now() - query_start))/60.0, '99,999.00') as qry_min ,to_char(xact_start, 'YYYY-MM-DD HH24:MI:SS.MS') as "Txn Start" ,to_char((EXTRACT(epoch FROM now() - xact_start)/60.0), '999.00') as txn_min ,state query from pg_stat_activity WHERE pid != pg_backend_pid() order by 6 desc; > > Also tried this with 150 `max_connections` to see if it just “doubles” the > current connections, but as it turned out, it still exhausted all the > connections until it reached `max_connections`. > Double it again? > > This was cross-checked, as the postgres-exporter could not connect, and I > manually was not allowed to connect with `psql`. > > Is this expected or is this a bug? > Depends on what you set these to: autovacuum_max_workers max_parallel_maintenance_workers max_parallel_workers max_parallel_workers_per_gather max_worker_processes -- Death to America, and butter sauce! Iraq lobster... ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-08 11:43 Francisco Olarte <[email protected]> parent: Costa Alexoglou <[email protected]> 2 siblings, 0 replies; 8+ messages in thread From: Francisco Olarte @ 2024-08-08 11:43 UTC (permalink / raw) To: Costa Alexoglou <[email protected]>; +Cc: [email protected] On Thu, 8 Aug 2024 at 11:18, Costa Alexoglou <[email protected]> wrote: ... > So I am running Benchbase (a benchmark framework) with 50 terminals (50 concurrent connections). > There are 2-3 additional connections, one for a postgres-exporter container for example. ... > So far so good, and with a `max_connections` at 100 there is no problem. What happens is that if I execute manually `VACUUM FULL` the connections are exhausted. > Also tried this with 150 `max_connections` to see if it just “doubles” the current connections, but as it turned out, it still exhausted all the connections until it reached `max_connections`. > This was cross-checked, as the postgres-exporter could not connect, and I manually was not allowed to connect with `psql`. Have you tried to check where the connections are coming from and what are they doing? Apart from the max-paralell-worker stuff already commented by Ron in an scenario with a long live locking processes ( vacuum full ) combined with potentially aggresive connecting ( a benchmark tool ) I would verify the benchmark tool is not timing out and disconnecting improperly leaving connections hung up. Francisco Olarte. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-08 14:11 Christophe Pettus <[email protected]> parent: Costa Alexoglou <[email protected]> 2 siblings, 2 replies; 8+ messages in thread From: Christophe Pettus @ 2024-08-08 14:11 UTC (permalink / raw) To: Costa Alexoglou <[email protected]>; +Cc: [email protected] > On Aug 7, 2024, at 10:34, Costa Alexoglou <[email protected]> wrote: > > Hey folks, > > I noticed something weird, and not sure if this is the expected behaviour or not in PostgreSQL. > > So I am running Benchbase (a benchmark framework) with 50 terminals (50 concurrent connections). > There are 2-3 additional connections, one for a postgres-exporter container for example. > > So far so good, and with a `max_connections` at 100 there is no problem. What happens is that if I execute manually `VACUUM FULL` the connections are exhausted. VACUUM FULL takes an exclusive lock on the table that it is operating on. It's possible that a connection becomes blocked on that exclusive lock waiting for the VACUUM FULL to finish, the application sees the connection stopped and fires up another one (this is common in container-based applications), that one blocks... until all of the connections are full of queries waiting on that VACUUM FULL. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-08 23:02 David Rowley <[email protected]> parent: Christophe Pettus <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: David Rowley @ 2024-08-08 23:02 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: Costa Alexoglou <[email protected]>; [email protected] On Fri, 9 Aug 2024 at 02:12, Christophe Pettus <[email protected]> wrote: > VACUUM FULL takes an exclusive lock on the table that it is operating on. It's possible that a connection becomes blocked on that exclusive lock waiting for the VACUUM FULL to finish, the application sees the connection stopped and fires up another one (this is common in container-based applications), that one blocks... until all of the connections are full of queries waiting on that VACUUM FULL. I also imagine this is the cause. One way to test would be to do: BEGIN; LOCK TABLE <name of table>; and see if the connections pile up in a similar way to when the VACUUM FULL command is used. David ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-09 04:15 Ron Johnson <[email protected]> parent: Christophe Pettus <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Ron Johnson @ 2024-08-09 04:15 UTC (permalink / raw) To: pgsql-general On Thu, Aug 8, 2024 at 10:12 AM Christophe Pettus <[email protected]> wrote: > > > > On Aug 7, 2024, at 10:34, Costa Alexoglou <[email protected]> wrote: > > > > Hey folks, > > > > I noticed something weird, and not sure if this is the expected > behaviour or not in PostgreSQL. > > > > So I am running Benchbase (a benchmark framework) with 50 terminals (50 > concurrent connections). > > There are 2-3 additional connections, one for a postgres-exporter > container for example. > > > > So far so good, and with a `max_connections` at 100 there is no problem. > What happens is that if I execute manually `VACUUM FULL` the connections > are exhausted. > > VACUUM FULL takes an exclusive lock on the table that it is operating on. > It's possible that a connection becomes blocked on that exclusive lock > waiting for the VACUUM FULL to finish, the application sees the connection > stopped and fires up another one (this is common in container-based > applications), that one blocks... until all of the connections are full of > queries waiting on that VACUUM FULL. > > "I see a lock, so let's cause another one!" That's crazy. -- Death to America, and butter sauce. Iraq lobster! ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-09 04:17 Christophe Pettus <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Christophe Pettus @ 2024-08-09 04:17 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general > On Aug 8, 2024, at 21:15, Ron Johnson <[email protected]> wrote: > > "I see a lock, so let's cause another one!" That's crazy. It's more "Oh, look, I need a connection to service this web request, but my pool is empty, so I'll just fire up a new connection to the server," lather, rinse, repeat. Pretty common these days, sadly. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum full connection exhaustion @ 2024-08-09 09:47 Costa Alexoglou <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Costa Alexoglou @ 2024-08-09 09:47 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Christophe Pettus <[email protected]>; [email protected] On Fri, Aug 9, 2024 at 1:02 AM David Rowley <[email protected]> wrote: > On Fri, 9 Aug 2024 at 02:12, Christophe Pettus <[email protected]> wrote: > > VACUUM FULL takes an exclusive lock on the table that it is operating > on. It's possible that a connection becomes blocked on that exclusive lock > waiting for the VACUUM FULL to finish, the application sees the connection > stopped and fires up another one (this is common in container-based > applications), that one blocks... until all of the connections are full of > queries waiting on that VACUUM FULL. > > I also imagine this is the cause. One way to test would be to do: > BEGIN; LOCK TABLE <name of table>; and see if the connections pile up > in a similar way to when the VACUUM FULL command is used. > > David > Thanks folks. David really straight-forward way to test. I validated this, when I lock the two tables involved in the benchmark the connections are constantly growing until they reach the `max_connections` ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-08-09 09:47 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]> 2024-08-08 10:22 ` Ron Johnson <[email protected]> 2024-08-08 11:43 ` Francisco Olarte <[email protected]> 2024-08-08 14:11 ` Christophe Pettus <[email protected]> 2024-08-08 23:02 ` David Rowley <[email protected]> 2024-08-09 09:47 ` Costa Alexoglou <[email protected]> 2024-08-09 04:15 ` Ron Johnson <[email protected]> 2024-08-09 04:17 ` Christophe Pettus <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox