Re: Need some ideas to create a disk IOPS profile for a RHEL 5.3 64bit system

the awr_iowl has the following columns

AAS
RMAN CPU
CPU IO WAIT Utilization
OS Load
Single Block R/W IOPS
Multi Block R/W IOPS
R/W MB/s
Total R/W IOPS
R/W Ratio
HW Disk IOPS
HW # of Disks

The first three columns relates to performance and load of the database. This will let you know if a specific SNAP_ID with particular IOPS or MB/s is already waiting on IO.. or has high AAS or there could be an RMAN backup on going..

Showing the data this way you'll not be blinded by the performance behind the IOPS and MB/s numbers. Plus you'll get the detail if it's a single or multi block read or write activity.

On Sat, Feb 12, 2011 at 10:07 AM, Karl Arao <karlarao_at_gmail.com> wrote:

> Attached is the awr_iowl.sql
>
> This script is ideal for monitoring the Oracle IO activity. Very
> useful for sizing and consolidating storage for Oracle
> databases. This can be used together with a storage
> monitoring tool to have a complete picture of IO performance. I would use
> collectl on this.
>
> The last two columns have the corresponding formula that is
> used by storage engineers to determine the number of disk
> needed by the database.
>
> HW Disk IOPS = (IOPS * Read Ratio) + (IOPS * Write Ratio * RAID penalty)
>
> HW # of Disks = Total disk IOPS / IOPS per disk
>
> Of course the “HW # of Disks” is not the final number. There
> are other factors (bandwidth, throughput, service time, etc.)
> that need to be considered to determine the right storage for a
> particular IO workload but this can be your starting point. Also
> benchmarking will help a lot on the storage decisions.
>
>
> To generate load on the disks I've create a toolkit for orion.. you can get
> the oriontoolkit.zip here http://karlarao.wordpress.com/scripts-resources/
>
> the toolkit automates the process of doing all the orion tests I need for
> DSS and OLTP.. as well as collecting collectl data and organizing them into
> folders once the run has finished.
>
> So the first part gives me a characterization of how the disks perform on
> sustained loads.
> params_dss_randomwrites
> params_dss_seqwrites
> params_dss_randomreads
> params_dss_seqreads
> params_oltp_randomwrites
> params_oltp_seqwrites
> params_oltp_randomreads
> params_oltp_seqreads
>
> The second parts gives me a characterization of how the disks perform on
> increasing loads.
> params_dss
> params_oltp
>
> Notice I am testing for both reads and writes as well as DSS and OLTP just
> to give a clear picture of how are my devices if I were to run these type of
> workloads.
>
> Then the next would be actually creating the databases and load testing to
> validate what I'm seeing from the OS/HW perspective.
>
>
> Here's the sample output.
>
>
> +++81 FULL run of oriontoolkit, ShortStroked five (5) disk on one LVM
> logical volume 467GB... four on 100/1000GB and one of them on
> 533GB-635GB/1000GB
>
> -----------------------------------------------------------------------------------------------------------------
> +++ params_dss_randomwrites Maximum Large MBPS=191.39 _at_ Small=0 and
> Large=256
> +++ params_dss_seqwrites Maximum Large MBPS=205.94 _at_ Small=0 and
> Large=256
> +++ params_dss_randomreads Maximum Large MBPS=229.20 _at_ Small=0 and
> Large=256
> +++ params_dss_seqreads Maximum Large MBPS=242.25 _at_ Small=0 and
> Large=256
> +++ params_oltp_randomwrites Maximum Small IOPS=570 _at_ Small=256 and
> Large=0 Minimum Small Latency=448.05 _at_ Small=256 and Large=0
> +++ params_oltp_seqwrites Maximum Small IOPS=574 _at_ Small=256 and
> Large=0 Minimum Small Latency=444.86 _at_ Small=256 and Large=0
> +++ params_oltp_randomreads Maximum Small IOPS=943 _at_ Small=256 and
> Large=0 Minimum Small Latency=271.14 _at_ Small=256 and Large=0
> +++ params_oltp_seqreads Maximum Small IOPS=954 _at_ Small=256 and
> Large=0 Minimum Small Latency=268.01 _at_ Small=256 and Large=0
> +++ params_dss Maximum Large MBPS=232.95 _at_ Small=0 and Large=15
> +++ params_oltp Maximum Small IOPS=603 _at_ Small=20 and Large=0 Minimum
> Small Latency=9.27 _at_ Small=1 and Large=0
>
>
> and here are my test cases around orion
> http://karlarao.tiddlyspot.com/#OrionTestCases
>
>
> Hope this helps..
>
>
>
> --
> Karl Arao
> karlarao.wordpress.com
> karlarao.tiddlyspot.com
>

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com

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