4

I have a PHP script that runs indefinitely, performing a specific task every 5-10 seconds (a do-while loop, checks the database at the end of every iteration to determine whether or not it should continue). This task includes MySQL database queries. What is the best way to handle the database connection? Should I:

a.) disconnect and then reconnect to the database every iteration?

b.) set the connection timeout to an indefinite limit?

c.) ping the database to make sure I'm still connected, and reconnect if necessary before executing and queries?

d.) Something else?

EDIT: To clarify, the script sends a push notification to users' iPhones.

Improve this question
9
  • What are you doing in every iteration? Why not use a schedule task/cron job to continuously execute the script? Commented Sep 11, 2012 at 4:25
  • sidenote: I suggest using cron jobs instead of while..do! b) don't do that ever Commented Sep 11, 2012 at 4:27
  • I recommend you wrap the PHP script in a launcher script and simply let it die in a controlled way on a DB connection error. Naturally the launcher script should restart it. Commented Sep 11, 2012 at 4:29
  • I'm using the IMAP library to check for new emails, and then sending push notifications if new emails exist. I need to access the database to update user data after each iteration. I feel like this approach isn't the most efficient - can you suggest alternatives? Either way, the script needs to run for simultaneously for multiple users, and it needs to run at these fairly quick intervals. Doesn't that more or less rule out using a cron job? Commented Sep 11, 2012 at 4:30
  • 1
    @James The launcher script can be PHP or even better a shell script. A shell script as a SysV init script allows you to run this at OS startup exactly the way everything else does - I use this a lot. You already have to reestablish the DB connection, if it went away, so this is no overhead. Concerning the IMAP connection: I am very sure, that this is negligable in relation to the real work done on the IMAP. Commented Sep 11, 2012 at 11:32

1 Answer 1

Reset to default
1

The suggestion that you cannot run your PHP script as a daemon is ridiculous. I've done it several times, and it works quite well. There is some example code to get you started. (Requires PEAR... if you're not a fan, roll your own.)

Now, on to your actual question. If you are making regular queries, your MySQL connection will not timeout on you. That timeout is for idle connections. Definitely stay connected... there is no reason for the overhead of disconnecting and reconnecting. In any case, on a database failure, since your script is running as a daemon, you probably don't want to immediately kill the process.

I recommend handling the exception and reconnecting. If your reconnect fails, fall back for a little while longer before trying again. After a handful of failures (whatever is appropriate), you may kill the process at that time, as something is probably broken that requires human intervention.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.