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.
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.
We were unable to retrieve our session 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.