Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

engineyard.com

Post reply on HN
Print view View original

11–20 of 66 posts

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

#11

Post by alnayyir »

rbranson wrote: post #6
andrewvc wrote: post #2

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 dat…

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

I'll borrow one from C-land:

Undefined behavior!

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

#12

Post by fhars »

Woost wrote: post #4

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…

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.

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

#13

Post by cellularmitosis »

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

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

#14

Post by mbreese »

cellularmitosis wrote: post #13

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…

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.

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

#15

Post by snprbob86 »

cellularmitosis wrote: post #13

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…

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?

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

#16

Post by k7d »

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.

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

#17

Post by lunaru »

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.

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

#18

Post by k7d »

Woost wrote: post #4

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…

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

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

#19

Post by brndnhy »

rbranson wrote: post #6
andrewvc wrote: post #2

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 dat…

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

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

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

#20

Post by cellularmitosis »

mbreese wrote: post #14
cellularmitosis wrote: post #13

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…

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…

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.

Post reply on HN