Whats the best method for queuing time-sensitive messages with PHP/MySQL?

I'm building an SMS call and response system in a new app that receives a message via an aggregator gateway, checks it for functional keywords (run, stop, ask, etc), then processes it appropriately (save to the database, return an answer, or execute a task based on the users authorization). It's running fine at the moment as there are only a few users, but I figure its going to have more issues as we scale it up. We're currently running it on a single DV machine (mediatemple base dv).

My question is this: does it make more sense to set something up like Memcached to run a queue, or a simple database with a daemon running to process each message one by one? I don't have much experience with either, so any advice would be helpful. Since the messaging is somewhat time-sensitive, what would be the fastest and most reliable way to handle this?

Also, since we're sending responses, I'll probably need to set up and outbound message queue as well. Would it make sense to use the same concept for both?

"Fastest" and "most reliable" don't necessarily go well together : it's often one or the other.

ABout memcached and a database, you must be aware that memcached is a caching mecanism, and not a data-storage mecanism.

It means it should not be used to store data that you cannot regenerate :

if there is a system failure (like a crash / reboot), you will lose what was in memcached -- while database are more able to recover
if there is not enough RAM to store the data, memcached will remove some old items from the cache, even if you didn't ask for it

memcached is great when it comes to creating a distributed caching cluster ; but should not be used to store data that is important and that you cannot afford to lose.

Makes sense. So if I was to go with a simple database approach, what would you recommend? Auto-increment id's and process the lowest id each pass (then delete)?

Mike Diena 2010-03-22 05:32:33

That would be a possibility ; You could also use a timestamp column, and sort using that one, instead of sorting by the autoincrement *(wouldn't make much of a difference, if properly indexed, I suppose)* ;;; also, I would not delete the rows once processed : instead, I would use an additionnal "has_been_processed" column, and update it to 1, so indicate the line has been processed *(This way, in case of a problem, I could find the rows some time after, which might be useful)*.

Pascal MARTIN 2010-03-22 05:43:10

ansaurus

tags:

views:

answers:

Whats the best method for queuing time-sensitive messages with PHP/MySQL?

related questions