- Newest
- Most votes
- Most comments
Hi Krishnakumar,
The calculated default max_connections value of approximately 2730 for a db.r6g.xlarge instance is based on the formula DBInstanceClassMemory / 12582880. However, the available connections scale in 1000-connection increments, so based on the User Guide, the actual default value for your instance is 2000 connections. That said, I strongly recommend against manually increasing max_connections beyond this calculated value. Modifying this parameter adds operational overhead when scaling your instances because you'll need to remember to modify this parameter each time you scale up or down. More importantly, modifying max_connections without careful consideration, testing, and monitoring can result in memory starvation for other processes.
When you increase max_connections beyond the memory-based formula, each connection reserves memory for its own buffers and thread stack, even if idle. This can lead to:
- Memory starvation for critical database processes like the buffer pool, query cache, and sort operations
- Increased context switching overhead as the OS manages more threads
- CPU pressure from thread management, even with optimized queries and indexes
- Overall database slowness exactly as you're experiencing
- The formula exists specifically to prevent these issues by ensuring adequate memory remains available for database operations.
Recommended Solutions
Instead of increasing max_connections, consider these alternatives:
-
Amazon RDS Proxy (Recommended) RDS Proxy provides connection pooling at the infrastructure level, allowing your applications to maintain many connections while the database handles far fewer. This is the most effective solution for connection scaling without impacting database performance.
-
Application-Level Connection Pooling Implement connection pooling in your application layer to reuse connections efficiently and reduce the total number of concurrent database connections needed.
-
Amazon ElastiCache Offload read-heavy workloads or cache frequently accessed data using ElastiCache (Redis or Memcached), reducing the number of direct database connections required.
-
Instance Size Upgrade If you genuinely need more concurrent connections after implementing the above solutions, upgrade to a larger instance class (e.g., db.r6g.2xlarge) which will provide a proportionally higher max_connections limit based on available memory.
Immediate Actions
- Revert max_connections back to the default calculated default value
- Set your CloudWatch DatabaseConnections alarm threshold at 2000
- Audit your application for connection leaks or inefficient connection management
- Review idle connections and implement connection timeouts in your application
Best Practice
The general recommendation is to always keep max_connections at the default calculated value and scale your connection capacity through RDS Proxy, connection pooling, or instance upgrades rather than by modifying this parameter.
The max_connections parameter in Aurora MySQL limits the number of concurrent database connections per DB instance. For a db.r6g.xlarge instance with 32 GB of RAM, the default calculated value would be based on the instance memory.
When you increased max_connections to 3000, the performance degradation you're experiencing is likely related to resource constraints. Increasing this parameter beyond the calculated default can cause several issues:
-
Memory pressure: Each connection consumes memory resources. When you set
max_connectionshigher than the recommended value, you risk running out of memory, especially if Performance Schema is enabled, as it sizes its memory structures based on this parameter. -
CPU contention: More connections can lead to more threads competing for CPU resources.
The best practice for max_connections is to set it slightly higher than the maximum number of connections you expect to open on each instance, not arbitrarily high. Before upgrading to a larger instance class, consider these tuning steps:
- Implement connection pooling: Use a proxy layer like Amazon RDS Proxy between your application servers and database to reduce the total number of connections.
- Review idle connections: Identify and close unused application connections.
- Monitor Performance Insights metrics: Check metrics like Threads_connected, Threads_running, and Aborted_clients to understand your connection patterns.
- Adjust thread cache: Properly tune thread_cache_size to reduce the overhead of creating new threads.
For your CloudWatch DatabaseConnections alarm, setting the threshold at the calculated default (around 2730) would be safer than using the manually configured 3000, as it aligns with the instance's memory capacity.
To determine if you need an instance class upgrade or configuration tuning, monitor these key indicators:
- If you're consistently approaching the connection limit with active connections
- If CPU utilization and memory usage are consistently high
- If performance issues persist after connection pooling and other optimizations
In general, it's recommended to stay close to the default calculated value for max_connections unless you have specific requirements and have verified that your instance can handle the increased load without performance degradation.
Sources
Troubleshooting connection issues for Aurora MySQL databases - Amazon Aurora
Server configuration - Amazon Aurora MySQL Database Administrator’s Handbook
Managing connections - AWS Prescriptive Guidance
answered 10 months ago
