SQL Database Stuff
Posted: Wed May 22, 2013 9:45 pm
I've been working a lot with SQL databases lately, and I am beginning to seriously respect the power of the things. It does queries and stuff, which is great, but more than that it takes care of disk management, including rearranging data for coherent access on the hard drive. It handles multi-threading I/O in a thread-safe manner, and can handle a massive number of simultaneous threads. It can do version control and roll-backs if there are any errors, and you can make automated backups. You can farm the data off onto multiple servers if one is not powerful enough. And aside from the setup, it manages all of this on its own.
I am beginning to think that Ectrimble was absolutely right to try and move user data into a database instance. I thought it was overkill, but now I am thinking it is just what we need to increase the scalability of the system. And not just for user data, either.
I've looked around before to see what other people think, and the general consensus is that for user data, sure, but chunk data is better in flat files. It is certainly the case for a single player or a basic server because you are never accessing that many files at any given time, and the data compression is much more important for terrain bandwidth than the I/O tweaks an SQL server would give you. But that doesn't apply so much to us.
For one, we are not dealing with data from a single world. The master servers may be moving data around for hundreds of worlds, each of which need to be archived and synchronized. Chunk caching will become even more important than it is now, and it will likely be necessary to cache parts of the terrain to a player's local storage to avoid swamping the cloud with redundant chunk access.
A second reason is the Copernicus terrain system is built with the concept of layers, in which the world is separated into what is needed right away and what can wait. You might separate exposed blocks and buried blocks, or light values touching light vs. light values for empty space or underground. You might want to store heightmaps at different levels of detail for distant rendering, which in turn may act as hints for a compression scheme on the other terrain info. Using flat files, you would have to store all of that together, and load the whole thing into memory to pull out the parts you want, or you would need to create additional files for each layer, which would further fragment the data on the disk. In a database, you can arrange it according to how you intend to access it, and pull out just what you need. Backups can be made smaller by only storing the data which can't be re-generated, so no un-modified chunks or LOD perspectives.
In short, replacing the filesystem with an SQL database for Futurecraft is beginning to make a crazy amount of sense.
Dedicated planet servers can use hardcore MySQL. Player-run servers and clients can use SQLLite, which is embeddable in-process, and both the client and server instance can access the database directly, which might be handy.
I am beginning to think that Ectrimble was absolutely right to try and move user data into a database instance. I thought it was overkill, but now I am thinking it is just what we need to increase the scalability of the system. And not just for user data, either.
I've looked around before to see what other people think, and the general consensus is that for user data, sure, but chunk data is better in flat files. It is certainly the case for a single player or a basic server because you are never accessing that many files at any given time, and the data compression is much more important for terrain bandwidth than the I/O tweaks an SQL server would give you. But that doesn't apply so much to us.
For one, we are not dealing with data from a single world. The master servers may be moving data around for hundreds of worlds, each of which need to be archived and synchronized. Chunk caching will become even more important than it is now, and it will likely be necessary to cache parts of the terrain to a player's local storage to avoid swamping the cloud with redundant chunk access.
A second reason is the Copernicus terrain system is built with the concept of layers, in which the world is separated into what is needed right away and what can wait. You might separate exposed blocks and buried blocks, or light values touching light vs. light values for empty space or underground. You might want to store heightmaps at different levels of detail for distant rendering, which in turn may act as hints for a compression scheme on the other terrain info. Using flat files, you would have to store all of that together, and load the whole thing into memory to pull out the parts you want, or you would need to create additional files for each layer, which would further fragment the data on the disk. In a database, you can arrange it according to how you intend to access it, and pull out just what you need. Backups can be made smaller by only storing the data which can't be re-generated, so no un-modified chunks or LOD perspectives.
In short, replacing the filesystem with an SQL database for Futurecraft is beginning to make a crazy amount of sense.
Dedicated planet servers can use hardcore MySQL. Player-run servers and clients can use SQLLite, which is embeddable in-process, and both the client and server instance can access the database directly, which might be handy.