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

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

#61

Post by iradik » Sat, Sep 17, 2011, 6:37 AM UTC

3am wrote: post #52

iradik wrote: post #35

Earlier quoted context omitted.

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…

i agree with your point. yet i've seen people make the same mistakes with sql too (they have autocommit=on haha).

hopefully whatever solution you have is tested and designed defensively so you don't accidentally rm the queue.

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

#62

Post by IgorPartola » Sat, Sep 17, 2011, 11:50 AM UTC

molesy wrote: post #59

IgorPartola wrote: post #58

Earlier quoted context omitted.

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…

Fair points. This solution works for me for now and I certainly know it is not limitless. The solution with setting selected_by is something I thought about and may implement at a later point. I also am a big fan of using GET_LOCK(), for locking rather than relying on MySQL/InnoDB's built-in locking since you have finer grained control over timeouts, etc.

I understand your concern about sharing this "dangerous" knowledge, but I disagree that the solution is to hide it in a deep dark place. Would you find it acceptable if I updated my post with a discussion on scalability and a link to TFA? That way a reader will get more information about building such systems, not less.

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

#63

Post by Limes102 » Sat, Sep 17, 2011, 1:44 PM UTC

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!

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

#64

Post by Limes102 » Sat, Sep 17, 2011, 1:46 PM UTC

mojuba wrote: post #47

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

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

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

#65

Post by pkteison » Sat, Sep 17, 2011, 4:02 PM UTC

mbirk wrote: post #60

pkteison wrote: post #54

Earlier quoted context omitted.

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

Wow, looks like I got that completely wrong w.r.t. innodb specifically. Wish I could still edit my first post. :( Thank you.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-rea...

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

#66

Post by molesy » Sat, Sep 17, 2011, 9:30 PM UTC

IgorPartola wrote: post #62

molesy wrote: post #59

Earlier quoted context omitted.

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…

Fair points. This solution works for me for now and I certainly know it is not limitless. The solution with setting selected_by is something I thought about and may implement at a later point. I also am a big fan of using GET_LOCK(), for locking rather than relying on MySQL/InnoDB's built-in locking since you have finer grained control over timeouts, etc. I understand your concern about sharing this "dangerous" knowl…

The point of the UPDATE .. SELECT if updated pattern is that it's a mark-and-sweep completely outside of a transaction. Avoid locking > lock as little as possible > lock as quickly as possible.

Your blog is your business, the Lorax speaks only for HN.. (hey is Internet Lorax a job?) It would of course be great to update your readers, it'd be cooler to update your application and tell everyone how it worked out! Then you've got a story you can actually submit again