Hello,

I have a database question and would value some input from more experienced MySQL users;

I have two programs (one of which I would like to run on more than one computer). The model is a producer and then 1+ workers. All programs written in Java.

The producer downloads some information and stores it in a database. This happens at unpredictable times.

The workers process the new data in the database, before changing it slightly (including setting a flag that says that data was successfully processed). Currently the 1 worker just works in a loop. It does a select and gets all the unprocessed data. Processes it and then does another select to see if new data has arrived. If not it waits for a while before trying again.

So, the processing takes a while. about 2mins per data point there are about 11000 data points and its going up. So we need more horsepower....

If I run more than 1 worker on more than 1 computer then I have a problem that I need help with. Worker 1 does select gets list of unprocessed data. Worker 2 does the same gets a similar list, they end up processing the same data some of the time. FAIL. Is there an SQL solution to this problem? Only request one data point at a time and somehow flag the row? Seems rubbish. Or should I make another program that gives tasks out to the workers... sort of a man in the middle?

I am guessing that this sort of access problem is solved pretty well for databases?

I have written distributed systems before but not ones that use a MySQL table to store the data and results and has computers putting in new data that are not even on the same network....

Cheers,
Phil

Yeah, I'd go master process and 'N' worker processes/threads.
The Master scans for new rows and hands off sets of them eg sets of 10, to each worker.
Obviously each row would need a unique identifier, if necessary set by the Master.
The Master just hands out the 10 unique ids to each worker. This would be a natural problem to solve with threads. as the Master would want to know when each worker is ready for the next set.

Thanks, I will do it that way. That's basically how I do things normally using Java Remote MPI. I will build a producer that hands out the work and checks it back in again.