Nodejs Mysql lib error "Too many connections"

Question

Libary that I use: mysqljs/mysql

I have 250 max connections limit on mysql server, but my application sometimes get "Too many connections".

When I checked, mysql server only have 3 connections using show full processlist; command

Am I doing something wrong in my code?

app/config/database.json

{
    "production": {
        "db1": {
            "multipleStatements": true,
            "host": ****,
            "user": ****,
            "password": ****,
            "database": ****,
            "connectionLimit": 190,
            "port": 3306
        }
}

app/models/index.js

const mysql = require('mysql')
const config = require('../config/database')

const db1 = mysql.createPool(config[process.env.APP_ENV].db1)
const db2 = mysql.createPool(config[process.env.APP_ENV].db2)
const db3 = mysql.createPool(config[process.env.APP_ENV].db3)
const db4 = mysql.createPool(config[process.env.APP_ENV].db4)

const connections = {
    'db1': db1,
    'db2': db2,
    'db3': db3,
    'db4': db4
}

// Models
const News = require('./News')
const Programs = require('./Programs')

module.exports = {
    News: new News(connections),
    Programs: new Programs(connections)
}

app/models/News.js

class News {
    constructor(connections) {
        this.db = connections;
    }

    getNews(limit, offset) {
        return new Promise((resolve, reject) => {
            this.db.db1.getConnection((err, db1) => {
                if (err) {
                    console.log(err)
                    db1.destroy()
                    reject(err)
                }

                db1.query(query, (err, rows) => {
                    if (err) {
                        console.log(err)
                        db1.destroy()
                        reject(err)
                    }
                    db1.destroy()
                    resolve(rows)
                });
            });
        }); 
    }
}
module.exports = News;

app/controllers/NewsController.js

const models = require('../models/index')

class NewsController {

    index (req, res) {
        models.News.getNews(limit, offset).then((result) => {
            res.status(200).send(result);
        }).catch((err) => {
            res.status(503).send(err);
        });
    }

    // more functions that can perform 2 or 3 queries consecutives 
    // with differents model functions.
}
module.exports = NewsController

app/controllers/index.js

const NewsController = require('./NewsController')

module.exports = {
    NewsController: new NewsController()
    // more controllers...
}

app/routes.js

const express = require('express')
const router = express.Router()
const controllers = require('./controllers')

router.get('/news', (req, res) => {
    controllers.NewsController.index(req, res)
})
// more routes with newscontroller...

Check `SHOW GLOBAL STATUS LIKE 'max_used_connections';` in mysql client. This will tell you the high-water level of number of connections (since the last time mysqld restarted). It's possible you hit the max connections limit briefly, and then it drops back down. — Bill Karwin, Jul 11 '18 at 17:26

score 0 · Answer 1 · answered Jul 11 '18 at 18:09

0

The documentation states:

pool.getConnection → connection.query → connection.release

You're not calling release but destroy, which immediately terminates the connection, instead of releasing it back to the pool. So your pools fill up with new connections.

Also, given how you only use the pooled connection to run a single query, you can use the shortcut also mentioned in the documentation:

this.db.db1.query(query, (err, rows) => {
  if (err) {
    console.log(err)
    reject(err)
  } else {
    resolve(rows)
  }
});

That way, all the pool management is being done automatically.

answered Jul 11 '18 at 18:09

robertklep

198,204
35
394
381

"If you would like to close the connection and remove it from the pool, use connection.destroy() instead. The pool will create a new connection the next time one is needed." - Documentation. I think there is no problem using destroy instead release, because it will create a new connection when it is needed. I don't use release because it keeps connections idle and new users can face "too many connections". I already tested this... – Lollorcaust Jul 11 '18 at 18:18
What's the use of a connection pool (which, in the case of `mysql`, is lazy) if you're destroying each connection that it provides? It means that for each query a new connection is created, so you might as well not use a pool at all. Also, I think that `connectionLimit` in that situation doesn't do what you think it does, namely rate-limiting the number of connections that get created. And lastly: are `db1-4` hosted on different MySQL server instances, or are they 4 databases on the same server. Not sure if MySQL connection limits are per-database or per-server (I think the latter, though). – robertklep Jul 11 '18 at 18:26
All databases are on another server. You are right about everything that you said, but I don't see what can cause too many connections problem... In documentation says "connectionLimit: The maximum number of connections to create at once. (Default: 10)" If I undestand well, if I use 1 connection this number will decrease 1, but when I close it (destroy), this number keeps on 10, not 9. – Lollorcaust Jul 11 '18 at 18:39
The "at once" in that sentence is vague. It doesn't explicitly say that it will create _at most_ 10 connections, so I'm not sure what it actually does when you take a connection from the pool and subsequently destroy it. My sense is that it will not limit the amount of total connections to `connectionLimit` that way, in which case the # of connections may grow if the query takes a bit of time and the requests keep coming in. – robertklep Jul 11 '18 at 19:04
Yeah, I will change to not use pool and see if this will stop too many connections error. – Lollorcaust Jul 11 '18 at 19:06
It's beginning to sound like the server limit is less than 250. Try using a pool as intended (`getConnection`, `query`, `release`), but with a much lower `connectionLimit`. I assume that you can't actually raise the # of connections in the MySQL-server? – robertklep Jul 12 '18 at 06:08

Nodejs Mysql lib error "Too many connections"

1 Answers1