Using MySql as a bootleg message queue | Hacker News Reader

Using MySql as a bootleg message queue

adamilardi.com

5 pointsadamilardi7 comments

Speaking from experience using mysql as a message queue.

Lock contention will be a bitch past a certain number of processes/threads accessing the DB. Also, hopefully your application doesn't crash during the long-running work. If so, your messages are gone forever.

As for a simpler idea, message queues aren't that difficult. Why not just install a proper one to start off with.

Do you have any recommendations for something simple?

It depends on what your specific needs are with respect to persistence and other aspects of your application. Take a look at http://blogs.digitar.com/jjww/2009/01/rabbits-and-warrens/ and read about RabbitMQ and ZeroMQ to see if either sounds like a solution for your problem.

There is a temptation to use MySQL message queues to provide more complex queueing strategies - prioritization, retries, folding multiple queues into one table, claim tokens to avoid the global lock, and in the very worst case keeping all of the queue data to double up as log records. All of these must be resisted - they scale and perform poorly.

If they use MyISAM as the table format for the queue, it may crash and require repairs. If they use InnoDB, then the queue is no longer very lightweight.

Queues in MySQL certainly can work, and are pretty straightforward, but they'll need to be replaced with something else eventually.

"The right tool for the right job" and all that. I just wish AMQP didn't seem quite so sketchy ...

check q4m, it has been out in production use for 3 years.

There is a mysql storage engine that works as a persistent high throughput message queue. http://q4m.github.com/. It can deliver to 15k items/sec on a 4GB RAM, Intel X3220 @ 2.40GHz, with 7200 rpm disk. The performance of the queue is proportional to the disk, cpu and ram just as mysql.

beanstalkd hits the sweet spot for me.

Reply on news.ycombinator.com