I/O Component Bottleneck Theory

[Ornac]

I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.

Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)

Configuration:
Controller: Ultra320
RAID Config: RAID 5
Drives on RAID: 14 - 73gb/15k - SCSI Drives
Type of Writes: Random

Question Background:
I currently have the configuration above on a local box. Our company
has ordered a SAN and and has space set aside. What I'm trying to
acertain is whether or not I'd be better off staying local or if
putting my DB on a SAN would be more beneficial.

Other Notes:
I've determined using a formula that determines the max theoretical
I/O operations/sec for the # of drives (spindles) and RAID Config...
but I've not been able to establish a relationship between
Operation/sec -> MB/Sec. I'm sure this is for obvious reasons...

If anyone has had a related scenario, I'd be interested in hearing
your thoughts.

Thanks,
Ornac

[Zach Wells]

Ornac wrote:

I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.

Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)

You need a "D" option of "Poorly designed database and poorly written
sql queries".

I'm being serious there. Once you have a high-end hardware solution,
like the one you're talking about, your bottlenecks will undoubtedly be
in the code and/or database design. The other thing is that RAM is very,
very helpful when dealing with large tables. SQL will basically cache
entire indexes or tables in RAM if it can, which obviously makes for
some great performance.

Zach

[Simon Hayes]

"Ornac" <ds*****@navigantconsulting.com> wrote in message
news:e8**************************@posting.google.c om...

I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.

Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)

Configuration:
Controller: Ultra320
RAID Config: RAID 5
Drives on RAID: 14 - 73gb/15k - SCSI Drives
Type of Writes: Random

Question Background:
I currently have the configuration above on a local box. Our company
has ordered a SAN and and has space set aside. What I'm trying to
acertain is whether or not I'd be better off staying local or if
putting my DB on a SAN would be more beneficial.

Other Notes:
I've determined using a formula that determines the max theoretical
I/O operations/sec for the # of drives (spindles) and RAID Config...
but I've not been able to establish a relationship between
Operation/sec -> MB/Sec. I'm sure this is for obvious reasons...

If anyone has had a related scenario, I'd be interested in hearing
your thoughts.

Thanks,
Ornac

I don't really understand from your post if you have a performance issue, or
if this is a theoretical question. Assuming that you do have an issue, is it
clearly I/O related?

Also, bear in mind that a SAN may offer functionality which you don't have
with local disks, such as easier disk space allocation, snapshot backups
etc. Depending on your situation, those might be worthwhile reasons to move,
even if the I/O throughput remains more or less the same.

Simon

[John Bell]

Hi

With your current configuration you don't say if the logs are on the same
subsystem as the data files. If they are you should see an improvement in
performance by moving them onto their own subsystem.

You may also want to check out:
http://www.sql-server-performance.co...e_planning.asp and
http://www.microsoft.com/mspress/books/index/4944a.asp for more details .

John

"Ornac" <ds*****@navigantconsulting.com> wrote in message
news:e8**************************@posting.google.c om...

I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.

Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)

Configuration:
Controller: Ultra320
RAID Config: RAID 5
Drives on RAID: 14 - 73gb/15k - SCSI Drives
Type of Writes: Random

Question Background:
I currently have the configuration above on a local box. Our company
has ordered a SAN and and has space set aside. What I'm trying to
acertain is whether or not I'd be better off staying local or if
putting my DB on a SAN would be more beneficial.

Other Notes:
I've determined using a formula that determines the max theoretical
I/O operations/sec for the # of drives (spindles) and RAID Config...
but I've not been able to establish a relationship between
Operation/sec -> MB/Sec. I'm sure this is for obvious reasons...

If anyone has had a related scenario, I'd be interested in hearing
your thoughts.

Thanks,
Ornac

[Erland Sommarskog]

Zach Wells (in*************@nibsworld.com) writes:

You need a "D" option of "Poorly designed database and poorly written
sql queries".

I'm being serious there. Once you have a high-end hardware solution,
like the one you're talking about, your bottlenecks will undoubtedly be
in the code and/or database design.

I'll challenge that, and say it is very likely that even with a fairly
plain solution, your biggest problem is in poor code or poor design.

It may of course be cheaper to throw hardware at the problem, rather than
spending man-hours of rewriting the system. (And if it's a vendor
system you only have the indexes to play with anyway.)


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Zach Wells" <in*************@nibsworld.com> wrote in message
news:2j************@uni-berlin.de...

Ornac wrote:

I understand that there is much involved in figuring up I/O
throughput, but I'm hoping the answer to the following question will
be a simple A, B OR C.

Given the configuration below, what hardware component would
bottleneck first.
A. Drive Spindle Throughput
B. SCSI Controller
C. Other Component(if so what component?)
You need a "D" option of "Poorly designed database and poorly written
sql queries".

I'll second this. Changing a single line of code in a stored proc more than
doubled our capacity on one of our servers.

(now we're starting to hit disk I/O limits.)

I'm being serious there. Once you have a high-end hardware solution,
like the one you're talking about, your bottlenecks will undoubtedly be
in the code and/or database design. The other thing is that RAM is very,
very helpful when dealing with large tables. SQL will basically cache
entire indexes or tables in RAM if it can, which obviously makes for
some great performance.

Zach

[Ornac S]

Thanks everyone for your prompt responses. I'll try to address everyone
with this post.
Zach
We do not run a transactional database but use it more for analytical
purposes. Poorly designed? Initially, Yes. Most are mainframe tables
that need normalization and standardization (among other things).
Normalizing a 200GB table is taxing and is going to bottleneck
something. While we could debate the most efficient way to normalize it,
it's beyond the scope of this post.

Simon
The performance bottlenecks are not just theory, but a reality for us.
I commonly monitor the server w/PerfMon, and the memory and processors
are relatively untouched. The I/O, however, is bottlenecking with Avg.
Disk Queue Length getting up to 27 w/ a single query and into the 70's
and 80's with multiple queries. (from what I've read, anything over 20
is considered an "I/O Bottleneck").

John
Yes, the log files are on their own subsystem and when we moved them, it
did increase performance. To take it a step further, we have moved the
Master and TempDB to their own subsystem.
All- Perhaps I've muddied the waters by giving to much specific
information in my initial post (and this post for that matter). My
intentions were not for this to become a case study, and as such trying
to find the causal factors of the bottlenecking is out of scope. With
that, I pose the theoretical question again ...

Given the configuration what hardware component would bottleneck first?
(regardless of why it's bottlenecking)
Is it because the drives can't read/write fast enough to keep up with
the Ultra320?
OR Is it the Ultra320 can't keep up with the throughput that many drives
@ that speed can generate?

Ornac

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Townsend]

Ornac S wrote:

Thanks everyone for your prompt responses. I'll try to address everyone
with this post.

Apologies if this is a dup - email barfed on me.

We (Oracle) did some work a few years back that looked at I/O
bottlenecks etc - you can see the results in White Paper called "Optimal
Storage Configuration Made Easy (SAME)", available at
http://otn.oracle.com/deploy/availab...w2000_same.pdf (You may
need an account, but it is free). Note that the findings challenge the
'seperate data from logs' mantra (at least for Oracle, but I suspect the
same is also true for SQLServer)

Zach
We do not run a transactional database but use it more for analytical
purposes. Poorly designed? Initially, Yes. Most are mainframe tables
that need normalization and standardization (among other things).
Normalizing a 200GB table is taxing and is going to bottleneck
something. While we could debate the most efficient way to normalize it,
it's beyond the scope of this post.

Simon
The performance bottlenecks are not just theory, but a reality for us.
I commonly monitor the server w/PerfMon, and the memory and processors
are relatively untouched. The I/O, however, is bottlenecking with Avg.
Disk Queue Length getting up to 27 w/ a single query and into the 70's
and 80's with multiple queries. (from what I've read, anything over 20
is considered an "I/O Bottleneck").

John
Yes, the log files are on their own subsystem and when we moved them, it
did increase performance. To take it a step further, we have moved the
Master and TempDB to their own subsystem.
All- Perhaps I've muddied the waters by giving to much specific
information in my initial post (and this post for that matter). My
intentions were not for this to become a case study, and as such trying
to find the causal factors of the bottlenecking is out of scope. With
that, I pose the theoretical question again ...

Given the configuration what hardware component would bottleneck first?
(regardless of why it's bottlenecking)
Is it because the drives can't read/write fast enough to keep up with
the Ultra320?
OR Is it the Ultra320 can't keep up with the throughput that many drives
@ that speed can generate?

Ornac

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Townsend]

Ornac S wrote:

Thanks everyone for your prompt responses. I'll try to address everyone
with this post.
FYI - we (Oracle) did some work on I/O performance, bottle necking etc a
few years back that may be relevant to your questions (it's pretty much
generic, and not specific to any one database solution). See the white
paper "Optimal Storage Configuration Made Easy (SAME)" at
http://otn.oracle.com/deploy/availab...w2000_same.pdf (you may
need to set up an account, but it's free). Note that it challenges the
'seperate logs and data' mantra.

Zach
We do not run a transactional database but use it more for analytical
purposes. Poorly designed? Initially, Yes. Most are mainframe tables
that need normalization and standardization (among other things).
Normalizing a 200GB table is taxing and is going to bottleneck
something. While we could debate the most efficient way to normalize it,
it's beyond the scope of this post.

Simon
The performance bottlenecks are not just theory, but a reality for us.
I commonly monitor the server w/PerfMon, and the memory and processors
are relatively untouched. The I/O, however, is bottlenecking with Avg.
Disk Queue Length getting up to 27 w/ a single query and into the 70's
and 80's with multiple queries. (from what I've read, anything over 20
is considered an "I/O Bottleneck").

John
Yes, the log files are on their own subsystem and when we moved them, it
did increase performance. To take it a step further, we have moved the
Master and TempDB to their own subsystem.
All- Perhaps I've muddied the waters by giving to much specific
information in my initial post (and this post for that matter). My
intentions were not for this to become a case study, and as such trying
to find the causal factors of the bottlenecking is out of scope. With
that, I pose the theoretical question again ...

Given the configuration what hardware component would bottleneck first?
(regardless of why it's bottlenecking)
Is it because the drives can't read/write fast enough to keep up with
the Ultra320?
OR Is it the Ultra320 can't keep up with the throughput that many drives
@ that speed can generate?

Ornac

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Ornac S (or***@cox.net) writes:

The performance bottlenecks are not just theory, but a reality for us.
I commonly monitor the server w/PerfMon, and the memory and processors
are relatively untouched. The I/O, however, is bottlenecking with Avg.
Disk Queue Length getting up to 27 w/ a single query and into the 70's
and 80's with multiple queries. (from what I've read, anything over 20
is considered an "I/O Bottleneck").
Looks like more memory could help to reduce this.

Then again, if you are scanning that 200 GB table you need loads of
memory, about 200 GB...

Better indexing and better queries could help, but of course, depending
on the nature of what data people are asking for this may be more or less
difficult to achieve.
Yes, the log files are on their own subsystem and when we moved them, it
did increase performance. To take it a step further, we have moved the
Master and TempDB to their own subsystem.

Since the database is not that transactional, moving log files may not
give that much.

You might be better of moving tables over file groups, and you could
also split table in partitioned views and distribute the partitions
over disk. But give the size of your data, is not something you
reconfigure over lunch to see if it helps.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp