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

http://www.engineyard.com/blog/2011/5-subtle-ways-youre-using-mysql-as-a-queue-and-why-itll-bite-you/

by abredow • 15 years ago

184 65 15 years ago

andrewvc• 15 years ago

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.

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.

rbranson3• 15 years ago

What happens when the queues back up and Redis runs out of RAM?

alnayyir• 15 years ago

I'll borrow one from C-land:

Undefined behavior!

brndnhy2• 15 years ago

Good example of a real situation you always end up facing at some point. Why I use ZeroMQ when logging or queueing with Redis.

j_baker3• 15 years ago

If your queues are getting that backed up, you're either facing bigger problems than your queueing system (most likely workers being down), or you're big enough to afford more machines and/or a custom solution (such as kestrel).

kingkilr• 15 years ago

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

jauer• 15 years ago

Fine for me with Chromium 13.0.782.220 on Arch.

nullpixels• 15 years ago

Normal on Chromium 13.0.782.215 on Ubuntu 11.04

angusgr• 15 years ago

Was broken on first load for me, Ubuntu 11.04 Chrome 13.0.782.220.

However when I reloaded the page it fixed itself. In fact as the page reloads I can see the text layout first breaking and then immediately fixing itself...

BauerUK• 15 years ago

Yes, rendering issues for me, too.

Chrome 13.0.782.220 Ubuntu 11.04 (Linux 2.6.38-11-generic) GNOME 2.32.1

Extensions:

- Adblock Plus for Google Chrome™ (Beta) - Version: 1.1.4

- Xmarks Bookmark Sync - Version: 1.0.16

- Reddit Enhancement Suite - Version: 3.4 (Disabled)

Examples:

1. http://i.imgur.com/TD8UU.png

2. http://i.imgur.com/HIXbP.png -- with text selected.

Woost• 15 years ago

Percona always seems to have good articles.

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.

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.

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.

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.

fhars• 15 years ago

That is about one task per 50 trillion CPU cycles, you would be hard pressed to write a queue implementation that is to slow for that. Numbered files in a single directory on a synchronously mounted filesystem designed for few large files that only allows linear directory scans might qualify, but I am not even sure about that.

k7d2• 15 years ago

5000 tasks per day is of course far from limit. we used to build Mysql queues that process close to 100 tasks per second.

Ogre• 15 years ago

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.

kogir• 15 years ago

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.

MySpace used it to keep their partitioned databases in sync: http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?...

cellularmitosis5• 15 years ago

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.

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).

mbreese• 15 years ago

You know, I set something up in a very similar way a few years back for a client. It was a quick a dirty hack to get a processing queue up and running fast with low overhead on the server (a VM with no resources). The processing was to take a PDF that would appear in the directory and then email or fax it depending on the directory.

I felt dirty while doing it, but didn't want to build up a whole ActiveMQ (or similar) queue solution - it was just overkill.

6 years out that simple hack is still working today without needing any sort of maintenance.

cellularmitosis5• 15 years ago

I suspect there's a large overlap between the people who would ridicule such an approach and the very people who find themselves in need of this article :)

A while back I looked at moving part of the queue into mysql, but I got stuck while trying to keep it a polling based system (I should have been able to accomplish this by having a mysql trigger touch a file in the filesystem, which would trigger inotify / wake up the queue, but I couldn't get it to work as described in the docs). After reading the author's mention of postgresql having some sort of listen/notify feature, I'll have to give that a look.

snprbob86• 15 years ago

I recently read through news.arc (the source to Hacker News itself) and was dumbfounded by how such a simple, file-system backed system was able to cleanly and performantly handle many of the use cases of a document store or key value store. Are there any good resources on the DOs and DONOTs of building apps in this "Hey.... dummy.. Just use the file system!" -style?

cellularmitosis5• 15 years ago

Watch out for the 32,000 subdirectory limit. If your job tickets are complex enough to be implemented as a directory instead of a file, you'll get bitten by this (the number of files in a directory is only limited by the number of inodes in the entire filesystem).

If you are really lucky, and your tickets only need to represent a single piece of data (some sort of ID for example), you can just use the name of the file itself for the data storage and deal only with empty files. Because this only uses a single inode/block, it represents the best case scenario for speed and scalability in terms of the number of tickets which can accumulate before you need to archive. But more likely, you are going to have to worry about ticket namespace collisions (unless you have some sort of "set" like requirement where each ID can only be in the queue once at a time) which means you are using something like mktemp to create the file and then storing the ID inside the file.

Another key is to make sure you create new jobs in a "staging" dir, and then mv them into the "in" dir. Otherwise you have a race condition between your queuing system and whatever creates the tickets.

Here's a basic layout: /stage, /in, /active, /done. Some process on your system creates a ticket (which could be a single file or a dir) in /stage and then moves it into /in. This wakes up your queue, which moves it to /active when it starts processing it, and then moves it to /done and moves on to the next ticket in /in.

Another nice thing this gives you is that recovering from a crash / unclean state amounts to running ls on /stage, /in, and /active.

kilburn• 15 years ago

I can not find a reference to explicit "DOs and DONOTs", but you can surely gather experience from systems that have used this schema for a looong long time: mail handling systems.

For a quick start, I would look at the maildir specification, that includes instructions on how you should read form and write to maildir folders to avoid locking and get good performance: http://www.qmail.org/man/man5/maildir.html

Then, I would dive deeper by looking at the processes used to maintain the mail queues in qmail: http://www.qmail.org/qmail-manual-html/misc/INTERNALS.html . Obviously, you could also look at how postfix or exim handle their own queues.

Anyway, gathering all the experience buried in those systems and summarizing it in a logical way would make a great great article...

regularfry• 15 years ago

One big DONOT is: Don't do this if you need more than one physical host to be processing the jobs at the same time. Resiliency is hard to get right with shared filesystems.

j_baker3• 15 years ago

This solution isn't necessarily terrible.

...but why would you worry about these problems when other solutions like kestrel, beanstalk, and redis (my personal favorite) are equally easy to set up and understand?

And for that matter, how do you give multiple machines access to this workqueue?

cellularmitosis5• 15 years ago

As you point out, both of those are excellent points at which you should consider a "real" queuing system :)

cageface• 15 years ago

Quick & dirty solutions like this often get dismissed out of hand but in practice something like this can be thrown together in a day but perform well enough to last until you know you've built something that merits a more robust implementation.

Unix's "everything is a file" philosophy can be stretched pretty damn far.

iradik4• 15 years ago

haha.. i've gotten those looks as well.

but i agree. files and folders are an elegant abstraction, that when combined with the unix toolset become extremely powerful.

The big shortcoming I see with this solution, and maybe this is what you are saying in the caveats, is that it doesn't support multiple worker boxes.

Of course you could use NFS, but this complicates it. Suddenly the consistency model is more complex and workers must partition work, and so on.. At that point, a mysql backed queue becomes an appealing and easy way to make a distributed queue.

3am• 15 years ago

My experience is that when something is filesystem based, you eventually have someone write a not-robust-enough bash script to do some maintenance operation (find|xargs|rm cleanup script, a sed based update script, etc) and it blows stuff up.

I think the transaction log and the forced structure of using SQL (barring some yutz carelessly using TRUNCATE) add some value managing the data, too. Not as big an issue where it's a single person maintaining the app.

dramaticus3• 15 years ago

Those that don't undestand Unix ....

Blow their mind and show them join(1)

MySQL, nope I use postmap - http://www.postfix.org/postmap.1.html

snorkel• 15 years ago

Agreed++. I've done similar filesystem queues and have been told by corworkers "Yuck! That needs to be in a database!" ... so I ask why ... and the answer is "Because that's what databases are for!" Inevitably these cowrokers inherit the project, database every aspect of it, and then the app promptly collapses into a steady stream of downtime alerts. Yes, that's what databases are for: keeping DBA's gainfully employed.

k7d2• 15 years ago

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.

Devilboy• 15 years ago

MySQL is not too hot on Transactions

mootothemax2• 15 years ago

MySQL != MyISAM. Check out InnoDB :)

lunaru• 15 years ago

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: https://github.com/lunaru/MongoQueue

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.

dmk23• 15 years ago

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".

MySQL queues work just fine with the recommendations Barron provides himself "1) avoid polling; 2) avoid locking 3) avoid mixing queue and archive tables".

iradik4• 15 years ago

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).

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.

damir• 15 years ago

Openbsd folks used lpq to queue mp3 playlist. Cups is also an option and you get full stack of goodies built in.

http://patrick.wagstrom.net/weblog/2003/05/23/lpdforfunandmp... http://rendermania.com/building-a-renderfarm-with-cups/

mojuba• 15 years ago

> Instead of SELECT FOR UPDATE followed by UPDATE, just UPDATE with a LIMIT, and then see if any rows were affected

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.

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.

Limes1022• 15 years ago

I agree. I set a process identifier and the time of the update.

IgorPartola3• 15 years ago

And here is one explicit way to use MySQL as a queue: https://www.pingbrigade.com/blog/entry/selector-workers-reco...

molesy3• 15 years ago

Hi! Please read TFA. You should not be advocating this pattern to anyone. It sucks, it will break very quickly, and Baron explains why.

IgorPartola3• 15 years ago

Hey there. The pattern in TFA is somewhat different: in the SWR pattern not every worker talks to the DB. Instead, only the selector does. It then hands out the work to the workers via a fast local queue. The ratio I set up for Ping Brigade is 1:1000 selector to workers. Thus a handful of selectors can feed a few thousand workers.

codehero• 15 years ago

My project uses couchdb for task queuing: https://github.com/codehero/scheddesk

Still kind of alpha, but working for my purposes.

Limes1022• 15 years ago

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.

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.

If anyone has any better ideas, please let me know!

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

Related Stories