6 ms·
I once used a MySQL database as a replacement for a message queue. This was the easiest solution to implement since all the servers were already connected to th
by tschumacher 3y ago
I once used a MySQL database as a replacement for a message queue. This was the easiest solution to implement since all the servers were already connected to the database anyways. A server would write a new row to the table and all the servers would remember the last row they had already seen. Occasionally the table is cleared. I'm sure there are some race conditions in the system but its only purpose is to send Discord notifications when someone breaks a highscore in a video game, so its not really critical. It's still working that way today.
- _yb2s 3y agoWhy not sqlite with a lockfile? /s
- renewiltord 3y agoSegment did so quite successfully https://segment.com/blog/introducing-centrifuge/ https://segment.com/blog/introducing-centrifuge/
- pinkcan 3y agoevery half year or so I remember centrifuge, and get a little sad they didn't write more about it
- hu3 3y ago> We decided to store Centrifuge data inside Amazon’s RDS instances running on MySQL. RDS gives us managed datastores, and MySQL provides us with the ability to re-order our jobs. Interesting. Thanks for sharing
- bob1029 3y agoI am doing the same with SQL Server. The messages table is more of a bus than a queue in our case (columns like ReplyToId, etc). Using it for RPC communication between cloud bits. Much cheaper than Azure Service Bus and friends.
- deleted 3y ago[deleted]
- Digit-Al 3y agoJust out of interest: any reason you're not using the SQL server service broker?
- bob1029 3y agoIt's only available in managed instances or on-prem. We are trying to use the "serverless" flavor of Azure SQL throughout: https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql https://learn.microsoft.com/en-us/azure/azure-sql/database/f...
- Digit-Al 3y agoAh, okay. Cheers for the reply.
- nickpeterson 3y agoI don’t know the OPs answer, but I’d hazard to guess because ssmb is a completely neglected feature with very little in the way of community or UI. In theory it would be great, but MS basically never invested in it after its release and now it’s just a random, “who knows when we’ll drop support for this” sql server feature.
- Ozzie_osman 3y agoThis is actually pretty common, and usually a "good enough" solution. You can also add things like scheduling (add a run_at column), at least once execution (mark a row when it is being processed, delete it only when successful), topics, etc with minor modifications to your table. If you want something that works "well enough" I'd say it's a reasonable choice.
- larperdoodle 3y agoYeah, I'm using it as a transactional outbox to ensure at least once delivery to SNS. Can't really think of a better way to ensure that a message is always sent if the DB transactions succeeds and is never sent if the DB transaction fails
- Ozzie_osman 3y agoYou can get so far by ensuring at least once and making everything idempotent (will get you as close to "exactly once" as you can). With a database, the most common pattern is: insert the row for the job, when a worker starts working on it, mark it as in progress so it doesn't get started again, if the task fails, or after some reasonable time-out period, another worker can pick up the task again, ultimately the row for the task is only ever deleted when a worker successfully completes it.
- deleted 3y ago[deleted]
- hardwaresofton 3y agoSee also: postgres with SKIP LOCKED. Great talk on it from Citus Con 2022: Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 -- https://www.youtube.com/watch?v=WIRy1Ws47ic&list=PLlrxD0HtieHjSzUZYCMvqffEU5jykfPTd&index=20 https://www.youtube.com/watch?v=WIRy1Ws47ic&list=PLlrxD0Htie...) Citus is a horizontal scale out extension for postgres -- imagine the power of those two things together! JK you don't have to imagine: https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-postgresql/ https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partm...
- andrewstuart 3y agoI wrote an implementation in Python. https://github.com/starqueue/starqueue https://github.com/starqueue/starqueue The code is in there for Postgres, MS SQL and MySQL (which all support SKIP LOCKED) though at some point I abandoned all but Postgres. If I was to write another message queue then I wouldn’t use a database, I’d use the file system based around Linux file moves, which are atomic. What I really want is a message queue that is fast and requires zero config, file based message queues are both…. better than a database.
- gchq-7703 3y agoInteresting. What makes you want to switch to the file system? I wrote one for a project[0] a while back (for MongoDB) and it didn't seem like the database introduced too much complexity. I didn't write the implementation from scratch, but the couple hundred lines of code were easy to reason about. [0] https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue https://github.com/gchq/Bailo/tree/main/lib/p-mongo-queue
- deleted 3y ago[deleted]
- andrewstuart 3y agoThe filesystem means zero configuration. I found almost all message queues to be horribly complex to configure, debug and run. Even database queues require a lot of config, relative to using the file system. I did actually write a file system based message queue in Rust and it instantly maxed out the disk at about 30,000 messages a second. It did about 7 million messages a second when run as a purely RAM message queue but that didn’t use file system at all. It depends what you’re doing of course… running a bank on a file system queue wouldn’t make sense. A fast message queue should be a tiny executable that you run and you’re in business in seconds, no faffing around with even a minute of config. I just hate configuration in general.
- moron4hire 3y agoI briefly worked for a major corporation 15 years ago that did this with SQL Server to create distributed worker processes to handle all the AI-generated used car listings and photo recolorings [0] for almost all of the used car lots in the country. [0] Why take hundreds of photos of Honda Civics in red, green, blue, and black when you already have a dozen in white?
- dylan604 3y agoWhy even take the dozen in white when they have a model you can render in any manner? Most car commercials do not have real cars in them. Maybe the shots of a car actually in motion, but most of the static shots are 3D models placed onto backgrounds. I don't know why, but I was surprised by this when I worked in a post house that did a lot of car commercials. One of the roles for a coworker was to get flown around to locations to take the images for the background plates using photogrammetry. "Can't fly an Alexa through the back glass to zoom in on the dash now can we" was one comment.
- moron4hire 3y agoAgain, 15 years ago, this was a web app company, and Honda ain't giving any models to no used care sales anything.
- scarface74 3y agoI’ve run into all sorts of database locking issues and concurrency issues when using a database as a queue. I saw that mistake made a long time ago and I would never do it myself.
- wolfgang42 3y agoDatabase engines are getting features like SELECT FOR UPDATE SKIP LOCKED, so what were once serious blockers on this idea may no longer be as much of a problem.
- scarface74 3y agoAnd if you skip records that you are depending on for your poor man’s queue, aren’t you just hiding bugs?
- Spivak 3y agoNo? Unless there's some edge case with that statement I don't know about. That statement is basically tailor made for queues so you can select jobs that aren't currently being worked on by other workers. Inasmuch as you trust your db's locking correctness it eliminates the concurrency issues. You can very naively have n workers pulling jobs from a queue not stepping on each-other.
- deleted 3y ago[deleted]
- wombatpm 3y agoIs that necessary if you can just have your processes handle locking themselves?
- wolfgang42 3y agoIt’s not necessary, but it is a lot less fiddly: you automatically look at only the tasks that someone else isn’t currently working on, and because the lock is held by the database connection you get automatic retries if your worker crashes and drops the connection. You could figure out all of the interactions needed to make this work yourself, but if the database already has support built in you may as well use it (and there’s a straightforward path to migrate if you need more sophistication later).
- aa-jv 3y agoThis has been a thing since before databases were relational. 4G languages (Progress, etc.) were especially nice for their ability to wrap a queue table around a series of reversible transactions, if you coded things right .. meaning a lot of modules written for app infrastructure were based on an 'inbox table' methodology ..
- rollcat 3y agoI've built a hybrid task queue/process supervisor on top of SQL. Classical task queues like Celery didn't exactly fit our use case: a single process could run for hours or days, but in case of a node failing, it must be resurrected elsewhere as soon as possible (within seconds). I didn't have the time to re-architect everything for Kubernetes, or rewrite half the product in Erlang; so I built that weird thing. It's been super stable, running mission critical code, and making us money - for several years now.
- wombatpm 3y agoI’ve yet to find the project where Celery is the best solution, despite using it on several
- waplot 3y agoNothing wrong with using the DB as a mq, especially if the load is small enough. Plenty of tools are built on that, these two come to mind https://github.com/procrastinate-org/procrastinate https://github.com/procrastinate-org/procrastinate https://github.com/bensheldon/good_job https://github.com/bensheldon/good_job
- avereveard 3y agoNuxeo unironically used a db table as a pub sub system between the cluster nodes for cache invalidations.
- ljm 3y agoIt’s not so out of the ordinary. A few libraries in Rails create message queues in Postgres using advisory locks and listen/notify. Hell, if it’s not an RDBMS then it’ll be Redis (at a much greater expense for a managed instance). I’ve seen that setup in the Ruby world far more often than using a dedicated message queue.
- glun 3y agoIf you don't want to publish events from uncommitted transactions you'll have to first store them in a local table and then move them to the queue after the commit. But if all consumers have direct access to the database anyway...
- takinola 3y agoI implemented a message queue in MySQL too and it worked pretty well. Incoming messages would be written to the table and the workers would poll the database each cron period and process whatever rows were in the queue. To avoid race conditions, the workers would lock the records they were working on and then delete them as soon as the work was complete. It was simple but it worked just fine for my purposes