Data access thoughts

#1 Why do anything in XML at all? Database access is normaly comparable to file access and parsing XML isn't the fastest way to do anything. I guess i'm just misisng your goal or some important peice of info.

#2 Hitting a db 100 times/second would not be as efficient as hitting an xml file. I can't imagine anyone could make an argument that going through a db would not be slower than a file system access. Especially when the db is not located on teh same server so you have to go through the network or web to get to it. I'm trying to speed everything up as best possible. How is xml parsing not quick? I was under the impression that it was.

#3 Uh, jonathon, i work full time with this type of stuff, and you probably should rethink your data access strategy here.

xml is a great format, for human readability and passing data to 3rd parties in a relativly neutral way.

However, since you are writing the system for yourself to use, that need for data neutrality goes away

And since your program is reading stuff, the need for human readability goes away

and xml is text.. it needs to get parsed, etc.. which is NOT fast, and you do not want to use xml for high performance applications, such as this.

--------------

since you must know a bit about databases already, you should consider using a local database for the client (mysql and mssql2k5 express are both free) and to prevent multiple database hits, cache the data in your application. (though you could still use xml, just cache it, you shouldnt hit anything 100 times a second, database OR xml)

to send to the server, use a webservice, or the equivlant (i am not sure what the impact of an odbc connection would be with thousands of users, if someone else knows that info, please share)

--------------------------

Actually, now that i read a little bit into your strategy here, it looks like your game only gets an updated packet from the server every 30 min or so. xml would be fine, just cache stuff inside your game, so you are not churning with data access i/o

#4 @jason - That's the point of this thread, to 'rethink' my data access strategy. =P I'm confused by your post. You say one thing and then at the end say my ideas are ok? The point of the xml would be for storage. If I cache the things and the server crashes, power outage, player crashes, etc, I would loose the data in that cache, which we can't do in an MMO. The whole thing with XML is that I could easily persist it back to the DB in the case of a failure, but also keep it on the server for quick access.

I too work in this stuff every day, just not at the caliber that an MMO would be requiring and obviously I'm looking for the best possible solution by posting here. A webservice would be incredibly slower. Any time you go through the web to access data, especially when not using OLE-DB, it is going to start slowing things way down.

I don't see how my strategy wouldn't work and work well for it's inteded use. No one has given examples of why it is a bad idea. Just saying that something is bad doesn't make it bad.

#5 Jonathan-

you seem to just want to use xml. That's fine if you're comfortable with using that format, just realize there ARE much more efficient ways of storing and sending the data.

"The whole thing with XML is that I could easily persist it back to the DB in the case of a failure, but also keep it on the server for quick access."

in effect youd be using the xml file to cache the data. that's a sound strategy. however, what happens if the game server fails? I'm sure the players won't be happy when they finally connect to the game and find the last 30 minutes of player data wasnt even recorded.

also hitting a db 100 times a second probably would be more efficient than hitting an xml file 100 times a second. and if that xml file happened to reside on your local machine your hardrive would probably melt.

#6 I want to find the quickest, best option for speed and reliability. If the game server failed, it wouldn't matter because it's not 'cached' in the terms of being in memory, so when the server came back up it could persist it back to the DB right away and the XML would be gettin updated every time a change occurs so the player wouldn't ever loose anything.

Again, no one is telling me WHY it would be less efficient than hitting a DB. I can't imagine how going across a network or the web to hit a DB Server and polling the DB itself to return the info and then going BACK across the network or web to the game server with the info could be FASTER than hitting a flat file on your file system??

What makes you guys think this would be slower than direct DB access? Do you have benchmarks somewhere or something? Why are you forming this opinion?

#7 Jonathan-

1. I think you misunderstood jason's response. youre database doesn't need to reside on a server. just like the xml file on a local system, you can have a mysql database on your local system. mysql is free which makes it a good choice for a db program. there's no reason for you to HAVE to go across a network or the internet to interface with a db. you can just download mysql, set it up, and have your program interface with it directly. in fact, you wouldnt even need an internet connection to access it because it's on your local system.

2. Databases are designed for storing and retrieving data efficiently. there's a multitude of ways to store the data, you could use a formatted text file, an xml file, a binary file, hell you could even print the player stats onto pieces of paper and put'em into a shoebox. choose whatever method works best for you. the reason databases are more efficient is because data storage and retrieval is their intended use when theyre created. basic file write and read operations aren't created with speed, accuracy, or security in mind. that's like hopping into a volkswagen and wondering why everyone says it's not as fast as a porsche. why do you think people use databases?

#8 I think i understand what the OP is trying to get at.

in a typical server/db setup especially in an MMO environment the db server is not going to reside on the same box as the application server. in a high performance web server farm you dont put the web servers on the DB server hardware.

so i think what he's looking for is a caching solution to go from the app server to the DB server without pegging the network constantly.

unfortunately i dont think there's a way to do what he wants to do. whether you send the data in one packet or que it up into 1000 packets. that data still has to cross the wire eventually to the DB. having a local cache of player stats and whatnot is something that most MMO application servers do already in memory. they only send changes to/from the database. in reality that's not as much data as you might think.

every time you get xp, lose xp, gain a new item, etc... not very often. once every few seconds per character?

i remember reading a brief by the guys that run sony not too long ago. they were talking about the architecture EQ uses. what they use is 2 databases per world, and an app server per zone. one database is optimized for sequential reads and doesn't get updated very often. perfect for things like item data that doesnt change very often. then there is the persistant state data db like what you're talking about. thats the database that holds a players xp, what his inventory contains, etc... but it's still on a separate box.

and like i said getting the data from the app server to the db that holds it still has to cross the wire eventually, it's best to send bit data instead of character data from an XML.

let me explain it to you this way. lets say you wanted to transmit from the app server to the database that the player now has 4,239,001 experience. for your application to update the database with this information directly it would have to send the database what table it wants that info in, and what the data is. the field for what the data is would contain a single byte of data most likely if you were sending data bitwise.

now lets take that same amount of xp that you wanted to send the db in xml format. depending on where you convert the xml to bit data someone is going to have to do some extra work. that number is 7bytes in character format as opposed to the single byte that just sending the bit data from your app to the db would have been. so let's say your app decides it's time to send that data to the db. it has to read the xml file back into memory and convert all that to int data and send it to the db, which takes extra time. or it sends the file to the db server, eating up more network bandwidth and then the database server has to crunch more numbers to convert the xml to bite code. that's ignoring the extra work your app has to do to put the data into character format in the first place.

i hope that explained why using xml is slower than just doing direct writes to a database.

#9

Quote:I think i understand what the OP is trying to get at

This means... what?


Let me try to explain this better as I think confusion is setting in to my exact idea. The database server IS going to be on a different machine, period. No local db will exist at all. So every hit to the db and back will have to go across the network or through the web. For ease sake, I'm going to pretend that for every player that is logged in, they have 1 item that gets updated every 5 seconds and that item will be their XP total. Let's say that 1000 players are currently on that server.

Now, with the direct db structure, every 5 seconds a round-trip happens with the database per player. This translates into 1000 round-trips every 5 seconds. Now, if instead I persist the XP into XML residing on the world server, this would be 5000 file system hits every 5 seconds instead. Then, ONE time every 30 minutes, you make 1000 round trips to the database.

So, that's 1000 round-trips * 12 (5 seconds * 12 = 60 seconds) = 12000 * 30 (60 seconds * 30 = 30 minutes) = 360,000 round-trips to the database in your model versus 1000 in mine. So no, eventually that round trip wont happen except ONE time per 30 minutes, not one time per 5 seconds.

I find it hard to believe without proof that file system access is slower than database access. A project I'm currently working on backs up xml data to our SAN and then sends data to the database letting the UI know that new data has arrived, and our UAT's have proven that the bottlenecks are in the db writing and accessing, NOT in the file system writing/accessing.

If it is slower, then by all means, let me know that my model wouldn't be as efficient as it's less programming on my end. However, don't just shoot down my ideas without justifiable proof that what I'm saying will not in fact improve performance.

thanks,

dish

#10 Even over a network the access time should be very very rapid. If its not then you probably have some issue in your configuration thats stopping hendering you.

"For ease sake, I'm going to pretend that for every player that is logged in, they have 1 item that gets updated every 5 seconds and that item will be their XP total. Let's say that 1000 players are currently on that server."
Instead of that figure that all the player data is currently in memory on the server. When a player logs in then the server does one query (or maybe several) to get all the players data. Then any updates happen in the servers memory. Then as updates to players happen you send those updates to the server. Since they are write updates and you don't care about the response you don't have to wait and the server doesn't slow down for them. This means one update to the server per update in the game world and no file system updates. Also no blocking actions that require your server to sit there and wait for the DB.

BTW the updates could happen in a number of ways. Your server could flag data that needs to be sent to the DB. Then when it gets a free minute it puts a batch of updates together, sends them to the server and unmarks that data. Any changes to the data flag it for another update. In this way you can throttle how much of your servers time is spent saving the data, and how much of a loss is acceptable if the server goes down (loosing marked data).

If your server crashes then you don't loose any data. The server restarts and queries the database for all the information it needs. No data is lost except possibly a the few queries that where executing while the server crashed.

What I don't see is where your XML cache is needed in this situation. Are you saying that you are going to save the player data in memory (in the game engine), on disk (xml), and on a seperate server (DB). I think part of the confusing thing is that you want to update there XP in the DB every 5 seconds. In reality you should only be sending updates when there are in fact updates. This leads me to beleive that I'm still not realy clear on what you are trying to say.

I said "Database access is normaly comparable to file access and parsing XML isn't the fastest way to do anything." When I said that it was based on a few things. Databases are built to store and retrieve data rapidly. XML is designed to store data readably. XML is text based and requires you to open a file and scan through it character by character. Databases use indexs store directly access data. This of course assumes some things about your setup because you weren't very clear originaly. however even if your database server is on a seperate machine it should still be sitting right there next to your server. Preferable on a 1gig link that should rival your text access if not in speed then in codeing effecieny and recoverability. Most databases can be slaved out to backup databases so that even a server crash doesn't cost you any loss in data.

#11 Sorry my post was a bit rambling.

From what i could tell from your strategy, you want your client to talk to the server every 30 min or so.

to do that, xml is fine.

however, once that xml file is on the client, you should parse it once, load all that data up into your program, and dont use that xml again.

you shouldnt hit an xml file 100 times a second, and you shouldnt hit a database 100 times a second either. read the data once, and let it persist in your application. aka "caching"

i say "xml is fine" because it seems like you are more familiear with the technology. however, xml seems to not be the BEST tech for you to use, but honestly, the tech you use doesnt matter as much as your implementation. do it in an optimal way, and you are fine whatever you choose to use.

#12 Little benchmark done in Perl. I tested a local SQLite, XML file and a remote MySQL database. These arn't conclusive of course because the methods you use to store and retrieve data will vary. This also just deals with inserts, not updates.

R is for Read, I is for Insert

Rate    XML R    XML I SQLite I  MySQL R SQLite R  MySQL I
XML R    26.1/s       --     -52%     -79%     -88%     -95%     -99%
XML I    54.5/s     109%       --     -57%     -74%     -90%     -98%
SQLite I  125/s     381%     130%       --     -40%     -77%     -96%
MySQL R   210/s     704%     285%      67%       --     -62%     -93%
SQLite R  557/s    2037%     923%     344%     166%       --     -82%
MySQL I  3175/s   12076%    5729%    2430%    1414%     470%       --

use strict;
use warnings;
use Benchmark qw(:all);

use DBI;
use XML::Simple;

my $xs = new XML::Simple;
my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");

my $mysql = DBI->connect("DBI:mysql:database=test;host=192.168.15.101;", "root","root");
my @data;
$dbh->do("drop table test;");
$dbh->do("create TABLE test ( id, name)");
$mysql->do("TRUNCATE test;");

print "All Connected\n";

sub db_insert {
	$dbh->do("INSERT INTO test VALUES (1 , 'eric')");
};

sub db_read {
    my $data = $dbh->selectall_arrayref("SELECT * FROM test;");
};

sub mysql_insert {
	$mysql->do("INSERT INTO test VALUES (1 , 'eric')");
};

sub mysql_read {
    my $data = $mysql->selectall_arrayref("SELECT * FROM test;");
};


sub xml_insert {
	push @data, {id => 1, name=>'eric'};
	my $xml = $xs->XMLout({ data => \@data } );
	open( FILE, ">", "dbfile.xml");
	print FILE $xml;
	close FILE;
}

sub xml_read {
   my $data = $xs->XMLin("dbfile.xml");
}

cmpthese(200,  {
		"SQLite I" => sub { db_insert() },
		"SQLite R" => sub { db_read() },
		"MySQL I"  => sub { mysql_insert() },
		"MySQL R"  => sub { mysql_read() },
     	        "XML I"    => sub { xml_insert() },
		"XML R"    => sub { xml_read() },
		});;

You can see from that MySQL easily handled 3k plus inserts per second. Thats without any tuning at all and on a mediocre machine, certainly not server material. Any of the database solutions blew the crap out of the XML solution. Though i'll be the first to admit i don't work with XML often so there are probably better and faster ways to do it, but i doubt any are the 6000% better needed to compete with MySQL. Now just imagine that with a power house database (think Oracle on a dual processor machine with a couple gig of ram.)

Anyone just some data to backup what our intuition was telling us about data storage and retrieval.

#13

Quote:


This means... what?

that means precisely what it says. it's not a jab as you seem to have taken it. it just means that i think i understand what you are trying to say. dont be defensive man.


anyway, the last poster did a better job of proving what i was trying to say. the cpu power required to convert to and from the xml could be better used for something else. it DOES take more cpu power to convert to character from int. i know this for a fact, it takes alot more. i built a character math library while learning how to program that creates a character string in memory from an int and then uses that to do the math "human" style. very interesting but abysmally slow. now imagine that if you had to wait for the HD to do its thing at the same time. it'd be even slower.

if you want to cache the data and only transmit to the server every 30 minutes or so, use binary files. in a high traffic MMO style system you cannot afford to waste cpu cycles on conversion between char and int. you just cant.

#14 know this is an old thread but i would also think that using xml to store a multiplayer games sensitive data and only updating it every 30 min would be a huge window for hackers, just give yourself a fiew thousand gold every cycle, max out your stats, whatever

#15 Just don't trust the client. The client receives data for reference, but the game world server handles the actual calculations from the real numbers, and decides what input to accept from the client.

This is some serious thread necromancy, by the way ;)

#16

Quote:1. Player logs in
2. Game server requests player info from DB server.
3. DB Server sends Game server player info.
4. Game server persists player info to xml
5. Every time player info needs updating, Game server does it in the xml.
6. Every 30 minutes, Game server persists player info xml back to DB server.
7. Player logs out.
8. Game server persists player info xml back to DB server.

Make it so the XML file is deleted after player is logged out. That way, there is less junk on your server.

Am I right?

#17 Just read through this... an interesting and informative thread! And yes, I'm raising it from the dead yet again! =)

An important point may have been missed in previous comments... The OP said "4. Game server persists player info to XML" so all concerns about client-side hacking are completely moot; the cache is on the server -- the world server, separate from the database server -- not the client.

I do agree that XML is not the best solution; as mentioned before, a binary format (which closely matches the layout of the database tables, I might add) would be vastly superior to XML -- XML parsing is just going to waste time. Especially if you consider how much data might potentially need to be cached: How many players are there concurrently on the server? How dynamic is the world? I.e. how much world content will be changing and needing to be cached and eventually updated to the database?

XML is useful on the client, however, e.g. for things that are loaded only once or are specific to that client. For example, World of Warcraft uses XML to define the layout of its GUI.