Find why a server is running more sessions at once than it has CPUs to run them on.
promptread-only tools
triage-active-sessions([connection])The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Work out why this server has so many sessions running at once on connection shop_prod. Two things have to be established before any of it means anything, and both are routinely assumed instead. First, ACTIVE IS NOT ON-CPU. pg_stat_activity calls a backend active while it is executing a statement, and a backend waiting on a lock, on a disk read, or on a parallel sibling is executing a statement. A server with fifty active sessions where forty-five are waiting on locks is not short of CPU; it has one blocker and a queue. Only backends with no wait event are actually competing for CPU, and that is the number to compare against the core count. Second, HIGH CONCURRENCY IS USUALLY A SYMPTOM. The number of sessions in flight is the arrival rate multiplied by how long each one takes, so it rises when statements get slower even though nothing about the load changed. Treating that as a capacity problem -- more cores, a bigger pool -- treats the symptom and leaves the cause. Establish which of the two moved before proposing anything. 0. Call checkPrivileges. Without stats access currentActivity hides the query text of other roles, which is most of what this reads, and statementStats hides most of its rows. 1. Call hostCapacity for the core count. If it reports host vCPUs unknown, stop with a partial answer and say so: PostgreSQL cannot see the machine it runs on and the number has to be declared per connection. Everything below is a comparison against it, so guessing it invents the conclusion. 2. Call currentActivity and count properly, because the raw number of active rows is not the concurrency. - Keep only client backends. Autovacuum workers, the walsender, the checkpointer and background workers all appear here and none of them is user concurrency, though they do compete for the same cores -- count them separately rather than dropping them. - Collapse parallel workers into their leaders using leader_pid. One query with four workers is five rows and one unit of user concurrency. Report both numbers, because the gap between them is how much of the load is parallelism rather than clients, and max_parallel_workers_per_gather above 1 means a single statement can oversubscribe the machine by itself. - Split what remains by wait_event_type, and read wait_event_description where it is unfamiliar rather than guessing from the name. 3. The split is the diagnosis, and it decides which investigation this actually is. Compare ONLY the no-wait group against the core count from step 1. - No wait event: on CPU. If this is at or above the core count the server is genuinely CPU-bound and step 4 is the question. If it is well below, the server is not CPU-bound whatever the active total says, and the dominant wait below is the real subject. - Lock: they are queued behind something, not working. Hand off to triage-lock-contention, which follows the chain to its root. A single blocker can make a server look saturated. - IO: reading from disk rather than from cache. That is buffer-cache-review, and ioStats separates real pressure from a large sequential scan recycling its own ring buffer on purpose. - LWLock: contention inside PostgreSQL rather than on user data, commonly around WAL or buffer mapping. checkpointStats and ioStats are where that shows, and it usually means the write path rather than the query. - IPC: frequently parallel workers waiting on each other, which points back at the parallelism settings rather than at the statements. - Client: waiting on the application to send or read. The database is not the bottleneck; something on the other side is slow, or a transaction is being held open across application work. 4. Only if the no-wait group really is at the core count: find out which of the two terms moved, because they need opposite fixes. Call statementStats. - More statements arriving, with mean_exec_time roughly where it was, is genuine growth. That is a capacity answer: a connection pooler to bound the concurrency, or more cores. - The same statements taking longer than they did is a regression, and the concurrency is its consequence rather than its cause. Take the worst offender to diagnose-slow-query. A plan that flipped, statistics that went stale, a table that bloated or a working set that outgrew the cache will all show here as a load problem and none of them is one. - Say which it is and what the reading was. If you cannot tell because there is no earlier figure to compare against, say that too, and note that mean_exec_time needs a before-value or a reset to mean anything. 5. Read the duration spread from query_duration_s and xact_duration_s, because an average hides the shape. Many short statements and a few very long ones are different problems: the second is not concurrency at all, and a long-running transaction also holds back the xmin horizon, which makes it bloat-and-vacuum-review's problem simultaneously. Name the applications behind them using application_name and client_addr, since a pid is gone by the time anyone acts on the report and a connection pool or a deploy is something an operator can go and change. 6. Check whether the pool is the problem rather than the workload. databaseStats gives numbackends against max_connections, and the session counters say whether connections are churning: sizing anything against a backend count that is high because sessions keep dying is sizing for the symptom. Without a pooler in front, every burst opens real backends and each one is a work_mem allocation waiting to happen -- capacity-check does that arithmetic. 7. Account for the work nobody asked for. progressStats shows a VACUUM, CREATE INDEX or CLUSTER in flight, any of which takes parallel workers and I/O from everything else; wraparoundStatus shows an antiwraparound autovacuum, which will not yield and cannot be postponed, and which explains a server that is busy with no corresponding user load. 8. Report the count that matters -- backends genuinely on CPU against cores -- not the active total, and say what the rest were waiting on. Then the cause, then the fix, ranked. Be explicit that a connection pooler bounds the damage rather than repairing it: it stops an overloaded server from getting worse, and it makes no statement faster.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "triage-active-sessions",
"arguments": {
"connection": "shop_prod"
}
}
}