2
\$\begingroup\$

My entire game is server side and I use nodejs with mysql.

When players connect or join a game, their player data (Items, Stash, Quests, etc) all get stored into the memory buffer.

For example, if a player joins a game and there are 40 items on the floor and he decides to pick them all up, it's easier just to add that to their Inventory object instead of using a insert statement each time. Then, when that user disconnects I can save all that data respectively. (Now that I think of it, that query would run 40 insertion rows..)

That's the problem I have though, do I save the data during the looting process or wait until the player disconnects and run a db update to save their memory buffer (only the changed buffer) to the database?

The Issue I have with that is, if a player picks up over 40 items in the game, that's going to be a lot of sql insertions when they disconnect..

Imagine them finding a ton of items from mobs and having a huge amount of new updated buffer data that needs to be stored.. (after they disconnect), that would be a dreadful update. Especially if they stored hundreds of items in their stash or whatnot. Hmmm.

enter image description here

\$\endgroup\$
2
\$\begingroup\$

[1] Database Reader / Writer Forked Process

You could use a db update child process that is called when a user leaves the game.
That would be non-blocking for your node.js socket server's main thread and the other clients wouldn't experience the lag during the db update
------------[EXAMPLE]------------------

// server.js
var process = require('child_process'); 
var fs = require('fs'); 
var db_rwp = process.fork('db_rwp.js');
    db_rwp.on('message', function(data) {
      switch(data.acid){    
        /* INIT : ERROR  */ case 'error':
            console.log(' # database reader / writer ERROR  ');     
            console.log(data.arg1);
          break;    
        /* INIT :  OK!   */ case 'success':
            console.log(' # database reader / writer : 0N   ');     
          break;
        }
    });


// db_rwp.js
var mysql = require('mysql');
var fs = require('fs');
//When Spawned Return Connection Status
var db = mysql.createConnection({
    host:'host', user:'user', password:'pass', database: 'database'
});
db.connect(function(err){
    if (err) { process.send({acid:'error',arg1:err}); }
    else { process.send({acid:'success'});}
});


Comunication example :
(maintain the socket.id of the request to return post-action results to specific client)

// server.js -to- db_rwp.js :
db_rwp.send({acid:'action_id', csid:socket.id});
// db_rwp.js -to- server.js :
process.send({acid:'action_id', csid:data.csid});
// server.js -to- client :
io.to(data.csid).emit('action_id_result');


------------------------------------

[2] Global save from main thread

You could call a db update using a desired world save frequency.
Freezing all the clients to prevent dupes/etc...
While the server saves you could display a message in the clients:
'The world is saving please wait...'

#1 would need a way to detect when a client is performing a log-in whilst another client is already inside that account. (Avoid loading obsolete data from db)
#2 is useful for having a recent backup to load in case of crash, RunUO (Ultima Online Server Emulator) does this to save the world state.

\$\endgroup\$
  • \$\begingroup\$ Oh wow, I forgot about this topic :P. I went with the db.update [1] for non blocking and I'm cringing hard looking back at this question. Storing the items into the buffer data and then saving them later is a really bad idea I have no idea what I was thinking back then, part of learning process I guess . \$\endgroup\$ – NiCk Newman Feb 12 '16 at 23:18
  • 1
    \$\begingroup\$ I added a code example to the answer, it might help someone else. \$\endgroup\$ – unknown.0perator Feb 12 '16 at 23:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.