Developing a HTML5 MMORPG - Part 2 : SQL is too slow

In my last MMORPG project post, I explained what the Entity System is and how it should be managed.

So these last days I worked on my Entity System framework. I created a little node.js server associated to mysql. But here's what happened

Asynchronous hell

The only existing MySQL drivers for node were asynchronous. This is not a surprise as node is thought for non-blocking asynchronous operations. I faced this when working with mongodb too.

But to be honest, this is extremely boring. Writing a few callbacks is fine and I got used to it so I don't really make errors with them, but seriously it's just ugly. If you need to make two queries in a row, you end up with something like that:

EntitiSystem.prototype.createEntityAndAddComponent = function (label, component)  
var that = this;  
var query = "INSERT INTO entities (label) VALUES('" + label + "')";  
this.connection.query (query, function (err, res) {  
    if (err) throw err;
    var entityID = res[0]['entity_id'];
    var query = "SELECT * from components WHERE name='" + component + "'";
    that.connection.query (query, function (err, res) {
        if (err) throw err;
        var componentID = res[0]['component_id'];
        var query = "INSERT INTO entity_component (entity_id, component_id) VALUES (" + entityID + "," + componentID + ")";
        that.connection.query (query, function (err) {
            if (err) throw err;
            var query = "SELECT LAST_INSERT_ID()";
            that.connection.query (query, function (err, res) {
                if (err) throw err;
                var componentDataID = res[0]['component_data_id'];
                var query = "INSERT INTO position_data (component_data_id, x, y, z) VALUES (" + componentDataID + ", 10, 5, 10)";
                that.connection.query (query, function (err) {
                    if (err) throw err;
                    callback();
                });
            });
        });
    });
});

If you want you can check the whole entity framework on github

This example is a fake and exagerated example because I created many levels of abstraction for that and most of the data is cached, but it's sort of what happens in the background.

And even in the game code, each time I have to query data, I need to make an asynchronous call. Pretty boring. These asynchronous calls we see everywhere in node are not elegant. It reminds me of the jump instructions in ASM.

Hard drive speed

I was brave enough to build my Entity System anyway. While building it I wrote mocha unit tests on a special database that would be dropped/recreated everytime. That's the first time I went for test-driven development and it was actually pretty great. I was able to write some test uses for my entity system while creating it. So I wrote tests that would generate the needed databases, cache the list of components/assemblages and then create some entities, set their component values, etc.

Then I ran speed tests along with it and I was very sad.
My tests were on tables of 1,000 to 10,000 entities. Considering everything in the game is stored in databases, this is not that much.

So what happened is that any request on complicated tables (1,000 to 10,000 entries) took at least 10ms, for the most simples, and arround 30ms for more complicated ones.

Considering a game loop runs in ~30ms for 30 FPS, you can easily understand that these requests time make a realtime game impossible to run.

Then I noticed that my hard drive was making noise during MySQL requests : I initially assumed that MySQL automatically cached things in ram and wrote it to the disk in background. Turns out it wasn't.

A little hope : Memory databases

So I went looking for memory-stored SQL. I initially found MemSQL but their only dev package is for arch linux with 8GB ram minimum, and there seems to be not much support for it, so I didn't try it.

In the end I used MySQL cluster, which is a sort of manager that combines multiple MySQL servers for load balancing. What was interesting is that its db engine actually used memory, and only wrote to hard drives to replicate the data, this way promising high write/read speeds.

So after a few hours trying to figure out how to make this work (I also was working on windows and had to use its poor command system) I managed to launch a MySQL cluster server running in memory.

So, I re-launched my benchmarks, looked at the results and... Well it didn't change that much. The most complicated requests had drastically reduced times (I think a 490ms requests went down to 20ms). But the thing is, most of them were still more than 10ms, which is still unsuitable for real-time processing.

No SQL for me

So after losing my last hope I had to face the facts: SQL is just not fast enough for game programming. I mean you can use it to store data of course, but don't even think of using it in real-time.

I have no idea how Adam in his post series initially imagined his system to actually work in SQL, maybe I misunderstood something I don't know, but it seems to me that, at least on normal computers, there is just no way of using SQL to store realtime game data.

So now I still need to think another way of managing my data structures. The thing is, I really liked this SQL idea because SQL requests are extremely powerful to select data and it would be awesome for game systems. Well I could still keep it for some persistant datas while using another structure for real-time, but maintaining different data structures is hard.

Using only SQL was the promise of a consistent data structure between both the client, the server, and the database storage.

I will probably end up using noSQL I guess, which can offer similar functions if I do it right, but with way, way greater performances. In folks, mongodb was able to manage a big bunch of data in real time, so why not. Still, I would like to be able to use SQL requests :(

Or maybe I'll just go back to simple lists. The problem being that lists are very rigid data structures. Being able to insert, select and update data is the power of SQL

For the solution to this, let's go to the next post.


comments powered by Disqus