RAID Configuration - R10 v. R5 - Spindle Count

We are in the process of procuring a new IBM SAN for our most business
critical SQL database. The database right now runs great on a 10 disk
RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
of the new hardware implementation, we want to go to RAID10 and as per
a vendor recommendation, break the database out into file groups on
separate physical disks in order to boost performance.
I keep getting hung up on spindle count. I have always subscribed to
"the more spinning heads the better". The flaw in this theory appears
to be as the smallest disk you can buy for a given solution gets larger
and larger , where is the trade off? Do you still stripe across as
many disks as possible and end up burning a lot of disk space? Does
the large amount of cache (4GB on our new system) decrease the number
of spindles needed?
Is there an "easy" way or rule of thumb that would allow me to
translate 10 disk wide RAID5 performance into X disk wide Raid 10
performance. In a nutshell, how can I estimate what the following gets
me with regard to reducing the number of spindles necessary:
1. Going to RAID10 from RAID 5
2. New solution has 4GB of cache / old solution had 512MB
If I go with the vendor recommendations for table placement in file
groups I will end up with something in the range of four (4) - two (2)
disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
disks, but only 2 will be useable for space and my understanding is
that only two (2) will be counted for parallel I/O performance. I am
nervous about how narrow these RAID 10 sets are but maybe it does not
matter as much?
As a side note, i have no good way other than what I can get through
Profiler to load test the new solution with actual production data.
Any insight/thoughts would be appreciated.
Thanks!
PaulPaul;
4GB of cache is unlikely to significantly decrease the number of spindles
you need to stripe across to get the performance. Very large cache size does
have an tendency to mask the need for more spindles. But that really depends
on the workloads (on whether the workloads can take advantage of that cache)
.

> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance.
There is commonly known RAID formula for doing this estimate. Assume that
the read:write ratio of the workload is 3:1--a very typical ratio, and assum
e
that you are using 15,000rpm disks, which can roughly do 180 IOs per second
(IOps).
RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I/O
,
the I/O penalty is 4/(3+4) = 0.57
RAID 10: Since each write needs two disk I/Os and each read needs one,
the I/O penalty is 4/(3+2) = 0.80
For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
Of course, this is just a theoretical rough estimate with many factors
absent. For instance, it doesn't consider the effect of the SAN cache. But
it's safe to say that the vendor suggestion of 4 disk RAID 10 is unlikely to
cut it (unless there is anyting specific to the workload of your app).
Running application-specific benechmark is important to verify whether the
disk config is adequate. However, you can also use disk performance tools
such as IOMeter or sqlio.exe to specifically examine the performance of your
disk subsystem outside of SQL Server. I would run sqlio.exe whenever I get a
new storage so that I know what storage performance to expect, and have data
for later comparison when needed.
In general, it's far better and simpler to just stripe across as many disks
as you can (within the disk subsystem limits such as controller throughput).
Linchi
"Paul" wrote:

> We are in the process of procuring a new IBM SAN for our most business
> critical SQL database. The database right now runs great on a 10 disk
> RAID 5 set with TEMPDB and TLOGS on separate RAID1 arrays. As a part
> of the new hardware implementation, we want to go to RAID10 and as per
> a vendor recommendation, break the database out into file groups on
> separate physical disks in order to boost performance.
> I keep getting hung up on spindle count. I have always subscribed to
> "the more spinning heads the better". The flaw in this theory appears
> to be as the smallest disk you can buy for a given solution gets larger
> and larger , where is the trade off? Do you still stripe across as
> many disks as possible and end up burning a lot of disk space? Does
> the large amount of cache (4GB on our new system) decrease the number
> of spindles needed?
> Is there an "easy" way or rule of thumb that would allow me to
> translate 10 disk wide RAID5 performance into X disk wide Raid 10
> performance. In a nutshell, how can I estimate what the following gets
> me with regard to reducing the number of spindles necessary:
> 1. Going to RAID10 from RAID 5
> 2. New solution has 4GB of cache / old solution had 512MB
> If I go with the vendor recommendations for table placement in file
> groups I will end up with something in the range of four (4) - two (2)
> disk wide RAID 10 sets. The RAID 10 sets will actually encompass 4
> disks, but only 2 will be useable for space and my understanding is
> that only two (2) will be counted for parallel I/O performance. I am
> nervous about how narrow these RAID 10 sets are but maybe it does not
> matter as much?
> As a side note, i have no good way other than what I can get through
> Profiler to load test the new solution with actual production data.
> Any insight/thoughts would be appreciated.
> Thanks!
> Paul
>|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||Just to clarify, the vendor's recommendation would leave me with 4
separate RAID 10 sets, each of which would be two (2) disks wide. So I
would have a total of eight (8) disks with different tables assigned to
each RAID set via file groups.
I guess I am trying to determine whether it is worth my time to try and
break this thing out into file groups to separate specific tables onto
different RAID sets, therefore isolating I/O, or simply stripe across
as many disks as I can in a newly built RAID 10 array. The obvious
problem with breaking the tables out across multiple RAID sets, is that
I have no idea if the I/O generated by table X will be supported on a 2
disk wide, R10 array.
I agree that the simpler approach would be to simply stripe across as
many disks as possible. Not only would I have the benefit of R10 over
R5, but spindle count would not be an issue.
So Linchi, we could also use your formula to determine how a new 24
disk (12 useable) R10 array would compare to the existing 10 disk R5
array?
Paul|||If your database was running great on the Raid5 then what basis does the
vendor have to warrant splitting it up? While there are times when
splitting up tables, indexes etc. onto separate arrays will boost
performance you need to know you are doing the right thing. Have you done
any analysis that indicates this will help performance? You are probably
better off making a large RAID 10 and put all the files you now have ont he
Raid 5 on it. Keeping the Logs and Tempdb on their arrays either raid 1 or
10. If you don't know the capacity of the 4 disk Raid 10's and how much I/O
you will have for each you are more likely to make a bottleneck.
Andrew J. Kelly SQL MVP
"Paul" <ptimmerm@.gmail.com> wrote in message
news:1149023773.865667.104550@.g10g2000cwb.googlegroups.com...
> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>|||Andrew,
These all are very good points. Thank you for raising them. The
recommendation for breaking the database up is based solely on their
poor design. 7 tables (including indexes) in their database make up
75% of the size of the DB. The DB is approximately 200GB. These
tables and a few others (not so large) contain 50 - 200 million records
a piece. It is not until a later release (a couple of years down the
road) that these tables are broken out into 6 or 7 "rolled up" tables.
Given the growth rate, many customers with minimum spec hardware (of
which we are not one) begin to see serious I/O problems that are
"resolved" by placing these tables, and others, on their own dedicated
RAID sets. As I continue to discuss this with others, I am leaning
more towards the large RAID10 array. I could still implement a
multiple data file structure in case we needed to make some moves down
the road, but for now stripe all of the files across the one large
RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
performance bump from going to RAID10, which on top of the abundance of
cache and brand new hardware, should make for a very high performance
system. I am just looking for some way to quantify all of this.
Management likes numbers and statistics.
Another good point of your leads me to a specific question..... Is
there any way for me to measure I/O per database object? Is there any
way for me to estimate how much I/O would be hitting a given RAID set
if I know what tables will be located there?
Thoughts/ideas?
Paul|||Paul wrote:
> Andrew,
> These all are very good points. Thank you for raising them. The
> recommendation for breaking the database up is based solely on their
> poor design. 7 tables (including indexes) in their database make up
> 75% of the size of the DB. The DB is approximately 200GB. These
> tables and a few others (not so large) contain 50 - 200 million records
> a piece. It is not until a later release (a couple of years down the
> road) that these tables are broken out into 6 or 7 "rolled up" tables.
> Given the growth rate, many customers with minimum spec hardware (of
> which we are not one) begin to see serious I/O problems that are
> "resolved" by placing these tables, and others, on their own dedicated
> RAID sets. As I continue to discuss this with others, I am leaning
> more towards the large RAID10 array. I could still implement a
> multiple data file structure in case we needed to make some moves down
> the road, but for now stripe all of the files across the one large
> RAID10. FYI... RAID 10 was my decision, not theirs. We will get a
> performance bump from going to RAID10, which on top of the abundance of
> cache and brand new hardware, should make for a very high performance
> system. I am just looking for some way to quantify all of this.
> Management likes numbers and statistics.
> Another good point of your leads me to a specific question..... Is
> there any way for me to measure I/O per database object? Is there any
> way for me to estimate how much I/O would be hitting a given RAID set
> if I know what tables will be located there?
> Thoughts/ideas?
> Paul
>
Hi Paul
You don't mention anything about your READ/WRITE ratio. If you have far
more reads than writes, it could be that you'd be better off with a RAID
5 array. We have been through the same thoughts for some time ago and
ended up with a 24 disk RAID 5 array. I can't remember the exact
figures, but in our database we have something like 90 % read time so we
where really after a solution that could give a good read performance.
We talked with various storage experts and they more or less all agreed
that a RAID 5 array would give better read performance than a RAID10
assumed we used the same number of disks. That leaves us with a 1,5 TB
array where we're only using approx. 150 GB so there're a lot of "waste"
of space but that doesn't really matter.
We haven't tested the various solutions so actually we don't know if our
RAID 5 array performs better of worse than if we had a RAID 10 array
with the same number of disks.
Regards
Steen|||Linchi Shea wrote:
> Paul;
> 4GB of cache is unlikely to significantly decrease the number of spindles
> you need to stripe across to get the performance. Very large cache size do
es
> have an tendency to mask the need for more spindles. But that really depen
ds
> on the workloads (on whether the workloads can take advantage of that cach
e).
>
> There is commonly known RAID formula for doing this estimate. Assume that
> the read:write ratio of the workload is 3:1--a very typical ratio, and XXX
ume
> that you are using 15,000rpm disks, which can roughly do 180 IOs per secon
d
> (IOps).
> RAID 5: Since each write needs 4 disk I/Os and eahc read needs one disk I
/O,
> the I/O penalty is 4/(3+4) = 0.57
> RAID 10: Since each write needs two disk I/Os and each read needs one,
> the I/O penalty is 4/(3+2) = 0.80
> For 10-disk RAID 5, you can do 180 * 0.57 * 10 = 1026 IOps.
> For the RAID 10 to do 1026 IOps, you need 1026/(180 *0.80) ~= 8 disks
>
Hi Linchi
Do you have any links to this formula/calculation?
Regards
Steen Schlüter Persson
DBA|||There is no need to find any link to see how this formula works. Here's how.
Again assume the read:write ratio is 3:1 (or any other ratio you may fancy),
and assume that the total number of I/Os per second submitted to the RAID
device is Total_raid_iops. Hence,
The total number of writes per second = Total_raid_iops * 1/4, and
The total number of reads per second = Total_raid_iops * 3/4
For RAID 10, at the individual disk level, the total number of I/Os per
second for all the disks inside the RAID device are as follows:
The total number of writes per second = Total_raid_iops * 1/4 * 2
The total number of reads per second = Total_raid_iops *3/4 * 1
And
The grand total number of I/Os per second at the disk level
= (Total_raid_iops * 1/4 * 2 + Total_raid * 3/4 * 1)
= Total_raid_iops * (2/4 + 3/4)
= Total_raid_iops * 5/4
Given that a 15,000rmp disk can do about 180 I/Os per second, we have the
following:
Total_raid_iops * 5/4 = 180 * Number_of_disks
So if you know how many I/Os per second you want the RAID 10 device to do,
you can determine how many disks you need in this RAID 10 device, or if you
know the number of disks already in a RAID 10 device, you can determine how
many I/Os per second this device can do given the assumptions on write:read
ratio and the disk rotation speed.
Linchi
"Steen Persson (DK)" wrote:

> Linchi Shea wrote:
> Hi Linchi
> Do you have any links to this formula/calculation?
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
Yes, but I wouldn't want to bet my house the results. I trust the results
from controlled tests much better. As mentioned before, I'd use a tool such
as sqlio.exe or IOmeter to profile each RAID configuration. It is not an
extraordinary request to ask your SA or storage folks to give you the two
configurations for you to test. If the system is important, make a case to
your management to allow time for these tests. That would be time much bette
r
spent than dwelling on theoretical estimates.
Once you have the solid test results, you can much better confront (okay
discuss with) your vendor with those numbers and ask them to justify their
recommendations in light of the test results.
Linchi
"Paul" wrote:

> Just to clarify, the vendor's recommendation would leave me with 4
> separate RAID 10 sets, each of which would be two (2) disks wide. So I
> would have a total of eight (8) disks with different tables assigned to
> each RAID set via file groups.
> I guess I am trying to determine whether it is worth my time to try and
> break this thing out into file groups to separate specific tables onto
> different RAID sets, therefore isolating I/O, or simply stripe across
> as many disks as I can in a newly built RAID 10 array. The obvious
> problem with breaking the tables out across multiple RAID sets, is that
> I have no idea if the I/O generated by table X will be supported on a 2
> disk wide, R10 array.
> I agree that the simpler approach would be to simply stripe across as
> many disks as possible. Not only would I have the benefit of R10 over
> R5, but spindle count would not be an issue.
> So Linchi, we could also use your formula to determine how a new 24
> disk (12 useable) R10 array would compare to the existing 10 disk R5
> array?
> Paul
>sql