Sev1: RDS rejects logins with too many connections. Connection storm triage for PostgreSQL and MySQL

#Symptoms

• PostgreSQL: FATAL: sorry, too many clients already or remaining connection slots are reserved for non-replication superuser connections.
• MySQL: ERROR 1040 (HY000): Too many connections.
• DatabaseConnections metric flat at a ceiling; CPU may be high or oddly low.
• Often follows a deploy, an autoscaling event, or a dependency outage that made clients retry.

#Step 1: confirm and see the ceiling (2 minutes)

aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name DatabaseConnections \
  --dimensions Name=DBInstanceIdentifier,Value=orders-db --statistics Maximum --period 60 \
  --start-time $(date -u -d '-2 hours' +%FT%TZ) --end-time $(date -u +%FT%TZ) --output table

aws rds describe-db-instances --db-instance-identifier orders-db \
  --query "DBInstances[].[DBInstanceClass,DBParameterGroups[0].DBParameterGroupName]" --output table

max_connections defaults to a formula based on instance memory. Check the effective value from a session (next step).

#Step 2: get an admin session

PostgreSQL reserves slots for superusers (superuser_reserved_connections, and rds.rds_superuser_reserved_connections on RDS). Connect as the master user from a bastion or Session Manager host:

psql "host=orders-db.xxxx.eu-west-1.rds.amazonaws.com dbname=postgres user=dbadmin sslmode=require"

MySQL lets one extra connection through for a user with the CONNECTION_ADMIN (or legacy SUPER) privilege. On RDS the master user is the one to try.

#Step 3: see who holds the connections

PostgreSQL:

SHOW max_connections;
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY count(*) DESC LIMIT 20;

SELECT pid, usename, state, now() - state_change AS idle_for, left(query, 80)
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction') ORDER BY idle_for DESC LIMIT 20;

MySQL:

SHOW VARIABLES LIKE 'max_connections';
SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS n
FROM information_schema.processlist GROUP BY 1,2,3 ORDER BY n DESC LIMIT 20;

The pattern tells you the cause: hundreds of idle connections from one app means a pool sized too large per instance multiplied by many instances; idle in transaction means application code holding transactions open; many active queries means a slow query backing everything up.

#Step 4: free slots now

PostgreSQL, kill long-idle sessions from the app user:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user' AND state = 'idle' AND now() - state_change > interval '5 minutes';

MySQL, generate kill statements for idle app sessions:

SELECT CONCAT('CALL mysql.rds_kill(', id, ');')
FROM information_schema.processlist
WHERE user = 'app_user' AND command = 'Sleep' AND time > 300;

Run the generated statements. On RDS, use mysql.rds_kill rather than KILL for other users' threads.

Warning

Killing connections only buys minutes if the source keeps opening new ones. Do step 5 in parallel.

#Step 5: stop the storm at the source

• Scale the application in, or reduce pool size per instance, so total = instances x pool size fits below max_connections with 20% headroom.
• If a dependency outage triggered aggressive retries, add backoff or temporarily disable the retrying job.
• If one slow query is the cause, find it (pg_stat_activity where state='active' ordered by query_start, or Performance Insights top SQL) and cancel it: SELECT pg_cancel_backend(<pid>);.

Setting idle timeouts helps immediately:

aws rds modify-db-parameter-group --db-parameter-group-name orders-pg16 \
  --parameters "ParameterName=idle_in_transaction_session_timeout,ParameterValue=60000,ApplyMethod=immediate"
# MySQL: wait_timeout / interactive_timeout in seconds

#Step 6: raise the ceiling (with care)

max_connections is a static parameter on some engines and needs a reboot; more connections also means more memory per connection. Scaling the instance class up raises the default formula and is often safer than forcing a larger number on a small instance.

#Long-term fix: RDS Proxy or a real pooler

aws rds create-db-proxy --db-proxy-name orders-proxy --engine-family POSTGRESQL \
  --auth AuthScheme=SECRETS,SecretArn=$SECRET_ARN,IAMAuth=DISABLED \
  --role-arn $PROXY_ROLE_ARN --vpc-subnet-ids subnet-a subnet-b --vpc-security-group-ids sg-proxy
aws rds register-db-proxy-targets --db-proxy-name orders-proxy --db-instance-identifiers orders-db

RDS Proxy multiplexes many client connections onto fewer database connections. Watch for connection pinning (session state such as temp tables or SET commands prevents sharing); the DatabaseConnectionsCurrentlySessionPinned metric shows it. PgBouncer in transaction mode is the self-managed equivalent for PostgreSQL.

#Prevent it next time

• Alarm when DatabaseConnections exceeds 80% of max_connections.
• Document pool size per service and the maximum instance count; review on every autoscaling change.
• Lambda in front of RDS without a proxy is a common cause; use RDS Proxy.
• idle_in_transaction_session_timeout (PostgreSQL) or wait_timeout (MySQL) set deliberately.