Sunday, 24 November 2019

How to properly use Knex / Bookshelf with MySQL on RDS

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.



from How to properly use Knex / Bookshelf with MySQL on RDS

No comments:

Post a Comment