public inbox for [email protected]
help / color / mirror / Atom feedTroubleshooting a brief spike in active sessions
4+ messages / 3 participants
[nested] [flat]
* Troubleshooting a brief spike in active sessions
@ 2026-01-09 21:41 Dirschel, Steve-CW <[email protected]>
2026-01-09 21:58 ` Re: Troubleshooting a brief spike in active sessions Joshua Banton <[email protected]>
2026-01-09 22:27 ` Re: Troubleshooting a brief spike in active sessions Nikolay Samokhvalov <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Dirschel, Steve-CW @ 2026-01-09 21:41 UTC (permalink / raw)
To: [email protected] <[email protected]>
Aurora Postgres version 17.4
This application mainly runs 3 queries very frequently- around 12000 executions per second across the 3. Normally the server handles this fine and at any given time there are between 0 - 10 active sessions running the queries. The 3 queries are tuned- doing 3-7 logical reads per execute and run in < .1 millisecond.
Periodically there will be a huge spike in active sessions up to 400+ sessions all on CPU (it can be higher depending on how many app servers are in the mix at the time). This spike will last 2-5 seconds but it causes an increase in app response time which is a problem.
We snap info from pg_stat_activity (where state <> ‘idle' ) every 1 second for troubleshooting issues and a couple observations from that snap data when this spike happens:
1.
The app sessions will show as wait_event_type and wait_event as null which I understand means they are on CPU.
2.
Some of those sessions will not have queryid populated. But all the app sessions do is execute queries.
3.
With aurora Postgres they store plan information in pg_stat_activity in planid column. For some sessions the queryid will be populated but the planid will not be populated. According to AWS this can happen if the session has completed parsing the query but has not finished plan generation or sessions are in the bind phase where parameter binding occurs before final plan selection.
With Aurora Postgres we do not have access to the server to look at the unix processes but we enabled enhanced monitoring in 1 second intervals which does provide a little info on session processes. One thing that stood out when the active session spike happens if I looked at the process list (it only shows ~ 95 of the processes) I would see a bunch like this:
"vss": 640900784,
"name": "postgres: prcoffrcnsmruser prcoffrcnsmr 172.29.145.159(53026) BIND",
"tgid": 26247,
"parentID": 584,
"memoryUsedPc": 0.02,
"cpuUsedPc": 0.06,
"id": 26247,
"rss": 30496,
"vmlimit": “unlimited"
Where they had BIND next to the process info (vs others will show things like idle or SELECT). In this case of the 95 processes shown it may have shown 59 of them were idle, 2 were SELECT, 2 were PARSE, and 32 were BIND.
So based on these processes showing BIND and pg_stat_activity either not showing a queryid or planid if it did show a queryid it seems these sessions may be getting hung up parsing the queries or specifically at the step it does binding.
Can anyone come up with any theories what may be going on here or is my description/explanation above too vague? The app is using prepared statements. The connection pool is HikariCP which does not support caching prepared statements. The thing that throws me off is so far I’m unable to see anything the app is doing to cause this. I don’t see a spike in query activity. I don’t see a spike in new connections from the pools. I don’t see anything leading up to this other than this spike in sessions out of nowhere. I don’t see any resource issues on the server. It will run this same load consistently throughout the day and the active session spike may happen 0 - 10 times in a day.
And ideas would be appreciated.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Troubleshooting a brief spike in active sessions
2026-01-09 21:41 Troubleshooting a brief spike in active sessions Dirschel, Steve-CW <[email protected]>
@ 2026-01-09 21:58 ` Joshua Banton <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Joshua Banton @ 2026-01-09 21:58 UTC (permalink / raw)
To: Dirschel, Steve-CW <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, Jan 9, 2026 at 4:41 PM Dirschel, Steve-CW <
[email protected]> wrote:
> Aurora Postgres version 17.4
>
> This application mainly runs 3 queries very frequently- around 12000
> executions per second across the 3. Normally the server handles this fine
> and at any given time there are between 0 - 10 active sessions running the
> queries. The 3 queries are tuned- doing 3-7 logical reads per execute and
> run in < .1 millisecond.
>
> Periodically there will be a huge spike in active sessions up to 400+
> sessions all on CPU (it can be higher depending on how many app servers are
> in the mix at the time). This spike will last 2-5 seconds but it causes an
> increase in app response time which is a problem.
>
I'm not super familiar with Aurora, but I've seen this caused by a few
different things in vanilla Postgres. I don't think Aurora has checkpoints,
so it's probably not that. I have seen where a vacuum can cause various
Postgres buffers to have a lot of churn, which can cause higher query
latency for a period of time. I have also seen temporary increased disk
latency to cause higher query latency.
Josh B
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Troubleshooting a brief spike in active sessions
2026-01-09 21:41 Troubleshooting a brief spike in active sessions Dirschel, Steve-CW <[email protected]>
@ 2026-01-09 22:27 ` Nikolay Samokhvalov <[email protected]>
2026-01-09 22:29 ` Re: Troubleshooting a brief spike in active sessions Nikolay Samokhvalov <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Nikolay Samokhvalov @ 2026-01-09 22:27 UTC (permalink / raw)
To: Dirschel, Steve-CW <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, Jan 9, 2026 at 1:41 PM Dirschel, Steve-CW <
[email protected]> wrote:
>
> Aurora Postgres version 17.4
>
> ...
>
> The app sessions will show as wait_event_type and wait_event as null
which I understand means they are on CPU
>
> ...
Not necessarily. It also might be an uninstrumented wait event.
Do the spikes of "CPU" AAS (I prefer to call them "CPU*") correspond well
to the spikes of high usage?
If you have N active sessions marked as CPU in ASH, does it match to the
CPU load of similar scale?
If the VM has much less than 400 vCPUs and you see overall CPU load far
from 100%, then we should suspect some missing wait event.
Nik
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Troubleshooting a brief spike in active sessions
2026-01-09 21:41 Troubleshooting a brief spike in active sessions Dirschel, Steve-CW <[email protected]>
2026-01-09 22:27 ` Re: Troubleshooting a brief spike in active sessions Nikolay Samokhvalov <[email protected]>
@ 2026-01-09 22:29 ` Nikolay Samokhvalov <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Nikolay Samokhvalov @ 2026-01-09 22:29 UTC (permalink / raw)
To: Dirschel, Steve-CW <[email protected]>; +Cc: [email protected] <[email protected]>
BTW, what are you logging settings for slow queries?
(log_min_duration_statement, auto_explain.log_min_duration)
On Fri, Jan 9, 2026 at 2:27 PM Nikolay Samokhvalov <[email protected]> wrote:
> On Fri, Jan 9, 2026 at 1:41 PM Dirschel, Steve-CW <
> [email protected]> wrote:
> >
> > Aurora Postgres version 17.4
> >
> > ...
> >
> > The app sessions will show as wait_event_type and wait_event as null
> which I understand means they are on CPU
> >
> > ...
>
>
> Not necessarily. It also might be an uninstrumented wait event.
>
> Do the spikes of "CPU" AAS (I prefer to call them "CPU*") correspond well
> to the spikes of high usage?
>
> If you have N active sessions marked as CPU in ASH, does it match to the
> CPU load of similar scale?
>
> If the VM has much less than 400 vCPUs and you see overall CPU load far
> from 100%, then we should suspect some missing wait event.
>
> Nik
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-01-09 22:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-09 21:41 Troubleshooting a brief spike in active sessions Dirschel, Steve-CW <[email protected]>
2026-01-09 21:58 ` Joshua Banton <[email protected]>
2026-01-09 22:27 ` Nikolay Samokhvalov <[email protected]>
2026-01-09 22:29 ` Nikolay Samokhvalov <[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