#### Archived

This topic is now archived and is closed to further replies.

# Database Question

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

## Recommended Posts

I''m developing an BBG (browser based game) that will eventually store the data of thousands of users inside a database. I''ve already started on the web interface and much of the PHP code (i''m useing mysql for the database) Ok, great. The problem (at least, I think it will be a problem... I can''t imagine the brute for approach working)is that I need to change the value of several colums over thousands of rows ever hour (at least, thats the only way I know to do it at the moment). Specifically, each user will have a certain gold income/hour, ore income/hour etc and I need to change the value inside the "bank". If I just recalculated totals every time the player logged in (which seems like the logical approach), then enemy attackers wouldn''t benefit from the actual surplus of gold in the bank. I also need to be able to keep track of when new gold mines, etc are built, and then add their effects to the income rate at the appropriate time. Any suggestions on how to do this efficiently so I don''t kill my server?

##### Share on other sites
Just calculate the balance when you need to. If you''re storing the necessary dates (last updated, etc.) this should be easy.

##### Share on other sites
a few thousand updates over an hour isn''t all that big a deal, as long as it isn''t all at once.

##### Share on other sites
That''s going to be tough to do in PHP unless you keep a daemon running on the server all the time. The daemon could be PHP, perl, Python, C++, whatever, but it will need to sit in the background and check each player''s account to see which one''s need to be updated every so often.

Each player will need a last_updated datetime column. Then you''ll just do a select on that column to determine who needs to be checked.

$five_min_ago = mktime ( date(H),date(i)-5,date(s),date(m),date(d),date(y) );$sql = "SELECT * FROM players WHERE last_updated >= ''\$five_min_ago''";

As long as everyone didn''t login at the exact same time, their changes won''t get written back at the same time so it should load balance itself.

##### Share on other sites
If I was doing something like that I would also reserve a constant for the maximum amount of changes you could do at any given time. Just so you dont overload your server. Then as your community grows, and you get another server, you could change the max.

##### Share on other sites
Ok, I don''t want to run a daemon if I don''t have to.... and updating every account all at once isn''t pratical. So, I guess I''m sorta force to updated via the ''as needed'' guideline, but I still have a problem (I think). Let me try to describe my senerio in full detail.

I amass an army and send them off to attack player X. However, my forces won''t arrive at player X''s providence for another 12 hours, so, in the meantime, I can see 2 ''difficult to deal with'' senerios takeing place.

Senerio 1. I log back in after 12 hours while player X hasn''t logged on in a while. Here, I have to force an ''update'' of player X''s account so that, if I win the battle, I can obtain the maximum amount of booty. I also need to have my login ''trigger'' the battle simulation as well as change player X''s stats such that all the lands/buildings, etc he lost have a ''time of loss'' associated with it. That way, he doesn''t gain gold form mines he lost in battle 5 hours ago.

Senerio 2. Player X logs on after the 12 hours, and before I log back on. Here, I almost need the login of player X to first update his true income, then initiate the battle, then go back to updateing income post battle (which will change significantly if he does lose).

And I feel that there may be some other senerios in here that I''m missing, but I can''t think of any off the top of my head. Also, it may be important to note that I''m useing the old ''hours act as turns'' format. Every hour, you get another ''turn'', and everyone can use their turn at once.

Thanks in advance for any help.

##### Share on other sites
quote:
Original post by Cygnus_X
Ok, I don''t want to run a daemon if I don''t have to.... and updating every account all at once isn''t pratical. So, I guess I''m sorta force to updated via the ''as needed'' guideline, but I still have a problem (I think). Let me try to describe my senerio in full detail.

If you are running a server basd game then the daemon is the app running constantly on the server, updating things like game logic. There is no other easy solution (Peer to Peer games usually set one user as the server for this reason). Updating a few thousand rows once each hour, even at the same time is no big deal for a database app like MySQL. It would take no more than a fraction of a second even on a old machine (p100 etc).

These SQL databases are designed for multiple tables containing hundreds of thousands of rows with calculated (cached) querys and stored procs. Don''t worry about speed until you have it working.

Jay

##### Share on other sites
Thanks! I''ll try the ''brute force'' tatic first and hopefully it won''t be a big deal. I was just worried that I''d have to go back and redesign everything once I developed a large user base.... and I''d rather just do it right the first time.

But before I dive in... does this take into account battles? I.e, if i update on the hour every hour, can I also run a script that simulates the battles that need to be simulated and still not overload the system (and if not, how would you approach it)? I don''t exactally have the battle code completely written, so I can''t tell you how involved it is. But, the general idea is to determine an attack order (each player might have 12 units attacking at a time), run a script that tells the attacking unit who he/she can/should attack, generate random numbers for strike and dodge, if strike > dodge, inflict damage (which is determined on weapon and armour), etc, etc... and keep on going until everyone is either dead, or I decide to cut the battle short (via ingrained code) because the teams are deadlocked (maybe after 100 loops of code). Now, prehaps i''m just inexperienced, but my intuition says that all of this is going to require quite a few ghz of processing power. But, I have no feel for anything yet... so I don''t really know. Again, thanks in advance for any help.

##### Share on other sites
If you can place the game logic inside programs running on seperate servers (split the load) then you can use database clustering (if necc) to spread the data load. How you do it is up to you, I''m told that commerical mmrpg''s tend to limit users per server so that each server has only a certain amount of processing to do. Again I''d just write it and worry about speed later.

I''ve been playing Activision''s Total War recently, even with armies of 1000 vs 1000 the biggest hit is always graphics, otherwise my archaic P100 would be running those as well. Bear in mind that players will take a lot of time in between turns looking at positions/next moves etc, you could run a time difference between sets of players that takes advantage of this.

Jay

##### Share on other sites
this sounds like a classic problem for an n-tier solution. The general consensus is if you are creating anytype of application where clients need to access data in a database you need to split up the buisness and presentation logic. In your case, the presentation logic would be the browser code for the game, and the buisness logic would be anything associated with retreaving data.

the way this works is all clients asking for data make a request to a single service that handles all the data collection. The service would interact with the database, not each client. this makes a few things easier.

there are less concurency problems with the data because only one source is accessing the data. i.e. if you want to update every hour, you have one connection that sends all the updates, instead of every client initiating a call to the database.

the logic for reading and writing to the database is seperated, so its easier to change or tweak. For instance, you could create a cache for all clients online, where thier data resides in a list or something that memory, and thier "state" is saved in a database when they log off.

scalibility is easy to implement because everything is seperated. lets say your users grow to the point where a single server cant act as a webserver, execute your program, and be a db, you can distribute the different tasks to multiple machines. you could have a dedicated webserver on the front end and a dedicated data server on the back end, and connect them with a server running the buisness logic communicating with the two. you could eve, as jason said, use clustoring and each component spread out accross multiple machines.

also, performance is better if the numbercrunching happens on compiled code as oppesed to scripted.

1. 1
2. 2
Rutin
17
3. 3
4. 4
5. 5

• 9
• 9
• 14
• 12
• 10
• ### Forum Statistics

• Total Topics
633270
• Total Posts
3011159
• ### Who's Online (See full list)

There are no registered users currently online

×