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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.