Way to process and monitor time-based events stored in MySQL DB?

Question

I am creating a browser-based MMO that has time-based events: I.E. the construction of a building will take 23 hours.

I can log that in my MySQL database and then loop through an events table (or equivilent) using a cron script or watch (this question was immensely helpful).

There seems to be a general consensus that I can either:

(1) Run a cron job every second for a script that processes the events and their related triggers.

(2) Run a cron job every minute that loops every second for 59 iterations that processes the events and their related triggers.

(3) Use watch to run a php script every second.

Essentially, upon completion of an event, the script will process some triggers that range anywhere from changing states, updating databases, triggering other "events" that are queued after the recent event, and so on.

My biggest concerns come where there is the potential to have 10,000+ events and triggers to process every second. What are some strategies I could use to avoid performance concerns? Are these all of the solutions, or are there better alternatives?

Example events:

Construction of Building A - 00:43:21

Fast forward 2601 seconds and the following triggers are executed:

Place Building A on map at Location x,y
Update resource total given by building
Start timer on next building in the queue
Construction of Building B - 02:10:49

To better explain: In the early stages of the game these events will take anywhere from 5 minutes to an hour. In the later stages of the game these events will be spaced apart by hundreds of hours. I need these events to process in real time because they will impact other aspects of the game (Such as a resource structure improving the amount of resources given every hour). Thus, these events need to be processed by the server, regardless of whether or not the user is active. The theoretical script would check for expired events, and then based on the event, run several other methods related to the expiration of said event.

HOW GAME AND CRON JOB IS RELATED ..10,000+ events and triggers ..CAN YOU GIVE AN EXAMPLE... — zod, Jun 01 '12 at 18:55
Why use cron jobs? I would just make a function in PHP to, whenever any interaction goes in with the buildings, it "loops through the events table." You'll save an immense amount of processing power this war. Having played many RTS game myself, probably 60-70% of users aren't active. There's no point in updating their stuff when no interactions are taking place. — Connor Peet, Jun 01 '12 at 19:00
I think choice of technology is an important factor here. PHP and MySQL aren't really suited to the task that you're trying to accomplish. I would look at creating a nodejs process that tracks your events and uses timeouts to handle the actions. That way you'll have a lightweight process that can track thousands of active events and will only spawn new processes when you need them. — Isaac Hildebrandt, Jun 01 '12 at 19:06
I've added the last paragraph to provide a little more context. — Nick, Jun 01 '12 at 19:47

Nick · Accepted Answer · 2012-11-16T15:06:31.230

https://github.com/prggmr/prggmr

Sounds like an event library might help you tremendously here,

Instead of trying to have a "catch-all" that monitors all events to take place in the future you could write a simple interval in prggmr that will pick up events that need to happen in the future and register them into the event engine to signal at that time.

Something like the following might work,

prggmr\interval(function(){
    $query = mysql_query("SELECT event, when FROM events");
    while($event = mysql_fetch_assoc($query)) {
        prggmr\setTimeout(function() use ($event){
            prggmr\signal($event['event']);
        }, $event['when']);
    }
    // What this would do is look for any events that require signaling in the
    // in the future such as a building construction and signal it when the 
    // given amount of time in milliseconds has run
}, 3600);
// This simply tells the engine to run the function every minute

And to run this code say it is stored in event_detect.php

prggmr event_detect.php

You would generally want to have the process managed by another app such as runit.

With this approach you could build in all events that can happen into your application as registered "signal handlers" and have an actual gaming engine running in the background that waits for and monitors events as they happen in real-time without any user interaction.

We are actually using this approach for a similar situation for a game we are currently developing ... logically quite different but non-user interactive event processing all the same.

Also for performance you can very easily handle 10,000 events in a second or less depending on your server.

Way to process and monitor time-based events stored in MySQL DB?

1 Answers1