High Connection Attempts but Low Database Connections on RDS

The situation you're describing, where you have a high number of ConnectionAttempts but a low number of DatabaseConnections on your RDS instance, suggests that there might be issues with clients attempting to connect to the database but failing to establish successful connections. This discrepancy can occur due to several reasons:

1. Connection Limit: Your RDS instance may have reached its maximum allowed connections. The db.t4g.medium instance type has a default max_connections limit that's calculated based on the instance's memory. If you've hit this limit, new connection attempts will fail, leading to high ConnectionAttempts but low DatabaseConnections.

2. Security Group Configuration: Ensure that the security group associated with your RDS instance allows inbound traffic on the correct port (typically 3306 for MySQL or 5432 for PostgreSQL) from your application's IP range or security group.

3. Network Issues: There might be network connectivity problems between your application and the RDS instance. This could be due to VPC configuration, route tables, or network ACLs.

4. Authentication Problems: If there are issues with user credentials or permissions, clients may attempt to connect but fail due to authentication errors.

5. Instance Capacity: The db.t4g.medium instance might be struggling to handle the connection load, especially if you're using CPU credits (as it's a burstable instance type).

6. Connection Pooling: If your application isn't using proper connection pooling, it might be creating and closing connections rapidly, leading to high ConnectionAttempts.

To troubleshoot and resolve this issue:

1. Check the max_connections parameter in your RDS parameter group and consider increasing it if necessary.

2. Review your security group settings to ensure proper access.

3. Monitor the CPUUtilization and FreeableMemory metrics to see if the instance is under resource pressure.

4. Examine the database logs for any error messages related to failed connections.

5. Implement or optimize connection pooling in your application to manage connections more efficiently.

6. Consider upgrading to a larger instance type if the current one can't handle the connection load.

7. Use RDS Proxy to manage database connections more efficiently, which can help reduce the number of open connections to the database.

The change from db.t3.medium to db.t4g.medium shouldn't inherently cause this issue, as both are similar in capacity. However, the t4g uses ARM-based processors, which might affect certain workloads differently. If the problem persists after trying these solutions, you may want to consider switching back to the t3 instance type to see if it resolves the issue.

Remember to monitor your database closely after making any changes to ensure they have the desired effect and don't introduce new problems.

Sources
Troubleshoot Amazon RDS for MySQL and Amazon RDS for MariaDB Errors | AWS Database Blog
Troubleshooting connection issues for Aurora MySQL databases - Amazon Aurora
MySQL Connection Error | AWS re:Post
RDS Proxy max_connections | AWS re:Post

Check the log files > "Every RDS database engine generates logs that you can access for auditing and troubleshooting. The type of logs depends on your database engine." >> With CloudWatch Logs, you can perform real-time analysis of the log data. You can also store the data in highly durable storage and manage the data with the CloudWatch Logs Agent.

Amazon RDS provides a REST endpoint that allows access to DB instance log files. This is useful if you need to write an application to stream Amazon RDS log file contents.

One might also check the application logs Look for errors or warnings related to database connections. Common issues to check:

• Connection timeouts.
• Authentication errors.
• SQL syntax issues causing frequent retries.

Sources
Reading log file contents using REST https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/DownloadCompleteDBLogFile.html

Monitoring Amazon RDS log files https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.html