Free performance gains with MySQL connection pools

Many tutorials tell us to create a single connection on start, performing any database queries with that connection. Worst case scenario? You'll setup a database connection in each request, but thankfully I've not seen this yet!

Say you have 8k users connecting at once: imagine performing the same query 8k times using 1 connection to the database. Each query has to complete before the next one executes! You don't need to be a rocket scientist to assume this doesn't scale well. Let's experiment and see the numbers.

Single connections vs connection pooling: A benchmark

I coded an experiment with a simple SELECT SQL statement, running 2000 times concurrently:

const mysql = require('mysql2');

const runTest = async (db, type)=>{
    let promises = [];
    let runTimes = [];
    const runQuery = async ()=> new Promise((resolve)=>{
        let currentRuntime = new Date().getTime();
        db.query("SELECT * FROM users LIMIT 2", [], (error, rows)=>{
            const finalRuntime = new Date().getTime() - currentRuntime;
            runTimes.push(finalRuntime);
            resolve(finalRuntime);
        })
    })
    for (let index = 0; index < 2000; index++) {
        promises.push(runQuery());
    }
    Promise.all(promises).then(()=>{
        const runtimeAverage = runTimes.reduce((a, b)=>a + b) / runTimes.length
        console.log(type+" completed with a runtime of ", `${runtimeAverage}ms average mean`);
    })
}

const runSingleConnectionTest = ()=>{
    let db = mysql.createConnection({
        host:'localhost',
        user:'admin',
        password:"password",
        database:'testdb'
    })

    db.connect(error=>{
        if(error) throw error;
        runTest(db, 'Single connection');
    })
}

const runConnectionPoolTest = ()=>{
    let db = mysql.createPool({
        host:'localhost',
        user:'admin',
        password:"password",
        database:'testdb',
        connectionLimit: 10,
        maxIdle: 10,
        idleTimeout: 60000, 
        queueLimit: 0,
        enableKeepAlive: true,
        keepAliveInitialDelay: 0
    })
    runTest(db, "Connection pool")
}

It's quite basic: it only does a simple select, there's no WHERE clauses or anything. So imagine a production scenario where you have more complex JOIN queries or INSERT queries to think about.

On my work machine, running the database queries 2000 times at the same time the results were:

Single database connection: 370ms average mean

Database connection pool: 250ms average mean

As you can see, the database connection pool offers a 30% improvement in query runtime! But with database connection pools, the benefits increase with scale: at 9000 rows, it gets closer to 34% on my machine.

There's further optimisations too: for an INSERT query, you could queue a bunch of queries together and generate an INSERT query that does inserts in batches.

I was glad to write this benchmark, because when I tried to find out the performance penalty of single database connections, all I could find was the performance penalty when opening a connection on each query! Anyway, use database connections: it's free performance.