How to properly use Knex / Bookshelf with MySQL on RDS

Question

I have a Node.js application using MySQL on an AWS RDS with Bookshelf & Knex libraries. The RDS Instance have a max_connections value 90. I am using the following as the connection object.

knex: {
  client: 'mysql',
    connection: {
      host: 'xxxxxxx.rds.amazonaws.com',
      user: 'xxx',
      password: 'xxxxx',
      database: 'xxxx',
      charset: 'utf8'
  },
  debug: true,
  pool: {
    min: 2,
    max: 20
  },
  acquireConnectionTimeout: 10000
},

const config = require('./environment');
const knex = require('knex')(config.knex);
module.exports = require('bookshelf')(knex).plugin('registry');

'use strict';

const bookshelf = require('../config/bookshelf');
const config = require('../config/environment');
module.exports = bookshelf.model(`TableA`, {
    tableName: 'TableA'
}, {});

I have many requests coming along to the application and sometimes crashes with the following errors.

Unhandled rejection TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

and

Error: ER_CON_COUNT_ERROR: Too many connections

Also I see a number of connections (40 to 50 on an average) in the server PROCESSLIST with Command as sleep.

I suspect these error happen when all the 90 connections on the server used fully / knex cannot acquire a new connection from he pool when it tries to. What could be a permanent solution for this, and best practices for handling these kind of applications.

Answer 1

I dont think it is the RDS max_connections that is causing the issue, assuming you only have one instance of the above application code running at any time.

Your application uses a DB connection pool, which can hold up to 20 connections. If all those connections are in use, then the application waits for up to acquireConnectionTimeout ms in your case that is 10000 before connection timeout.

So I suspect that your application either has a lot of db queries to be processed due to load or there are some slow queries hogging connections. This causes a backlog of queries waiting for connections that eventually times out. Investigate which might be the case and do update us.

Things you can try in the mean time.

• Increase acquireConnectionTimeout.
• Increase connection pool size.

If caused by slow queries then optimize them before trying the above.

Possible methods for logging slow queries:

• Enable slow query log on RDS.
• Knex query event to log transaction duration assuming you are using transactions.