The other day I had to troubleshoot my MySQL database when I started getting errors that my applications can't connect to MySQL due to this error: Too many connections.
These were the steps that I took
Check Maximum Allowed Connections
To check the current max_connections limit:
SHOW VARIABLES LIKE 'max_connections';
Check Current Active Connections
To see how many connections are currently in use:
SHOW STATUS LIKE 'Threads_connected';
Identify Users with High Connections
To find which users have the most open connections:
SELECT user, host, COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY user, host
ORDER BY connections DESC;
View Detailed Connection List
To get details on all active connections:
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
ORDER BY time DESC;
Kill a Specific Connection
To terminate a connection by its ID:
KILL <connection_id>;
For example:
KILL 1234;
Increase Maximum Connections (Aurora MySQL)
Step 1: Modify Parameter Group
- Go to AWS Console → RDS → Parameter Groups.
- Select or create a custom parameter group.
- Search for
max_connectionsand increase the value. - Save changes.
Step 2: Apply Parameter Group to Aurora Cluster
- Go to AWS Console → RDS → Clusters.
- Select your Aurora Cluster.
- Click Modify, then change the DB Parameter Group to your custom one.
- Click Continue, then Apply Immediately.
Verify the Change
After applying changes, verify the new value:
SHOW VARIABLES LIKE 'max_connections';
Identify Long-Running Queries
To find queries running for a long time:
SELECT * FROM information_schema.processlist WHERE time > 30 ORDER BY time DESC;
(Change 30 to any duration in seconds.)
Reduce Idle Connections
To identify and remove long sleeping connections:
SELECT id, user, host, time, state
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 60;
KILL <connection_id>; -- Kill idle connections if necessary
Set Automatic Connection Timeouts
To prevent idle connections from staying open too long:
SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;
For a permanent change, update my.cnf:
[mysqld]
wait_timeout = 60
interactive_timeout = 60
Grant PROCESS Privilege to View All Connections
If you only see your own connections, you may need this privilege:
GRANT PROCESS ON *.* TO 'your_user'@'%';
FLUSH PRIVILEGES;
Monitor Connections in Real-Time
To continuously monitor connections, run:
SHOW FULL PROCESSLIST;
These steps helped me to identify which user was using a lot of connections, how to increase the max connections if needed and how to remove sessions.
Comments