Updating DB only when required as opposed to running Crons?
Posted: Thu Oct 19, 2006 7:35 am
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.
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.