Database problem

Quote:

Original post by Anonymous Poster use mySQL, it's free, and you have all the docs on their site (www.mysql.com)

mySQL is good for most things, if you need more raw DB power though, use postgres. I believe there are ports for both mySQL and Postgres for Win32 and Linux as well.

How much data will be stored?

If it's less then 2GB, MSDE 2000 (Microsoft SQL Server Desktop Edition) would be a good choice. Once you agree to the licence (which basically states that you can freely distribute it with your application, so long as you do not imply any relationship between Microsoft and yourself and that you won't use their logos/trademarks in any of your advertising) you have a completely free copy of SQL Server (each database on it is limited to 2GB) whose installer is designed to be integrated with the installer of another app (you create an ini file for the installer to follow, there are no option screens).

If it's more then 2GB, PostgreSQL would be the next good free choice. It's extremely fast and uses an equally powerful SQL implimentation. It's only real con is that it is very UNIX centric - getting it setup in the first place can be a challenge. However its BSD licence means you can easily integrate its installer with your own, so that you can have some sort of pre-configured out of the box setup.

MySQL I would not recommened. The first point is because of licensing issues - for commercial use, unless you are willing to open source your application under the GPL licence (which would mean your application would be under GPL forever) the licence will start at about $600 US. While MySQLs extremely limited SQL subset and serious speed issues doing anything outside of basic SELECTs (you'll need to find a way to batch your updates to avoid MySQLs problem in doing INSERTs at a reasonable speed) can be worked around, its essentially non-existent data integrity make it unacceptable for a MMO game of any kind (unless you like the idea of users getting their characters reversed by several days on a frequent basis)

Quote:

Original post by Michalson its essentially non-existent data integrity make it unacceptable for a MMO game of any kind (unless you like the idea of users getting their characters reversed by several days on a frequent basis)

i know for a fact at least one commercial MMORPG uses MySQL (Dark Age of Camelot). im also pretty sure Eternal Lands uses MySQL as well. also, another MMORPG that uses it is called Mystera Legends. i also use it myself in my persistant online RPG and have had no problems so far. its quick and easy to setup and has proved to be fast for me.

how true is the statement about inserts being slow? do you have personal experiance or just going on what you have heard? im curious myself since im using it and i could probably batch inserts if its nessasary. i know that Mystera Legends saves character back to the MySQL database very frequently (like every 10 seconds for each characters frequently changing data e.g. health and stuff), and i'm pretty sure he does not batch any data. however that is an update statement i guess, not an insert. still, it is proven to be fast. all the people i have talked to have said that PostgreSQL is slower (although it offers more features) then MySQL.

Quote:

Original post by graveyard filla i know for a fact at least one commercial MMORPG uses MySQL (Dark Age of Camelot).

Thats not true really. They only use it for accounting purposes and the likes. The only part that it is used for the game directly is for the consignment merchant, also believe they have some tools that work with the flat files through mysql. For the server itself they use a proprietary flat file format. You can dig up some of the information looking up Scott Jennings, as he talks about it some.

i see.. sorry about that, i had heard from a few people that DAOC had used it and i was assuming they used it in all of their game and not just the (im assuming) non real time stuff.

"The entire budget for the game was $4.2 million dollars, and Oracle wanted $995,000 of that for licensing fees.

Each server is comprised of a Linux cluster of 6 systems. The sixth was added for ToA. There is no particular correlation between one box and a particular purpose; the entire game is spread across all servers. They were originally Dual-P3's, but many of them have been upgraded to Dual-P4's. It doesn't take much to run these servers, apparently. I remember Matt mentioning that the hard cap on servers is 3500 simultaneously.

At least one server was, for about six months, running on a desktop box, a P4 1.6Ghz systems, Matt seemed to recall. They called the servers (around four of them), the "gimp servers". The mysterious messages you heard last year about them changing hardware on some of the servers was them removing them from desktops and putting them on servers finally."


Taken from the following site:

http://www.circleofpowerguild.com/forum/reply.php?topic=2776&forum=2&post=22216&quote=1

I'd love to know how they get 3500 people on 6 servers. I'm pretty shocked at those figures.

Another issue is how do you do transactions with a flat file system, so that if player A give player B an item, both characters are either updated at the same time or the transaction is rolled back in the event of a server crashing. They're pretty necessary to prevent dupe hacks.

I've never done a flat file type of DB for storing information, but if its a good way to go, I'd be willing to look into it.

-=[ Megahertz ]=-


-=[ Megahertz ]=-

Thanks for the help.

Quote:

I'd love to know how they get 3500 people on 6 servers.

For an RPG with very little physics like DAoC, that sounds about right.


Regarding databases, our commercially operating virtual world uses MySQL, and it's fine. We store all persistent object/avatar attributes in it, as well as things like catalog, auctions, clubs, etc.

Another database I would look into is SQLite. It's fully open, and it's super tiny in code size, so you can easily understand and extend it yourself. It doesn't do much error checking, but it's really quite fast, and embeds using a very simple API into any C/C++ program you want.

"Regarding databases, our commercially operating virtual world uses MySQL, and it's fine."

That's currently what I'm developing with, so perhaps I'll just stay with that.

Another question I have regarding databases and the design of the server is if its necessary to write the server in such a matter that you can have multiple queries to the DB going on at any one time. I know your a big proponent of doing things with out using multiple threads but doing so limits you to only being able to do one query at a time. (Tho I think the DB is gonna limit this anyway if your're working on the same table/row)

I guess what I'm concerned with is if a player update requires running a sluggish query that it doesnt hold up the rest of the server while it completes.

-=[ Megahertz ]=-

You absolutely should not block your game loop at all waiting for the database. That'd cause major performance problems.

You may get away with putting the MySQL code in a thread of its own, and queue queries for this thread, and poll completed query results in the main loop. You will note that I suggest using threads in the case where you need to turn a synchronous API into asynchronous behavior -- this is one such example.

What we did, was write our own MySQL client library that talks the MySQL wire protocol over TCP -- in fact, our database instances often run on other machines than the actual servers. Because our networking is already asynchronous, this makes it easy to provide asynchronicity in our database interface, too.

hplus,

I've been giving this some thought as it's something I'm going to need to implement in my own project.

There's always more than one way of doing something and this seems to be no exception. I'd like to keep things simple for now since this is my first go round with something like this.

Right now my architechture follows the Everquest way of doing things. Theres login servers, world servers, and zone servers. Right now there's only one login server and each world server connects and registers itself with it. Likewise the zone servers for a given world all connect to the appropriate world server.

Each server maintains a connection to the database server accesssing different DB's. The login server connects to the accounts DB while the world and zone servers connect to a DB that is exclusive to the world the servers maintain. Given just a few worlds and zones the amount of connections remains pretty low, however once you start scaling up to a commercial game size the number of connections increases quite a bit.

I'm wondering if having 200-300 connections to a database open at a time is a "bad thing" and what would be a better way of going about it if it is. At a prior company I worked for we had several OCC machines that would open a set number of connections to the database and then queries were sent to these OCC machines where they were queue'd and then executed as time allowed. Is something like this necessary?

The other side of the coin is the servers themselves actually queueing queries to be executed (OCC's or not) in such a manner that they dont stall the main server loop but I'm guessing that they will stall the actual processing of the client since the client will need to block on waiting for the results of that query.

It wouldnt be hard to create a DBQueryQueue class running in a separate thread that queries could be pushed on and popped off in turn to be executed. My main issue is how to keep the main loop going but be able to come back to a client, see that a query has completed and know what the query was for.

Clients on the server are derived from a base entity class and the main processing loop just runs through the entity list processing them one at a time. In the process method for the client, network events are popped of an incomming queue and dealt with. Some of these will require hitting the DB and updating some record, say if a player deleted an item or something. Right now the loop would just wait for the query to be completed and then continues on, but as you said this is a bad thing.

Obviously what needs to happen is the item deletion event is popped off the queue, a DB Query is created and pushed onto a pending queue and the loop continues on. Later on once I hit the client again, the completed query is popped off a completed queue, but how do I know what the query was for in case I need to take action based off the result of the query?

I do realize that alot of queries probablly wont even touch the DB server and the information will already be stored locally on the zone server in memory, but a few will require DB hits to ensure an exploit free game (i.e. transactions for trades).

As I sit here and think about this some more, I see that alot of it is going to depend specifically on what events my game has to deal with and while I know that theres no magic answer that works for everbodys project in all cases, any insight and/or idea would be apprecated.

-=[ Megahertz ]=-

Quote:

Original post by Megahertz I've been giving this some thought as it's something I'm going to need to implement in my own project.

Lots of questions in one message. I'll answer some, and I'm sure others will chime in.

Quote:

Given just a few worlds and zones the amount of connections remains pretty low, however once you start scaling up to a commercial game size the number of connections increases quite a bit.

"World" servers would also be known as "shard" servers, and could be considered to be clustered with the "zone" servers. You would need one database instance per "world," but you don't really need a unified database instance for all of them, except for login data.

Thus, you have a single database that has all players in it, with login name, password, account status, and some statistics. This is what the login server uses. Then, per shard (world instance), you have a database which contains characters, items, possibly world layout and mobs, etc. Queries/joins between the world instance and master login instance just aren't necessary, except for possibly some minor statistics gathering, which can probably take a back-door route, maybe even offline.

Quote:

I'm wondering if having 200-300 connections to a database open at a time is a "bad thing" and what would be a better way of going about it if it is. At a

It can be done, but unless you have 200 zones in your game, I don't see why you would have to. Each zone server, plus the world server, could open a single connection to the world instance database, and that would be it.

If you want to scale even better, then you make the world server be the arbitrator, cache, and application server for the zone servers, and have the actual database connection open; the zone servers would just speak an application-level protocol (XML or similar) to the world server, which would turn around and run the query.

Quote:

The other side of the coin is the servers themselves actually queueing queries to be executed (OCC's or not) in such a manner that they dont stall the main server loop but I'm guessing that they will stall the actual processing of the client since the client will need to block on waiting for the results of that query.

You should design everything to be asynchronous. Many games do local updates on the zone servers, and then checkpoint the actual state back to the database every so often, if changed. Typically, once every five minutes, plus when you zone to difference zones. That way, the impact on the database is low (3000 players over five minutes is about 10 updates a second) and you can update state synchronously on the zone servers.

If you want better transactional semantics (for trade, say), you should make the UI such that you initiate the operation, but don't see the actual results until you get confirmation from the database that whatever you were doing, has gone through.

Quote:

My main issue is how to keep the main loop going but be able to come back to a client, see that a query has completed and know what the query was for.

Either build the client as a state machine, where it starts a request, and keeps the request around, checking status once each game loop until it completes, or use micro-threads (fibers). For interactive simulation stuff, I find the state machine to work better. For semi-interactive operations, like you'd get on a web server, I find fibers more convenient.

Quote:

Later on once I hit the client again, the completed query is popped off a completed queue, but how do I know what the query was for in case I need to take action based off the result of the query?

Each request you can queue should have some kind of identity. You can use that in the client. Typically, you'll create trampoline worker classes, one for each kind of request you might want to make off the database.