Best RAID setup for large MySQL (4+ gig) tables?
I have a server with has some very large tables, multiple gigs in size (some up to almost 6 gigs). I recently moved servers, and my old server handled things perfectly fine, but that had a 2X146 SCSI RAID setup (I think it was a RAID 1 setup but I'm not positive).
The new server while mostly superior (8 CPUs vs. 4) has SATA, and no RAID, and is having major problems - usually it works OK, but sometimes it goes down and when it does it goes down hard, with maxed RAM use and loads of 50-100, and MySQL non-responsive. Httpd must be killall -9'd and MySQL restarted, and even then reboots are often the only solution.
This seems to be due to elevated iowait according to my host, which leads to memory shortages (despite 4 gigs RAM) and increased swap usage, and when this happens the server quickly becomes unusable; also it sometimes crashes/corrupts tables.
I think the SCSI vs. SATA aspect might be important, but I don't know how that compares to the importance of the RAID aspect. As far as I'm aware, the hard disk setup is the only way in which the new server is deficient compared to the old; otherwise it's got more everything, and my host's techs can't think of any optimization tweaks that haven't already been applied.
What type of RAID setup would be best suited for dealing with very large MySQL tables, and combating an iowait/RAM use/swap use cycle of death problem?