How to control connections with a database from within a node lambda environment

Running out of database connections is a scary thing. Out of nowhere, multiple support requests pop out saying that the service is 'laggy', 'taking too much time, 'slow', then you start looking around and find that the problem is that you're topping the number of connections allowed for that database, you try to upgrade the database and goes to search on how to fix it.

Regarding this tutorial

It is focused on problems related to code or configuration, so cases related to a peak in the number of simultaneous lambdas instances that is bigger than the number of connections are not a topic

We're using javascript/typescript running on NodeJS, but it's possible to extend the reasoning behind these solutions to other languages and runners

Why it happens?

There are many reasons that it can happen, but the main ones that are related and can be solved by code or configuration are:

Lambda functions create new connections without dropping them
Handlers creating connections with a big connection pool

The first one can only happen if the lambda instance keeps the connection to the database. So let's say that you're expecting that 200 lambda instances are executing at the same time over the timespan that a lambda can live, but the DB accepts only 100 connections simultaneously, that means that 100 lambda instances won't be able to connect if each lambda keeps its connection and then the service stop working.

The second one mostly happens if there is no tunning in the configuration used to create a connection to the DB.

Identifying

Identifying each problem can be a burdening task, but here are some characteristics that are related to each problem

Creating new connections and not dropping them afterward

A problem like this will appear on a graph of the number of connections to the database over time. So if you have a lot of long straight lines on normal usage, it's usually a sign that you have a problem dropping the connections. But if you cross-reference with the number of lambda instances that were executing at the time, and both are stable, it's almost sure that you're having a problem keeping connections alive.

Big connection pool

This problem is usually easier to identify if you have a small number of lambda instances running at some point, let's say 10 lambda instances, and at the same time have a very high amount of connections, let's say 200 connections, then this is a very strong sign that the connection pool is not configured properly.

How to solve "Create new connections without dropping them"

There are two main ways to solve this problem, one consists of making a connection and then dropping it before returning a result, and the other one is to tweak the connection configuration to make it drop after some idle time if your driver and database have this feature.

Create a connection and drop it after use

Because of the assumption that the instance is keeping the connection, using this solution means a change in the structure, so be careful when choosing this solution

The solution will differ from synchronous and asynchronous handlers because of how those instances are handled.

Synchronous

In a synchronous handler, the first thing to do is to remove anything that allows the lambda return without dropping the connection, for NodeJS lambda functions this is the context.callbackWaitsForEmptyEventLoop = false. After this step, just adjusts the handler to create a connection and then drop it at the end. Because the handle is synchronous, there is no need to bother with a connection being stored globally, as no other requests are running at the same time.

An example of synchronous code can be:

let connection = null

const connect = () => {
  // create a connection and save it
}

const disconnect = () => {
  // disconnect the connection saved
}

const handler = (event, context, callback) => {
  connect()
  const response = doSomething(event, context)
  disconnect()
  callback(null, response)
}

Asynchronous

Here things start to get a bit complicated, first because of the way async lambda works, anything in the global scope is kept alive throughout the lifetime of the instance and second as handling connections like the synchronous case would fail and probably cause more problems as it would create race conditions over the connection and be possible to try to use a connection that was disconnected by another request.

So as storing the connection in a global scope would cause problems because of racing conditions, using this type of solution probably will mean a bigger refactor if you use mongoose with an 'export Model pattern' or related patterns.

All it's needed to use this solution is to create a connection inside the handler at the start of the request (Be careful with connection configuration and especially the connection pool), pass this connection to every place that uses a connection to the DB, and in the end close it.

An example of asynchronous code would be:

const connect = async () => {
  // create a connection and return it
}

const disconnect = async (connection) => {
  // close the connection
}

const handler = async (event, context) => {
  await connect()
  const response = await doSomething(connection, event, context)
  await disconnect()

  return response
}

Tweaking the connection configuration to auto disconnect after idle time

Most of the database and database drivers support a configuration like maxIdleTimeMS. If you're using one that supports it, at the moment of creation of the connection just set the config to something close to 10x the time of the slowest query that you have, it'll be probably something around 1000ms, after that is manual tweaking, but by my experience, it will be enough to keep in control

How to solve "Connections with big connection pool"

The problem is self-explanatory, so following the same steps as tweaking the max idle time, you can probably change the size of the connection pool and some drivers may offer the possibility to control even the range of the number of connections in the pool. For example, using mongoose (MongoDB), it's possible to control the max and the minimum size of the pool by setting maxPoolSize and minPoolSize respectively.

Lucas Duarte Sobreira