FW: How to caliculate the IO Demand for IOPS and ThroughtPut

From: Robin Moffatt <Robin.Moffatt_at_morrisonsplc.co.uk>
Date: Thu, 23 Jun 2011 07:33:02 +0100
Message-ID: <02527969A439A74998488AC5540765809FFCF397DE_at_EXCH1.morrisonsplc.co.uk>

If you're planning for the capacity of a new system then make sure you consider the peaks of your current workload, not the averages. For example, AWR is going to give you the *average* IO throughput over the snapshot period. If you size your new system based on an average then you'll obviously hit problems.

ObBlogPlug: http://rnm1978.wordpress.com/2011/03/11/getting-good-quality-io-throughput-data/

v$sysstat plus OS level utils (sar, etc) for a belts-and-braces approach, is what I'd use. Kevin Closson's script http://kevinclosson.wordpress.com/2009/04/28/how-to-produce-raw-spreadsheet-ready-physical-io-data-with-plsql-good-for-exadata-good-for-traditional-storage/ is very useful.

Oh, and you will find Alex Gorbachev's presentation "Database I/O Performance: Measuring and Planning" very relevant.

Robin

--
http://rnm1978.wordpress.com/
http://twitter.com/rnm1978


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber
Sent: 11 June 2011 22:01
To: kamusis_at_gmail.com
Cc: post.ethan_at_gmail.com; dbaprimatics_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: How to caliculate the IO Demand for IOPS and ThroughtPut

This will get most of the information that you need.   You can modifiy it as necessary to get other information:

select
   to_char(sn.end_interval_time,'yyyymmddhh24') the_date,
   sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)) reads,
   sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value)) writes,
   (sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)))+
   (sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value))) total
from
   dba_hist_sysstat oldreads,
   dba_hist_sysstat newreads,
   dba_hist_sysstat oldwrites,
   dba_hist_sysstat newwrites,
   dba_hist_snapshot   sn,
   dba_hist_snapshot   sn2,
   dba_hist_snapshot   sn3
where sn.instance_number=dbms_utility.current_instance
   and sn.instance_number=sn2.instance_number
   and sn2.instance_number=sn3.instance_number
   and oldreads.instance_number=sn3.instance_number
   and newreads.instance_number=oldreads.instance_number
   and oldreads.instance_number=oldwrites.instance_number
   and oldwrites.instance_number=newwrites.instance_number
   and newreads.snap_id=sn.snap_id
   and newwrites.snap_id=newreads.snap_id
   and sn.instance_number=oldreads.instance_number
   and oldreads.instance_number=newreads.instance_number
   and sn.instance_number=oldwrites.instance_number
   and oldwrites.instance_number=newwrites.instance_number
   and oldreads.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newreads.snap_id and
 sn.instance_number=newreads.instance_number and newreads.instance_number=oldreads.instance_number)
   and oldreads.snap_id=sn2.snap_id
   and newreads.snap_id=sn3.snap_id
   and oldwrites.snap_id = (select max(sn.snap_id) from dba_hist_snapshot sn where sn.snap_id<newwrites.snap_id and
 sn.instance_number=newwrites.instance_number and newwrites.instance_number=oldwrites.instance_number)
   and oldreads.stat_name = 'physical reads'
   and newreads.stat_name = 'physical reads'
   and oldwrites.stat_name = 'physical writes'
   and newwrites.stat_name = 'physical writes'
group by to_char(sn.end_interval_time,'yyyymmddhh24')
order by to_char(sn.end_interval_time,'yyyymmddhh24')
;
On Sat, Jun 11, 2011 at 8:39 AM, Zhang Leyi (Kamus) <kamusis_at_gmail.com<mailto:kamusis_at_gmail.com>> wrote:
If Karl Arao has seen this post, I'm sure he will recommend you to check his blog article: Workload characterization using DBA_HIST tables and kSar
You can find it in: http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/

And also, he has written a couple of scripts to determine the system capacity which you can find in his shared Google Docs:
http://docs.google.com/leaf?id=0B5H46jS7ZPdJMDEyMmYyYjEtZDA2MC00NWRlLWIzMWYtMWQyZDlmYTA5YWM1&hl=en

--
Zhang Leyi (Kamus) <kamusis_at_gmail.com<mailto:kamusis_at_gmail.com>>

Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org



On Jun 11, 2011, at 3:24 PM, Ethan Post wrote:



> If Oracle is the only major software running on the server you should be able to get this from just grabbing iostat and vmstat data. Oracle has a free tool called OSWatcher (Google it) which I run on most of my servers.

>

> I would confirm the iostat data with data from gv$filestats and some of the values from gv$system_statistics for things like physical writes, reads, amount of redo generated to make sure they fall in line as I don't always trust the accounting I see in iostat.

>

> If you have OEM/Grid Control installed you should be able to see all this stuff without homegrown scripts.

>

> You could also run some ash reports during peak hours and see what that tells you. Look for ash* files in $ORACLE_HOME/rdbms/admin directory.

>

>

> On Fri, Jun 10, 2011 at 3:26 PM, Vamshi Damidi <dbaprimatics_at_gmail.com<mailto:dbaprimatics_at_gmail.com>> wrote:

> Hi All,

>

> We are currently on DAS file system  and would like to move to SAN. and with New servers

> and we need to calculate the demand for IO and CPU so that we can design the disk architecture and buy the server accordingly.

> Please let me know if you need any more information.

>

> Any help would be greatfull.

>

--
http://www.freelists.org/webpage/oracle-l



--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

______________________________________________________________________
Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential. 

If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email. 
If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way. 

This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility, 
and it is the responsibility of recipients to carry out their own virus checks. 
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 23 2011 - 01:33:02 CDT