Hey there. Stumbled by, and thought I would show this topic some love.
From a performance standpoint, in your typical PHP+MySQL web application, updating the game state on demand is an extremely bad idea, on par with holding a metal pole in a thunderstorm or bathing in shark-infested waters with a bathing suit made of fresh seal meat. You can try it, and you can make it work (if your server is hideously over-powered for the number of users you have), but it has severe scaling issues.
The
first problem with on-demand updates has already been mentioned: it might take a while between sessions. The next user to come online has to suffer a huge performance hit to let the game server catch up with the current state. Not because the language is slow or because it's doing a+=1 five times instead of a+=5 once (all of this can be optimized, although not necessarily for free) but because a massively concurrent web application needs to save the data back to the database, and writing data to MySQL is insanely expensive. If your application has to perform even one indexed INSERT per interaction, then a single user catching up on all the interactions for an hour of idle time will have one hell of a wait time. And without one INSERT per interaction, good luck implementing the «here's what happened while you were away» feature on your game.
But that's not the worst issue (you can kind-of-solve-it by making sure someone requests an update every X seconds, just like the Original Poster is trying to solve it here).
The
second problem with on-demand updates is stampeding (or serialization, depending on how you look at it). Stampeding basically happens like this:
- User A connects to the server, notices that some updates should be done, and starts performing them transactionally.
- User B connects to the server. Since User A is acting transactionally, User B sees the same needs-to-be-updated game state and starts updating it transactionally. This doubles the performance load of the update.
- More users connect. Since the performance load increased, the updates take longer, so there's a higher probability of starting an upload that is already being processed. The server load increases until it basically gives up and takes its own life in frustration (yes, MySQL does that).
Depending on what your updates look like and how your transaction isolation level is configured, you might experience serialization instead: User A connects and does an update. User B connects and reads the "Todo" table(s) but is locked out because User A is working on it, so User B idles until User A is done. The lock on the "Todo" table(s) means your server can now only serve one user at a time. This is a major performance problem if you intend to serve more than a handful of simultaneous users, because locking problems can
not be solved by adding more hardware.
The ideal architecture for a web game is to have a single update "thread" (might be a cron job or a script running without a time limit) that polls the database for things to be done and does them, and any number of threads to serve the HTTP requests from the players by reading data from the database and using lock-free, possibly delayed, cluster-indexed INSERTs to tell the update thread what to do. If you have the money, replicate the main database on another server and have all the players read from the slave to lighten the load and read locks on the master (if the server holding the update thread is powerful enough, you can scale your game to infinity). If you don't have that kind of money, and you see the update thread getting locked out too often, you might want to set lower a thread isolation level (READ COMMITTED) to reduce that (at the cost of sometimes displaying some inconsistent data on the user's screens).
In practice, it also helps if the game is designed so that most updates cost nothing. This can be done with two techniques:
- Do not compute or store anything the game logic does not need. If you don't need it to run the game logic, then you can compute it on demand when (and if) the user asks to see it and discard it afterward. For instance, if your units gain ranks based on the number of enemies they kill, then you do need to compute and store the number of kills, but you don't have to find out the name of the current rank until the user needs to see it.
- Use "do not open until" timestamps. Instead of the user inserting a "Build Space Station" order in an order table, they should instead insert a "Space Station" building in the building table with a timestamp of NOW() + buildtime. This means the act of successfully building the space station does not involve any computation (at the cost of an additional timestamp test when reading from the building table, but that can be easily indexed).
These will decrease the load on your server.
By the way, the default time limit for a PHP script running in CLI is
infinite (and defaults to 30 seconds for CGI and Apache mod_php), so you don't have to configure anything.