FW: How to caliculate the IO Demand for IOPS and ThroughtPut
- From: Robin Moffatt <Robin.Moffatt@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Jun 2011 07:33:02 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Andrew Kerber
Sent: 11 June 2011 22:01
To: kamusis@xxxxxxxxx
Cc: post.ethan@xxxxxxxxx; dbaprimatics@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:kamusis@xxxxxxxxx>> 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@xxxxxxxxx<mailto:kamusis@xxxxxxxxx>>
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@xxxxxxxxx<mailto:dbaprimatics@xxxxxxxxx>> 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.
>
--
//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.
______________________________________________________________________
Other related posts:
