Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 07 Jun 2007 17:37:46 -0700
Message-ID: <1181263065.524632@bubbleator.drizzle.com>


beth.stover_at_gmail.com 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..._at_gmail.com> wrote:

>> On Jun 7, 3:49 pm, beth.sto..._at_gmail.com wrote:
>>
>>
>>
>>
>>
>>> On May 25, 7:42 am, EscVector <J..._at_webthere.com> wrote:
>>>> On May 24, 5:56 pm, beth.sto..._at_gmail.com wrote:
>>>>> My apologies if this subject has been discussed. I searched the
>>>>> 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!
>>>> Don't use the windows tools to check on SAN performance. Talk with
>>>> 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 -
>>> Thanks for all of the information and suggestions.
>>> 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.
>> Congratulations. So you're the new DBA. Don't panic.http://www.ooug.org/presentations/newDBAexpanded.pdf
>>
>> 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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jun 07 2007 - 19:37:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US