MySQL database hosting for MMORPG
- Thread starter bobbobbob
- Start date
Cogman
Lifer
- Sep 19, 2000
- 10,277
- 125
- 106
What are you using MySQL for?
It might be an OK choice for storing things like character profile information. It's probably not a good choice for state coordination (IE, character position).
Also, how are you connecting to it? One big difference between running locally and remotely is the latency on requests. If you are doing a lot of connection cycling then you are probably witnessing that firsthand.
MMORPGs require a lot of careful planning to work properly. You can't have a lot of shared state among servers, otherwise you run into issues where the DB becomes a major bottleneck. The way that's usually handled is that maps are subdivided and, as needed, new servers are spun up to handle specific sections of the various maps. Most of the game state is stored in memory on the various servers.
It might be an OK choice for storing things like character profile information. It's probably not a good choice for state coordination (IE, character position).
Also, how are you connecting to it? One big difference between running locally and remotely is the latency on requests. If you are doing a lot of connection cycling then you are probably witnessing that firsthand.
MMORPGs require a lot of careful planning to work properly. You can't have a lot of shared state among servers, otherwise you run into issues where the DB becomes a major bottleneck. The way that's usually handled is that maps are subdivided and, as needed, new servers are spun up to handle specific sections of the various maps. Most of the game state is stored in memory on the various servers.
Red Squirrel
No Lifer
Why not host it on a server at home on your own network? No reason to pay a company per month when you can setup a local server and pay for it once. If you want to test bad connectivity I think there are some tools in Linux/Iptables. You can simulate dropped packets and such and even set speed limits. I never played with it myself though. I know pfsense can do this too.
Once you release the game then that's another story as you probably want it somewhere with a good solid connection.
I run an Ultima Online server and converted the flat file system to MySQL years back. The UO server runs Windows as it's a .net application and did not want to mess with emulation such as mono as it would require rewriting a lot of core stuff that did not work properly when emulated in Linux, but the Mysql runs on a separate Linux VM. The database structure would make any DBA squirm as it's not exactly structured like you would expect but is rather serialized data in a bigtext field for each game object (items, mobiles, accounts etc) but it does work. The game server loads everything into memory at startup and only updates changes to the DB as needed. Previously with the flat files it had to write the entire thing to file for each save, and it had to be done in the main thread which interrupted the game. Now I use a multi threaded loop to handle actually saving to the DB. Objects that get changed are flagged, then the data is queued up into a buffer (this part has to be done in real time but it's fast as it's in memory) then the thread goes through buffer to submit the queries.
The tricky part with real time saving though is ensuring the data is always atomic. I have some various provisions for that which are handled in the first queue up part that is done in real time. One thing you don't want to happen is something like a player buying something from a vendor, so a new object is created and put in their pack, and their bank account is deducted. If the server crashes for any reason at the wrong time and the bank account change made it to the DB, but the item did not, you end up with a non atomic save and a player that is not too happy.
Once you release the game then that's another story as you probably want it somewhere with a good solid connection.
I run an Ultima Online server and converted the flat file system to MySQL years back. The UO server runs Windows as it's a .net application and did not want to mess with emulation such as mono as it would require rewriting a lot of core stuff that did not work properly when emulated in Linux, but the Mysql runs on a separate Linux VM. The database structure would make any DBA squirm as it's not exactly structured like you would expect but is rather serialized data in a bigtext field for each game object (items, mobiles, accounts etc) but it does work. The game server loads everything into memory at startup and only updates changes to the DB as needed. Previously with the flat files it had to write the entire thing to file for each save, and it had to be done in the main thread which interrupted the game. Now I use a multi threaded loop to handle actually saving to the DB. Objects that get changed are flagged, then the data is queued up into a buffer (this part has to be done in real time but it's fast as it's in memory) then the thread goes through buffer to submit the queries.
The tricky part with real time saving though is ensuring the data is always atomic. I have some various provisions for that which are handled in the first queue up part that is done in real time. One thing you don't want to happen is something like a player buying something from a vendor, so a new object is created and put in their pack, and their bank account is deducted. If the server crashes for any reason at the wrong time and the bank account change made it to the DB, but the item did not, you end up with a non atomic save and a player that is not too happy.
Cogman
Lifer
- Sep 19, 2000
- 10,277
- 125
- 106
I agree with your sentiment. You'd ideally want your servers to be running physically close to the DB to avoid latency issues.
As for the atomicity problem, the easiest way to handle that (IMO), is basically doing what DBs do under the covers anyways. That is, write the intent then the commit.
So, for a bank/item scenario, rather than updating a field that is the "player money" field, you'd write out to an intention log "I intend to deduct X money from player money" and in the same log you can write "I intend to create a new item xyz" and "I intend to give created item to player" and finally "commit".
from there, you can pull up all transactions with corresponding commits and process them at your leisure. If the server crashes while the log is being written, (before commit), you throw that log away. If the server crashes while you are processing the log, you just pick up where you left off. The only trick is making sure the processing and removal of each log item is done as an atomic operation.
Of course, using SQL transactions if you are using a SQL server anyways would generally be better. Though, it might not be desirable if you have to do a lot of processing while the transaction is open (that could slow things down). Ideally, your transaction are relatively short lived.
Another way to accomplish the log/transaction stuff is versioned data. You reserve a version, do all your operations with that version as the version number, and finally write out that "Active version for player x is 123 if the previous version is the same as what I started with". If the version changes while you are doing all this processing, then you just reprocess with a new version.
With that approach, you'll want something to regularly go back and clean up old versions, but it's semi easy to implement and relatively quick (especially if contention is low).
The version approach has the added benefit of working well with nosql solutions. That because the only thing that needs to be atomic is the update to the new version if the new version is what you started with. Most nosql dbs support that concept.
Red Squirrel
No Lifer
Yeah the way I did it is essentially any time an object is written to DB rather than overwriting the existing info it's flagged as changed, and the new data is written to a separate column. At some point in the game server process, in real time and not in the thread, when the system sees that everything is caught up, it issues a snapshot command, which simply tells the next thread to write all the changed data to the actual data row for each item, then reset the "changed" flag. If at any time the server crashes or something happens, the changed flags and the new data in the new data row is irrelevant, so it's reset at server startup and then everything loads from the atomic state.
Originally I actually had two sets of each table and it would copy the entire table to do a snapshot, and do the whole thing in a SQL transaction, but that was quite dirty and also very hard on the server. New way has proven to reduce the load quite a bit on the server.
In the case of my setup the process that handles the data is object agnostic, meaning it does not know what objects are linked with others, so it can't really do any special case handling, so rather than do that it just makes sure the entirety is atomic.
Originally I actually had two sets of each table and it would copy the entire table to do a snapshot, and do the whole thing in a SQL transaction, but that was quite dirty and also very hard on the server. New way has proven to reduce the load quite a bit on the server.
In the case of my setup the process that handles the data is object agnostic, meaning it does not know what objects are linked with others, so it can't really do any special case handling, so rather than do that it just makes sure the entirety is atomic.
Red Squirrel
No Lifer
No you can put multiple tables in same database.
You may want to read up on databases before you take on such a project though. Since you are doing it from scratch you have opportunities to optimize stuff way better than I did and you'll be able to learn about those things such as indexing etc.
SQL injection is also something very good to be aware of. The jist of it is don't trust any user input. That could be things like any items in game that can get a custom name entered by the player for example.
You may want to read up on databases before you take on such a project though. Since you are doing it from scratch you have opportunities to optimize stuff way better than I did and you'll be able to learn about those things such as indexing etc.
SQL injection is also something very good to be aware of. The jist of it is don't trust any user input. That could be things like any items in game that can get a custom name entered by the player for example.
cytg111
Lifer
- Mar 17, 2008
- 23,174
- 12,833
- 136
??? Why was your game crashing?
Are you trying to synchronize realtime multiplayer net code around crud operations on a database? Dude!
Dude!
Anyway, mysql may have some memory only temp tables or something... you do not wanna go to hard storage for that kind of thing.
Last edited:
- Aug 31, 2016
- 123
- 8
- 81
I have figured out.
That was 'concurrency problem'. Now MySQL and javafx nodes is working on diffrend threads. So Nodes dont get freeze now.
TRENDING THREADS
-
Discussion Intel current and future Lakes & Rapids thread- Started by TheF34RChannel
- Replies: 23K
-
Discussion Zen 5 Speculation (EPYC Turin and Strix Point/Granite Ridge - Ryzen 9000)- Started by DisEnchantment
- Replies: 9K
-
-
Discussion Intel Meteor, Arrow, Lunar & Panther Lakes Discussion Threads
- Started by Tigerick
- Replies: 7K
-
Facebook
Twitter