Mysql: Am I supposed to be re-initializing a connection each request?

[ghost]

Forgive me for the following code, but I'm copying and pasting it out of sheer stupidity. Why? Cause I
know if it's bad code, you can call me out on it.

// Only use MYSQL for specific functions ~
if (['JOIN', 'ATTACKMOB', 'JOINGAME', 'PINFOUPDATE', 'FA', 'BR', 'BL', 'CG', 'EQI', 'SU'].indexOf(Command) != -1) {

    var mysql = require('mysql');
    var db_config = {
        host: 'localhost',
        user: 'root',
        database: 'forum',
        password: 'blowme'
    };
    var db;

    function handleDisconnect() {
        db = mysql.createConnection(db_config); // Recreate the connection, since


        db.connect(function(err) { // The server is either down
            if (err) { // or restarting (takes a while sometimes).
                gamelog('error when connecting to db:', err);
                setTimeout(handleDisconnect, 5000); // We introduce a delay before attempting to reconnect,
            } // to avoid a hot loop, and to allow our node script to
        }); // process asynchronous requests in the meantime.
        // If you're also serving http, display a 503 error.
        db.on('error', function(err) {
            gamelog('db error', err);

            if (err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
                gamelog("MONKEY?");
                handleDisconnect(); // lost due to either server restart, or a
            } else { // connnection idle timeout (the wait_timeout
                //throw err;                                  // server variable configures this)

            }
        });

    }
    handleDisconnect();
}

Okay.. See, for example if a user is updating their item slot in their inventory... there is going to be a new mysql connection each time a user clicks on their item and moves it around. That's not good for optimization is it, or will it be even noticeable to MYSQL? Is this where pooling comes in?

For example, let's say I have an item that modifies an item each time it is used ON the item. And a user has a STACK OF 200 of them. They will obviously use each one over and over again on an item, that would mean over-time, they would have made the SQL server re-connect and end 200 connections. I know that cannot be good, or is this normal?

How exactly does pooling work? Is it like Websockets for MYSQL? Keeps the SQL connection open and allows queries to be ran without ending the connection over and over again?

Thanks!

[sidorares]

connection pool allows you to reuse existing connections, by keeping a dynamic list of connections and signalling when it can be acquired for usage. Connections in the pool can be in various state: "not created yet" ( number of opened connections is less than allowed maximum, we can add new if need to );
"in use" - connection taken from pool and not returned back; "idle" - connected, ready to be used; "dead" - used to be alive but now not to be reused for some reason

Suppose you created pool with var pool = mysql.createPool(db_config)
1) "please give me already opened connection if available, or just create new one if there is no idle connection in the pool"

 pool.getConnection(function(err, connection) {
        // ... use it here
    });

2) "I'm not going to use this connection anymore, add it back to list of idle connections and allow to re use later"

connection.release()

3) "if connection is dead, don't reuse it later"

• no need to do anything explicitly, handled automatically by pool

[dougwilson]

Hi @Dillybob92 , did @sidorares answer everything for you?

[ghost]

Sorta, and I do appreciate his response. I'm just wondering.. Once that connection pool is made... When another client connects... is that pool being re-created each time or will stay the same? It's not possible to have multiple pools right?

[sidorares]

@Dillybob92 pool is just a container for multiple connections. You usually initialise it only once ( it remembers config to use when it needs to create a new connection ). Later you do pool.getConnection() using the same single pool instance, and it tries to give you best possible connection ( by re-using idle or creating new ). Under low load it's usually results in getting idle connection from array which is very fast. (new connection is 3 network roundtrips + full OS thread created on mysql server )

[ghost]

Alright. And in my case.. Using pooling for tasks that require extensive MYSQL updating would be appropriate and beneficial for performance correct?

[sidorares]

you need multiple connection to speed up execution ( mysql protocol is sequential ) and at the same time you want to minimize number of times connection is established because that's expensive. Yes, pool tries to handle all this for you

[ghost]

Okay, i'll try to get this started with my nodejs WS game server. So I need to run the pool just once upon loading the gameserver and let it's do its thing. How about... initializing / reconnecting each request, remove that old mysql config and replace it with pooling then right?

And, when I do start using pooling I don't need to use db.end(); all the time then, right?

[sidorares]

you do pool = mysql.createPool(db_config) initially. As soon as you don't need connection you release it back to pool with conn.release()

[ghost]

But when my gameserver is running, I don't want to release the pool ever.. because players are doing stuff like equipping items, moving items in their inventory and all that data is stored server side and I make mysql calls to update that all the time. Hmm

[dougwilson]

@Dillybob92 the way the pool works is you need to forget about holding onto a single connection; that is dangerous and you shouldn't do it unless there is a very good reason.

You should call mysql.createPool(db_config) once in your code and then, every time a user equips an item, moves an items, etc., you just do pool.query(...) instead of standard connection querying. This will manage everything for you in the simple case.