Oracle

March 4, 2011

Calculate iops for a RAC database

Filed under: Uncategorized — srivenu @ 11:13 am

I had blogged previously about calculating iops for a non-RAC database instance. It could be a little tricky to get the IOPS for a RAC database.

In the previous blog entry, i had shown sql which allows you to get the iops during peak hours. The following sql can be used to get the iops for all times.

define start_time='03-FEB-2011 10:00'  -- please specify this in 'DD-MON-YYYY HH24:MI' format
define end_time  ='03-MAR-2011 18:00'  -- please specify this in 'DD-MON-YYYY HH24:MI' format
define rangesize=500
define name='physical read total IO requests'
define inst_no=1

undef v_noofmins

col mns new_value v_noofmins

select trunc((to_date('&end_time', 'DD-MON-YYYY HH24:MI') - to_date('&start_time', 'DD-MON-YYYY HH24:MI'))*1440) mns
from dual
/


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

with 	v2 as 
	(
	select	/*+materialize*/
		to_date('&start_time', 'DD-MON-YYYY HH24:MI')+(rownum/1440) timewithmin
	from	dual
	connect	by rownum < &v_noofmins
	)
select	/*+use_merge(v1 v2)*/
	lpad(trunc(vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range,
	count(*) noofslots
from	(
	select 	/*+ full(st) full(ss) use_hash(st)*/
		lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) over(order by ss.snap_id) dsst,
		ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') dest,
		(st.value-lag(st.value,1,0) over(order by ss.snap_id)) st,
		round(
		(st.value-lag(st.value,1,0) over(order by ss.snap_id) ) /
		round((CAST(ss.END_INTERVAL_TIME AS DATE) - 
			lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI')) over(order by ss.snap_id)) * 1440 * 60))
		vpersec
	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.END_INTERVAL_TIME > to_date('&start_time', 'DD-MON-YYYY HH24:MI')
	and	ss.BEGIN_INTERVAL_TIME < to_date('&end_time', 'DD-MON-YYYY HH24:MI')
	order	by 1
	) v1,
	v2
where	v2.timewithmin between v1.dsst and v1.dest
and	v2.timewithmin between v1.dsst and v1.dest
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 output is something like this

                          No Of
   IOPS Range           Minutes
-------------------- ----------
     0 -    499              59
   500 -    999             479
  1000 -   1499            1804
  1500 -   1999            3595
  2000 -   2499            8092
  2500 -   2999           11107
  3000 -   3499            7074
  3500 -   3999            3578
  4000 -   4499            2342
  4500 -   4999            1260
  5000 -   5499             539
  5500 -   5999             420
  7000 -   7499              59

13 rows selected.

For Instance 2 this is the data.
define inst_no=2

                          No Of
   IOPS Range           Minutes
-------------------- ----------
     0 -    499             179
   500 -    999             543
  1000 -   1499            1680
  1500 -   1999            3419
  2000 -   2499            4251
  2500 -   2999            4503
  3000 -   3499            4617
  3500 -   3999            3852
  4000 -   4499            1976
  4500 -   4999            2126
  5000 -   5499            1260
  5500 -   5999             840
  6000 -   6499             540
  6500 -   6999             300
  7000 -   7499             661
  7500 -   7999             240
  8000 -   8499             120

17 rows selected.

The question now is, what is the peak iops for both instances combined? Can we just sum up the peaks of the 2 nodes and say that the peak is 15500 – 16000? This might result in overestimation as both nodes might not have peaked at the same time.

I use the following sql to get the IOPS for all (could be more than 2) nodes.

define start_time='03-FEB-2011 10:00'  -- please specify this in 'DD-MON-YYYY HH24:MI' format
define end_time  ='03-MAR-2011 18:00'  -- please specify this in 'DD-MON-YYYY HH24:MI' format
define rangesize=500
define name='physical read total IO requests'

undef v_noofmins

col mns new_value v_noofmins

select trunc((to_date('&end_time', 'DD-MON-YYYY HH24:MI') - to_date('&start_time', 'DD-MON-YYYY HH24:MI'))*1440) mns
from dual
/

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

with 	v2 as 
	(
	select	/*+ materialize */
		to_date('&start_time', 'DD-MON-YYYY HH24:MI')+(rownum/1440) timewithmin
	from	dual
	connect	by rownum < &v_noofmins
	)
select	lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6) range,
	count(*) noofslots
from	(
	select	/*+use_merge(v1 v2)*/
		v2.timewithmin, sum(vpersec) vpersec
	from	(
		select 	/*+ full(st) full(ss) use_hash(st)*/
			lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) 
				over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) dsst,
			ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') dest,
			(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) st,
			round(
			(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) ) /
			round((ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), 'MI') - 
				lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), 'MI')) 
				over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) * 1440 * 60))
			vpersec
		from 	sys.WRH$_SYSSTAT 	st,
			sys.WRM$_SNAPSHOT 	ss
		where 	ss.snap_id=st.snap_id
		and	ss.dbid=st.dbid
		and	ss.INSTANCE_NUMBER = st.INSTANCE_NUMBER 
		and	st.stat_id in (
			select	stat_id
			from	v$statname
			where	upper(name)=upper(trim('&name'))
			)
		and 	ss.END_INTERVAL_TIME > to_date('&start_time', 'DD-MON-YYYY HH24:MI')
		and	ss.BEGIN_INTERVAL_TIME < to_date('&end_time', 'DD-MON-YYYY HH24:MI')
		order	by 1
		) v1,
		v2
	where	v2.timewithmin between v1.dsst and v1.dest
	and	v1.st > 0
	and	v1.dsst is not null
	group	by v2.timewithmin
	) v11
group	by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
order	by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||' - '||
	lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) - 1, 6)
/

This is an output from our 2-node RAC database.

                          No Of
   IOPS Range           Minutes
-------------------- ----------
     0 -    499              59
  1000 -   1499             601
  1500 -   1999             359
  2000 -   2499            2094
  2500 -   2999            5407
  3000 -   3499            3303
  3500 -   3999            2459
  4000 -   4499            1496
  4500 -   4999            2224
  5000 -   5499            3238
  5500 -   5999            3294
  6000 -   6499            3482
  6500 -   6999            2400
  7000 -   7499            2400
  7500 -   7999            1558
  8000 -   8499            1111
  8500 -   8999            1203
  9000 -   9499            1019
  9500 -   9999             481
 10000 -  10499             482
 10500 -  10999             300
 11000 -  11499             721
 11500 -  11999             238
 12000 -  12499             241
 12500 -  12999             177
 13000 -  13499             179
 13500 -  13999              61

27 rows selected.

So we can see that the peak was only around 14000.

Just for a little bit of explanation about the above sql.

Get the Number of Minutes between the start_time and end_time with this sql
select trunc((to_date(‘&end_time’, ‘DD-MON-YYYY HH24:MI’) – to_date(‘&start_time’, ‘DD-MON-YYYY HH24:MI’))*1440) mns
from dual

Using sub query factoring, generate a row for each minute between start_time and end_time
with v2 as
(
select /*+ materialize */
to_date(‘&start_time’, ‘DD-MON-YYYY HH24:MI’)+(rownum/1440) timewithmin
from dual
connect by rownum < &v_noofmins
)

In AWR, we have snaps running at regular intervals on all nodes. The start and end timings might not exactly match all the time.

Lets say we take hourly snaps. So from AWR, we get the value of the stat (lets say “physical read total IO requests”) for 1 hour.
(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id))

Then get the per second average
round(
(st.value-lag(st.value,1,0) over(partition by ss.INSTANCE_NUMBER order by ss.snap_id) ) /
round((ROUND(CAST(ss.END_INTERVAL_TIME AS DATE), ‘MI’) –
lag(ROUND(CAST(ss.END_INTERVAL_TIME AS DATE)+(1/1440), ‘MI’))
over(partition by ss.INSTANCE_NUMBER order by ss.snap_id)) * 1440 * 60))

Then join this row with the view V2 so that we get the per second average of data for each instance for each minute.
v2.timewithmin between v1.dsst and v1.dest

Then sum up the data from all instances for each minute.
group by v2.timewithmin

Then generate the IOPS ranges for good presentation of the data
group by lpad(trunc(v11.vpersec/&rangesize) * &rangesize, 6) ||’ – ‘||
lpad(((trunc(v11.vpersec/&rangesize) + 1) * &rangesize) – 1, 6)

I hope i did not mess up this explanation part!

Advertisements

2 Comments »

  1. Hi, this is great! However, if we gather snapshots every 30 minutes instead of 1 hour, will the result be different? Which statement should we modify to reflect the frequency of the snapshots?
    Thank you very much.
    Li

    Comment by Li Liu — December 8, 2011 @ 12:42 pm | Reply

    • Sorry for the delay,
      This should work for any snap frequency without any modifications.
      regards
      srivenu

      Comment by srivenu — December 20, 2011 @ 9:49 am | 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: