Dmitriy Trunov

Posted on Oct 11

AWS DevOps Agent, Part 4: RDS Connection Exhaustion, and Why You Should Still Check the AI's Answers

#agents #aws #database #devops

Part 4 of 4. Overview · Setup · EC2 · Network · RDS

"Too many connections" is one of the most common database incidents, and diagnosing it usually takes a lot of manual work: Performance Insights, SHOW PROCESSLIST, parameter groups and CloudWatch, all in separate tabs. This was the most interesting lab in the workshop. The agent's investigation was the best of the three, and then the follow-up chat taught me something about how far to trust it.

The setup

Lab architecture (redrawn from the AWS DevOps Agent workshop)

ecommerce-db: RDS MySQL on db.t3.medium (2 vCPU, 4 GB), with Performance Insights and Enhanced Monitoring, and max_connections=150 in a custom parameter group
Lab4-BastionHost: the only host that can reach the database, accessed through SSM Session Manager (no SSH keys)
Lab4-RDS-HighConnections: an alarm on DatabaseConnections above 100

From the bastion:

sudo su - ec2-user
cat baseline_complete.txt         # Connection Status: SUCCESS
cd ~/scripts
./simulate-db-performance-issue.sh

The script:

creates an ecommerce database (10K customers, 5K products, 20K orders)
starts 100 concurrent inefficient queries (full scans, complex JOINs)
opens 130 connections, then 140, close to the 150 limit
keeps the CPU busy with continuous heavy queries

The alarm fired within a couple of minutes.

The investigation: about 16 minutes

I triggered it at 11:40:04 and the banner said Investigation completed at 11:56:03.

Severity: high. The summary:

ecommerce-db experienced connection exhaustion: DatabaseConnections stepped from 0 to ~148 (flat against the max_connections=150 ceiling) and CPUUtilization pinned at ~100% in the same minute, causing "Too many connections" errors and elevated query latency.

And the cause:

A runaway, manually launched workload from the Lab4-BastionHost client (10.10.20.201), authenticating as MySQL superuser admin, started right after an interactive login by principal Participant at 09:36 UTC. It combined ~108 concurrent SELECT SLEEP(?) sessions holding connections open with a pathological customers CROSS JOIN customers … ORDER BY RAND() query (~38 active sessions) pinning the 2 vCPUs at ~100%.

This is close to what an experienced DBA would write. It used Performance Insights to split the load into "User sleep" (108 AAS) and executing CROSS JOIN (38 AAS). It traced the workload to one host, one DB user, and one interactive login. It explained all three symptoms (connection exhaustion, CPU and latency) with a single cause. It also listed what it ruled out: no infrastructure, parameter group, security group or scaling change; not I/O-bound; not memory-bound; no lock contention; no gradual leak.

The connections-and-CPU chart shows the incident in one view:

It found a second problem I didn't plant

Under Investigation gaps:

RDS log file download blocked by IAM permissions (rds:DownloadDBLogFilePortion denied to the agent role); the CloudWatch error log stopped ingesting after 2026-09-30 and the general/slowquery log groups were never created despite being enabled for export.

So the log export was broken even though the configuration said it was enabled. In a real incident you'd go looking for the slow query log and find nothing. The agent added this to the mitigation plan as its own item ("Repair CloudWatch log export so future diagnosis is not hampered").

The mitigation plan

Kill the runaway MySQL sessions from the Lab4-BastionHost client, stop the source process, and raise max_connections from 150 back toward the db.t3.medium engine default (~341).

The steps are specific to RDS:

List the offending sessions with information_schema.PROCESSLIST WHERE HOST LIKE '10.10.20.201%'
Kill them with CALL mysql.rds_kill(<id>), because on RDS you can't use plain KILL for other users' threads
A helpful warning: "With connections near the 150 cap, you may need to connect using the RDS reserved superuser slot… if 'Too many connections' blocks login, briefly raise max_connections first."
Stop the source process on the bastion through SSM. Without this, "mysql.rds_kill alone will not hold; connections will be re-opened within seconds."
Raise max_connections to 341. It's a dynamic parameter, so no reboot is needed. It also warns you to watch FreeableMemory and SwapUsage.

Then post-validate (parameter value, connections dropping, alarm back to OK) and a rollback command.

Agent-ready spec

At the end there's a "Next steps → Agent-ready spec" block with Copy spec. It's a list of change requirements written for a coding agent (or a developer) to implement in source code and IaC:

Introduce bounded, pooled database connectivity (RDS Proxy or an app-side pool)
Stop using the all-privileges admin account, and cap per-user connections with MAX_USER_CONNECTIONS
Fix the Cartesian ORDER BY RAND() query
Set max_connections durably in IaC
Repair CloudWatch log export

Each item has acceptance criteria, for example: "A load test reproducing the 09:38 workload no longer trips the Lab4-RDS-HighConnections alarm."

Where the chat contradicted the investigation

Next I asked the workshop's follow-up questions in chat.

The answers were fluent and mostly good. "Should I increase max_connections or implement connection pooling?" got a clear pooling first answer with sensible reasoning. But two claims contradict the agent's own mitigation plan:

❌ Contradiction 1: is 150 above or below the default?

Mitigation plan: 150 is "artificially low". The db.t3.medium engine default is ~341, so raise it.
Chat: "max_connections=150 was manually **raised above* the db.t3.medium default (~90–100)."*

The plan is correct. The RDS MySQL default is {DBInstanceClassMemory/12582880}. With about 4 GiB that comes to roughly 341, so 150 is well below the default.

❌ Contradiction 2: will `wait_timeout` help?

Mitigation plan: "wait_timeout/interactive_timeout do **NOT* reap SLEEP() sessions (they count as active), idle-timeout reaping is not an effective lever here."*
Chat: "wait_timeout: this is **the big one* for your SLEEP() problem… Lower this significantly so idle sessions get auto-killed."* It ranked this as priority #1.

The plan is correct again. SELECT SLEEP(n) is an executing query. In the processlist it shows Command = Query, not Command = Sleep. wait_timeout only closes connections that are idle between statements. The chat mixed up "connection in Sleep state" with "connection running SLEEP()". (In the previous answer it also told me to look for command = 'Sleep' to find these sessions, which is the same mix-up.)

✅ What the chat did well

To be fair, the answer to "Would migrating to Aurora MySQL help?" was well calibrated. It said the root cause is a workload problem, not an engine limitation, that Aurora would not have prevented this incident, and it added: "I haven't verified current Aurora MySQL connection-handling specifics… against AWS documentation. I'd want to confirm those before you make a migration decision." That is the kind of honesty I want from an agent.

What I take from this

The structured investigation (timeline, findings, root cause and mitigation plan) is built from evidence: actual API calls, metrics and Performance Insights data. The chat answers sound just as confident but rely more on general knowledge, and that's where the mistakes appeared.

Treat the investigation output as the source of truth, and check chat answers against it, especially before changing parameters in production.

Takeaways

🧠 Performance Insights used well: it separated idle-holding sessions from CPU-burning sessions, which is the key to the right fix
🎯 It traced the problem to one host, one DB user and one login session without being told where to look
🪵 It found broken log export, a problem I didn't plant, that would hurt the next investigation
🤖 The agent-ready spec with acceptance criteria is a good handoff to development
⚠️ The chat contradicted the investigation twice. Verify before you act.

That's the end of the series. For the overall verdict and what I'd tell a team considering AWS DevOps Agent, see the overview post.