1

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)?

zaadeh
  • 161
  • 1
  • 6
  • 2
    The [WikiPedia article on IOPS](http://en.wikipedia.org/wiki/IOPS) is a pretty good start. It probably needs emphasising that IOPS are always for a specific data size i.e. 4 64 or 128 kilobyte or even megabyte sized datablocks. Then `IOPS * blocksize = bandwidth (in MB/s)` – HBruijn Jan 04 '15 at 12:47
  • Can you provide more details on the application for the SSDs? What will they be doing? Also what type of server hardware are you using and what operating system(s) will be in use? This is important information. – ewwhite Jan 04 '15 at 13:46
  • And by server hardware, I mean, can you specify the Manufacturer/brand/model of your server equipment and whether there's an existing hardware RAID controller, etc. – ewwhite Jan 04 '15 at 14:04
  • I find that most manufacturer specs are misleading (or at least obscure) in one way or another. Even a single brand uses different and confusing notation for various models. look at: http://www.adata.com/index.php?action=product_specification&cid=3&piid=290&lan=en and http://www.adata.com/index.php?action=product_specification&cid=3&piid=211&lan=en. Looks like they try to make the most out of people's ignorance on the topic while it lasts. – zaadeh Jan 04 '15 at 15:04
  • @aalizadeh What type of server are you using? Dell? HP? IBM? Do you have a RAID controller? – ewwhite Jan 04 '15 at 15:56
  • Server hardware is a custom setup on a SuperMicro board, 2 Inter Xeon 5620 CPUs, 16GB RAM, 2 SATA HDD Drives in software RAID1 mode. – zaadeh Jan 04 '15 at 16:20
  • 2
    Former measures throughput, latter latency. – user541686 Jan 05 '15 at 07:22

2 Answers2

14

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:

  1. An enterprise, business or server class SSD of SLC (best, but most expensive) or MLC type. Try to avoid a TLC type SSD disk.

  2. 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.

  3. Ideally use a RAID controller with a battery backup unit and RAM to cache even writes.

  4. High IOPS. A database reads and writes rather many smaller transactions. You benefit more from IOPS than from a high throughput.

  5. 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.

mgabriel
  • 1,091
  • 8
  • 15
  • These days hardware RAID controllers can form the bottleneck of setups with lots of SSDs. Each SSD can handle lots of IOPS, but the RAID controller may be unable to handle the combined IOPS of all SSDs. Software RAID (or ZFS) can be a better solution. – ptman Jan 04 '15 at 13:00
  • ZFS can be a solution, but is probably not an enterprise solution as you don't get any support. It's not included in the mainline kernel, although it's easy to integrate it. I can only recommend strongly against mdraid (linux software raid). It delivers a fairly high throuput but fails miserably in high IOPS scenarios. Of course you should not go for a $20 Raid Controller if you want to max out SSD drives. The Controllers should be in a position to handle the IOPS and drives like the top ARECA or LSI Controllers (840.000 IOPS, Dual Core CPUs) can. – mgabriel Jan 04 '15 at 13:02
  • You are clearly talking about ZFS-on-Linux. Oracle sells Solaris with enterprise supported ZFS and OmniTI sells commercial support for Illumos (i.e. OpenSolaris) based OmniOS with ZFS. – ptman Jan 04 '15 at 13:04
  • Yes, you're right. If we talk about solaris, illumos and Oracle, I'd recommend ZFS as well. There is no need for a hardware raid controller. – mgabriel Jan 04 '15 at 13:07
  • 1
    @mgabriel: at `1)` rather than SLC or MLC it is better to emphasise it as *buy a drive suitable for the workload* i.e. specific for read or write heavy performance and supported by the server vendor as for instance HP gen8 servers are quite picky `2)` when mirroring you should theoretically hit the max number of write cycles more or less simultaneously on both drives :( so again buy something suitable for the workload and monitor individual drive health. – HBruijn Jan 04 '15 at 13:13
  • @HBruijn: I didn't state it, but for me it was clear that in a HP server you should only run drives that are supported by HP for that server. Regarding your number 2) - if a sector of a drive would fail exactly after X write cycles, you'd be right. But it does not. Some sectors fail after X-20%, some at X, some at X+20%. Generally, there is no hard limit for X. Failed sectors are indeed replaced as long as sectors are available. This is done by the SSD controller in the drive. If there are no more sectors available, the drive stops working and is probably dead. Then you have a mirror. – mgabriel Jan 04 '15 at 13:19
  • **"and at the end of the write cycles, SSD simply fail instantly"**. This is absolutely not true. I have driven many SSDs beyond their write/erase cycle limit and they continue working just fine at that instant of time, but they do start showing increasing errors over time. The SSD warranty generally expires immediately once you hit the flash write limit, is that what you were thinking of? – Daniel Lawson Jan 06 '15 at 08:59
4

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.

Community
  • 1
ewwhite
  • 194,921
  • 91
  • 434
  • 799