5 subtle ways you’re using MySQL as a queue, and why it’ll bite you

I've been met with looks of disgust for using a filesystem to implement a queue, but I feel it's unjustified. A modern unix filesystem is surprisingly well suited to this task: You get atomicity "for free", inotify allows it to be interrupt driven rather than polled, it inherently supports multiple processes (thus different parts of the system can be implemented in different languages), there's no need for locking as long as you implement the queue using directories and 'mv', and it's extremely quick to implement, understand, and modify.<p>The only caveats are that of performance (with a traditional server I wouldn't worry about performance until you need to process hundreds of items per second, but on EC2 nodes that threshold is more near the range of dozens per second), and the need to regularly archive the "done" directory (cron solves this nicely).

Percona always seems to have good articles.<p>I think, as he said, everyone shouldn't run out and replace a mysql job queue for their wordpress blog. In a great many cases it doesn't matter.<p>I also like how he never said "Don't use mysql as a queuing system" but "be careful of these things". I've used mysql as a queuing system, and it works fine. I looked at replacing it with a different database, but in that situation it was not worth the investment.<p>Signaling mysql + archiving performed work + no locks that lock more than the exact row that's being updated (and also avoiding concurrent workers acting on the same task) will take a mysql backed queuing system far. I've set up a system that processes well over 5,000 tasks / day using it.<p>Do I think everyone should use mysql as their queuing backend? No. People should probably use a queuing library, with persistence to a database (redis?) enabled for critical tasks. Of course, as the article said, be careful about the choice of backends.

This is why Redis / *SQL is my favored stack. It just covers so many bases, you get things like safe queuing, caching, pub/sub, and weird high-performance low-durability cases from Redis, and great, safe relational support from SQL.<p>For best results, it's good to have at least two redis servers, one with snapshotting as a cache (fast, less durable), one with 1 second Append only files (still fast, but slower) for data you care more about.

Queues in the DB are so common that in MSSQL they made it a first class feature: SQL Server Service Broker. Using it is an XML and T-SQL nightmare, but since it guarantees in-order, only once delivery, and supports routing and in-DB worker activation, you can build some really robust and powerful stuff with it.<p>MySpace used it to keep their partitioned databases in sync: <a href="http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000004532" rel="nofollow">http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?...</a>

&#62; Instead of SELECT FOR UPDATE followed by UPDATE, just UPDATE with a LIMIT, and then <i>see if any rows were affected</i><p>Should be noted, this is not necessarily a good solution: a concurrent consumer, which may be another incarnation of a given script running with a lag, may hijack the queue element locked this way; as a result you may end up having two or more incarnations of the consumer handling the same queue element.<p>The most universal approach to DB queues is to assign each consumer process a unique ID which it should use for locking queue elements in their UPDATE ... LIMIT 1.

I'm just going to count killing a SLEEP(100000) query as a means of signalling a worker as the something new I learned today. I'm not sure I've ever written anything where implementing that would have had any real impact, but it's filed away for the future.

The article didn't mention the main advantage of storing queues in DB - transactions. Say you need to update other records in DB while processing a job with 100% consistency. If it's all in the same DB you can update both job as well as data in a single transaction.

The title of the article is not very representative of its contents. It should be: "5 subtle ways you’re using MySQL as a queue, and how it COULD bite you IF you use poor schema design NOT optimized for YOUR workload".<p>MySQL queues work just fine with the recommendations Barron provides himself "1) avoid polling; 2) avoid locking 3) avoid mixing queue and archive tables".

Openbsd folks used lpq to queue mp3 playlist. Cups is also an option and you get full stack of goodies built in.<p><a href="http://patrick.wagstrom.net/weblog/2003/05/23/lpdforfunandmp3playing/" rel="nofollow">http://patrick.wagstrom.net/weblog/2003/05/23/lpdforfunandmp...</a> <a href="http://rendermania.com/building-a-renderfarm-with-cups/" rel="nofollow">http://rendermania.com/building-a-renderfarm-with-cups/</a>

Is the page's rendering totally busted for anyone else? Chrome on Ubuntu.

I've been in a situation where I've needed to queue about 100k of messages. Each message unique with custom attributes populated also from MySQL.<p>I used to generate the messages and then insert them into queuing system but for 100k messages I never managed to make this fast... I have managed to queue all these messages in less than half a second using just one MySQL query.<p>If anyone has any better ideas, please let me know!

MongoDB offers findandmodify which makes for a good synchronized queue up to some point. If anyone's using PHP and Mongo, feel free to take a look at MongoQueue: <a href="https://github.com/lunaru/MongoQueue" rel="nofollow">https://github.com/lunaru/MongoQueue</a><p>Once you start hitting hundreds of jobs per second, you'll want to scale horizontally, but that shouldn't be the case for 99% of use cases.

And here is one explicit way to use MySQL as a queue: <a href="https://www.pingbrigade.com/blog/entry/selector-workers-recorder-pattern" rel="nofollow">https://www.pingbrigade.com/blog/entry/selector-workers-reco...</a>

My project uses couchdb for task queuing: <a href="https://github.com/codehero/scheddesk" rel="nofollow">https://github.com/codehero/scheddesk</a><p>Still kind of alpha, but working for my purposes.

i always wished that mysql had a skip locked rows feature, so if you do a select for update it would skip any rows that are already locked. this way if you created a queueing system you could run select for update, but then skip rows that are already being processed (the locked rows).<p>i actually implemented this once partially on innodb, and it worked pretty well, no waiting for locks, but abandoned my efforts due to another project.