I am working on a multiplayer RPG with a persistent world (UO, EQ), and am going to develop the server program on linux. I was wondering, what do you guys think about using a MySQL database to store the player accounts and information? How would CPU/RAM/HD usage and performance compare to saving the accounts directly to disk? Shoot, why not store all the level, object, and monster information in a set of SQL databases? Would it be better than using a normal resource file system? Thanks, Saai

Hmm, i''ve heard this idea before, but i''m not completely sure what you''d gain by using an SQL backend.. Much of the relational stuff that makes SQL databases so great is almost useless in this realm. However, i''ll examine the benefits that come to my mind.

First, a disclaimer: i''m not a real database programmer. I''ve had brief encounters with MySQL databases, but i''m by far no expert. You have been warned.

Ok, benefits:

• Easy to use interface: compared to mucking with files directly, a database backend would be pretty easy to maintain and modify when needed.
  
• Network transparent: with a database backend, it''s easy enough to begin with a local database, and as need grows, migrate to a seperate server for the data. If you wrote your own direct filekeeping routines, this would require a lot more work.
  
• Advanced database functions: in the rare cases where you DO want all that advanced database functionality, you have it. I can see this useful in doing statistical analysis for gameplay study and for detecting large scale cheats, but i think the benefit is limited.
  
• Multithreading: on very SMP''d machines, having the data layer multithreading might be an advantage depending on how much processor time the data layer takes up, and if it bursts often (ie doing a backup at certain times, and keeping it all in ram/swap the rest of the time would cause bursts of activity) and on the database you use, multithreading might be advantagous, and you get that without actually writing it yourself with an SQL database.
  

Ok, now the drawbacks:

• Overhead: on smaller servers, the overhead of running a database might actually be a problem. I don''t know your target server, so this may or may not be a concern. Also, however, remember all that SQL stuff that you won''t be using usually is still in there. If you wrote your own really tight methods, you could probably get them smaller and or faster than any SQL database.
  
• Cost: for comercial use, most databases cost money-- some more than others. Again, whether this is a concern for you or not, i don''t know.
  

Well, that''s what i can think of. Everyone''s welcome to add more (or only slightly less welcome to discredit these ones )

What i would do, personally, is write an interface for your data layer, and i''d start with just writing my own layer that uses normal files. You''ll know your needs better later, but i''d lay simple plans (or even a simple implementation) for both systems now.

-ben.c

IF you are going to use an SQL server for your game it should be MySQL, since MySQL doesn''t support advanced DBMS features such as nested selects and atomicity it is a lot faster than any other DBMS I know of, but since you won''t be needing those features much (well, I could see a use for atomicity, sort of dupechecking and stuff) it would be perfect for your game, I''m not sure if MySQL is totally free when it is used in commercial programs so you''ll have to look that up.



Gyzmo
=======================
Meddle not in the affairs of dragons for you are crunchy and go well with toast.