Michael wrote: > I have tried using InnoDB instead of MyISAM, though this has created new > problems- the database is really slow and the row count changes every time. > > In short I would rather stick with the devil I know for now. > > I have managed to improve performance a bit by adding another field to store > a > search variable result rather then doing the search every time... however I > still have performance issues once I try to speed things up by having more > then 10-12 parallel processes accessing the database (not all at the time > time, but close enough together). > > I think the next step is to get rid of the ORDER BY rand(), however I need a > reliable way of 'locking' that row, so another process doesn't try to do it > as well thus wasting resources. > > I am doing this by issuing an UPDATE to a field after the SELECT, so the next > process would exclude this result by virtue of it's WHERE clause, however I > was finding that due to MySQL queuing requests or something, that sometimes a > new SELECT request was coming in the time between the SELECT and UPDATE > commands from the other process, so I would still end up with process > duplication, and this was why I used the ORDER BY rand(), so the processes > wouldn't all pile up in the same data base area, however this rand() part > really sucks the juice after a certain point. > > This is the code in a nutshell - > > --- Start loop --- > > 1. SELECT job-list-variables WHERE x,y,z LIMIT 1 ORDER BY rand() > > 2. UPDATE job-list SET processing=1 WHERE id=id > > 3. PROCESS TASK (Time several seconds to a few minutes) > > 4. UPDATE job-list SET processing=done WHERE id=id > > --- Go back to start of loop --- > > If I can somehow stop other processes coming in between 1 and 2, I could > remove the ORDER BY rand() part...
Rather than doing SET processing = 1, do something like: - generate a unique ID MYID for this process (there are several approaches here) - fetch a candidate job ID to do - UPDATE that job SET processing_id = MYID *WHERE processing_id = 0* - SELECT processing_id FROM table - if processing_id is MYID then we have a lock - carry on processing - if it's some other ID then some other process grabbed this ID, give up and try a new job. -- Tim Oliver Software Engineer 160 Cashel Street (Level 2) PO Box 22 128 Christchurch New Zealand P +64 3 377 0007 F +64 3 377 6582 E [email protected] www.e2media.co.nz --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
