Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    829
    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)

  2. #2
    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. #3
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,681
    My suggestion is:
    Store user accounts in mysql (with INDEXES!)
    Cache info into memory with memcached.

  4. #4
    Join Date
    Feb 2008
    Posts
    829
    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. #5
    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. #6
    atariko Guest
    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. #7
    Join Date
    Feb 2008
    Posts
    829
    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. #8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •