Posted on 15 Comments

Challenge: build a queue/IPC in standard MySQL without “polling”

Following scenario… you want to pass on some basic info to another task, through MySQL, and the other task can just wait for it – naturally you don’t want to poll (keep issuing a query to see if there’s anything new) as that’s hideously inefficient even if you use the query cache.

Rules: standard MySQL (no special storage engines); and stay within MySQL, no other tools.

I have some thoughts on this, for instance using the behaviour of InnoDB with isolation levels and locking… can be engineered to either acquire a lock (after waiting for someone else to release) or timeout, where the timeout can be set so you don’t have to poll all the time… but curious if anyone might have more nifty ideas!

Posted on 15 Comments

15 thoughts on “Challenge: build a queue/IPC in standard MySQL without “polling”

  1. Isn’t abusing triggers the standard way to do this?

    And if you want to be evil you make it so the trigger launches the mysql client against your waiting host, to act as a ping, but sticking to the letter of your request.

  2. Lack of “select … for update nowait” or “select … for update wait n” or “select … for update skip locked” is a real problem for this kind of stuff.

    IMO, the main issue is not even the polling, but rather the lack of scalability due to locking. Dequeuer processes will lock each others on the first item, even when there are plenty of items to process in the queue.

    My suggestion would be to poll the queue with an exponential sleep time (limited to n secs), and categorize the items in the the queue by using some hashing mechanism on the item primary key. (i.e. so you can run several dequeuer processes in parallel with a bit less of contention, 1 per category).

  3. I used KILL QUERY as event notification in production code. The receiver has to open a second session in a thread that does nothing but SLEEP 3600 in an infinite loop and signals the main thread that something happened, everytime the query ended (this way you have a built in time out mechanism, but is seems to work because we would notice that). The main thread find the event’s content in a table, after all pending events are handled the main thread has to poll for events during the dead time until the SLEEP is active again and once more. The signaling back works the same way, the event is marked as handled in the event table and the client’s QUERY is killed (another SLEEP of course, but here I used growing intervals between 1 and 60 seconds, because the wait time is not so long and for safety reasons the killing is turned off if something strange happened). If the client doesn’t want to wait, it can update the event table to switch this off.

    strcmp

  4. Having a trigger does not remove the need for the other task to poll, as there’s no callbacks towards the client.

  5. Very interesting!

  6. Hi!

    “The other task can just wait for it”

    Do you mean, sit idle? In that case, the working task could do a

    GET_LOCK(‘task1’, )

    and the other session could also do

    GET_LOCK(‘task1’, )

    The second task would resume as soon as the first one releases the lock with say RELEASE_LOCK(‘task1’)

    kind regards,

    Roland Bouman
    http://rpbouman.blogspot.com/

  7. I typed GET_LOCK(‘task1’, [large-enough-timeout]) but with angle brackets which are ruthlessly filtered out…

  8. Ye know about those, but they’ve proven to not always work reliably… I forget what the problem was but people have had issues with them. Not recommended foo.

  9. “Ye know about those, but they’ve proven to not always work reliably… I forget what the problem was but people have had issues with them. Not recommended foo.”

    so you’re changing the rules afterwards? :p

  10. I’d also be really interested in the problems you’ve seen with a locking approach. I’ve used it on a number of projects with a single producer/many consumers without any problems.

    There *can* however be a hit when multiple producers push messages onto the same queues, producer #1 can cause a delay in consumers receiving messages from producer #2 or vice versa. Maybe this is the issue you were thinking of? In that case, I guess it’s a matter of setting up your messaging architecture with unique lock names from each producer and configuring your consumers to listen accordingly.

    Baron posted a suggested algorithm here – http://www.xaprb.com/blog/2007/08/29/how-to-notify-event-listeners-in-mysql/ – which is more or less the approach I took without major hiccups.

  11. Personally, I would not use the database as a messaging service. There are better things which exist specifically for that purpose.

    But if I were forced to use the database as the messenger, I would not use “standard MySQL”.

    I would use XML-RPC stored procedures which are invoked from triggers.

    Cheating? Well, I am not using a special storage engine 🙂

    If you really wanted to stick with a stock MySQL, I suppose that there are ways to abuse the InnoDB pessimistic locking:

    The sender first prepares by beginning a transaction and then inserting a row into a table. It then tells the receiver of the PK value.

    The receiver then tries to insert a row into the same table with the same PK value. It would then block.

    To “send” the message, the receiver would then update its row and commit. At commit, the receiver will have its insert operation aborted with dup PK error. It can then abort the transaction and read the row.
    If the receive’s insert operation returned successful, then the sender probably timed-out. rollback and start again.

    If the storage engine uses optimistic locking, then you cannot use this technique. I believe that Solid/Amira/Gemini are in the family of optimistic locking storage engines.

  12. Hi Roland,

    The GET_LOCK() solution as you presented it only works one, and assumes the publisher acquires the lock before the subscriber.
    I assume that by asking for a “Queue” the request is to infinitely do this round trip.
    For example, once your subscriber obtains the lock – what does it do next? Will it release it, try again to acquire it? It may win it over the publisher, in which case we’re at deadlock.

    Regards,
    Shlomi Noach

    PS Arjen – the Captcha is extremely difficult.

  13. Jacob Nikom does what you’re looking for. You may find some details in a presentation he gave at the User’s Conference in 2007 (and also at the Boston UG). The description is here:

    http://www.meetup.com/mysqlbos/calendar/5563329/?from=list&offset=0

    and the presentation and video is here:

    http://technocation.org/content/using-mysql-active-dbms-monitoring-applications-april-2007-boston-user-group

  14. Hi Shlomi,

    I should say I focused on the literal text in the body of Arjens post, “you want to pass on some basic info to another task, through MySQL, and the other task can just wait for it”, not so much on the title (which seems to hint at implementing a full blown queue indeed).

    As for GET_LOCK, I can’t say I thought it through to the full extent, but my gut feeling is that the problems you pose should not be to hard to work around.

    I don’t mind spending more time thinking about a solution but I think a better specification of the problem is required, as well as any constraints that are to be imposed on any solution.

    kind regards,

    Roland

Comments are closed.