Overloaded connection pool with bad MySQL driver implementation

Summary

TLDR
Intro
Strategies
Solution

TLDR

Strategies to fastly pinpoint problems:

Comment out half of the code and try to reproduce the issue. If the uncommented code works, the issue is in the commented. Repeat the process in the remaining code until you pinpoint precisely the problem.
Create a small-scale project to bypass any complex production logic like authorization/authentication, parameter validations, and so on.
Pressure test your application, better on the small scale part.

Do not use pool.getConnection() and connection.release() from mysql or mysql2 nodejs driver in different closure functions. On high load, you get into the state that all connections are marked "in work" and can't acquire anything new.

Intro

Recently at work got a weird problem: After some time, our application was getting stuck. The logs showed no errors, nothing special to pinpoint any problem. Requests from UI started to timeout. We spend a couple of days in high stress trying to find and fix the problem. We had no idea what is causing this behavior.

In this article, I want to share my thought process and some strategies to fastly find and pinpoint the exact problem.

Strategies

We are working on a decently big application—more than 70K lines of code, hundreds of endpoints. Our single piece of information was the fact that the application is getting stuck. In NodeJs event loop is getting stuck only in a couple of cases:

Sync code that got into an infinite loop or some slow algorithm.
Unfulfilled promise or io operation, which blocks the code from moving on.

We branched out the first idea quickly by monitoring our instances, and CPU was at 10 - 15%. Usually, you will have the CPU near 100% if you have infinite loops or slow algorithms.

Then we started to look at our express middlewares trying to find any async/await code which can get stuck.

Strategy #1 "Divide et Impera"

I've commented half the async middlewares and checked if it works; If yes, I expected the problem to be in the already commented part. Usually, you repeat this process by dividing in half the code with the problem until you pinpoint precisely where the issue is.

In my case, both halves were working fine, so this strat didn't have any success.

Strategy #2 "Small scale project"

I've created a small express application that simulates our implementation of mysql-pool. Source code at mysql-pool-blockage-example.

// index.ts

import express from 'express';
import dotenv from 'dotenv';
dotenv.config();

import { pool, PoolConnection } from './src/mysql-pool';

// Create the express application

const app = express();

// Attach a middleware to get a connection from the pool and set it to the req.app object
app.use((req, _res, next) => {
  pool.getConnection((err, connection) => {
    if (err) {
      return next(err);
    }
    req.app.set('connection', connection);

    next();
  });
});

// Attacha a middleware to release back to the pool the connection once the request is closed
app.use((req, _res, next) => {
  req.once('close', () => {
    const connection = req.app.get('connection');
    if (connection) {
      connection.release();
    }
    req.app.set('connection', null);
  });
  next();
});

// Add the simplest possible route to do a select from db and returning the output
app.use('/', (req, res, next) => {
  const connection: PoolConnection = req.app.get('connection');
  connection.query('SELECT 1 + 1 as two', (err, result) => {
    if (err) {
      return next(err);
    }

    res.json(result);
  });
});

app.listen(3000, () => {
  console.log('Server is running at http://localhost:3000/');
});

// mysql-pool.ts

/** Initializing a pool to connect to mysql driver */

import { createPool } from 'mysql2';

export const pool = createPool({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  connectionLimit: 10,
});

//Logs on the current connection

pool.on('acquire', function (connection) {
  console.log('Connection %d acquired', connection.threadId);
});

pool.on('enqueue', function () {
  console.log('Waiting for available connection slot');
});

pool.on('release', function (connection) {
  console.log('Connection %d released', connection.threadId);
});

export { PoolConnection } from 'mysql';

I've run the code using curl localhost:3000 every time saw that connection acquired, and right after the request finished, it was released. So still did not find out the problem:

Strategy #3 "Pressure your App"

Generate some heavy load on your application. Usually, when pressured, things go wrong, not only in software engineering.
I used autocannon, but any stress testing tool will do.

Pressure testing a small part of your code, like in our case, the small project is much faster than generating super-specific configurations like valid tokens, bypassing authorizations/authentications.

This strategy gave excellent results as we managed to reproduce precisely the behavior from production, the application is getting stuck.

Overloaded mysql2 pool performance benchmark

Solution

Most of the time, you will find your solution by carefully reading the library's official docs. In our case, we execute the query by using the pool.query() method exposed to the driver API, which internally handles getConnenction and conn.release().

import express from 'express';
import dotenv from 'dotenv';
dotenv.config();

import { pool } from './src/mysql-pool';

//Create the express application

const app = express();

app.use('/', (_req, res, next) => {
  pool.query('SELECT 1 + 1 as two', (err, result) => {
    if (err) {
      return next(err);
    }
    res.json(result);
  });
});

app.listen(3000, () => {
  console.log('Server is running at http://localhost:3000/');
});

After the fix, we managed to get rid of the problem. We also, by accident, increased the performance 10x—a great result after a good day of work.

Overloaded mysql pool after fix performance

Let's be in contact and join my newsletter.

Join 0 other subscribers.

Blog posts

Overloaded connection pool with bad MySQL driver implementation