RE: Query Spins - Mailing list pgsql-admin

More information....
1) At 5:30pm, the query is fired 5 times from a client
2) 4 of them finished. One stuck (spins - takes CPU)
3) The underlying tables are pretty big and dynamic. But I have been running autovacuum and analyze aggressively at
tablelevel.
 
4) max_parallel_workers and max_worker_processes are both set to 8.

I collected following info just a little after 5:30pm after all 5 queries have fired.

ps -aef | grep postgres | grep worker

postgres 2162504 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162501
postgres 2162505 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162501
postgres 2162506 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162502
postgres 2162507 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162502
postgres 2162508 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162503
postgres 2162509 1802555 99 17:30 ?        00:00:04 postgres: parallel worker for PID 2162503
postgres 2162827 1802555 99 17:30 ?        00:00:03 postgres: parallel worker for PID 2162703

It is almost certain that max_parallel_workers have exhausted. So, what? The remaining query should not get stuck!

The stuck query leader PID is 2162502. The parallel workers of this PID 2162502 are long gone.
ps -aef | grep 2162506 | grep -v grep
ps -aef | grep 2162507 | grep -v grep

Following is from pg_stat_activity:

pid           | 2162502
client_port   | 37264
xact_start    | 2025-09-04 17:30
query_start   | 2025-09-04 17:30
state_change  | 2025-09-04 17:30
wait_event    | 
state         | active
now           | 2025-09-04 20:10
time_runnning | 02:40:00.509246

Interesting part is, temp file... last change timestamp changes but file size doesn't. File pgsql_tmp2162502.1 has been
at242155520 bytes for a long time but the last change time stamp keeps changing.
 

ls -ltr base/pgsql_tmp/
total 277864
-rw-------. 1 postgres postgres  16359424 Sep  4 17:30 pgsql_tmp2162502.0
-rw-------. 1 postgres postgres 242155520 Sep  4 20:21 pgsql_tmp2162502.1



-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at> 
Sent: Monday, September 1, 2025 2:52 AM
To: Murthy Nunna <mnunna@fnal.gov>; pgsql-admin@lists.postgresql.org
Subject: Re: Query Spins

[EXTERNAL] – This message is from an external sender

On Mon, 2025-09-01 at 03:52 +0000, Murthy Nunna wrote:
> Pg16.10
>
> I have a query which runs fine most of the time. When it runs fine, it 
> spawns parallel workers. In pg_stat_activity, wait_event is blank, 
> state is active and backend_type = "client backend" for the main 
> query. For parallel workers of this query I see wait_event = 
> MessageQueueSend, state is active and backend_type = "parallel worker"
>
> But some times, it has no parallel workers. wait_event is blank, state 
> is active and backend_type =client backend. And it never ends. It takes up lot of CPU.
> The socket on both server and client server are in ESTABLISHED state 
> (netstat -tulpa | grep <client_port>).

Perhaps no workers are spawned because "max_parallel_workers" has already been exhausted by other backends.  Check for
thenumber of concurrent parallel workers next time you get the error.
 

I cannot know the source of your performance problems, but perhaps it is a combination of system overload and lack of
availableparallel worker processes, which might well go together.
 

Yours,
Laurenz Albe