MySQL Aurora connection management with SQS and Lambda

Question

I have a use case in my system where I need to process hundreds of user records nightly. Currently, I have a scheduled Lambda function which pulls all the users to be processed and places each onto an SQS queue. I then have another Lambda function that reads from this queue and handles the processing. Each user requires quite a lot of processing which uses quite a few connections for each user. I use a mysql transaction in as many places as I can to cut down the connections used. I'm running into issues with my Aurora MySQL database hitting the connection limit (1000 currently). I have tried playing around with the batch sizes as well as the lambda concurrency but I still seem to run into issues. Currently, the batch size is 10 and concurrency is 1. The Lambda function does not use a connection pool as I found that caused more issues with connections. Am I missing something here or is this just an issue with MySQL and Lambda scaling?

Thanks

Answer 1

Amazon RDS Proxy is the solution provided by AWS to prevent a large number of Lambda functions from running at the same time and overwhelming the connection limit of the database instance.

Alternatively, you could use this trick to throttle the rate of lambdas:

1. Create another SQS queue and fill it with a finite set of elements. Say 100 elements, for instance. The values you put into this queue don't matter. It's the quantity that is important.
2. Lambdas are activated by this queue.
3. When the lambdas are activated, they request the next value from your first SQS queue, with the users to be processed.
4. If there are no more users to process, i.e. if the first queue is empty, then the lambda exits without connecting to Aurora.
5. Each lambda invocation processes the user. When it is done, it disconnects from Aurora and then pushes a new element onto the second SQS queue as its last step, which activates another lambda.

This way there are never more than 100 lambdas running at a time. Adjust this value to however many lambdas you want to allow concurrently.