Re: Moving to a faster machine does not improve performance

[James Day]

A queue daemon will reduce the number of locks the database has to try to get on the tables. So would the GET_LOCK approach. Either is likely to have much less overhead than making a database table handler do it. It could be IO_WAIT but my guess is it's just routine database concurrency/locking issues and you now reaching the point where designing the system to reduce the effect of them is becoming significant.

You might also try using MyISAM for the table, if only for a temporary test period. Becaue it locks the whole table it can sometimes be much faster than InnoDB - I've had a situation where a quick change from InnoDB to MyISAM dramatically improved high-concurrency lock performance. Or you might try one of the other table handlers - BDB perhaps, with it's part way between InnoDB and MyISAM locking approach.

My problem case was a query using SELECT ... FOR UPDATE on a whole table. Bad for InnoDB, superb match for MyISAM. The code is being modified so it doesn't do that any more.