Oracle

February 18, 2011

Calculate iops for a database instance

Filed under: Uncategorized — srivenu @ 9:26 pm

This blog entry is about the method i normally use to estimate the iops for a currently operational database instance.
There was a SIEBEL database at a client site that was presently running on a 2 node RAC (10.2.0.4) with SUN Cluster and QFS. A high level decision has been made to move the database to a new storage and to ASM. I was asked to provide the IO load of the existing database so that the new storage could be provisioned. AWR in that database was scheduled to run every 15 minutes.

I used the following sql to get the data from AWR.

define  name='physical read total IO requests'
define inst_no=1
define slotsize=900
define rangesize=500
define start_peak_hour=8
define end_peak_hour=15

col range head "   IOPS Range" form a20
col noofslots head "No Of|Occurences" form 999,999


select	lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range,
	sum(noofslots) noofslots
from	(
	select	st/(extract(DAY from (dest - dsst)) * 86400 + extract(HOUR from (dest - dsst)) * 3600+ 
		extract(MINUTE from (dest - dsst)) * 60+ extract(SECOND from (dest - dsst))) vpersec,
		round((extract(DAY from (dest - dsst)) * 86400 + extract(HOUR from (dest - dsst)) * 3600+ 
		extract(MINUTE from (dest - dsst)) * 60+ extract(SECOND from (dest - dsst)))/&slotsize) noofslots 
	from	(
		select 	/*+ full(st) full(ss) use_hash(st)*/
			to_char(ss.BEGIN_INTERVAL_TIME,'DAY DD-MM-YYYY HH24:MI:SS') est, 
			lag(to_char(ss.BEGIN_INTERVAL_TIME,'DAY DD-MM-YYYY HH24:MI:SS')) 
			over(order by ss.snap_id) sst,
			ss.BEGIN_INTERVAL_TIME dest,
			lag(ss.BEGIN_INTERVAL_TIME) over(order by ss.snap_id) dsst,
			st.value-lag(st.value,1,0) over(order by ss.snap_id) st
		from 	sys.WRH$_SYSSTAT 	st,
			sys.WRM$_SNAPSHOT 	ss
		where 	st.snap_id=ss.snap_id
		and	ss.INSTANCE_NUMBER = &inst_no
		and	st.INSTANCE_NUMBER = &inst_no
		and	ss.dbid=st.dbid
		and	st.stat_id in (
			select	stat_id
			from	v$statname
			where	upper(name)=upper(trim('&name'))
			)
		and 	ss.snap_id in (
			select 	/*+push_subq*/
				snap_id
			from 	sys.WRM$_SNAPSHOT 
			where	INSTANCE_NUMBER = &inst_no
			and	to_char(BEGIN_INTERVAL_TIME, 'HH24') between &start_peak_hour and &end_peak_hour
	   		) 
		)
	where   sst is not null
	and	st > 0
	and	to_char(to_date(sst, 'DAY DD-MM-YYYY HH24:MI:SS'),'dd-mon-yy') 
		= to_char(to_date(est, 'DAY DD-MM-YYYY HH24:MI:SS'),'dd-mon-yy')
	)
group	by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
order	by lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
/

The peak hours were defined by the client as between 8AM and 3PM. Each snap of 900 seconds is considered as 1 slot. Sometimes a snap might have missed out or sometimes a manual snap might have been taken between a regular 15 minute slot. The sql would handle such cases.


For Write iops, we run the above sql by redifining name
define  name='physical write total IO requests'

For redo writes, we run the sql by redefining name
define  name='redo writes'

If the AWR snaps are scheduled every 1 hour and the peak times are 10AM to 6PM, we can run this sql with these defined values.

define slotsize=3600
define start_peak_hour=10
define end_peak_hour=18

Consider this sample data from one of our instances

*******************************
physical read total IO requests
*******************************

                          No Of
   IOPS Range        Occurences
-------------------- ----------
  2000 -   2499              41
  2500 -   2999              84
  3000 -   3499              57
  3500 -   3999              29
  4000 -   4499               9
  4500 -   4999               5
  5000 -   5499               2
  5500 -   5999               2


*******************************
physical read write IO requests
*******************************

                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499              75
   500 -    999             147
  1000 -   1499               4
  1500 -   1999               2
  2000 -   2499               1


***********
redo writes
***********


                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499             229

Interpreting data from a single instance database is a bit easier. I would consider the less than 1% occurences as abnormalities that could be ignored. From above data, i would consider the read IOPS to be 5000. I would consider the 2 occurences each between “5000 – 5499” and “5500 – 5999” as abnormalities and ignore them. Using similar logic, i would consider writes to be 1500 (or 2000 for safety margin) and redo writes to be 500. To be cautious, we could look at the snap intervals, corresponding to the abnormal data, in more detail to discern the cause.

But things get a little more complicated when we have RAC.
Consider this from one of our 2-Node RAC instances.


*******************************
physical read total IO requests
*******************************

Node 1

                          No Of
   IOPS Range        Occurences
-------------------- ----------
  2000 -   2499              41
  2500 -   2999              84
  3000 -   3499              57
  3500 -   3999              29
  4000 -   4499               9
  4500 -   4999               5
  5000 -   5499               2
  5500 -   5999               2


Node 2


                          No Of
   IOPS Range        Occurences
-------------------- ----------
  1500 -   1999               4
  2000 -   2499              40
  2500 -   2999              69
  3000 -   3499              36
  3500 -   3999              20
  4500 -   4999               4
  5000 -   5499               2
  6500 -   6999               1
  7000 -   7499               2
  7500 -   7999               2


*******************************
physical read write IO requests
*******************************


Node 1

                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499              75
   500 -    999             147
  1000 -   1499               4
  1500 -   1999               2
  2000 -   2499               1


Node 2

                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499              77
   500 -    999              95
  1000 -   1499               5
  1500 -   1999               2
  2000 -   2499               1



***********
redo writes
***********

Node 1

                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499             229


Node 2


                          No Of
   IOPS Range        Occurences
-------------------- ----------
     0 -    499             180

Now what should we take the read IOPS to be for the whole database?
I would consider Node 1 max as 5000 and Node 2 max as 5000. So should i just add them up? That would mean assuming that both nodes peak at the same time and might lead to overestimation of the IOPS at the database level.
I would blog about that calculation in a seperate post.

Advertisements

5 Comments »

  1. Hi I am trying to use your script, but when I ran this it stop with the following error ORA-01008 at
    = to_char(to_date(est, ‘DAY DD-MM-YYYY HH24:MI:SS’),’dd-mon-yy’)

    I am not a sql person

    thanks and Regards

    Comment by IIL — July 11, 2012 @ 2:12 am | Reply

    • It works for me.
      regards
      srivenu

      Comment by srivenu — October 12, 2012 @ 2:37 am | Reply

  2. Excellent Post

    Comment by Raghunath — August 23, 2012 @ 4:00 pm | Reply

  3. Thank You Babai!! I used this query for calculating IOPS for my databases.

    Comment by Sunil — November 3, 2014 @ 3:14 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: