hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Specialty Hosting and Markets : Game Servers : postgres vs mysql for MMO server
Reply

Forum Jump

postgres vs mysql for MMO server

Reply Post New Thread In Game Servers Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 12-30-2008, 02:52 AM
Red Squirrel Red Squirrel is offline
Web Hosting Master
 
Join Date: Feb 2008
Posts: 807
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)



Sponsored Links
  #2  
Old 01-04-2009, 12:48 AM
AntX AntX is offline
WHT Addict
 
Join Date: Jul 2005
Location: Quebec, Canada
Posts: 128
I won't get into arguing your consideration of "fire" as an "item", BUT...
Writing this kind of stuff in the db will eventually consume all you server's ressources. I mean, in the even of a crash, what's the point of keeping the fire, right?
Keep that stuff in memory instead. When people leave that particular area, or after a while, the fire should be gone, so there's really no point in keeping it in the db.
About your postgresql/mysql question, I'd say work with what you like (or know) the most. They're both plenty fast for that task.
Or better yet, use a DB abstraction layer that would work with both!
Good luck with that!

  #3  
Old 01-04-2009, 01:23 AM
Steven Steven is offline
Problem Solver
 
Join Date: Mar 2003
Location: California USA
Posts: 13,113
My suggestion is:
Store user accounts in mysql (with INDEXES!)
Cache info into memory with memcached.

Sponsored Links
  #4  
Old 01-04-2009, 02:32 PM
Red Squirrel Red Squirrel is offline
Web Hosting Master
 
Join Date: Feb 2008
Posts: 807
One thing I've been considering as well is have some data real time (such as accounts) but most not, but instead be on a save basis. So every 5-10 or even 30 minutes I could have a period where all the data that has changed saves (would be a large amount of update queries, as opposed to doing them all real time). So the more often the saves, the less queries there would be.
I could probably get away with not locking the server during this time too as mysql is less sensible to changing information. C# is bad for that though, and will crash if I loop through a collection and it changes, so I'll have to find a way around that but that's another story. for() loops should be ok with that, it's the foreach that will crash.

  #5  
Old 01-04-2009, 10:51 PM
AntX AntX is offline
WHT Addict
 
Join Date: Jul 2005
Location: Quebec, Canada
Posts: 128
One thing I've been considering as well is have some data real time (such as accounts) but most not, but instead be on a save basis.
Why bother? The more often you save, the safest your data if (when?) a crash happens. Queueing your SQL querries in "batches" is a good way to lose information, and is a good way to put stress on the I/O.
I could probably get away with not locking the server during this time too as mysql is less sensible to changing information.
Hint: use InnoDB, as it is transaction-aware. Plus, is your database gets larger, you won't drop in performance as with myISAM.
Just a little question... are you modifying a UO shard? Just asking, because RunUO already supports mysql

  #6  
Old 01-05-2009, 03:50 AM
atariko
Guest
 
Posts: n/a
I don't know if this is still true, but Postgres used to have problems with lots of open connections at once, where as MySQL scaled a bit better in this respect. Im not sure how you are coding the system, but this might matter if you are doing it all async.
And as the previous poster noted, indexes are your friend When done correctly.

  #7  
Old 01-05-2009, 04:11 AM
Red Squirrel Red Squirrel is offline
Web Hosting Master
 
Join Date: Feb 2008
Posts: 807
Why bother? The more often you save, the safest your data if (when?) a crash happens. Queueing your SQL querries in "batches" is a good way to lose information, and is a good way to put stress on the I/O.
Hint: use InnoDB, as it is transaction-aware. Plus, is your database gets larger, you won't drop in performance as with myISAM.
Just a little question... are you modifying a UO shard? Just asking, because RunUO already supports mysql
Yeah it's for UO and RunUO does not support mysql, in fact I've suggested it to them countless times but they think it would drop in performance. But serialization is not exactly the most scalable when you have to save the entire world each time.
I'll be sure to use InnoDB as well.
Also you're figuring real time writing would be better performance? (better safety though thats for sure) What about stuff that changes very fast, like x/y/z (ex: someone running). Should I have some exceptions but make most of it real time?

  #8  
Old 01-05-2009, 05:42 AM
mike_wt mike_wt is offline
Newbie
 
Join Date: Apr 2008
Posts: 20
My vote is for Postgres, you need a very reliable rdbms and Mysql is known more for speed than for reliability. While plpgsql is more advanced (AFAIK) than mysql's procedural language, the deal breaker for you might be the need to search full text on your tables and then you'll have to choose between search and speed (myIsam) and reliability (InnoDb) . Postgres doesn't have this problem; in fact doesn't have many problems now. Historically it was the slower rdbms, but nowadays it is as fast or faster (on heavy load and multiple CPUs) than mysql.
Postgres connection are still slower than Mysql connection but that's why you have pooling (PgPool on Linux, Ado.Net on windows)
If you webapp it's written in C# you might want to use Windows as a platform, because Mono still isn't a complete rewrite of .Net 3.5 and you'll get in a lot of trouble because some features (Custom Session Storage or Asynchronous Pages/Asynchronous IO, for example) work differently that MS.NET (I know, I've tried it and you get almost no support, my questions are still unanswered on Mono forum).
If you decide for Windows and want the biggest performance, MS Sql Server (Express) is your best friend, because of the close integration with .net . Do some benchmarking and prepare to be amazed.

Reply

Related posts from TheWhir.com
Title Type Date Posted
Tesora's OpenStack DBaaS Supports MongoDB, Cassandra, Redis, and MySQL Web Hosting News 2014-05-23 14:53:11
Uhuru Joins Effort to Bring .NET and SQL Server to OpenShift Web Hosting News 2014-03-06 10:33:15
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
PHP And MySQL Scaling: Preparing A Startup For Growth Blog 2014-04-24 13:27:35


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?