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?
Your old setup was probably RAID 1. That is, mirrored drives.
In general, the disk subsystem and memory are the two most important physical factors affecting performance.
The iops is particularly important.
iops is influenced by disk type, controller type and number spindles.
The ideal situation is battery backed raid controller with large memory cache, scsi/sas drives with *lots* of spindles configured as raid 10/50. You will not usually find that in a dedicated server offering because it costs money.
An example of such a configuration?
HP SmartArray + 12 scsi drives + 2 hot spares
The drives aren't huge, but they're fast.
The system dumps the write on the raid card, the raid card puts it into the battery backed ram and tells the system its done, then it starts writing out the data spread out over multiple drives in parallel. Each eligible drive contributes to the iops total that can be achieved by the subsystem as a whole.