When do I save the buffer data to the database?

Question

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.

Answer 1

[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.