Best approach for running an "endless" process monitoring MySQL?

Question

I have a process that has to be ran against certain things and it isn't suitable to be ran at the users end (15+ seconds to process) so I considered using a cron job but again, this is also unsuitable because it will create a back log. I have narrowed my options down to either running an endless process that monitors for mysql changes, or configuring mysql to trigger the script when it detects a change but the latter is not something I want to get into unless it's my only option, which leaves me with the "endless" monitoring option.

The sort of thing I'm considering with PHP is:

while (true) {
  $db->query('SELECT * FROM database');
  while($row = $db->fetch_assoc()){
    // do the stuff here
  }
  sleep(5);
}

and then running it via the command line. Now this is theoretically sound but in practice it isn't doing as well as I hoped, using more resources than I would expect (but not out of my range, just not what I'm aiming for optimally). So my questions are as follows:

1. Is PHP the wrong language to do this in? PHP is what I work with, but I understand that there are times when it's the wrong choice and I think maybe this is. If it is, what language should I use?

2. Is there a better approach that I haven't considered and that isn't any of the ideas I have listed?

3. If PHP is the correct option, how can I optimise the code I posted, is there a method better than sleeping for 5 seconds after each completed operation?

Thanks in advance! I'm open to any ideas as long as they're not too far out there, I'm running my own server with free reign so there's no theoretical limit on what I can do.

Answer 1

what kind of problems are you experiencing? i dont know about the database class you have there in $db, but it could generate a memory leak.

furthermore i would suggest closing all your connections and unsetting all your variables if necessary at the end of the loop and re open on the beginning! if its only 5 second sleep maby only on every 10th interation or something. you can do a counter for that...

theese points considered theres nothing wrong with this approach.

Answer 2

PHP might be the wrong language as it's really designed for serving requests on an ad-hoc basis, rather than creating long-running daemons. (It was originally created as a preprocessor language, then later on came into general use as a web application language.)

Something like Python might work better for your needs; it's a little more naturally designed for "daemon-like" programs.

That said, it is possible to do what you want in PHP.

Answer 3

I recommend moving the loop out into a shell script and then executing a new PHP process for every iteration. This way PHP will never use unbounded resources (even if there is a memory/connection leak somewhere) since the process is terminated on each iteration. Something like the following should be fine (Bash):

while true; do
  php /path/to/your/script.php 2>&1 | logger ...(logger options) 
  sleep 5
done

I've found this approach to be far more robust for long-running scripts in PHP, probably because this is very like the way PHP operates when run as a CGI script.

Answer 4

1. You should always work with the language you're most familiar with. If this is PHP, then it's not a wrong choice.

2. Disconnect from the database before sleeping. This way your script won't keep a connection reserved, and it will work fine even after database restart.

3. Free mysql result after using it. Always check for error conditions in daemonized processes, and deal with them appropriately.