Aurora Max Connections

0

A customer ran into a max connections issue with Aurora. I am having them review their code to insure that they are properly closing their connections but it raised a broader question around best practices for that setting. I see that we have a table of default values and that the value can be raised to 16000. Does anyone have any guidance or recommendations on this? I'm ultimately looking for guidance on how/when that setting should be tweaked.

I have read over the following documentation and forum thread to try to get some background on this. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Managing.html https://forums.aws.amazon.com/thread.jspa?messageID=608877

AWS
heeki
asked 6 years ago1717 views
1 Answer
0
Accepted Answer

There's a few concerns when changing the default number of maximum connections, which is based on DB instance memory. More connections lead to more threads, and MySQL (and thus Aurora) doesn't have great workload management, so no thread prioritization, limits on resource consumption, etc. This all can increase resource contention. Memory management is also difficult, there are connection level buffers/caches and controlling their memory footprint is non-deterministic (it depends on sessions and active queries). This can be problematic because you can run out of memory if there's a lot of variability in these memory consumers, and the only way to limit that risk is by reducing the more predictable global memory consumers, such as the page cache/buffer pool, which are easier to control. That in turn can have detrimental performance implications.

While there's no set guidance on when to change the max connections value, stability of resource utilization and predictability of query patterns is something that is key. For example, a workload that only performs simple key/value lookups and updates with not a lot of variability in query patterns would be a better candidate for increasing max connections, then a workload with variable queries, complex aggregations, joins, and multi step transactions.

Ultimately, if you have to change that value, consider making small incremental changes, monitor freeable memory to make sure it's stable, and monitor lock contention between queries.

AWS
EXPERT
answered 6 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions