32

People keep telling me that in order to improve an SQL server's performance, buy the fastest hard disks possible with RAID 5, etc.

So I was thinking, instead of spending all the money for RAID 5 and super-duper fast hard disks (which isn't cheap by the way), why not just get tonnes of RAM? We know that an SQL server loads the database into memory. Memory is wayyyy faster than any hard disks.

Why not stuff in like 100 GB of RAM on a server? Then just use a regular SCSI hard disk with RAID 1. Wouldn't that be a lot cheaper and faster?

2
  • 34
    Whoever is telling you RAID 5 doesn't have a clue. If you really care about performance, use RAID 10
    – MDMarra
    Feb 13, 2012 at 0:54
  • 5
    What's the D in ACID stand for? Eventually, you're gonna need to write stuff down.
    – Adam Musch
    Feb 13, 2012 at 18:24

11 Answers 11

52

Your analysis is fine -- to a point -- in that it absolutely will make things faster. You still have to account for a couple of other issues though:

  1. Not everyone can afford enough memory; when you have multiple terabytes of data, you have to put it on disk some time. If you don't have much data, anything is fast enough.

  2. Write performance for your database is still going to be constrained by the disks, so that you can keep the promise that the data was actually stored.

If you have a small data set, or don't need to persist it on disk, there is nothing wrong with your idea. Tools like VoltDB are working to reduce the overheads that older assumptions in RDBMS implementations made which constrain pure in-memory performance.

(As an aside, people telling you to use RAID-5 for database performance are probably not great folks to listen to on the subject, since it is almost never the best choice - it has good read performance, but bad write performance, and writes are almost always the production constraint - because you can throw RAM into caching to solve most read-side performance issues.)

10
  • 1
    General users always complain read issues. Seldom on write issues Feb 13, 2012 at 0:51
  • 2
    @user1034912 - varies on the use case, and users. Generally, write performance issues are harder to resolve, and end up placing greater constraints on overall system performance, which means that when you solve the read problem they start complaining about the write problem... Feb 13, 2012 at 0:53
  • 2
    @user1034912, users don't normally see write delays, so are unaware of them. Most of what users see as read delays are due to slow queries, not slow disks. Feb 13, 2012 at 3:52
  • An excellent answer! @user1034912 they might complain of read issues which could of course be a knock-on effect of poor write-performance (and poor-scaling concurrency code).
    – Alex
    Feb 13, 2012 at 11:42
  • RAID5 in Relational Databases: en.wikipedia.org/wiki/… - I'm not saying that you're wrong, but the conventional wisdom may be based on old info. Personally, I don't use RAID5 anymore; I use RAID6 unless it's too slow.
    – gWaldo
    Feb 14, 2012 at 13:14
11

Short version: consider the working set size. Long version: How big is your data? If it can fit in memory of a modern server, yes, you're absolutely right. Unfortunately, the biggest Xeon can address 2TB of RAM right now, and that's not that big of a dataset any more. If you can't buy machine big enough to house your entire working set in RAM, you're forced to solve problems with your brain, not your wallet.

2
  • 1
    +1 for the last sentence being extremely quotable. :D
    – pkoch
    Feb 14, 2012 at 19:38
  • Let's say I need to spend a month of work, e.g., $4000 from my employer's pocket, to go deep into the database to speed up the queries, while I can get the same result by spending $500 for a new RAM. In this case, my business brain (not the developer's brain) says "use your wallet, not your brain"!
    – Mojtaba
    Aug 24, 2022 at 16:19
8

If you want speed:

  • Increase RAM so at least frequently used indexes can entirely fit into RAM (for example, on a system I work on, 32GB RAM is plenty for a 350GB database, because indexes are what you need in RAM, not raw data)
  • Use RAID10 with any disks (faster disks are better)
  • Avoid RAID5
  • Split mdf, ldf and temp DB onto discrete spindle sets (example: tempdb on its own RAID1 set, ldf on its own RAID1 or RAID10 spindle set, mdf on a RAID 10 set with at least 4 total disks)

Follow those steps, and SQL Server will fly.

Then if you want, add more RAM... but do the above first, and you may well find you are done.

2

RAM is the new disk, disk is the new tape.

In http://www.tbray.org/ongoing/When/200x/2006/05/24/On-Grids . Note that was six years ago. Yes, we have database systems that try (and try hard) to keep the entire dataset in RAM and rather shard to multiple machines than to use the disk because disk is magnitudes slower anyways. You need to write out the dataset to disk but as in the motto above, that's more akin to a background backup task than an online operation. Durability is achieved through append only logs with these databases (I am thinking MongoDB and Redis but there are tons more).

1
  • 4
    -1 because nice as this stuff is, it is not really accessible or appropriate for most apps or most of us here. For up to 500gb of data (or even more), all you need are two SQL Servers (primary and backup), and you have a really fast using normal tools for hundreds or thousands of users. Very few of us need to scale to hundreds of thousands of concurrent users or multiple data centers, so the complexity of your proposed approach far outweighs the benefit for most of us. IOW: Vertical scaling is easy, cheap, and effective for everyone who is not facebook or google. Feb 13, 2012 at 18:37
2

Over the years I’ve done ton of work in the space. Here’s what you really have to consider. The first thing is is how largest you’re working data set. You ideally want your working data set to be cash as much as possible in RAM. The second problem is not so much the speed, but I would imagine for any database. You’re gonna have some kind of protected storage. Given that you have protected storage, it’s obviously some kind of raid set up. The problem with RAID is that there are certain penalties, and trade-off that you need to consider. However, most of these trade-offs can be reduced, if not outright eliminated, except for very extreme conditions, by simply having a large, right cash, and for consistency it is mirrored.

Here are my recommendations.

  1. the RAM should be what you need for your OS, the services running on the system, the working data set for the database application, and the database cache size for Ram, that does not require swapping to disk.
  2. Your storage, if on device, or across a network should have low latency, and high bandwidth. For example, if you’re using a NAS device, insure that you’ve got a very high-speed network, minimally 2×10 Gb, but in today’s world I would go 25 Gb. If it’s local then it’s easy except for problem in 3 below.
  3. Your disks are obviously protected, and that protection is done by some kind of RAID controller typically. if you’re using an external device, then get a device that has a minimum mirrored write caches. at this point, an internal survey based rate controller may not provide that capability, but Naz devices of a certain quality often do, along with SAN devices.

Long long time ago, spent a lot of money for EMC storage, the primary reason was the mirrored write caches were much, much larger than anyone else’s, and this allowed the writes to be committed in the database faster, it essentially wrote to mirrored RAM and acknowledge the Wite. We even compared five or six high-performance, but smaller raid storage devices to the one AMC, but the large cache just blew away all the other permutations of storage. The larger Read caches a very useful, but, extra RAM on a server is much cheaper for caching to provide improved read performance.

1
  • and within the newest filesystems it will became more handy to use ram and ssd in the meanwhile to cache writes and reads
    – djdomi
    Mar 10 at 6:04
1

This question is similar to a basic one that has led to a lot of research and development in database architectures over the past 5-10 years. Now that it is feasible to store an entire database in RAM for many use cases, the database needs to be designed around working in RAM, rather than simply applying older inherited architectures to RAM-based storage.

Just as many smaller and more special-purpose languages have been widely adopted in recent years, we are entering an era more special-purpose databases will be needed.

For some further reading on this topic, I recommend the academic paper The End of an Architectural Era (It’s Time for a Complete Rewrite). It's not a difficult read.

It's unclear if this question was specifically about SQL Server. The original poster should clarify this.

Daniel Pittman wrote:

If you have a small data set, or don't need to persist it on disk, there is nothing wrong >with your idea. Tools like VoltDB are working to reduce the overheads that older assumptions >in RDBMS implementations made which constrain pure in-memory performance.

Reducing the overheads from older assumptions in RDBMS implementations was exactly the design goal of VoltDB, but it does scale horizontally with no architectural limit on the data size, and it can persist to disk for full durability using snapshotting and command-logging.

0

If you can get a server with enough RAM to hold, at least, the hot part of your dataset, you'll be fine. Also, RAID 1 and 5 are not the fastest way to arrange your data - RAID 0 is faster, but, then, you'll have to consider the higher odds of a filesystem failure that wipes out your database - not a nice thing to happen. You can RAID 1 or RAID 5 your RAID 0 array, provided you have enough drives and controllers.

You can even play with replication here - do your writes to a disk-heavy server which replicates to one or more memory-heavy servers where you run complicated queries.

Sadly, RDBMSs appear to be in the big-iron realm - they aren't that easy to grow horizontally.

0

This is a case of "it depends what you are doing." Perhaps the "right" advice is to avoid SQL altogether and use memcache/redis/etc!

I agree with you that extra RAM will help a lot, especially if you are able to read the whole working set into RAM. Yes it will still have to write data, but if you have mostly reads then the writes will have no contention for disk I/O.

However disk performance is often a bottleneck on SQL servers and harder than other things like RAM to upgrade later (if you have a server that's not fully populated with DIMMs).

There were a number of comments about RAID5 being slow, but I would say this is not always the case, so be careful before making sweeping statements. Really high end servers with fast RAID cards and lots of BBWC sometimes go much faster in RAID5 (or RAID50 with >4 disks) than they do in RAID10...

Over the years I've personally experienced slow RAID5 arrays, but after benchmarking a DL360 G5 with 4 146G SAS disks in ~2009, we had to double check our tests. Indeed, the array went faster with RAID5 than RAID10 in nearly every test. BBWC and fast parity calculations allowed the server could use the 4 disks much more effectively as a RAID5 array than RAID10. Some of the tests showed 50% better throughput with RAID5, and nearly none were slower. The tests that were slower were only 5-10% off.

I would caution the people who make blanket statements that RAID5 is slow, everybody says it online, but it is simply not true in every case.

0

Late answer stating an obvious thing, but... not all SQL database loads are equal.

For read-heavy workloads, increasing RAM probably is the most valuable path, followed by using fast NVMe disks (even consumer-level ones). The reason is simple: reads can be easily cached in RAM, which is much faster than any block devices.

Write-centric workloads are different. While writes can (and are) cached, persistency needs to write things to disks. A basic INSERT or UPDATE with autocommit will create many synchronous IOPs which returns only after data was actually writes to stable storage. This kind of load requires fast block devices and, more importantly, ones designed to be fast for synchronous requests - which generally means "enterprise SSD drives and/or RAID controllers equipped with powerloss protected writeback cache".

The last point is a key difference between consumer-level and enterprise-level SSDs: even when the former can sustain +1M 4K "normal" (async) writes, they often struggle to provide 500 4K sync writes. Enterprise SSDs equipped with persistent write cache can ignore the "cache flush" command, providing much higher synch write performance.

Finally, a note about the RAID level to choose. When dealing with HDDs do not even consider RAID5 (due to reliability issues) and do not use RAID6 unless using an high-end controller with plenty of persistent cache, and avoid it altogether if you space requirement are satisfied by RAID10.

When using fast NVMe drives, parity RAIDs become more applicable. But for the highest performance, again select RAID10.

-1

You have a mix bag of candy to select from and really depends on what the flavor you want is.

  1. DBs will have config to cache queries and where this cache exists, memory or hard drive.
  2. RAID 5 is not always the fastest but RAID 0 (JBOD) is a stripe and is fast, since RAID 5 is also a stripe the idea is much the same.
  3. RAID 1 will not improve your speed, it is just a mirror.
  4. SQL performance is based on Indexing, and is the first thing to check. Very important in relational databases.
  5. Dont index everything, over indexing can also reduce speed because your indexing becomes over loaded.
  6. Sometimes with SQL Joins the database becomes slower. Using programming to loop a set of minimal indexed results improves speed.
  7. Virtual Servers are a nightmare on speed if you dont pay the dollars.

Simply put invest in the knowledge (free) before forking out cash. 1. Learn the configs for your database and look at your current config to optimize. 2. Look at the programming and sql statements, unit test with simple scripts that mimic the operations involved, it may not even be what you think is the issue. IF the simple scripts take up time using SQL Joins, split it up and do the same thing with a programmed loop to do the same. This is were memory can help 3. Look at the hosting plan and server. Use ps aux in a linux console and see if there is something sucking up your memory and processor.

The absolutes Hard Drive improves speed but is not up to you in a virtual server space. Memory does not improve speed unless you config the services for it, period. Striped RAID (0,5), RPM and Synchronous Read/Write with a fast bus helps that. A core processor with good l1,l2,l3 cache will help processing bottleneck. can I hear it for Xeon!

1
  • 2
    RAID1 absolutely will improve speed in read situations. Most controllers are smart enough to use multiple spindles to read from the (identical) data sets at once. RAID0 is a bad idea because you're limited to a spindle at a time. Feb 13, 2012 at 18:33
-4

Overall, you must keep size and scalability in mind. While you may seem to begin with small storage needs, your data will grow very quickly and exponentially. DB's are best using atomic data, which are data broken down to the smallest possible size. Because of the small size, it travels faster within the data warehouse. Then, you also factor in the DB structure. In the future, you could be linking to outside DB's, which is why structure is also crucial. In this scenario, it would make little difference for your query if half of the data lives outside of your data mart. When data is queried, the point is not to keep stored data on the RAM; rather, the query should be quick in accessing and returning data.

  • You really don't always use RAID 5 for data. It depends on the data & its importance, beside what was previously mentioned about back-ups. RAID 1 can be used and is.
  • You would have to upgrade all servers within your range of query to improve speed. Since much of data is outside of your control, it is going to bottleneck somewhere outside of your data mart. (In the case that you upgrade your own)
2
  • Wow, did you copy that from your (misunderstanding of) your textbooks ?
    – adaptr
    Feb 13, 2012 at 13:01
  • Ugh. How many times do people have to be told that RAID is not a backup solution?
    – Cromulent
    Feb 16, 2012 at 18:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .