I’ve been away from writing blog posts — the last one was more than two years ago. I decided to write one on this topic, however; I found out that implementing a queue was harder than it should be (in my opinion). Although Magento’s shenanigans don’t really surprise me anymore, the complexity of getting a simple queue up and running caught me (and my estimate) by surprise. There’s a few other blog posts on this topic already, but I found they don’t quite get into the nitty gritty that you potentially can get stuck on.

You might be the first person to mention simple and async in one sentence.

— Fooman (@foomanNZ) July 8, 2019

That said, today I’m going to write about how to create a queue that does additional order processing using Magento 2 and MySQL only — without RabbitMQ.

Why …

Every so often you need to use a queue to manage operations in an application. Python makes this very simple. Python also, as I’ve written about before, makes threading very easy to work with. So in this quick program I’ll describe via comments, how to make a simple queue where each job is processed by a thread. Integrating this code to read jobs from a mysql database would be trivial as well; simply replace the “jobs = [..." code with a database call to a row select query.

#!/usr/bin/env python
## DATE: 2011-01-20
## FILE: queue.py
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
from Queue import *
from threading import Thread, Lock

'''this function will process the items in the queue, in serial'''
def processor():
    if queue.empty() == True:
        print "the Queue is empty!"
        sys.exit(1)
    try:
        job = queue.get()
        print "I'm operating on job item: %s"%(job)
        queue.task_done()
    except:
        print …

Kazuho Oku of Cybozu Labs, Inc., a community contributor to MySQL and SCA signatory, gives a talk on Q4M, a message queue stroage engine for MySQL.

Kazuho Oku of Cybozu Labs, Inc., a community contributor to MySQL and SCA signatory, gives a talk on Q4M, a message queue stroage engine for MySQL.

Kazuho Oku of Cybozu Labs, Inc., a community contributor to MySQL and SCA signatory, gives a talk on Q4M, a message queue stroage engine for MySQL.

It’s been a few months since we‘ve started actively using ActiveMQ queue server in our project. For some time we had pretty weird problems with it and even started thinking about switching to something else or even writing our own queue server which would comply with our requirements. The most annoying problem was the following: some time after activemq restart everything worked really well and then activemq started lagging, queue started growing and all producer processes were stalling on push() operations. We rewrote our producers from Ruby to JRuby, then to Java and still – after some time everything was in a bad shape until we restarted the queue server.

So, long story short, after a lots of docs and source code reading we’ve found really interesting thing. There is a “feature” added in the recent ActiveMQ release …

Few months ago I’ve switched one of our internal projects from doing synchronous database saves of analytics data to an asynchronous processing using starling + a pool of workers. This was the day when I really understood the power of specialized queue servers. I was using database (mostly, MySQL) for this kind of tasks for years and sometimes (especially under a highly concurrent load) it worked not so fast… Few times I worked with some queue servers, but those were either some small tasks or I didn’t have a time to really get the idea, that specialized queue servers were created just to do these tasks quickly and efficiently.

All this time (few months now) I was using starling noticed really bad thing in how it works: if workers die (really die, or lock on something for a long time, or just start lagging) …

I created MySQL Table Checksum because I was certain replication slaves were slowly drifting out of sync with their masters, and there was no way to prove it. Once I could prove it, I was able to show that replication gets out of sync for lots of people, lots of times. (If you really want to hear war stories, you should probably talk to one of the MySQL support staff or consulting team members; I'm sure they see this a lot more than I do).

I finally figured out what was causing one of my most persistent and annoying out-of-sync scenarios. It turns out to be nothing earth-shaking; it's just an easy-to-overlook limitation of statement-based replication. You could call it a bug, but as far as I can see, there's no way to fix it with statement-based replication. (I'd love to be proven wrong). Read on for the details.

A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL's GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.

This post was prompted by a message to the MySQL general emailing list some time ago, but I'm finally getting around to actually testing the theoretical solution I mentioned then. I can never just think my way through anything that involves locking and waiting... I have to test it.