Determining my IOPS need when planning Oracle RDS in AWS

When provisioning a database in AWS, an RDS database that is.  You must carefully look into your IOPS requirements.

Why,  well let me give you a hint  --> $$$$$$$

A classic line from AWS is – you pay for what you use – period!

So, the more IOPS you provision – the more that you will pay.

For example, in Sydney for a single AZ deployed database, you will pay:

Storage Rate
$0.138 per GB-month

Provisioned IOPS Rate
$0.11 per IOPS-Month

So, if I want 3000 IOPS, this is going to cost 3000x.11 per month.  or 330 bucks a month for guaranteed IOPS.  + my storage usage

How many IOPS do I need, well you could try this:  https://d0.awsstatic.com/whitepapers/determining-iops-needs-for-oracle-database-on-aws.pdf which is cool, but comes down to your writing some SQL:

CREATE TABLE peak_iops_measurement (capture_timestamp date,
total_read_io number, total_write_io number, total_io number,
total_read_bytes number, total_write_bytes number, total_bytes
number);

DECLARE
run_duration number := 3600;
capture_gap number := 5;
loop_count number :=run_duration/capture_gap;
rdio number;
wtio number;
prev_rdio number :=0;
Prev_wtio number :=0;
rdbt number;
wtbt number;
prev_rdbt number;
Prev_wtbt number;
BEGIN
FOR i in 1..loop_count LOOP
SELECT SUM(value) INTO rdio from gv$sysstat
WHERE name ='physical read total IO requests';
SELECT SUM(value) INTO wtio from gv$sysstat
WHERE name ='physical write total IO requests';
SELECT SUM(value)* 0.000008 INTO rdbt from gv$sysstat
WHERE name ='physical read total bytes';
SELECT SUM(value* 0.000008) INTO wtbt from gv$sysstat
WHERE name ='physical write total bytes';
IF i > 1 THEN
INSERT INTO peak_iops_measurement (capture_timestamp,
total_read_io, total_write_io, total_io, total_read_bytes,
total_write_bytes, total_bytes)
VALUES (sysdate,(rdio-prev_rdio)/5,(wtio-prev_wtio)/5,((rdioprev_rdio)/5)+((wtio-prev_wtio))/5,(rdbt-prev_rdbt)/5,(wtbtprev_wtbt)/5,((rdbt-prev_rdbt)/5)+((wtbt-prev_wtbt))/5);
END IF;
prev_rdio := rdio;
prev_wtio := wtio;
prev_rdbt := rdbt;
prev_wtbt := wtbt;
DBMS_LOCK.SLEEP(capture_gap);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/

This is cool, but you need to wait days…  It’ll sit there chugging too.

Of course, you could just use:

select METRIC_NAME,avg(AVERAGE) as "Value", max(average) as maxval
   from dba_hist_sysmetric_summary
   where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
   group by METRIC_NAME;

Which will give you something like:

METRIC_NAME                                                      Value                  MAXVAL                
---------------------------------------------------------------- ---------------------- ----------------------
Physical Write Total IO Requests Per Sec                         38.47016094280773108458390177353342428377 1616.23477993026      
Physical Read Total IO Requests Per Sec                          140.575645080103957442019099590723055935 2946.16113345313      

Wow, that is pretty neat – pretty much exactly what I want.  Note that this is the total AVERAGES for ever.  If you want some more information, you could graph the details in the results based upon the snap_id:

Get the snapshot information:

select * from dba_hist_snapshot;
 

Get the statistics per snap:

   select
  snap_id,
  metric_name "Metric Name",
  metric_unit "Metric Unit",
  minval "Minimum Value",
  maxval "Maximum Value",
  average "Average Value"
from
  DBA_HIST_SYSMETRIC_SUMMARY
where
       snap_id         

For a totally RAD summary:

select t1.snap_id, t2.METRIC_NAME,t2.average, t2.metric_unit, t1.begin_interval_time
   from dba_hist_sysmetric_summary t2, dba_hist_snapshot t1
   where t2.METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
   and t2.snap_id=t1.snap_id
   order by t2.snap_id desc;

 

Now, a much better view

 

select t2.maxval+t3.maxval ,t2.average + t3.average, t1.begin_interval_time
   from dba_hist_sysmetric_summary t2, dba_hist_snapshot t1, dba_hist_sysmetric_summary t3
   where t2.METRIC_NAME in ('Physical Read Total IO Requests Per Sec')
   and t3.METRIC_NAME = 'Physical Write Total IO Requests Per Sec'
   and t2.snap_id=t3.snap_id
   and t2.instance_number= t2.instance_number
   and t2.begin_time= t3.begin_time
   and t2.snap_id=t1.snap_id
   order by t2.snap_id desc;

Which produces the following

I would not use MAX, but trendline analysis.  These guys would probably be okay with 1500, but I’d make it 2000 IOPS.