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

51–60 of 66 posts

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

#51

Post by jbooth »

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.

Because then you have to admin the real queueing system. If it's something simple, sometimes the one-time cost of re-solving the problem is less than the ongoing cost of dealing with that damn queueing system every time someone wants to set the app up on a new host, or a new dev wants to work with it, or it crashes, etc.

Fine line for when either approach is appropriate.

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

#52

Post by 3am »

iradik wrote: post #35
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 an…

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.

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

#53

Post by BauerUK »

kingkilr wrote: post #3

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

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.

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

#54

Post by pkteison »

mootothemax wrote: post #46
moe wrote: post #45

Earlier quoted context omitted.

InnoDB isn't too hot on transactions either. Google for "InnoDB deadlock".

I've googled, but am seeing the same kind of results that I see when I try "oracle deadlock", "postgresql deadlock" and "db2 deadlock." Still being relatively new to InnoDB, what particular deadlock dangers make it more troublesome than other DBMS engines?

There are three possible ways that I know of to handle concurrency issues on shared data: Use some sort of a journaling approach (google keywords 'snapshots' or MVCC), or use locks, or ignore the problem.

If it ignores the problem, it's not a database.

Locks suck for volume. Locks cause much more deadlock than other options. Locks are fast in the simple case. Locks are easier to program and take less resources.

InnoDB uses locks.

SQL Server also defaults to locks. People often specify 'ignore the problem' mode (nolock/read uncommitted). There is a new journaling approach available, but it was only introduced in 2005 and I don't think many people are using it yet. Which is a shame, it's a great feature.

Oracle and Postgres both do a journaling approach. They will have less deadlock problems because readers and writers don't need to block each other. With InnoDB or default sql server, read locks block writes, which sucks. See http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.htm...

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

#55

Post by rbranson »

j_baker wrote: post #24
rbranson wrote: post #6

Earlier quoted context omitted.

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

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

The parent post referred to using Redis as a multi-purpose store. If there is a bunch of other non-queue data in Redis and the setup is only expecting it to use 1GB or so, there's likely not a giant amount of room for queue entries left. While everyone is sleeping, some crashed workers combined with broken or poorly configured monitoring can fill up queues very quickly. Been there, done that. Either the OOM hits and there is some data loss or the swap hits and brings everything down.

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

#56

Post by rbranson »

moe wrote: post #44
brndnhy wrote: post #31

Earlier quoted context omitted.

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.

high enough so that it's virtually impossible not to fix ... by the time I'll add that to my "famous last words" fortune cookies.

Ultimately, it's all about the odds. That's what HA, replication, and DR are all about. It's so statistically unlikely for certain things to happen, they just fall out of the realm of reason. Most operations folk I've talked to don't even consider their disaster recovery plans to be within the realm of feasibility. The chances of a catastrophic event rendering the owners of the system defunct is many orders of magnitude more likely than an event that breaks the standard data fail-safes that most datacenters have in place.

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

#57

Post by molesy »

IgorPartola wrote: post #48

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

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

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

#58

Post by IgorPartola »

molesy wrote: post #57
IgorPartola wrote: post #48

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

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

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.

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

#59

Post by molesy »

IgorPartola wrote: post #58
molesy wrote: post #57

Earlier quoted context omitted.

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

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.

This may be working great in your application but you're implying that it scales nicely and you're wrong about that - hand waving may work in your case, but Baron's whole point was that there are easy solutions that will make things better if and when an application grows to the point at which it's an issue.

I've personally been down this road many times, and the last time I made the mistake of relying on SELECT FOR UPDATE in a queueing system it broke down somewhere on the road between 1msgs/sec and 50msgs/sec. That application committed before it dispatched to the worker app so I would consider it a fairly similar access pattern as yours.

The solution I went with in that case was exactly what Baron describes at "Locking is actually quite easy to avoid." - something along the lines of UPDATE queue SET selected_by = dispatcher_id, selected_time = NOW().. and then SELECT * FROM queue WHERE selected_by = dispatcher_id. I hate putting pseudo-SQL because it's already setting bad ideas in some random reader's head. Anyways, that scaled up to several thousand messages per second and ran happily for years, long after I left that particular company. May still be running depending on who you ask.

Long story short, it's great that your solution is working for you but the weight of public knowledge suggests it's not a great solution for anyone else to pick up on. Ping Brigade looks nifty, I hope it works great for you. Please don't suggest this pattern to other people.

Personally the system I work on day-to-day these days runs a Redis set-based queue similar to Resque to send a few thousand emails per second and I'm ok with it. Not thrilled, but happy enough that I don't read the Resque introduction text and blanch in horror as I did reading your article, especially as a reply to Baron's which is based on... lots and lots of real world experience with many different applications.

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

#60

Post by mbirk »

pkteison wrote: post #54
mootothemax wrote: post #46

Earlier quoted context omitted.

I've googled, but am seeing the same kind of results that I see when I try "oracle deadlock", "postgresql deadlock" and "db2 deadlock." Still being relatively new to InnoDB, what particular deadlock dangers make it more troublesome than other DBMS engines?

There are three possible ways that I know of to handle concurrency issues on shared data: Use some sort of a journaling approach (google keywords 'snapshots' or MVCC), or use locks, or ignore the problem. If it ignores the problem, it's not a database. Locks suck for volume. Locks cause much more deadlock than other options. Locks are fast in the simple case. Locks are easier to program and take less resources. InnoD…

By default, InnoDB doesn't acquire read locks when doing queries. It "runs queries as nonlocking consistent reads by default, in the style of Oracle." See http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-mo...
Post reply on HN