mysql vs postgres sql for MMO server

[Red Squirrel]

I will be taking an existing MMO server that saves data in flat files. Every 15 minutes it saves the ENTIRE world data to the file, rewriting the original. Very bad design as the more mobiles/items, the longer these saves take (halting game play).

I will be rewriting most of the server application so that whenever something changes in the game (new item, modified item, etc) it is written to the DB immediately, or possibly in a queue that is processed in another thread.

So say there is a big raid and people are chucking potions at each other that create flames on the ground, these flames are items and would be written to the db. Let's assume that at any given time a big battle can break out and there could be over 100 write queries.

When the server starts all item/mobile data would be preloaded into memory while some data such as user accounts would be queried only when needed.

So what do you think would be better to use, postgres or mysql?

This will most likely be written in C#/mono or maybe C++ (the current app is C# so I'm hoping to just get it going in mono and recode lot of it)

 

[SJP0tato]

It really seems to depend on which you and the other programmers are more comfortable with, and has the features you need.

Most of the comparisons I could find are 3-4 years old now, and may not apply to the latest versions of each DB.

From a performance standpoint, implementation will count for a lot more than the choice of database.

Hopefully others more experienced in DB affairs can lend their $0.02 as well here.

Out of curiosity, how many simultaneous players play this game currently? What's the average number of updates every 15 minutes currently? It sounds like an interesting problem you've got there, if you can share any additional details we might be able to make additional suggestions.

 

[Red Squirrel]

Right now there are not that many players, it averages about 15, the highest I've seen was 60. Though I want to ensure I can support 100's easily without any major upgrades other then possibly separate db server from app server in the future. As this is on a leased server, the more powerful, the more it costs per month.

Right now it saves every 15 minutes. If for some reason the server is stopped, there is a "time warp".

 

[drebo]

There is always going to be a time-warp in the event a server fails due to one simple problem: disk write speed. On your scale, it might not seem to be a bad idea to write every single change to the database immediately. However, consider the possibility that you have 5000 players, needing to be written to the database every time they move, and 10,000 mobs doing the same, and 100,000 item spawns, etc. Every time a door is opened, a database query is needed.

No, it's not an optimal situation to do it that way.

Using a queue is also not optimal. Take, for instance, a monster. He spawns, so you have to generate a query about his location, a query for each of the items he holds, etc. Then he moves a bit, so you've got to update his location. Then he moves again, and his location updates again. That's 3 queries having to do with his location, when only the last one is relevant. What happens, then, when a player comes along and kills him? That's another few location updates, and ultimately, the monster is defeated. Say this happens within the span of 30 seconds and your worker thread processes the queue every minute...that's 10+ queries that are now completely irrelevant because the monster that was spawned and killed no longer exists.

This is part of the reason that MMO worlds are broken up into many zones. Even UO, which had no "zoning" was broken up and a single "shard" was actually multiple servers. Now, your whole world saves, which happen every 5 minutes or so, take much, much less time.

If your goal is scalability, you won't find it in a single-server scenario.

 

[ivan2]

Originally posted by: SJP0tato
It really seems to depend on which you and the other programmers are more comfortable with, and has the features you need.

Most of the comparisons I could find are 3-4 years old now, and may not apply to the latest versions of each DB.

From a performance standpoint, implementation will count for a lot more than the choice of database.

Hopefully others more experienced in DB affairs can lend their $0.02 as well here.

Out of curiosity, how many simultaneous players play this game currently? What's the average number of updates every 15 minutes currently? It sounds like an interesting problem you've got there, if you can share any additional details we might be able to make additional suggestions.

my feeling exactly. because the amount of writes that's necessary, the way you handle your transactions will affect the game performance much more than the DB of choice. I believe a delayed write in batches is in order.

 

[Red Squirrel]

Originally posted by: drebo
There is always going to be a time-warp in the event a server fails due to one simple problem: disk write speed. On your scale, it might not seem to be a bad idea to write every single change to the database immediately. However, consider the possibility that you have 5000 players, needing to be written to the database every time they move, and 10,000 mobs doing the same, and 100,000 item spawns, etc. Every time a door is opened, a database query is needed.

No, it's not an optimal situation to do it that way.

Using a queue is also not optimal. Take, for instance, a monster. He spawns, so you have to generate a query about his location, a query for each of the items he holds, etc. Then he moves a bit, so you've got to update his location. Then he moves again, and his location updates again. That's 3 queries having to do with his location, when only the last one is relevant. What happens, then, when a player comes along and kills him? That's another few location updates, and ultimately, the monster is defeated. Say this happens within the span of 30 seconds and your worker thread processes the queue every minute...that's 10+ queries that are now completely irrelevant because the monster that was spawned and killed no longer exists.

This is part of the reason that MMO worlds are broken up into many zones. Even UO, which had no "zoning" was broken up and a single "shard" was actually multiple servers. Now, your whole world saves, which happen every 5 minutes or so, take much, much less time.

If your goal is scalability, you won't find it in a single-server scenario.

Actually there are some things I wont be writing directly to the db such as moves. I have not figured out that part yet. Since yeah they'll backlog too quick. x y z map coords will be another system or at least, be written differently.

At worse I may even have flags set on items/mobs. If it changes the flag is set, then there would still be world saves but it would just be a bunch of "update" queries to save only what changed. That's another option. Would still be much faster then saving the entire world each and every time. I could maybe even thread it so it does not halt, but not sure on that.

I've thought of going with the capability of having multi servers though I'd have to rewrite a whole new engine for this. That's actually how EA are setup. It's much more complex to handle though and even on the EA servers there have been issues regarding "server lines". So if I go multi server it would be separating sql from the game server, possibly multiple sql servers. (one for items, one for mobs, etc). But the actual world would remain on one server.

SQL replication could be interesting as well (for redundancy), but probably not well suited for an application like this.

 

[drebo]

From my point of view, the only things that are important enough to be written to the database are where things are at the exact time that the server saves. Nothing else matters. Things like movement should be interpolated based on vectors (although if your server really is a UO server, this won't matter as it uses tiles)...i.e. a direction, a speed, and a start flag, then a stop flag...a bit of math, and bam, you have your location...but as far as memory and client communication goes, your requirements have gone way down, as now instead of communicating every single location update, you communicate a path and let the client plot it out. (this method is also why you get things like "ghosting" in games like WAR when the server is lagging).

Now, obviously, if you're building a server emulator for a game client that doesn't work this way (such as UO), then you're not going to be able to do this.

Anyway, I still maintain that preset writes are the best way to do it. Reads will be done ad-hoc, as not everything will be in memory...but writes should be scheduled. It's just not feasible to keep track of every single little detail like that. All you need to care about is where everything is at the moment the server saves its state.

 

[Red Squirrel]

It is a UO server. So yeah guess I should just write on a scheduled bassis instead then. That can probably be aranged as I only have to write what changed. Can probably get away with doing it like every minute, I'll have to experiment more once I get into it.

So as far as postgres vs mysql is there really any big performance differences? What about specific engines like innodb? (forget what the other one is).

 

[Drakkon]

What about Oracle? It is made for large scale / large transaction applications. Its what I would rather deal with if i was worried about a large scale. The uptime is associated with how awesome a db admin you have and Oracle admins do not come cheap. You get what you pay for as usual

InnoDB vs MyISAM i think is what you are looking for - since I would guess you'd want things to be relational you'd have to go for InnoDB. It used to be MyISAM is faster than InnoDB but as processors and the engine itself got better the difference is negligible from what I have read. The ability to have transactions and foreign keys is far more valuable than text searching to me too.

 

[Crusty]

Originally posted by: Drakkon
What about Oracle? It is made for large scale / large transaction applications. Its what I would rather deal with if i was worried about a large scale. The uptime is associated with how awesome a db admin you have and Oracle admins do not come cheap. You get what you pay for as usual

InnoDB vs MyISAM i think is what you are looking for - since I would guess you'd want things to be relational you'd have to go for InnoDB. It used to be MyISAM is faster than InnoDB but as processors and the engine itself got better the difference is negligible from what I have read. The ability to have transactions and foreign keys is far more valuable than text searching to me too.

Don't forget you can have more then one table type in a single database

 

[Red Squirrel]

lol I'm not willing to fork the cash required for oracle, trying to stay with free/open source. The server cost alone is enough. (Quad xeon leased at The Planet)

So guess mysql with innoDB should be best bet then right? Of course how I implement has a big factor too. I'll be learning a lot about SQL in this process as it will be my first time using a DB for a full blown application outside of web stuff.

 

[Markbnj]

Originally posted by: Drakkon
What about Oracle? It is made for large scale / large transaction applications. Its what I would rather deal with if i was worried about a large scale. The uptime is associated with how awesome a db admin you have and Oracle admins do not come cheap. You get what you pay for as usual

InnoDB vs MyISAM i think is what you are looking for - since I would guess you'd want things to be relational you'd have to go for InnoDB. It used to be MyISAM is faster than InnoDB but as processors and the engine itself got better the difference is negligible from what I have read. The ability to have transactions and foreign keys is far more valuable than text searching to me too.

The last big company I worked at ran something like 10 processor licenses of Oracle to support our PeopleSoft system, and the annual support fees were over $100k.

 

[Red Squirrel]

Originally posted by: Markbnj

Originally posted by: Drakkon
What about Oracle? It is made for large scale / large transaction applications. Its what I would rather deal with if i was worried about a large scale. The uptime is associated with how awesome a db admin you have and Oracle admins do not come cheap. You get what you pay for as usual

InnoDB vs MyISAM i think is what you are looking for - since I would guess you'd want things to be relational you'd have to go for InnoDB. It used to be MyISAM is faster than InnoDB but as processors and the engine itself got better the difference is negligible from what I have read. The ability to have transactions and foreign keys is far more valuable than text searching to me too.

The last big company I worked at ran something like 10 processor licenses of Oracle to support our PeopleSoft system, and the annual support fees were over $100k.

That's not the type of budget a free UO server has. haha.

 

[brandonb]

I know from using Postgres and MS SQL server that Postgres is absolute horseshit. I don't know what MySQL has to offer, but I'm thinking it has to be better than postgres.

Use the free edition of MS SQL server provided you don't need anything too powerful (I believe its limited to 1 cpu, 2 gigs ram, and 4 gigs worth of database data)... Which may or may not be a problem.

 

[Red Squirrel]

Rather not tie myself to MS limitations. So guess mysql it is.

 

[Nothinman]

I'd go with PostgreSQL. The reason MySQL became so popular is just because it's simpler to setup and reads were faster but I don't think that's the case anymore and PostgreSQL is a more complete database. I think the only thing MySQL actually does better now is clustering and replication.

You get what you pay for as usual

Good thing that's not the case with software...

I know from using Postgres and MS SQL server that Postgres is absolute horseshit. I don't know what MySQL has to offer, but I'm thinking it has to be better than postgres.

MS SQL is actually pretty decent (because MS bought it instead of writing it themselves) but PostgreSQL is fine too.