1

I have a MySQL(version 5.7.14-8) table that essentially acts like a queue(it's a legacy system) that has the following schema:

CREATE TABLE `myqueue` (
  `queue_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key identifying the queue event',
  `queue_insertiontime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The time when this queue event was inserted',
  `tracking_key` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`queue_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12819839853 DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (queue_id)
(PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

On the producer side we just insert rows into the database as soon as we get data for them. On the consumer side we consume in batches, finding the max id using

SELECT * FROM myqueue ORDER BY queue_id ASC LIMIT 1

And then:

SELECT * FROM myqueue WHERE queue_id >= (last processed queue id) AND queue_id <= (max we got before) ORDER BY queue_id ASC LIMIT 1000

Most of the time this system works fine, but sometimes we seem to "skip" rows, although upon subsequent queries those rows exist.

For example, let's say we have rows with queue ids 1-5 that are written one right after the other, if we query very close to the time the rows are written sometimes we will only see rows 1,2, and 5. Rows 3-4 eventually show up, but they aren't there when we first query. We have the default transaction isolation level on. Is there something in the MySQL internals that would allow us to query row 5, but not 3-4 even though the ids for those rows have obviously been reserved? How do we ensure that when we query like this all the rows are available before we query?

2
  • 1
    The rows are inserted by different sessions. One session gets ids 3 and 4, the other gets 5, 6 and 7. Sessions two succeeds (commits) first, you see ids 5, 6, 7. Next time you check, session one has committed, now you see ids 3, 4. Nothing strange, it all behaves as expected. Commented Jun 24, 2021 at 9:05
  • You won't get the max id with this by the way: SELECT * FROM myqueue ORDER BY queue_id ASC LIMIT 1 ; I suppose you meant DESC Commented Jun 24, 2021 at 9:09

1 Answer 1

1

Building a Queue with InnoDB is a nightmare, especially if Replication is involved.

One of my mantras is "Don't 'queue it', just 'do it'." In many 'worker' setups it is actually better to launch workers to do the tasks than to line them up in queue. This is especially the case when the tasks don't take long to run. With 12 billion items, it really sounds like they are 'fast'.

AUTO_INCREMENT may be part of the problem that you are encountering. Note the following:

  • A 'next' id is taken from the table when the INSERT starts running. It is not visible until the entire transaction is COMMITted.
  • In Replication, ids may be taken in one order but COMMITted in a different order. This says that the Replica cannot "remember where it left off" via the id. This may be sufficient to explain the problem you encountered.

myqueue has only one PARTITION? This seems like a performance problem, or at least not a benefit.

For long-running tasks, it is better not to hang onto myqueue for the duration. Instead, use a more complex mechanism:

  1. "check out" -- single SQL to atomically flag the queue item. This can be an UPDATE that checks that no one else checked it out and flags it with the worker_id and a starting timestamp.
  2. "process" -- worker takes as long as needed
  3. "clear" -- another atomic SQL statement, presumable a one-row DELETE.

But that needs a "reaper" job to take care of any worker-crashes that left unfinished jobs checked out.

If inserting a task in the queue takes a long time (eg, due to lots of associated data being INSERTed in ancillary tables), then this messy sequence may be needed:

  1. INSERT the task info into other tables. Capture a task_id so that the worker can locate it. Take as long as you need; don't worry about auto_inc ids, etc.
  2. As a separate transaction, do an atomic INSERT of task_id into myqueue. Hopefully, the Auto_inc queue_id of this table is provably consecutive.

If you would like to provide more info, I might have some further refinements. In addition to discussing the app, please provide SHOW CREATE TABLE, table size, Partition rationale, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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