I’m creating a basic ‘engine’ for multiplayer games, possibly for mmorpgs in the future, but I’m stuck. I have to choose a database, but I don’t know which one to choose.
Here is some info:
The data that has to be saved is something like: account data, skill levels, gear, inventory, friend list, guilds (players/guildbank/agenda), player’s completed quests, etc.
I’m saving some character data into server variables (skill levels, gear, inventory), so I only need to query the database when a new player logs in, when someone loads guild data / opens guild bank, when someone starts/completes a quest, and every xx seconds to save all changed character data into the database (skill levels, gear, inventory)
I’m using Java for my server
I want the server to be able to have around 1000 players at the same time on it
Should I use a flat-file database or an online (stand-alone) database? And which database should I use? (H2, MySQL, Postgres, etc)
Money is a problem too btw, if I have to choose between one that’s free and one that’s 20% faster, I’ll choose the one that’s free.
@zarch said:
This is really not true. It isn't massive changes but there are a lot of subtle differences between the three main flavours of SQL. Everything from limit vs top for selecting ranges through to how to handle rowcounts, rownumbers, etc. A lot of the DDL options are different. Oracle only likes uppercase table names...etc...etc.
Yes you can port SQL from one to another but it's a pain. Better to avoid it if you don't need to.
This.
For the most part, for normal operations you will have "the way everyone else does it" and "the way Oracle does it". I think postgres and hsqldb so far have been on par... and mysql has some differences but I left it by the wayside as a "toy" a long time ago when it choked on some queries that postgres ate up like bacon.
And anyway, for an entity system the mapping will be very thin and straight forward. Mostly only really standard SQL features used.
@zarch said:
This is really not true. It isn't massive changes but there are a lot of subtle differences between the three main flavours of SQL. Everything from limit vs top for selecting ranges through to how to handle rowcounts, rownumbers, etc. A lot of the DDL options are different. Oracle only likes uppercase table names...etc...etc.
Not to mention that often times learning SQL by using it with one DB means that you're learning a subset of functionality with vendor-specifics mixed in.
Just for auto-incrementing:
MySQL uses AUTO_INCREMENT
MSSQL uses IDENTITY
Oracle & Postgres use sequences
@polygnome said:
Use standard SQL, and it won't matter what SQL database you are running on. There are only very few special cases in which vendor-specific things are faster or can not be achieved by standardized SQL.
As long as you use standardized SQL, everything you need to do is change is JDBC driver name, and you are good to go.
This is really not true. It isn't massive changes but there are a lot of subtle differences between the three main flavours of SQL. Everything from limit vs top for selecting ranges through to how to handle rowcounts, rownumbers, etc. A lot of the DDL options are different. Oracle only likes uppercase table names...etc...etc.
Yes you can port SQL from one to another but it's a pain. Better to avoid it if you don't need to.
Use standard SQL, and it won’t matter what SQL database you are running on. There are only very few special cases in which vendor-specific things are faster or can not be achieved by standardized SQL.
As long as you use standardized SQL, everything you need to do is change is JDBC driver name, and you are good to go.
Personally, i’d use H2 or HSQLDB, since you can run them both in-process which means that you get rid of the networking overhead when connecting to a database.
But maybe not all of your data needs to be relational. Relational databases are good for stuff like account information and so on, things were you need to select based on conditions or need to merge data. But some other data can better be stored non-relational. Character data e.g. might be stored in a simple file, one file per character. When the user logs in you read the character id from the account table and load the character file.
Basically you might want to store different data in different ways based on the way you need to access them. It all depends heavily on what your game looks like under the hood.
talking about RAM i don’t mean database RAM type data - because it use querries too.
talking about RAM i mean using Java storages like Maps or Lists to store data.
i have server that store some data in RAM, and update MYSQL with that data from time to time.
this is most optimal duel IMO.
using only RAM could fill it all, using only database is slow. you should think, what you need to update often and put it into RAM, other data to database.
for example read from RAM: account data / skill levels / used inventory.
reading only to/from database: guilds / quests / unused inventory / other similar data
so when server start it collect some of data from databse to RAM, so it can read is fast.
also it give possibility to share this RAM data within splitted server apps(for example one for AI, second for players), so they don’t need to make SQL querry each other.
and why MYSQL? because it is fast for big data. the more data it have, the faster than postgres it is. don’t know how fast H2 exacly work so i can’t tell about that.
Ideally the interface to your actual database is small (like one or two classes) so I don’t think you need to overthink this at this stage. Just create it with exchangeability in mind and if you find that file-based or in-memory databases go to their limits, exchange the database
about connection java <-> MySQL server you need as i good remember “mysql-connector-java-bin”(Jar file) or something like that and OFC db user / password + db server ip.
HsqlDb works like a Charm for small Games, but for a MMO Project you’ll need a scaleable Database Server.
Think about 5000 ppl are playing in the same world, like it’s said before one Server should handle aprox 1000 player so you’ll need 5 Servers wich are Synchronizing against each other. So the Database need’s to handle massive ammounts of Updates.
If you don’t want to use a system like Persistence or Hybernate to handle your Database (which would work with all Database Engines) you have to choose a database engine that is scaleable for it’s own. Mysql will do this job because of the possibility to setup an cluster environment, without changing anything except of server IP and port. As far as I know there is no stable release of Postgres or HsqlDb (correct me if this isn’t true) that could do the same.
I would try to implement a communication with Persistence or Hybernate, so every user could choose his database type and setup. If you don’t want to do this, a Database System with clusterability should be used if you don’t want to exchange the Database system if your game should be a success.
@jstelter2012 said:
HsqlDb works like a Charm for small Games, but for a MMO Project you'll need a scaleable Database Server.
Think about 5000 ppl are playing in the same world, like it's said before one Server should handle aprox 1000 player so you'll need 5 Servers wich are Synchronizing against each other. So the Database need's to handle massive ammounts of Updates.
If you don't want to use a system like Persistence or Hybernate to handle your Database (which would work with all Database Engines) you have to choose a database engine that is scaleable for it's own. Mysql will do this job because of the possibility to setup an cluster environment, without changing anything except of server IP and port. As far as I know there is no stable release of Postgres or HsqlDb (correct me if this isn't true) that could do the same.
I would try to implement a communication with Persistence or Hybernate, so every user could choose his database type and setup. If you don't want to do this, a Database System with clusterability should be used if you don't want to exchange the Database system if your game should be a success.
Everyone has different requirements and definitions of what an MMO is. The original poster said 1000 players... hsqldb will handle that fine (especially with lots of RAM) and if you are using an ES then the mapping is simple enough to trivially move to another more scalable database if you need to.
But realistically, indie MMOs will probably never scale that high. The infrastructure costs rapidly become pretty prohibitive. Doesn't hurt to plan for it but realistically a scalable indie MMO will be using someone else's "big data" services instead of starting their own data center.
