mysql connection pooling in node.js

README.md

This is in reaction to http://www.madhur.co.in/blog/2016/09/05/nodejs-connection-pooling.html

It is critical that you remember to release connections to the pool if you are using pooling with the mysql module. It is best to use the pool.query() command if you can. You’ll know if you can’t.

Examples of things which cannot use pool.query() but must use pool.getConnection() followed by connection.release() are:

1. Using transactions where you send multiple commands to the server.
2. Sharing per-session data objects between subsequent commands sent to the server such as temporary tables.

If you must use pool.getConnection(), be sure to use some construct similar to try {} finally {} to ensure that connection.release() is always called.

Connection Reuse Performance

The following outputs show the benefits of connection reuse.

The following output is with connection reuse. I.e., the connection.release() line is left uncommented, allowing the connection to be used by the next set of parallel calls:

ohnobinki@gibby ~/repos/mysql-pool-broken-example $ ./index.js
Made 25 calls in 33ms
Made 25 calls in 9ms
Made 25 calls in 9ms

The following output is with connection reuse disabled. This was done by commenting out the connection.release() line. Because 25*3=75 is less than the connectionLimit value, the application was able to run to completion. However, each query required the mysql module to create a new connection to the mysql server. Note that the creation of 25 new connections added roughly 7ms to each batch of 25 queries.

ohnobinki@gibby ~/repos/mysql-pool-broken-example $ ./index.js
Made 25 calls in 33ms
Made 25 calls in 17ms
Made 25 calls in 16ms

Note that even with connection reuse, the first connection made to the server is special. I.e., in both outputs above, the first batch of 25 calls always took the same amount of time. This could be due to multiple reasons. JIT warmup (I think that JavaScript will defer at least some JITing until code is executed) is an example of a factor somewhat unrelated to mysql itself. The mysql module also has to do some extra work on the first connection to resolve the address or perhaps load dynamic libraries to establish the connection which it doesn’t need to do on subsequent connections.

index.js

#!/usr/bin/env node
const mysql = require('mysql');

process.on('unhandledRejection', (ex, p) => {
  // This log is from the docs.
  console.log('Unhandled rejection at:', p, 'reason:', ex);
  // I don’t know how to exit the process properly, but I want things
  // to die on this scenario to be like future node.js.
  process.exit(1);
});

// Code from
// http://www.madhur.co.in/blog/2016/09/05/nodejs-connection-pooling.html
// which lacks connection.release() and thus actually fails to
// demonstrate the benefits of pooling and suggests people use broken
// code.

const pool = mysql.createPool({
  connectionLimit: 100, //important
});

function hitQuery(callback) {
  pool.getConnection((ex, connection) => {
    if (ex) {
      callback(ex);
    } else {
      connection.query('SELECT 2*4 "value";', (ex, rows, fields) => {
        // Comment out the following line if you want your application
        // to freeze up after 100 requests and you don’t want to get
        // the performance benefits of connection reuse.
        connection.release();
        if (ex) {
          callback(ex);
        } else {
          callback(null, rows[0].value);
        }
      });
    }
  });
}

async function makeParallelCalls () {
  const startTime = Date.now();
  const count = 25;
  await Promise.all(new Array(count).fill(0).map(() => new Promise((resolve, reject) => {
    hitQuery((ex, value) => {
      if (ex) {
        reject(ex);
      } else {
        resolve(value);
      }
    });
  })));
  console.log(`Made ${count} calls in ${Date.now() - startTime}ms`);
}

(async () => {
  await makeParallelCalls();
  await makeParallelCalls();
  await makeParallelCalls();
  pool.end();
})();

thanks for taking the time! it helped me a lot to understand how to use connection pooling.
I made some changes to the code for usage in my application.

if you have a second id love some feedback if I did something stupid.

https://gist.github.com/Slurpgoose/f1af109c5ef87b3cd138ce1f9343632d