Using mysql as a bootleg message queue

This is not one of those typical high scalability articles about how to serve 10 billion pages from your iphone. This article is for the people just starting out, people who don’t want to spend a fortune on hosting, or even people who are just learning to program. As you may very well know it’s a good idea to push long running tasks off the execution of a web request. This is often done using a message q like rabbitmq or writting some erlang…however this brings me back to my point. We are either a newb or don’t have a server that can run additional process like rabbit mq on top of our apps that do the real work. Plus who wants to evaluate message q’s, learn the api, install it..etc. Lets keep it simple. We know mysql and we know…(insert language here) Just use them both.

The idea is simple. We will run a separate process that polls the database and picks off tasks. This process can be run from ANY server that can connect to the database including machines located in your house. Here is a sample table structure

CREATE  TABLE `mysql_message_q` (

`message_id` INT NOT NULL AUTO_INCREMENT ,

`message` MEDIUMTEXT NULL ,

PRIMARY KEY (`message_id`) );

I would recommend encoding your message as JSON. Now I can just run a few sql commands and my process will pick messages off the Q. OMG that is so simple. Not so fast. If we have multiple servers polling the database for tasks we will need to obtain an exclusive lock before picking off records. This can be done using mysql as well. Below are some sql queries and steps you should take to implement this.

 

SELECT GET_LOCK(‘someuniquename.lock.polling’,10)

select * from messageq limit 5

delete from messageq where message_id in (1,2,3,4,5) The numbers 1,2,3,4,5 will be replaced by the real id’s from the db that the previous select statement pulled in

SELECT RELEASE_LOCK(‘someuniquename.lock.polling’)  – Make sure to do this in a finally block

Do the long running work here

Update the database when the work is done

Rinse and Repeat

 

Boom you are done. Double love it. Enjoy it while your site is young and growing…once you hit twitter level traffic you can migrate to something that is more “enterprise level”.

Thanks for listening. Does anyone else have a simpler message Q idea?

Update:

@Nicolas Sebban suggested

I used linux native FIFO file type (man mkfifo) as a simple first-in-first-out queue. Each line of my file contains a json-encoded job, that I pop from the file when I’m ready to process a task.

It is simple and fast, and it has been working great for about 2 years now. It’s very low-tech, but it just works.

Great comments on hackernews

http://news.ycombinator.com/item?id=2528528

 

 

  • Anonymous

    The company i previously worked for paid percona a lot of money to tell us the following, and I’m going to pass it along to you, free of charge, because you seem like a nice kid.

    “Don’t use MySQL as a message queue. There are plenty of open-source message queues available for free. Whatever you do, *don’t* use MySQL”

    That’ll be $10k please.