How bad is it, really, to use InnoDB table as a queue?

Question

I have a product which uses two separate tables as queues for some processes. Generally speaking:

1. Interesting events occur in the application, and we write a record to the appropriate queue. That "queued" record points to another table whose records are a long-lasting record of the thing having been processed.
2. A background process comes along and processes the queue later, marks the job as "complete" and deletes the record from the queue.

This setup gives us several advantages over simply having the application do whatever the queue-processor is going to do:

1. If the action to be taken is long-running for some reason, the application need not be blocked.
2. If the action fails, it can be re-tried the next time the queue-processor runs.
3. The queue-processors can be multi-process/thread is desired.

Mostly I'm just describing reasons to use queues above, it of course has nothing to do with using the db as the underlying structure.

We are looking at "advantage #3' above a little closely, and expanding our queue-processors to be not just multi-process but also multi-server which means we need to be a little more careful about how we process that queue, because each item must only be successfully processed once. Two or more times is generally not okay.

So one of the questions we naturally asked ourselves was "well, should we even be using the database for this queue?"

That got me to thinking about how we use the queue, and why we might not want to do it this way. Briefly:

• SELECT id, queued_thing_id FROM queue WHERE [fairly general WHERE clause] FOR UPDATE
• For each record:
  • Do stuff
  • UPDATE queued_things SET status='complete', completed_date=NOW() WHERE id=[queued_thing_id]
  • DELETE FROM queue WHERE id=[id]

Potential reasons not to use a db:

• "This is wasteful."

  No it's not. The records are very compact.

• "Yeah but the table will grow forever."

  No it won't. Every DELETE makes room for another row. We aren't talking about millions of INSERTs and DELETEs at a time, here. Okay, maybe we'll have two whole db pages allocated all the time, but it's not like the file will grow forever just because the data are being cyclicly added and removed.

• "There are other products that are better at this."

  Sure, but we aren't using those products at this point. This is a working solution that does not seem to have any significant negatives.

So, it all comes down to whether or not we have correctly assessed the "risks" of using an InnoDB table as a queue. Other than "relational databases aren't a great choice for a queue because other products are better," are there any specific technical reasons to abandon a working solution based upon MySQL/MariaDB/InnoDB? I don't even think we need to periodically run an OPTIMIZE TABLE on these queues because they ought to maintain themselves, right?

Update 2022-11-21

As requested, some details:

1. We gather tasks based upon human actions, and we don't have enormous volume like Twitter or whatever. Several dozen tasks per minute are being queued and we run the queue-processor once per minute.

2. Tasks are indeed idempotent. They either totally succeed or totally fail, and are re-tried on the next attempt.

3. We stop trying after several attempts to avoid repeatedly-processing the same failing tasks.

4. We are not currently setting innodb_lock_wait_timeout for these queue-processors (we currently only have a single one, and would like to add a second one to simply not have a single point-of-failure), but we should probably go ahead and set that to some relatively low value, because if a processor is actively running, there is no need to rhave another one waiting on a lock. It can simply go back to sleep and try again later.

Given the above, I think while this is not a "toy project", it's still well within the realm of being "okay until it's not." Introducing a new paradigm and new software to the mix (on short notice) are probably not (yet) warranted.

I very much appreciate everyone's thoughts on the matter.

Answer 1

There are several downsides to using an RDBMS to implement a queue.

The first is polling queries. Your queue processor needs to run a query at regular intervals to check if anything is in the queue. How frequently should this polling query run? It depends on how promptly you want the processor to notice the presence of a queued item, and also on the throughput you need to keep up with the rate of new enqueued items. But the polling queries need to run all the time, just in case something is added to the queue.

If you have multiple processors, then each of them has to run its own polling queries against the database.

The worst case I observed was a deployment with queue processors on many application servers, all polling constantly, several times a second. The result was that the database had to serve 3,000 queries per second just for the polling by queue processors. Each query is individually not a big deal, but trying to serve 3k of them per second was causing a lot of load on that database server.

The second problem was locking. You need to come up with a complex algorithm to ensure only one client processes each item. This involves locking the row FOR UPDATE as you describe. In the scenario where you have multiple clients, this can cause lock contention. They end up waiting for each other to release their locks on the queue, and thus the parallelism is limited.

You mention using DELETE to remove items from the queue once they are finished. In theory, InnoDB reuses pages if it can. In practice, this causes progressive fragmentation in the file. Not every page is filled efficiently, so new pages gradually are added to the tablespace. InnoDB doesn't do any automatic defragmentation. This is what OPTIMIZE TABLE or TRUNCATE TABLE do (actually, they don't do defragmentation inplace either, they just make a new tablespace and drop the old one).

Some of these problems are mitigated if you use a kind of dispatcher architecture. That is, you only have one processor polling the queue. Then you don't multiply the queries per second by the number of workers. And you don't have lock contention, because only one client is pulling from the queue. When that dispatcher finds an item to work on, it delegates the work to one of the unoccupied workers, and resumes watching the queue.

Still, it's better to use a real queue service. This does not require polling or locking. The client applications each make one call to the queue, to request an item. If there is no item in the queue, the call blocks until there's an item in the queue (or there may be an option to time out). Once there is something to return from the queue, this call returns that item to the client immediately. This also guarantees against multiple clients receiving the same item from the queue.

So back to your original question: "how bad is it?" Well, as you have found, it works fine ... until you need to process the queue at a high rate. Eventually you need to run more polling queries than the database server can handle. Or there's so much lock contention that the queue processors can't get their items to work on in a timely way.

So it's fine for toy projects, not so good for high-scale projects.

Answer 2

Describe your tasks more.

In general, you will get quagmired in edge cases. Good luck.

My general answer is "Don't queue it, just do it."

So, as your app inserts into the "history table", spawn a process and give it the id of that entry.

In either case, you should have a "reaper" task that looks around for tasks that have not been handled in some [long] amount of time. Don't simple rerun, it may have crashed with a software bug, and rerunning will crash again. (This is one of the edge cases.)

Whether tasks are queued or immediately spawned, will there be a case where "too many" tasks are running and the system is slowing to a crawl? This, too, is an edge case in either method.

In the Queuing method (and you choose not to multi-process), can it happen that the backlog keeps growing and you never catch up?

When a process "checks out" a task to work on, you should write to the table both the time and which process has the task. Do not start an InnoDB 'transaction', you may need to run for too long before COMMITting. That is, you must build your own transaction-like thing outside of InnoDB's transactions.

Whenever possible, make tasks "idempotent" -- Rerunning the task won't hurt. This avoids some edge cases. (Think of the trivial task of UPDATE Likes SET likes = likes + 1 WHERE ...)

Be sure to include the optimal INDEX (possibly 'composite') for whatever table(s) are needed.

Here's an edge case to keep you up at night: Something goes wrong, leading to 100K items being queued. (PS -- That should be the only case where OPTIMIZE TABLE is needed.)

More

Is there any way to find out how much "wastage" there is in an InnoDB table? -- No.

That is, is it possible to detect whether OPTIMIZE TABLE is actually necessary (or will provide any benefit) beforehand? Or even afterward? -- No.

However, ...

• There are many things that count as 'overhead' or 'wastage', Data_free is only one of them. There are no easy tools for measuring the rest.
• Turn on innodb_file_per_table before first creating (or ALTERing) any table that will ultimately be big. This helps avoid an ever-increasing ibdata1 file for which the remedy is very painful.
• Avoid big DELETEs. Here are several techniques related to that: Big DELETEs . For a time-series (where you purge 'old' rows) is best handled by a certain flavor of PARTITION
• This formula usually comes close to estimating how big an InnoDB table should be. Add up the average size for each column (8 bytes for BIGINT, avg length for text (+2) for char/text/blob, etc.); then multiple by 2 to 3 to get a range for expected table size.
• Once you have improved any big DELETEs, never run OPTIMIZE again.