3

I'm putting together a new server for my current database which has 500GB of data and over 13million rows in 90 tables. My read speed is very slow at the moment using SATA drives.

I prefer to use a Dell server, but my biggest concern is selecting the correct drives and raid setup.

Can anyone recommend me super any fast drives(SAS, SSD, PICe), or extras?

  • 1
    "very slow". What does this mean? Are you sure it's not related to bad queries? How do you define "fast" drives? – Andy Oct 29 '15 at 16:26
  • Sorry I ment the SATA drives I'm using. Looking for fast read on the drives, not too bothered about the write speed. :) – Will Oct 29 '15 at 16:29
  • What is your budget? If you can stretch it, go with a 2x 1TB SSDs in RAID (mirrored, in the case of failure). – AStopher Oct 29 '15 at 21:13
  • @bob, 4x SSDs in RAID 10 may be faster (but will also cost more). – Mark Oct 29 '15 at 23:08
  • What is "Super fast" Do you want sequential speeds? 4KiB chunk speeds? Other? You should specify that in your answer – Rubydesic Oct 31 '15 at 23:02
3

13 million rows is 500GB?! You must have HUGE tables--that's 38KB/row! You must mean 13 Billion rows or 500MB?

I've been running a database that has grown from 500GB a few years ago to 1.5TB+ now. If your server has plenty of RAM, any popular brand of SATA SSD with sufficient capacity should be plenty for your use case (if your cache hit ratio is less than 99% what you really need is more RAM, not more raw disk performance). In a properly configured database server, the data drive spend most of their time writing, not reading (because reads can be cached--writes cannot). If you have the money and time spent replacing bad drives is more important than cost, just get the one with the best warranty (though to be honest, I've used both cheap desktop and mid-range enterprise SSDs and had very similar failure rates).

You most likely want to use a mirrored set for redundancy (this would have applied to HDDs as well), and possibly database mirroring as well (depending on your desired disaster recovery point and time).

Depending on how hard you hit them, you may also want separate volumes for the tempdb and for log files (HDDs are usually fine for logs, though, as they do very little random access).

When you need even faster performance, adding more drives to a RAID 10 volume will give it to you, up to the limit of what will fit in your case and attach to your controller(s). When you exceed that, you can switch to Fusion IO or similar ultra-high performance caching, or to external massive SAN RAID systems, which can also use SSDs and scale pretty much as far as you have money for.

| improve this answer | |
4

Depends on your servers and your needs.

Now, there's a few options with different levels of, well practicality. I'm suggesting samsung drives here since I've had decent experiences with them but your milage may vary - see this specific kernel bug related issue, and their TLC line seems to have had issues. The flagship pro line is solid tho

You could go with the newer NVMe drives - they fit into m2 slots, but by happy circumstances they are electrically x4 cards. There's also native PCIe cards they're relatively exotic. Of course, you need m2 or spare pcie slots of the same size.

Puget system has a nice review on what seems to be the fastest m4 drive available Samsung 950 Pro 512GB. in this specific case, a good solution would be to get a converter to install it to a x4 slot, and use one of two of them would give you pretty amazing speeds.

If you wanted something a little less exotic and newfangled, the 850 pro is about as fast as sata gets.

| improve this answer | |
1

Some hardware RAID devices like this have internal RAM cache of about 1 Gb in size (faster than any SSD and no care about wear), use supercapacitor to flush this cache to the non volatile memory in case of power loss and also can use connected SSD as additional cache.

Such cards may be a good solution if your database has grown to multiple terrabytes in size so it is too expensive to place it completely on SSD with the required RAID redundancy.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.