0

Sorry if this isn't the right place, this is my first time posting anything here. Anyway, I know I'm not supposed to but I can think of no other way.

I need to use a small MySQL table (70 rows x 6 columns) as a queue.

I'm writing a python application that requires a work queue to be shared between process windows (not sure what the proper name for them is).

Each job is repeatable, each use must be recorded and cleared at regular intervals (so each job is "weighted" so usage is evenly and fairly distributed). I attempted to base it on another work queue where each job is NOT repeatable, but it seems that multiple writes to the database (up to 19 at once per 12 seconds) are not properly being counted up.

Is there an alternative to doing something like this?

Perhaps some kind of cache sitting between python and MySQL that would convert many single "job + 1" updates into a singular "job + 19"?

I assumed being on a shiny new NVMe drive and a more than sufficient buffer_pool_size would make it plenty fast enough to handle that, but instead of counting up properly, over the course of 60 seconds it may reach 9, instead of 100+ where it should be.

2
  • MySQL should be able to do several thousand updates per second to your table without problems. So you should not worry about converting several updates to one to reduce a load of 1.5 update/s. Unfortunately it is not really clear what you are trying to do, and you didn't share any code (and it is most likely you did something wrong in your code), but it sounds like a transaction problem (maybe you are not (correctly) using transactions, or maybe your code looks like: read a value from database to a variable in php, do something, write that variable + 1 back to the database).
    – Solarflare
    Commented Mar 8, 2017 at 18:58
  • The code I currently call to update the database (this is Python's Peewee by the way). The table populates the jobs with varying maximum targets, and the remaining column (Jobs.remaining) is simply how much OF said maximum remains until it's considered 'empty', and resets to the maximum every 60 seconds. query = (Jobs .update(remaining=Jobs.remaining - 1, last_updated=datetime.utcnow()) .where(Jobs.id == id)) query.execute() EDIT: I can't seem to get it to format properly, hopefully that's readable enough Commented Mar 8, 2017 at 19:32

1 Answer 1

0

"Don't queue it, just do it". -- This is my advice on using queuing mechanisms in MySQL. The effort to enqueue and dequeue small tasks is often more than simply doing the task. However there are exceptions...

Is this a correct understanding of your "queue"? You have exactly 70 tasks and they need to be done at times based on various external criteria. That is, it is not really a typical queue, which can be thought of as "first in, first out". However, sometimes you may have several of the 70 ready to go, but not enough resources to do all at once. ??

In that case, let's call it a "task status" table. Dozens of updates per second feed status into the table. Worker(s) issue a SELECT to see what to do now. If the computation is too complex to do in a WHERE...ORDER BY...LIMIT 1, then simply readk all 70 and make the decision in a 'real' programming language. (Yes, I have implemented such.) Keep in mind that the focus of a database table is to hold raw data. If you can process the data in a SELECT, that is just a bonus.

"Transactions"... Keep in mind that transactions in InnoDB are not really designed to last more than a few seconds. So, if the tasks take longer than that, you need to 'assign' a task to a 'worker'. Better yet, have the 'worker' assign the task to itself. Here's one efficient way:

-- with autocommit=1; that is, no need for transactional semantics
UPDATE tasks
    SET worker = $me
    WHERE worker IS NULL
    ORDER BY ...
    LIMIT 1;
if Rows_affected() > 0, then I got a task, else none to get.
SELECT task_id FROM tasks
    WHERE worker = $me;   -- find out which task I got.

When finished:

UPDATE tasks
    SET worker = NULL
    WHERE worker = $me;

Beneficial:

INDEX(worker)

Sorry if I have rambled off in the wrong direction; perhaps there are useful clues anyway.

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.