Database Server Provisioning: How Many Disks Do I Need For RAID 10?

I have a new server box intended as a dedicated SQL Server 2012 server. With the new box, I am now faced with a question on what should be the optimal number of physical disk I would configure on each RAID 10 array that I would set. Having the answer to the question would greatly help me in deciding as to how many partitions I will create to support the separation of SQL Server’s data, transaction log and tempdb.

To come up with an answer, I conducted some simple SQLIO test to determine which array configuration is best suited for the read-query-intensive application that I am about to set up. The test should be able to answer the following:

  • Is a 6-disk RAID 1+0 array better than a 4-disk RAID 1+0 array?
  • Is an 8-disk RAID 1+0 array better than a 6-disk RAID 1+0 array?

The Test

I carefully devised the following tests:

  • Run an SQLIO READ test using a 10GB file on a RAID 1+0 array with either 4, 6, 8 and 10 disks.
  • Run an SQLIO WRITE test using the same configurations as the READ test.
  • Each SQLIO test case should be run 5 times and get the average from the results.
  • Prior to each test, I have to destroy the RAID configuration using the server’s RAID utilities, set it up with full initialization, create and format the partition.

The Results

The following are numbers produced by SQLIO:

READ Test

Disks

 IOs/sec

 MBs/sec

Latency (ms)

Min

Ave

Max

4

1,323.70

10.34

0

23

544

6

1,580.82

12.35

0

19

562

8

1,817.43

14.19

0

17

498

10

1,878.32

14.37

0

16

495


WRITE Test

Disks

 IOs/sec

 MBs/sec

Latency (ms)

Min

Ave

Max

4

715.82

5.59

0

44

152

8

1,197.28

9.35

0

26

199

My Conclusion

  • I conducted the READ test first which influenced my decision to just instead run the full WRITE test on a 4 and 8 disk array.
  • After the READ test, it seems to suggest that having 4 and 6 disks in an array don’t seem to have a significant difference in terms of performance between the two.
  • Going beyond 8 disks, performance wouldn’t improve much
  • With only 14 physical disks to utilize, I ended up with 2 RAID 1+0 arrays. One with 4 disks and the other with 8 disks.

Additional Information

  • I only run each test once on a 6 and 10 disk arrays so I have not included the figures in the results.
  • I tested the 10-disk array configuration just to confirm if there will be significant performance gain.
  • Disks used were the ordinary 250GB SATA drives.

Caution

The results presented here do not, in any way, represent a recommendation. It merely shows based on my specific configuration. It may differ in other server configurations and I have no way to know the possible results of other test using a different server configuration. It is therefore highly recommended to conduct your own testing when you have a new server.

**************************************************
IMG_0561
Mountain Province, Philippines

**************************************************

Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com

Database Server Provisioning: What’s Best Practice, What’s Real

Many months ago I have been tasked to recommend an upgrade to the server hardware of a medium-sized mission-critical OLTP dedicated database server running on SQL Server 2005. The client has gone through the regular 5-year maintenance/upgrading cycle and has added more applications that were not previously foreseen putting added stress on the database server. The combined size of the databases is rather small, roughly around 50GB that has accumulated for almost a decade. Around 15GB of which are highly active. Furthermore, the server is catering roughly to around 6000 active users with 10% of which doing complex, highly relational computation and aggregation. Most of the processing are complex querying on large data sets and which are largely read-intensive.

Though the database server is holding up, it was now considered near-end-of-life. Server utilization has increased to almost 60%-80% during regular working hours and on occasional peak, performance would degrade noticeably.

The client’s upgrading directive is for me to find out the best possible configuration for the least amount of money. And in the segment that I am active with, it is almost impossible to get a good idea on what is best out there based on an actual production setup and experience. With the absence of such information and with the lack of experience (as I do NOT get to do upgrading on a monthly or regular basis, who does anyway?), the most logical thing to do is check on best practices articles/guides that are vastly available over the Internet.

What is Best Practice?

What’s interesting about most of these articles and guides by Microsoft and various 3rd party experts is that, these are anchored on the premise of one having an ideal environment given ideal or best possible circumstances. And commonly, you will see that these best practices are presented in a way not based on various limitations but rather to almost always take advantage on what best/top solution is available out there. One would really have to work his way through these articles to get a balanced solution that is acceptable to a certain level if his circumstances are far from being ideal. But there is nothing wrong with these … and this is exactly why these are called ‘best practices’. No one would definitely be able to see all the variables and factors that can affect the outcome of what your ideal setup will become.

What is Real?

Out there however, reality bites. We all have to work with limits. Out there we are given constraints like having limited budget, hardware vendors carry only certain server models (some needed devices need to be imported from another country) and often we all don’t have the luxury of playing around the real stuff until you get one paid and delivered it to you. And there are lots of other unique and strange variables that we have to hurdle.

It is more than necessary for one to do some heavy sleuthing to come up with options to the client without being extravagant and not being unreasonably idealistic, and that one of these options could become acceptable and still be adhering to most, if not all, of the ideals that are staple in these best practices.

The Recommendations

So I came up with the following:

  • Recommendation #1: Upgrade 2005 to SQL Server 2012. Though SQL Server 2005 can provide for what the client requires today, the client’s thirst of technological solutions now seem to be picking up the pace, they seem bent on evolving their systems rapidly. This kind of upgrade though needs to be justified — for one, SQL Server 2012, for a 12-core server doesn’t come cheap.  So the justification here is that SQL Server 2005 SP2 has already reached end-of-life in 2010 while SP3’s life is on lease. Futhermore, the boatload of new features and performance enhancements in 2012 can surely can justify the upgrade. Though initially, the goal is to just simply move to 2012, once done, the direction is to take advantage of the bevy of new features available in 2012. FILETABLE and the new FULLTEXT SEARCHING capabilities come to mind rather quick. I’d be able to make a case to upgrade just for these two features alone.
  • Recommendation #2: I decided to stick with Windows Server 2008 R2 mainly because the client is not ready for an upgrade of its Active Directory to 2012 across all servers. Two years ago, we upgraded the OS/Active Directory from Windows 2003 and every server moved to 2008 R2. Running in 2008 R2 has been flawless for the past years. so I believe Windows 2012 at this point is not necessary. 2008 R2 is very much potent and very stable. I would probably recommend to them to skip fully Windows 2012 and just wait for the next/future releases of this server OS.
  • Recommendation #3: Mid-level DAS server that can handle more than 8 bays without special kits. IBM allowed me to have a server that can have up to 24. Old servers that were acquired only had 8 bays for DAS HDDs. SANs or Servers allowing more bays were then beyond reach for this client, money-wise — well, this was until IBM introduced certain server models that are very much affordable. With these new servers, these will allow me to configure more RAID arrays.
  • Recommendation #4: From a 2008 generation 2 x 4 core, client is moving to a two socket 6-core/socket server having a total of 12-core current generation technology with faster speed, hyper threading and double the cache. The 8-core per socket is beyond reach as this will also increase the spending on SQL Server 2012 which can now only be licensed on a per core basis . Besides, there isn’t much CPU issues with the existing setup. Having this new processing power could provide ample room for growth in the coming years.
  • Recommendation #5: From 16GB of RAM, now it would be 32GB. A fairly good portion of the database would fit into this space. As RAM gets cheaper in the coming years, we could probably have this server @ 64GB and make RAM less of an issue. Besides the current database is still small. But having RAM double for now will tremendously help and would probably cover some unforeseen growth in the coming years. There is a big chance that newer apps will utilize this server.
  • Recommendation #6: From the existing 6 x 7krpm SATA spindles fashioned in a single RAID controller (configured RAID 1 and RAID 5), I recommended 14 x 7krpm SATA drives on 2 RAID controllers. This is where I have done a lot of thinking, weighing the client’s directive to spend the least amount of money while still achieving some of the ideals of what should be is a best practice. After all, this is the most critical and the slowest of all the system’s components. Going for the considerably performant 15krpm SAS drives would have quadrupled the storage cost. My notion here is that, with me having my hand on Recommendation #3, I would still have gained substantial disk IO performance gain over the existing setup if I’d be able to spread at least three Raid 1+0 Arrays and will have 2 RAID controllers sharing the load. This way, I would be able to separate DATA, LOG and TEMPDB. I understand, the best practice out there is to have at least 3 Arrays so SQL Server’s data, log and tempdb can each be placed on separate arrays — that is what people are evangelizing, or so it seems.

What Came Out of My Recommendations

Now that the client has approved all my recommendations and the components for upgrading were delivered by various vendors, I would like to show what came out of it.

All recommendations were as expected other than Recommendation #6 where it turned out to be interesting and has shown another example of when best practice gets tweaked down to a level that is acceptable and also when harsh reality bites.  The 2 RAID controller setup is not possible (the vendor claimed). The first RAID controller (built-in) is getting disabled by the second RAID controller (add-on). I was fed a wrong information from the start by another vendor and only got to confirm that it is not going to work after the servers were delivered and configured. Unfortunately, the client ordered the server from another vendor so they cannot hold the vendor responsible for the wrong information. Client cannot afford to wait for another 30-day delivery so it has to deal with a single RAID controller setup.

So why 14? Why not 24?

First, given an extra push, the client could have gone for 24. However, what concerned me that time was that, I can’t be certain of the performance gain I will have with having the 24 — well, NOT until the client has paid and the server is delivered. This is one of the realities I have to face, server hardware vendors around here aren’t known  or say familiar with these realities, and they can’t give you figures on how their machines would fare given the load you expect. So I recommended a safe setup.  I came up with 14 thinking that if performance gain over the existing setup comes up short in one of the unforeseen rounds of expansion, I still would be able to tell the client to get the faster 15krpm SAS drives to fill the remaining bays without decommissioning some few hard disks. And now, after playing with the actual machine, my caution seem to hit the mark.

Upon testing using SQLIO, I seem to always get optimal performance with an array of 8 spindles. Increasing the spindles does not improve things much further. I am not sure if this is true with a different server brand/specs/configuration. So now I ended up with the following:

Array 1, RAID 1, 2xDisks, Single Partition (OS, MSSQL)
Array 2, RAID 1+0, 8xDisks, Single Partition (DATA)
Array 3, RAID 1+0, 4xDisks, Two Partition (LOG @ Partition 1, TEMPDB @ Partition 2)

The above setup, far from being ideal, seem to have gained me more than double the I/O performance than that of the existing just by running SQLIO. Testing further and having able to spread SQL Server’s data, log and tempdb across arrays further improved the system’s performance way better than I expected. I’d probably have another blog discussing in length how I came down with this decision. Some SQLIO numbers would definitely shed light on this.

In the end, I believe I was able to satisfy what was required of me without being unreasonably idealistic.

**************************************************

P1122746
Bataan, Philippines

**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com