Hello.
I have had this idea for a while, and I'm sure I'm not the only one who ever came up with it, so I want to know people's opinions on it.
Say you have an online game, or anything similar - where large amounts of data have to be updated every so often. Say, 10,000 players' db fields for money have to be increased by an amount, stored somewhere else.
Surely enough, this could be done in 10,000 queries. Now say there are 30 different things which need to be updated for each player, and some of those things could branch out into more queries, and you end up with 500,000 queries.
Running all at ones, or anywhere near each other this would put a LOT of strain on the CPU.
So instead of doing it that way, have a function, a class infact which updates certain data, right before it is accessed.
So anywhere in your program where you have to use that data, in any way, you first run the appropriate function to update it.
There are obvious pros and cons to this, main onces being:
Pros:
- Won't kill the server if a lot of queries need to be carried out all at once.
Cons
- Slows down execution of individual scripts used by users
Then there's the problem of frequency of updates. While some data may be updated frequently enough this way, other data which would be updated on a daily basis otherwise, would be untouched for days or longer.
There are obvious solutions, but none very efficient.
Some which I can immediately think of are:
- Run a cron daily to update all the data which already hasn't been updated.
- Have a date of last update timestamp to be used when updating, to make sure the the update caters in the length between now and last update.
Both of these methods in turn have pros and cons relating to cpu usage, and which one could be utilised better would depend on the situation.
- My questions are, has anyone tried this on a large scale project?
- How good did it work?
- What else can you suggest?
Thanks.
Updating DB only when required as opposed to running Crons?
Moderator: General Moderators
-
Starcraftmazter
- Forum Commoner
- Posts: 45
- Joined: Mon Apr 24, 2006 11:36 pm
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
Since I also develop online games in PHP...
I suggest you be very careful when designing your game. Your example of updating player money by a fixed amount can be done with a single SQL query
.
This is something you should keep in mind. PHP games have a habit of having dozens of folk making new requests every couple of seconds - it really pushes you into optimising your use of SQL since it's often the main bottleneck in executing PHP. So take it from that perspective - get experienced with using SQL before making assumptions. In particular, read up on using Transactions (since a half completed update is a really bad thing), Caching (see MySQL query caching and the slow queries log), Engines (try to use InnoDB over MyISAM on MySQL4), Prepared Statements (should the DBMS compile all those repetitive queries one after the other? NO). There's more of course, but the above are typical good starting points.
Your second point is the time honoured PHP game debate over using scheduled updates via cron or dynamic updates based on player requests. Both can be used actually, but I'd start with cron - since it's generally easier to implement and most linux or bsd hosts support it. Schedules are not the problem unless you're on a shared host who really dislikes regular schedules, it's typically the use of SQL and the design of your database tables that wind up causing the real problem. Which, of course, drags us back to my earlier point.
On the design front - try to keep your data segregated between what requires regular updates and what requires daily updates. Then plan your update script accordingly. You should never be in a situation where you are attempting to update already current data - if you are, I suggest you revisit your database design and your update logic. The danger with the second approach is that you end up with out of sync data - not all players are updated at the same time. The other danger is that updating only when required can lead to longer page loads for the player - something they'll not like depending on how long that empty browser tab stares back at them.
Fire away with any specific scenarios...
I suggest you be very careful when designing your game. Your example of updating player money by a fixed amount can be done with a single SQL query
Code: Select all
UPDATE players SET money = money + 1000;Your second point is the time honoured PHP game debate over using scheduled updates via cron or dynamic updates based on player requests. Both can be used actually, but I'd start with cron - since it's generally easier to implement and most linux or bsd hosts support it. Schedules are not the problem unless you're on a shared host who really dislikes regular schedules, it's typically the use of SQL and the design of your database tables that wind up causing the real problem. Which, of course, drags us back to my earlier point.
On the design front - try to keep your data segregated between what requires regular updates and what requires daily updates. Then plan your update script accordingly. You should never be in a situation where you are attempting to update already current data - if you are, I suggest you revisit your database design and your update logic. The danger with the second approach is that you end up with out of sync data - not all players are updated at the same time. The other danger is that updating only when required can lead to longer page loads for the player - something they'll not like depending on how long that empty browser tab stares back at them.
Fire away with any specific scenarios...
-
Starcraftmazter
- Forum Commoner
- Posts: 45
- Joined: Mon Apr 24, 2006 11:36 pm
Yer, I mentioned that.
Well, I'm not making any games right now, I think just to gasp this concept fully, if and when I decide to make one.
I know of transactions and InnoDB tables, and I have my own server....well VPS.
Now, the thing is, the quires would have to be a tad more complex, depending on how complex the game is.
I would hope that there would always be a field somewhere in the database, for each user, for each thing which needs to be updated, so that each update can be done in a single query.
These fields may in turn need to be updated before the update occurs.
Something like......
Which prompts me to ask.
If something needs to be updated by a corresponding value in another table, can the two tables be cycled through simultaneously, or would this need to be done individually?
If that makes sense.
Well, I'm not making any games right now, I think just to gasp this concept fully, if and when I decide to make one.
I know of transactions and InnoDB tables, and I have my own server....well VPS.
Now, the thing is, the quires would have to be a tad more complex, depending on how complex the game is.
I would hope that there would always be a field somewhere in the database, for each user, for each thing which needs to be updated, so that each update can be done in a single query.
These fields may in turn need to be updated before the update occurs.
Something like......
Code: Select all
UPDATE player_resource_update SET resource1_updater = some_table.factor1*multiplier1, etc
Code: Select all
UPDATE player_resources SET resource1 = resource1+player_resource_update.resource1_updater;
If something needs to be updated by a corresponding value in another table, can the two tables be cycled through simultaneously, or would this need to be done individually?
If that makes sense.
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
I couldn't say for sure without more detail, but if the update has a dependency - i.e. updates must follow a sequence, then cycling through them individually one after the other seems likely unless both entries follow a 1-to-1 relationship (then you might examine the alternative). Unfortunately it is hard to avoid this in many cases.
My own database design contains references to a 500x500 grid (250,000 Sectors) many of which I intend updating on an hourly basis. Since they all have varying characteristics, they need to be gone through one by one - I can't afford to have a fixed update Integer amount since it would play havoc with the game economy and resource scarcity. That's painful, to say the least...250,000 queries is hard to imagine. I expect I'll end up re-designing how sector's are handled in the database design to avoid that - even if boiled down to specific criteria, I suspect the number will remain significant.
My advice would be select the method that makes most sense to your app's logic - let optimisation wait for a while until you have a real feel for how much benefit it would bring to the table.
My own database design contains references to a 500x500 grid (250,000 Sectors) many of which I intend updating on an hourly basis. Since they all have varying characteristics, they need to be gone through one by one - I can't afford to have a fixed update Integer amount since it would play havoc with the game economy and resource scarcity. That's painful, to say the least...250,000 queries is hard to imagine. I expect I'll end up re-designing how sector's are handled in the database design to avoid that - even if boiled down to specific criteria, I suspect the number will remain significant.
My advice would be select the method that makes most sense to your app's logic - let optimisation wait for a while until you have a real feel for how much benefit it would bring to the table.
-
Starcraftmazter
- Forum Commoner
- Posts: 45
- Joined: Mon Apr 24, 2006 11:36 pm