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

31–40 of 66 posts

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

#31

Post by brndnhy »

justincormack wrote: post #29
brndnhy wrote: post #19

Earlier quoted context omitted.

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

But 0MQ can lose items too if the queue fills, how does this help? I can see adding a queue with persistence would work...

Yeah, that's true. But my environment is such that any one of 100 or so app servers has a significantly lower chance of running out of memory than the Redis server does.

The 0MQ high water mark is set high enough so that it's virtually impossible not to fix a broken DB by the time messages on the client side create an OOM condition being queued in memory.

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

#32

Post by gizzlon »

justincormack wrote: post #29
brndnhy wrote: post #19

Earlier quoted context omitted.

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

But 0MQ can lose items too if the queue fills, how does this help? I can see adding a queue with persistence would work...

Unless there are so many items per sec that your persistence can't keep up. Wouldn't this kind of create the same situation: You cant accept all the new items and have to throw some away. Only, now everything is slower. A lot slower.

Ok, the first scenario is caused by the workers being too slow, so it's not exactly the same :)

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

#33

Post by mootothemax »

Devilboy wrote: post #26
k7d wrote: post #16

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.

MySQL is not too hot on Transactions

MySQL != MyISAM. Check out InnoDB :)

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

#34

Post by cageface »

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…

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.

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

#35

Post by iradik »

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…

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.

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

#36

Post by iradik »

j_baker wrote: post #28
cellularmitosis wrote: post #25

Earlier quoted context omitted.

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

Yeah, but why not just skip the intermediary step and use a "real" queueing system to begin with? It doesn't sound to me like it's any more effort in the short term or in the long term, and it's one less thing you have to worry about as you scale.

Gonna play devil's advocate here:

I think making files in a folder represents the least amount of effort for making a queue. So using one of the systems you described is necessarily more work.

I think the commenter outlined the reasons: any process can access the data with simple unix commands, and everyone understands files.

Plus files could be more efficient. What if the work unit you are processing are files? If the files are the work and the folder is the queue, you don't need any extra abstractions to access the data.

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

#37

Post by dramaticus3 »

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…

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

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

#38

Post by iradik »

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.

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

#39

Post by kilburn »

snprbob86 wrote: post #15
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?

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

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

#40

Post by arethuza »

cellularmitosis wrote: post #23
snprbob86 wrote: post #15

Earlier quoted context omitted.

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?

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

"32,000 subdirectory limit"

One top tip from personal experience is to make the resulting structure reasonably straightforward to browse manually - having huge numbers of subdirectories is going to be a barrier to this.

Post reply on HN