public inbox for [email protected]
help / color / mirror / Atom feedVacuum full connection exhaustion
8+ messages / 5 participants
[nested] [flat]
* Vacuum full connection exhaustion
@ 2024-08-07 17:34 Costa Alexoglou <[email protected]>
2024-08-08 10:22 ` Re: Vacuum full connection exhaustion Ron Johnson <[email protected]>
2024-08-08 11:43 ` Re: Vacuum full connection exhaustion Francisco Olarte <[email protected]>
2024-08-08 14:11 ` Re: Vacuum full connection exhaustion Christophe Pettus <[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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
@ 2024-08-08 10:22 ` Ron Johnson <[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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
@ 2024-08-08 11:43 ` Francisco Olarte <[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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
@ 2024-08-08 14:11 ` Christophe Pettus <[email protected]>
2024-08-08 23:02 ` Re: Vacuum full connection exhaustion David Rowley <[email protected]>
2024-08-09 04:15 ` Re: Vacuum full connection exhaustion Ron Johnson <[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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
2024-08-08 14:11 ` Re: Vacuum full connection exhaustion Christophe Pettus <[email protected]>
@ 2024-08-08 23:02 ` David Rowley <[email protected]>
2024-08-09 09:47 ` Re: Vacuum full connection exhaustion Costa Alexoglou <[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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
2024-08-08 14:11 ` Re: Vacuum full connection exhaustion Christophe Pettus <[email protected]>
2024-08-08 23:02 ` Re: Vacuum full connection exhaustion David Rowley <[email protected]>
@ 2024-08-09 09:47 ` Costa Alexoglou <[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
* Re: Vacuum full connection exhaustion
2024-08-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
2024-08-08 14:11 ` Re: Vacuum full connection exhaustion Christophe Pettus <[email protected]>
@ 2024-08-09 04:15 ` Ron Johnson <[email protected]>
2024-08-09 04:17 ` Re: Vacuum full connection exhaustion 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-07 17:34 Vacuum full connection exhaustion Costa Alexoglou <[email protected]>
2024-08-08 14:11 ` Re: Vacuum full connection exhaustion Christophe Pettus <[email protected]>
2024-08-09 04:15 ` Re: Vacuum full connection exhaustion Ron Johnson <[email protected]>
@ 2024-08-09 04:17 ` Christophe Pettus <[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
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