Twitter  Facebook  YouTube  E-Mail  RSS
The One Man MMO Project
The story of a lone developer's quest to build an online world :: MMO programming, design, and industry commentary
Slowly Ripping out MySQL
By Rob Basler on 2016-07-16 11:10:48
Homepage: email:one at onemanmmo dot com

When I originally envisioned the server for Miranda, I thought it would have a massive Tranquility-style MySQL server at its core running on giant SSD's and a ton of RAM that contained every bit of information about Miranda's world where I would dramatically tweak game settings in real-time to fight griefers and continuously refine the gameplay. Initially this was how the game worked. All the information about players, every setting, every unit stat, all of it was loaded into MySQL.

[Miranda's Servers Under Development]

Then came slow load times. Downloading the database of unit component statistics every time the player logged in took about 20 seconds. So I removed the unit statistics from the database and converted it into a tiny, highly compressed static data file that can only be updated with a new version download. But login was 20 seconds faster.

When I did the Shroud a few months back, I had the problem of needing to store the complete shroud state (a potentially large chunk of constantly changing data) that also needed to be downloaded to the client every login. MySQL is not really ideal for storing and serving large binary data so after a lot of consideration of MySQL's abilities and a hard look at key-value stores, I decided to store the shroud data as a file and use a web server to send it to clients.

At Miranda's core is an Entity Component System so as Miranda grew, the natural way to store those components when players were offline was as a table per component, one row per entity in the database. Loading was a simple join query, saving an individual component update was easy, it all seemed so logical. It turns out though, that once you have 21 components and thousands of units to update it gets really super slow, particularly for deletions. Players couldn't tell there was a problem due to prioritizing and queueing on the server, but while I was working on the game, I would often have to wait minutes while the server laboriously ground through all the MySQL queries a simple login or logout would generate.

The obvious solution, and one I had heard other games have used, is to combine all the force data into one binary blob and store that in the database. You lose the ability to update individual components, but updating a force is only one database query. Looking at that solution, I again came out on the side of file storage, so this week I removed all of the component tables from MySQL and changed the server to write the data for all of the entities in each force to a file. While I was at it, I was able to move more of the work done during force loading to a thread which lightens the load on the main thread when a lot of players are logging in at the same time. The other significant improvement is that my haphazard component updates have been replaced with a system that saves a snapshot of the player's complete force data to disk every three minutes, so server crashes have much less potential for damage than before.

While Miranda still has lots of systems that rely on MySQL, and there are a few more I plan to rip out eventually, this was one of the big server performance problems I had to solve. Databases are great for storing smaller sized pieces of data that need to have calculations done on them, they just kind of suck at big binary data.

By Luke Thorne on 2016-07-16 13:10:26
Homepage: email:lukeypukey36 at gmail dot com
Very interesting update post. Its good that you are doing that optimization right now to keep the game flowing correctly. Thanks for the update :D
By Peter on 2016-07-23 13:03:16
Homepage: email:retron at retron dot sk
Interesting to hear your approach. Personally, I made something similar, that I moved every to be "storage" from HD to Database, but now I'm just migrating it back to harddrive. Mysql is simply not fast enough, I switched to Postgre (much better performance), but I realized that I can do the same, when I store data directly on to harddrive, encoded oldschool XML format. Much faster workflow, no more complicated modifications to data models in case of changes in classes and relations.
By Rob Basler on 2016-07-23 14:10:59
Homepage: email:one at onemanmmo dot com
I spent a lot of time looking at key-value stores for this since I mostly don't need relational features. Finding none of them particularly ideal, I pretty seriously considered developing my own file-system based key-value store with versioning, replication and journalling. I may do that eventually.

All my player data on HD is binary format (for speed.)

New Comment

Cookie Warning

We were unable to retrieve our cookie from your web browser. If pressing F5 once to reload this page does not get rid of this message, please read this to learn more.

You will not be able to post until you resolve this problem.

Comment (You can use HTML, but please double-check web link URLs and HTML tags!)
Your Name
Homepage (optional, don't include http://)
Email (optional, but automatically spam protected so please do)
How many minutes in an hour? (What's this?)

  Admin Log In

[The Imperial Realm :: Miranda] [Blog] [Gallery] [About]
Terms Of Use & Privacy Policy