> Its a bit of a rant
I would say it is a bit of a "rut". You have decided that I/O is the problem, and you have decided on how to solve it. My job is to get you to back up and question all of your assumptions...
> But I need more performance out of them, and the rows need to be on a separate disk,...
You are assuming your OS and device drivers will do anything in parallel. That is a big assumption!
> at best I get 100 a second on my hdds
Yeah, that's all you can get on standard hardware.
> there are millions to be updated
Why?
Do they have to be updated _now_? Or can they be delayed? Or batched? Or accumulated from a log? Or kept in RAM?
Have you tuned your cache?-- see
http://mysql.rjweb.org/doc.php/memory
InnoDB? If so, please provide SHOW STATUS LIKE 'innodb%'; -- some settings have a big impact on disk hits. Are you using transactions? autocommit=1?
Are the rows to be updated "clustered" at all? That is, could they be "near" each other on disk?
Edited 1 time(s). Last edit at 04/13/2012 04:50PM by Rick James.