I'm trying to find a suitable SSD drive for my database server (postgres on debian 7 amd-64), and I find that for some SSD drives, while read speed is specified in MB/s, write speed is in IOPS. To make things more complicated, sometimes sequential access is in MB/s and random access in IOPS.
How these units compare to each other and to hard disks (which use good old MB/s)?
MB/s states how many Megabytes per second the drive can handle as throughput. IOPS states how many single operations per seconds can be handled.
Sequential access means that for example one big file is read, random access means you're reading single parts of different files.
If you look for a drive for database usage, you should look for:
An enterprise, business or server class SSD of SLC (best, but most expensive) or MLC type. Try to avoid a TLC type SSD disk.
At least two of them, to form a RAID and prevent data loss. Best RAID strategy for databases is RAID10 of as many disks you can afford. If you have only two disks, use a RAID1 (mirroring). Avoid RAID5 or RAID6 for database storage if you need performance. SSDs have limited write cycles and at the end of the write cycles, SSD simply fail instantly. Then you want to have a mirrored copy of your data to replace the failed SSD asap.
Ideally use a RAID controller with a battery backup unit and RAM to cache even writes.
High IOPS. A database reads and writes rather many smaller transactions. You benefit more from IOPS than from a high throughput.
Throughput. This is the least important thing. You only benefit from high throughput rates when doing backups or restores.
Updated Answer based on comments:
Software RAID can be a solution, if your storage resides on an Oracle supported Solaris or Illumos or something similar. A hardware RAID controller is not needed then as ZFS does a pretty good job for high IOPS database loads without a hardware RAID controller. A hardware RAID controller can even slow down your setup.
I would absolutely recommend against using a linux software RAID (mdraid). It is good for high throughput rates, but it has very low IOPS rates. This is nice for a file server, but not for a database server.
Generically, Megabytes/second are a metric for drive throughput, while IOPS represent I/O operations-per-second, a way of detailing the random I/O performance of the drive.
Both measurements, as presented by manufacturers are fraught with inaccuracies. Throughput will be limited by SATA/SAS topology, the disk backplane (if present) and the RAID or storage controller. IOPS are usually reported under ideal conditions with datasets that are favorable for benchmarks.
There are other attributes that contribute to the performance profile of SSDs, such as latency, endurance, over-provisioning level, interface type, capacity, caching and write protection.
See: Are SSD drives as reliable as mechanical drives (2013)?
I asked if you could provide more details on the application for the SSDs and what type of server hardware and operating systems are involved. That information will help immensely, since you really can't just install any SSD in a server. Device choice is going to be driven by the server hardware and compatibility as well.
