What techniques enable web based games to update player's resources frequently?

Long ago there was this web-based game called Utopia (and I'm sure it is still around), turns are by per hour. You only get new gold/lumber or what-not every hour.

Some newer web based games have resources incrementing in finer resolution, like per minute. So say a player has a food production rate of 5 per minute - the player will have his food amount incrementing every minute. I am not sure if it is wise to constantly set a CRON job to update the SQL database, which I am assume what they are using, as it is web-based. Perhaps I am wrong, every minute.

What techniques do those web-based games use? Added on Edit: And can database can really handle the load?

From game development Extrakun

• I am in favor of KISS: "Keep It Simple, Stupid!"

  I see no reason why a cron job wouldn't work, and the upside is that cheap web hosting options often don't let you directly run commands, but they do allow you to run cron commands. So there's no need for an expensive dedicated server.

  You can also do your update code in PHP or your language of preference, by having the cron command either wget --spider http://yoursite.com/secretphppage.php or use the lynx text browser. The instructions at Configuring cron jobs have several example commands which you could use to ping your own page at an interval.

  Extrakun : I guess I am more concerned about a CRON job that runs every minutes updating the data of a few hundred users all at one time.

  bummzack : An update to several hundred datasets once in a minute is no problem for a modern Database. I don't know how your queries look like, but a single 'UPDATE' call should still perform nicely even with thousands of affected rows.

  Lo'oris : And in what world a cron job is considered "simple"? If you're not a sysadmin you'll likely get mad just at looking at its configuration file...

  Ricket : The world of webhosts, where they don't (usually) give you SSH access so they need to provide an easy interface to Cron. My webhost (Lunarpages) uses Cpanel, and I find it quite easy to use: http://img822.imageshack.us/img822/7022/easycron.png
  From Ricket

• Regarding the updates:

  Some use CRON jobs that hit a certain PHP page every so often.

  Some use CRON jobs, this time running a certain process.

  Another approach is to do 'just in time' updates - whenever any page is loaded, run through any pending updates and perform them at that point. This is generally what you have to do if you can't run CRON jobs or long-running processes.

  Finally, others are running the whole web application as one process, so they can update whenever they see it is time.

  The final system is the best if you have that option available as you can store the data in memory. Updating a few thousand players once per minute is trivial if you're just changing data in RAM rather than having to write to a traditional SQL database.

  But if you don't have that luxury, you can use some sort of caching. Something like memcached may be one option (dependent on your hosting) which is a half-way point between memory and a database. You can store transient values in memcache and only save them to the DB when absolutely necessary.

  Between memcache and a traditional SQL database there are other options, eg. the various key/value stores or document stores: stuff like MongoDB, CouchDB, the Amazon or Google offerings, etc... ie. all the systems under the umbrella term NoSQL. These typically don't give you the generic querying power nor always all the same safety guarantees of a traditional database but are often much faster in operation. (Which is not so surprising, as they are doing less for you.)

  But this is all assuming a normal database can't handle the load. In fact, in most cases it probably can. If I have to issue 10,000 UPDATE calls per minute to increase resource levels, that's not very scalable once you start adding everything else on top. But if you change that to update the resource for everybody with 1 SQL call, suddenly things look a lot more positive. So don't overestimate how expensive a certain feature is, as it can often be implemented in a more efficient form.

  From Kylotan

• It seems to me that you could just store the production rates, the base resource level, and a last-updated timestamp. Then, whenever you need to know the actual resource level, you could just multiply the production rate by the elapsed time.

  Whenever the actual resource level changes - simply update the base level. Whenever the production rate changes, update the base level to the actual level, and the timestamp to the current time.

  This way, instead of updating every player (presumably including those not currently logged in) every minute, you're updating only rows that are actually actively being used by players.

  (If your game "ticks" once a minute, I imagine a "turn number" of some sort might be better than an actual timestamp.)

  A side-benefit of this system is that you can use the same logic used to update the database, and to predict values without modifying them - to only send the necessary information to the client (rather than updating once per minute), and to predict values on the client.

  Kylotan : This does actually require some understanding of complex mathematics in some cases. Consider earning interest on money - adding 1% interest once per month is not the same as adding 12% interest once per year. Second example, if the chance of an event happening in a given minute is 10%, the chance of it happening after 10 minutes is not 100%. Interestingly this is just a very slow demonstration of the physics problems people see in other games, and the same method can be used to address them, ie. use a fixed timestep and run it as often as needed to 'catch up'.

  The Communist Duck : Kylotan, I wouldn't class compound interest and multiple probability chances as complex mathematics. :P

  Kylotan : If your understanding of calculus is ok, then sure, it's not complex. But these things don't work with "just multiply the production rate by the elapsed time" and it's important to highlight that.

  Andrew Russell : It's a valid point. But I would hope that anyone implementing non-linear resource production in their games understands how it works ;)

  Kylotan : But they don't need to, if they are using the old periodic update. That's the whole problem I'm getting at - using this method introduces a new potential error that was not a possibility before.
  From Andrew Russell

• Modern SQL databases that are commonly used (MySQL,Postgres,Oracle,MSSQL,etc) allow you to schedule stored procedures to be run with arbitrary time intervals. It's a very light weight method of running updates like this and removes the need to invoke external script or program to do it for you.

  This also allows the database to optimize the query you run just once, instead of doing it every time external script invokes it, so it's very useful from performance standpoint. Of course this only applies if your updates are trivial enough to be programmed in your databases own scripting language. Resource updates, automatic time interval log outs, record purging and the likes are excellent candidates for these scheduled stored procedures.

  Most likely the load your database is experiencing comes 99.9% from page loads, game engine updates and the like, as a popular game can easily have hundreds of thousands of queries per second. Compared to the load that arises from running a few queries (although ones that touch large portion of a table) once a minute or so, the load should not be an issue. That is, unless you update values that are indexed by the database. You should avoid updating indexed columns at all costs if you worry about performance.

  From Fuu

• I remember having a look at some Planetarion clone's code. Everything's PHP, but the ticker code is in raw C - with C MySQL bindings. Because the data model was very simple the updates happen really fast.

  From Ariejan