Re: Oracle Performance -- Possible Disk Bottleneck
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Thu, 07 Jun 2007 17:37:46 -0700
beth.stover@xxxxxxxxx wrote:
Thanks for the support and the links. I'll take a look at the links
and the book.
Maybe I'm asking the wrong questions.
Maybe a better question would be:
Can 6 15k fibre channel drives in a RAID 10 configuration support 2000
IO's per second?
I've read that 15k drives can perform 180 IO's per second. And that's
with no RAID penalty. If this is the case, then my 6 drives should
only be able to handle 6 x 180 IOps = 1080 total capacity. If this is
true, then my average IO's per second of 2000+ is WAY over the
capacity of the drives.
Questions:
1. Is the scenario above a reasonable approach to solving this
problem?
2. Can 15k drives really only handle 180 IO's per second?
3. What happens to an Oracle environment (transaction oriented) when
drive IO limits are reached. Are there other metrics I can look at to
confirm this is a disk bottle neck. Is there something in the
statsapack that can show me this is a disk bottleneck?
4. Does 2000 IOps seem really high?
On Jun 7, 1:44 pm, bdbafh <bdb...@xxxxxxxxx> wrote:On Jun 7, 3:49 pm, beth.sto...@xxxxxxxxx wrote:
On May 25, 7:42 am, EscVector <J...@xxxxxxxxxxxx> wrote:Congratulations. So you're the new DBA. Don't panic.http://www.ooug.org/presentations/newDBAexpanded.pdfOn May 24, 5:56 pm, beth.sto...@xxxxxxxxx wrote:Thanks for all of the information and suggestions.My apologies if this subject has been discussed. I searched theDon't use the windows tools to check on SAN performance. Talk with
groups, and I couldn't find a good thread.
We've been having performance problems with our Client/Server
application for months. Users contantly complain of slow response
times to their queries.
Here's the environment:
Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
backend. Users connect using FastEthernet. XP clients. All disks on
the SAN are fibre channel.
CPU utilization is fine. RAM utilization is fine. Throughput on the
NIC is fine -- maxes out at 50 Mbps for a short while when users first
log in in the morning. Averages are 20 Mbps.
Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
sec., here's what I see for averages:
Disk Reads/sec = 2200 Avg
Disk Writes/sec = 10 Avg
Our reads/sec seem EXTREMELY high for only 80 users.
Can someone help me understand if this is truly a disk bottleneck?
Thanks in advance!
the storage admins. They will have a much better perspective on both
throughput and utilization. The perfmon tool might indicate an issue,
but I doubt it will tell much. I like the statspack suggestion if
done accurately and snap are scoped correctly.
Do you have virus software running? This will typically bump up I/O
reads in perfmon.- Hide quoted text -
- Show quoted text -
I did some comparison of Windows tools (Perfmon) and EMC tools
(Navisphere analyzer). Perfmon was actually reporting the IO
correctly. Average IO reads are 2000, Average Write IO is @ 10.
There are some interesting metrics that I don't understand from Nav.
Analyzer: The storage processors are showing peaks close to 100% at
certain times of day, but the disks are not at 100%. I'm not sure
what that means. LUN % utilization is the same, so it looks like the
Storage processors are responsible for the high % utilization.
STATSPACK analysis shows something similar to Perfmon and Nav.
Analyzer: Physical reads are 2552, physical writes are 52. There's a
ton of information in the statspack report, so I'm not sure what else
to look at.
There is anti-virus software on this machine, but I don't think it
accounts for a significant part of the 2000+ IOPs per second. We use
the same product on all of our servers. IO is not a problem on any
other server. On this machine, there are peaks of 10,000 IOPs. This
seems abnormally high.
We're a very small shop, so I'm the storage person. This is a new
technology for us, so I'm in a learning curve here.
There don't appear to be many good SAN related groups that are active,
so I'm hoping someone here has experience with SANs and ORACLE to help
out.
EMC support is NOT what we were expecting. For the amount we pay for
support, the response is very very disappointing. I've had a ticket
open on this for MONTHS. Literally.
I guess the bottom line here is that I need to prove to my boss that
this is a disk bottleneck and that giving the database more spindles
will help. Also, I'd like to understand why IO reads are so high for
Oracle.
Any information would be appreciated.
Ok, that one is a little bit dated.
Someone posted that you want to install and use the Oracle-provided
tool "Statspack" to determine what is performing poorly or impacting
performance of business-critical usage. Some level 7 statspack
snapshots would be a good start (if statistics_level='TYPICAL').
Here is a place to start:http://www.oracle.com/technology/deploy/performance/pdf/statspack_opm...
There is a great deal of reference material regarding Oracle's
Statspack.
Install it, take a few snapshots, generate a report and the top
statements should jump right off of the page at you. What you do with
it after that is largely a matter of what can be changed in the
environment and in the application code.
If the database is configured with default parameters, there may be a
great deal of low-hanging fruit - in terms of:
- statistics have not yet been gathered or are stale.
- buffer cache is set to a very small value and can be increased
- pga_workarea_target is very small and can be increased
- optimizer settings can be altered resulting in more favorable
execution plans and response times.
If the budget and timeframe allows take classes.
If this is urgent and there isn't time to learn it on your own, hire
in a consultant.
Check out the documentation at:
http://www.oracle.com/pls/db102/homepagehttp://www.oracle.com/technology/documentation/database10gr2.html
The "2 Day DBA" might be good for you:
About this Book
Oracle Database 2 Day DBA is a database administration quick start
guide that teaches you how to perform day-to-day database
administrative tasks. The goal of this book is to help you understand
the concepts behind the Oracle Database. It teaches you how to perform
all common administration tasks needed to keep the database
operational, including how to perform basic troubleshooting and
performance monitoring activities.
The primary administrative interface used in this book is Oracle
Enterprise Manager in Database Console mode, featuring all the self-
management capabilities introduced in the Oracle Database.
If not, the Concepts Guide is located on that same page.
good luck.
-bdbafh
.- Hide quoted text -
- Show quoted text -
I think the answer to your question is yes. But to be honest I
haven't worked with a stand-alone 15K drive in my life: Always
striped and mirrored as part of a disk set.
By way of comparison though ... the 7500 RPM drive on my laptop,
using FORALL, can easily do 500,000 inserts/sec.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- References:
- Re: Oracle Performance -- Possible Disk Bottleneck
- From: beth . stover
- Re: Oracle Performance -- Possible Disk Bottleneck
- From: bdbafh
- Re: Oracle Performance -- Possible Disk Bottleneck
- From: beth . stover
- Re: Oracle Performance -- Possible Disk Bottleneck
- Prev by Date: Re: lsnrctl can start listener, crs_start cannot
- Next by Date: Re: single node RAC system?
- Previous by thread: Re: Oracle Performance -- Possible Disk Bottleneck
- Next by thread: Re: Oracle Performance -- Possible Disk Bottleneck
- Index(es):
Relevant Pages
|
