On 4/27/07, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote:
Anyone have any ideas on how to handle a work queue? I've been thinking
about optimizing this process for quite a while.
Basically, my queue table consists of a few-hundred-thousand records
describing "things to do". To pare things to the minimum, a queue record
can be considered to have a status (available, assigned, completed), a
priority, and a description-of-work.
A process will grab an item from the queue, mark it as in-progress,
process it, and, depending on success, update the item as completed or
as available with an updated priority. There may be upwards of a
thousand "worker" processes and the work of each process may be
completed in anywhere from a few seconds to nearly an hour. I expect the
system as a whole to be handling a few-dozen queue items per second.
My original plan to fetch work was:
begin;
select item-id, item-info
from the-queue
where available
order by priority
limit 1
for update;
update the-queue
set status = 'assigned'
where item-id = previously-selected-item-id;
commit;
how about this:
create table job(job_id int, [...])
create sequence worker;
your worker threads can do something like:
select * from job join
(
select nextval('worker') as requested_job
) on job_id = requested_job
and
(
(select (w.last_value, w.is_called) < (j.last_value, j.is_called)
from worker w, job_id_seq j)
)
and then sleep appropriately if there is nothing to do. Of course, if
the job fails you have to put it back on the queue. No locking
required! This relies on false being < true...safer to break out to a
case stmt but im just trying to be clever :-)
This has couple of advantages but is also pretty fragile. I'm not
necessarily suggesting it but it was a fun way to think about the
problem.
merlin
