How does mysql connection pooling works with Node microservices?

Question

I have two node microservices talking to one common mysql database. Both microservices have this below code to create a connection pool with a connectionlimit of 10 as follows:

// Initializing pool

var pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: 'root'
});

function addConnection(req, res) {
  pool.getConnection(function (err, connection) {
    if (err) {
      connection.release();
      res.json({ "code": 500, "status": "Error" });
      return;
    }

    connection.query("select * from user", function (err, rows) {
      connection.release();
      if (!err) {
        res.json(rows);
      }
    });

    connection.on('error', function (err) {
      res.json({ "code": 500, "status": "Error" });
      return;
    });
  });
}

For mysql database I have the max_connections set to 200(SHOW VARIABLES LIKE 'max_connections'; returns 200).

With the pool connectionLimit set to 10 for each of the microservice, in which cases or scenarios will the number of connections for any of the microservice will go above 10? i.e. When and how the node services would be able to maintain more connections then expected?
If I have 3 instances running of same microservice then how does the pool connectionLimit works? What would be the limit of connections for each instance of microservice?
In one of the microservice say I have two apis which does database transactions and both connects to the database(getting connection) through two different functions having same implementation of mysql.createPool({}) as above. What would happen if both apis are called concurrently and the number of requests made for each of them per second is 100 or more? Will the number of connections made available be 10 or 20(since there are two mysql pools created with a connectionLimit of 10 each)?

Also, If I'm load testing any node service api with request rate of 50 per second, then how do I check for that time duration of load testing how many connections were made by that node service? — userb, Apr 09 '19 at 10:12

score 2 · Accepted Answer · answered Apr 08 '19 at 18:02

2

Ideally it would not; but it can go above 10; if suppose some connections become stale i.e. they are closed from client end but are still open on Server end.
If you have multiple instances of same micro-service deployed in multiple VM or docker containers; they are like independent services.. and they have no connection among each other.. Hence, each of them will create its own 10 connection.
Firstly if u set connection pool limit as 10; that does NOT mean that during first moment 10 connections would be created.. While creating a pool; you also specify initial connection parameter suppose 5.. so, when service starts only 5 connections would be created.. and more created only when needed.. with UPPER Limit set as defined by parameter max_connections. Coming back to your question; well if you have NOT implemented synchronization etc. properly then yes it is possible that both pools will initialize their INITIAL_CONNECTIONS..

answered Apr 08 '19 at 18:02

Deepak Singhal

10,568
11
59
98

Thank you for answering...Could you please clarify on first point? How would the connection get closed on client end and not server end? I believed connections always gets closed on server end and not client end. – userb Apr 09 '19 at 10:07
Also, If I'm load testing any node service api with request rate of 50 per second, then how do I check for that time duration of load testing how many connections were made by that node service? – userb Apr 09 '19 at 10:12
on mysql; u can easily do sql query to see number of connections and the server originating for those connections – Deepak Singhal Apr 09 '19 at 11:25
regarding your first comment; it can happen in some race conditions when client does not explicitly close the connection and it times out from server end. – Deepak Singhal Apr 09 '19 at 12:57

How does mysql connection pooling works with Node microservices?

1 Answers1