When a MySQL pool connection is released, should the connection stay open and would this affect max connections?

user_605850 0
Viewed 161

I'm working with MySQL more and ran into a question I can't seem to find an answer for without experimenting myself or looking much deeper.

I create a pool with MySQL and release properly, but I see the that there's an active connection still through MySQL Workbench till process is closed. Code example below:

        pool.getConnection((err, connection) => {
        if (err) {
            console.log(err);
            return callback(err);
        }
        if (args.length > 2) {
            sql_args = args[1];
        }
        connection.query(args[0], sql_args, (err, results) => {
            connection.release();
            if (err) {
                console.log(err);
                return callback(err);
            }
            callback(null, results);
        });
    });

Is this normal behavior for the connection to remain open and if so, what happens if I had hundred servers connecting to this pool and all of them make a request but not all at once, and hit the max-connection limit with MySQL; Will there be a purge of inactive connections (not being used)?

edit I'd wonder if the connections don't close would running a shorter TTL be ideal or some type of clearing? I'd like to have 100 servers fit in a 50 max connection and not error out.

node.js mysql
1 Answers

It depends on the implementation of the connection pool package.

Most connection pools in my experience allocate a fixed number of connections, open them, and keep them connected permanently for the life of the application. Connections are "lent" to client code that needs to access the database, and when the client code is done, the connection is returned to the pool, but it is not disconnected from MySQL.

If a connection drops by accident, the connection pool makes a new replacement connection, again so it can maintain a fixed number of ready connections. They remain connected permanently, as well as the CP can maintain them.

Yes, if you had 100 app servers that each allocate a pool of 20 connections, you would see 2000 clients connected on the MySQL Server all the time if you ran SHOW PROCESSLIST. We do see this regularly in production where I work.

Is that a problem? MySQL can handle a lot of connections if most of them are idle. We set our max_connections to 4096, for example. But rarely are more than 20-40 of them actually executing a query at any given moment. In fact, we made an alert that fires if the Threads_running goes over 100 on a given MySQL Server instance.

What if you had 100 app servers each of which made a CP of 500 connections? Yes, that would create 50000 connections on the MySQL Server, and that's probably more than it can handle. But why would you do that? The point of a connection pool is that many threads in your app can share a small number of connections.

So do be mindful that the total number of connections is basically your connection pool size multiplied by the number of app instances. And you might even have multiple app instances per app server (e.g. if you run sidecar processes and so on). So it can add up quickly. Do the math.

Bill_Karwin_208600
Related