473,549 Members | 2,614 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

I/O Component Bottleneck Theory

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
Jul 20 '05 #1
9 1607
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
Jul 20 '05 #2

"Ornac" <ds*****@naviga ntconsulting.co m> wrote in message
news:e8******** *************** ***@posting.goo gle.com...
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
Jul 20 '05 #3
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*****@naviga ntconsulting.co m> wrote in message
news:e8******** *************** ***@posting.goo gle.com...
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

Jul 20 '05 #4
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

"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

Jul 20 '05 #6
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!
Jul 20 '05 #7
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!


Jul 20 '05 #8
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!


Jul 20 '05 #9
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2732
by: Tino Lange | last post by:
Hi! I identified a bottleneck in my programs. I just want to "encrypt" data by easy xoring. Ok - that's no encryption at all - I know. But it's hardly readable - and that's enough :-) Just some quick obscurity. It turns out not to be quick at all. I really didn't expect this to be a bottleneck, but it takes quite some time.
14
3163
by: Tina Li | last post by:
Hello, I've been struggling with a regular expression for parsing XML files, which keeps giving the run time error "maximum recursion limit exceeded". Here is the pattern string: r'<code>(?P<c>.*?)</code>.*?<targetSeq name="(?P<tn>.*?)">.*?<target>(?P<t>.*?)</target>.*?<align>(?P<a>.*?)</align>.*?<template>(?P<temp>.*?)</template>.*?<an...
1
2527
by: Xah Lee | last post by:
Dear functional programing comrades, Among the community of automatons of the IT industry, there is a popular quote about "theory vs practice" that goes something along the lines of "in theory this, but in practice that", which is often quoted by automatons to slight at computer science principles or practice. (especially by perl or unix...
1
2426
by: Raed Sawalha | last post by:
Hello I have ActiveX DLL and I usually register it using following procedure 1. gactutil -i ActiveXDLL 2. regasm ActiveXDLL /tlb:DLLNAME/codebase 3. then using the activeX can I automate step 1 and 2 inside ActiveX itself
8
1726
by: Amelyan | last post by:
I need some help to confirm my theory! I think I discovered something new for myself about behavior System.Web.UI.Page. THEORY: Every time I change control on my WebForm1 page that results in PostBack, it is handled by a new instance of WebForm1 class, i.e. a new instance of WebForm1 class is created every time. In general, any GET or...
4
1477
by: Bob | last post by:
I know this is a tall order, but I'm looking for a book that talks about the implications of alternative approaches to languages than we typically see, such as allowing multiple inheritance... detailed, but not so heavy that the interesting, qualitative conclusions are left to the reader to dig out of a set of equations. Any recommendations? ...
2
4843
by: senfo | last post by:
I'm using a ListView control to display pictures similarly to how Windows Explorer displays pictures in "Thumbnails" view. The images are stored in an ImageList component. I would like to provide the ability to rotate the images and I'm having some issues. Basically, what I've tried is: ...
7
1719
by: jeffc | last post by:
Not sure exactly what my question is or what forum to ask on. I know how to get version info from a particular file. e.g. FileVersionInfo.GetVersionInfo(filePath) What I'd like to do is get version info for Microsoft "components" such as MDAC, DAO, MFC Runtime, etc. An "overall" level rather than specific file version. For example .NET...
0
7532
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7462
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7730
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7975
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
5381
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5101
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3510
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1957
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
777
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.