I'm working on a system which reports certain events to an external service. Whenever a relevant change is made in the database, we want to guarantee that at least one message is sent to the external service.

I'm thinking this could be implemented with a message_queue table as follows:

  1. BEGIN a transaction
  2. Make the relevant database change
  3. Insert a message about the change into the message_queue table, including a UUID
  4. COMMIT the transaction
  5. Later (eg on a cron job), iterate through the message_queue table, attempting to send each message to the external service. Whenever we get a successful response, delete that row. When we don't, try again later. If the external service actually received that message the first time but the reply was lost, it can discard it based on the duplicate UUID.

Assuming we can send and delete message_queue rows faster than they are created, this table would tend to have very few rows at any moment.

Given this usage pattern of frequent inserts and deletes, I think we would not want to index the table. Is that correct? What else could we do to minimize the impact on overall database performance?

  • Does the "relevant database change" is always a point one (only one record per table is changed) or it can be bulk? If the first the trigger logic can be more suitable. I think we would not want to index the table. Is that correct? The only moment which can need in index is record deletion after successfull message sent. Synthetic primary index is enough for it, so no another indices needed. – Akina Apr 23 '19 at 5:00
  • The relevant database change could be more than one record, and we also want to capture information the database wouldn't have access to in a trigger (eg, the user performing the web request that caused the change). – Nathan Long Apr 23 '19 at 19:44
  • we also want to capture information the database wouldn't have access to in a trigger ??? Database have no access to trigger which is defined in database on the table which data is altered??? I don't even know what to tell You... – Akina Apr 24 '19 at 5:25
  • @Akina Maybe I didn't explain well? A request comes in to a web app. In the request parameters and session info we can see (eg) that User 5 performed a PUT to /widgets. We don't insert that kind of request info into the database. We can also see (by querying the db at the start and end of the request) that during this request, the widget row was updated. We currently capture both pieces of info in a message that's sent directly to an external system, but could be lost en route. To prevent loss, we could instead insert the message into a table as part of the original transaction. – Nathan Long Apr 24 '19 at 17:09

My first encounter with "using MySQL as a queue" ended in disaster. The team was very wedded to tossing things into a table, then pulling it out to work on. They were limited to how many things the could achieve per hour.

After studying not only the queuing mechanism but the enqueue and dequeue API and the worker threads, I decided on:

"Don't queue it, just do it".

I estimated (for their case) that they could increase the throughput 10-fold by removing the queue.

Here are some random lessons:

  • If the task to perform is fast enough, you may be spending more time on enqueuing/dequeuing than the task.
  • If the tasks arrive in a steady stream, the "buffering" that queuing gives you is unnecessary. On the other hand, if the tasks are "bursty", queuing may be beneficial.
  • Replication (HA was a requirement in the case) significantly complicates the queuing code.
  • Using AUTO_INCREMENT for queuing with replication adds a complication because the ids don't always arrive in the Slave in order! This happens randomly (but not very frequently) with InnoDB.
  • Deletion of queue items leads to fragmentation. This was especially bad with MyISAM.
  • Note how switching Engines solves one problem but creates another?
  • UUIDs are terrible for performance if the table gets bigger than can be cached.
  • Transactional integrity gets messy -- the item in the queue is mostly independent of the task it represents. Issues to resolve: requeuing a task that fails; crashing in the middle of a task; etc.
  • Sure, the queue will have very few rows at any normal moment. But there will come a time when something hiccups and there is a million tasks queued up. The system will croak for any of several reasons that you failed to plan for. You have a crisis on your hands and the looming job of analyzing and planning for such eventualities.
  • Based on my previous comment, do you really want to leave off any index? No. The dequeuing mechanism will get slower and slower, further exasperating the crisis.
  • If a queued task is assigned to a worker, but that worker crashes, then the item needs to stick around somewhere (in the queue or some other place) and some separate task needs to eventually discover an unfinished task after some timeout. More messy code to write.

An alternative (that may or may not apply to your situation): Leave information around and have a continuously running job (not a cron) that looks for the info and acts on it. This can be handy if the items arrive rapidly and they can be processed in batches.

  • Thanks for the lessons! As for "Don't queue it, just do it" - this won't work if you need a guarantee that the task gets done at least once. – Nathan Long Apr 29 '19 at 17:58
  • @NathanLong - That brings up another issue; I added another bullet item. Would you keep that job-in-progress in the queue? Or somewhere else? You can't trust the Worker, it could crash without notification. – Rick James Apr 29 '19 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.