How to handle MySQL database connections/pooling from Lambda -> RDS with Node

Question

I'm running into some problems while testing my Lambda function. I my test, I execute in parallel N executions of my Lambda function. In my lambda function, I retrieve a database connection through Knex. I was messing around with the setting in the db init pool: { min: 0, max: [1 or 5] }, which seems to fix/break things, depending on other things in my code.

Executing my lambda function 20 times outputs correct results, however, when I up it to 500 (what I am expecting to see in production will easily exceed this), things start to break. I'm running into errors such as Error: ER_CON_COUNT_ERROR: Too many connections, Error: pool is draining and cannot accept work (if I use Knex's knex.destroy() interface at the end of my lambda function). What is the correct way to handle connections and pooling for scale in AWS Lambda and RDS? Are the problems that I'm seeing on my local machine going to replicate if I were to run the same stress tests on AWS?

Answer 1

Spinning off thousands of processes to do tiny pieces of a task is simple. But inefficient. "Processes" are heavyweight. Threads are somewhat heavy. Thousands of connections to MySQL is possible, but not all at the same time.

In any multi-process or multi-threaded environment, having "too many" will slow things down. This is because the OS (or some entity) is doing a lot of work to share the inadequate resources. MySQL, for example, can handle hundreds of idle connections, but bogs down with more than a few dozen active connections. When that is exceeded, it is best to go up the chain to the client, and throttle how often it is spawning new connections.

I am not familiar with the other products you mention, but I would recommend keeping simultaneous activity down to dozens, not hundreds.

Or find a way. in your application, to "iterate" instead of "recurse". Meanwhile, keep in mind that MySQL is happier to handle "vectors" (tables) of data than to handle individual rows. Maybe you can push some of what is "parallel" in the app down into MySQL as "vectors"? Win-win: Fewer connections; more efficiency in MySQL.

Answer 2

The error is due to the number of connections to the database, each Lambda function will open at least one, and in your case, the number exceeds the number of connections available in you RDS instance.

Through the AWS Console you can raise it, just look for max_connections, here is a step by step tutorial:

https://github.com/jollygoodcode/jollygoodcode.github.io/issues/16

According to this link(many others shows similar values), here is a table with each instance size supported number of connections:

MODEL       max_connections
t1.micro    34
m1-small    125
m1-large    623
m1-xlarge   1263
m2-xlarge   1441
m2-2xlarge  2900
m2-4xlarge  5816

Hope it helps

Answer 3

To allow connection pooling with Lambda you need to create the pool outside of the handler function, as explained here.

The writer of the article points to examples at his Github, in case that survives but the original article disappears.