Implementing a Long Job Queue in MySQL + PHP

Question

Message Queues

There is a certain queue of tasks (> 1M). It is supposed to store the job queue in a MySQL table.

New tasks are marked with the NEW flag.
Each task from the queue is executed by a certain script handler for about 3 minutes.
After execution, the task is marked in the queue with the READY flag.
In case of an error or abnormal termination of the script, the task must be repeated.

You need to run several scripts in parallel.

Question1: How to show other script instances (which run in parallel) that the task is in progress, so that in case of an abnormal completion, the record in the database is rolled back to its original state, i.e. NEW?

I have this solution
a) run the handler script -> mark the task as PROCESSING + write the time
then
-> if everything is fine, then the task becomes READY
-> if there is an accident, then the task remains PROCESSING

b) Create another script that checks tasks for PROCESSING status for more than 10 minutes and resets it to NEW.

So the hung tasks will be launched again + you can fix the number of attempts.

Question2: Is there a more elegant solution?

Answer 1

Why not use MQ for the queue, for example the same RabbitMQ . It is both nimble and capable of several parallel workers

Answer 2

Yes, you need to use multithreading. PHP does not have native multithreading support, but there is a “crutch” described here phplens.com/phpeverywhere/?q=node/view/254

Answer 3

Item b - creepy crutch. You can wrap the query execution in a transaction and an exception. Already in the exception you change the status to some fatal and then you can hang a demon looking for fatals and processing them. If processing is not required, you can simply change the status to new and add one to the fatality counter.

Answer 4

If you run tasks with cron, then multithreading will be organized automatically.
On the question itself: Perhaps it is worth reworking the script itself so that it can correctly process at least some of the errors (and if possible, all of them) and can set the NEW status on its own. And use your decision as a backup plan :)

Answer 5

Job Queue is well implemented with Pub/Sub in Redis

Answer 6

We are using gearman in a similar situation .

Answer 7

Radishes are generally good for this. For lightweight options, I personally prefer BLPOP + RPUSH - blocking pop on the left, task replenishment on the right. Well, or vice versa. In its purest form, a FIFO queue, plus workers can easily return the task back through the same RPUSH in case of a temporary error. BLPOP returns both the key it popped from and the value itself. So it is convenient to differentiate the tasks themselves.
Question 1 - With blpop, put the key with the task identifier somewhere. In hash or just in casespace. Rollback - through transactions, of course, wrapping the entire body of the worker in the absolute catching of exceptions.