Sign in to follow this  

Game Database

This topic is 3935 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I've gotten my game to the size where I need a database, or at lease something like it to keep track of all data in play. I've searched the forum with no luck, although I've read tons of great insiteful information on all sorts of things. Therefore, what database would be best for tracking at max 3,000 players info which are all connected to the same server? I'm hoping to at least be able to process and replay to each player request per second. Is there a standard database which W.O.W, E.Q, and other big names use? [Edited by - BladeStone on March 6, 2007 12:15:20 PM]

Share this post


Link to post
Share on other sites
I've always used MySQL for 'heavy duty' database work as it's easy to interface with from Windows/C++ but, forgive me if I'm mistaken, I get the impression that you're requiring a database for real-time access?

MySQL runs as a seperate process connected to using TCP/IP (I'm pretty sure of this anyway) and although there is a cache, the data is stored on disk and I wouldn't think it would be as efficient as some data accessed internally from the server.

I would suggest looking into some data structures that you can employ to maximize access efficiency to your data:

I currently like hash maps, where you maintain a number of linked lists (typically a prime number count) and you create a hash of the 'key field' modulo the list count to hopefully distribute the data stored into shorter lists which will mean less time iterating through the linked lists looking for your data.

There are definitely more efficient structures than this... for example red-black trees, but you'll have to use Google because you'll find better explanations elsewhere than I can give!


Anyway I hope this helps, apologies if I've completely misunderstood what you were asking for, I don't known anything about the back-end of WoW or Everquest I'm afraid.

Share this post


Link to post
Share on other sites
In my experience, MySQL is ok for small projects, but is hard to keep stable for larger projects. If you need a separate database server, try PostgreSQL.

On the other hand, if your server is a single process and doesn't need an external database server, SQLite is a very handy database-in-a-library that writes out to local files.

Share this post


Link to post
Share on other sites
Quote:
Original post by justinian
In my experience, MySQL is ok for small projects, but is hard to keep stable for larger projects. If you need a separate database server, try PostgreSQL.

Hi, can you elaborate on what you mean by 'stable'? To me MySQL and PostgreSQL (which I've merely 'heard of') are the same - established, mature, etc. I don't want to be ignorant of issues that might sting me down the line!

Quote:
Original post by justinian
On the other hand, if your server is a single process and doesn't need an external database server, SQLite is a very handy database-in-a-library that writes out to local files.

Thanks for recommending this, I hadn't heard of it before and I like the idea of a nice little compact library you can throw around when you need a quick DB.

Share this post


Link to post
Share on other sites
I was thinking about MySQL, but from the sounds of it SQLite is a better choice if I can figure out what is considered 'small' when it comes to database use.

I've worked with databases but this game will push the envelope of what I've used a database for, so .... is 3000 calls to a database per second 'small' or how do I determine the database needs?

Share this post


Link to post
Share on other sites
Try not to access the database so much. I mean if your game is running you're storing a lot of the data in RAM and such. Just update when important things happen or when you need to get player info. Copy the data to RAM then use it, then when needed copy it back to the database to "update".

Share this post


Link to post
Share on other sites
Quote:
Original post by Hidden Asbestos
Hi, can you elaborate on what you mean by 'stable'? To me MySQL and PostgreSQL (which I've merely 'heard of') are the same - established, mature, etc. I don't want to be ignorant of issues that might sting me down the line!


When under heavy load, I've seen MySQL start dropping connections, or even start sending bad packets to the client. Obviously, there are a lot of large websites that put MySQL through some heavy load, so it seems this is more of an issue for longer-lived connections rather than short-lived connections like a web-app would use. I also know it's possible to use MySQL under heavy load, but it seems that it takes more configuration and active administration/maintenance.

My experience with PostgreSQL has shown it to be more robust and stable under load - it feels much more like a mature commercial database like Oracle.

Quote:
Original post by BladeStone
I've worked with databases but this game will push the envelope of what I've used a database for, so .... is 3000 calls to a database per second 'small' or how do I determine the database needs?


I've never pushed SQLite that hard, so I couldn't say. That doesn't sound 'small' to me, though. ;) Unless that number represents many thousands of users doing lots of database-intensive like logging in and out a lot, you might want to re-design the way you access the database.

Share this post


Link to post
Share on other sites
Quote:
Original post by Sirisian Try not to access the database so much. I mean if your game is running you're storing a lot of the data in RAM and such. Just update when important things happen or when you need to get player info. Copy the data to RAM then use it, then when needed copy it back to the database to "update".


I wasn't even thinking about that, that's so true. Therefore, it would be,... say,... maybe, 10-1000 database requests per minute. Depending on loads on the server it's self and update times for character back ups, loging on, and loging off. Maybe an average of about 600 players on with a max of 3000 players over a peek time of about 3.5 hours ... a few months after going live. (which still might be two or three years out.)

I guess the question to ask now is, where do I turn to learn about ram and character ram volume. Any advice on where to look?

Thank you in advance.

Share this post


Link to post
Share on other sites
Quote:
When under heavy load, I've seen MySQL start dropping connections, or even start sending bad packets to the client. Obviously, there are a lot of large websites that put MySQL through some heavy load, so it seems this is more of an issue for longer-lived connections rather than short-lived connections like a web-app would use. I also know it's possible to use MySQL under heavy load, but it seems that it takes more configuration and active administration/maintenance.

My experience with PostgreSQL has shown it to be more robust and stable under load - it feels much more like a mature commercial database like Oracle.

Ah, right - thanks for the information. I've only ever used it as a game server back-end so only one connection is being used. I guess it would be worth switching over if the SQL server was more client facing...

Share this post


Link to post
Share on other sites
Quote:
Original post by BladeStone
I guess the question to ask now is, where do I turn to learn about ram and character ram volume. Any advice on where to look?


Look at your code. The amount of RAM your characters take up is entirely down to how you coded it. All you have to do is ensure that you don't use the database when you can just use the data already in memory.

Share this post


Link to post
Share on other sites
Quote:
Original post by BladeStone
Is there a standard database which W.O.W, E.Q, and other big names use?


These games use different aproach.

Database is used for persistence only.

Characters are loaded into memory, and then the game servers themself are responsible for transferring this data across the cluster.

Usually, the server on which such object is physically located will be responsible for manipulating it (logic), and all servers which need to know about the object receive updates about it (ghost copies).

Periodically changed state is written back into SQL database.

3000 calls/sec as sustained load is a lot, especially for only 3000 users. There is almost never the need to persist the state of each object on every change. There are some exceptions of changes that need to be immediately persisted, or at least in sequence.

Since the properties of an object will not change in uniform manner (health will be modified every second, level only once several hours) if makes sense to cache the changes to each object for several minutes, then write only those. This way you not only cut down on data, but also don't update for idle/unchanged objects.

Share this post


Link to post
Share on other sites
Another thing to consider is database clustering.

Your dynamic data, such as other active players, there position, gear, etc. would be stored in RAM by the server, so really most of your database hits are reads for static data. Your writing less data then you are reading.

With a cluster (I can only speak of MySQL) you have a master server, and multiple slaves. You write changes to the master and they propigate to the slaves. So when you save a character at logout, you send that to the master, but when you read data you pull it from any one of the slaves. Now you are spreading out your reads (Majority of queries) to multiple servers.

I don't know what the latency is of writing data to the master before it shows up on the slaves without looking it up. Character data, or specifically the more dynamic data you may want to query from the master, but static data such as item stats, etc. grab from a slave.

This may be somewhat larger scale than your working with, but I thought i'd toss it out there. If your whole system runs as a single process or a single server than this is moot.

--Zims

Share this post


Link to post
Share on other sites
Quote:
Original post by Antheus
These games use different aproach.

Database is used for persistence only.


Pretty much on-target. Another thing to think about is that there are some things which are pretty volatile and you don't really care if you lose some incremental amount of change (say, hp).

On the other hand, things like item creation, 'quest' completion, character advancement, etc., you want to save as quickly as possible since players get annoyed if they lose advancement.

Share this post


Link to post
Share on other sites

This topic is 3935 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this