Cloud DB Migration: IO considerations

Gone are the days with running physical disks attached directly to the hardware on data centers. As we evolved with speed on spindle disks innovations brought SSDs. RAID technologies are abstracted behind the scenes. If you still use RAID with AWS EBS volumes, it is software based, already enough redundancy built in to protect the data at AZ level. With variety of use cases, we have several choices to make based on the workload characteristics.

With cloud providers jumping and offering variety of IO characteristic volumes, its on the implementer to decide  what makes sense for the price one choose to pay. Here is the condensed version if RAIDs are used, what would be best choice for Oracle DBs. With the advent of ASM for Oracle it does the job for us. Oracle Grid Infrastructure is free of cost to use with standalone database.  

Now, moving to cloud one has to find the best way to find what to allocate on target cloud platform. CPU and Memory can be easily mimicked. vCPU increase will affects your licensing terms. Read Oracle's Cloud Licensing Policy Document.  And you can scale up and scale down EC2 or AWS RDS based on usage on target cloud platform with minimal downtime. If you have multi AZ enabled then RDS scales up standby instance first before resizing primary to have low downtime. Summary version of IO characteristics and use cases.

To find source systems' IO usage use below script if you have Diagnostics Pack licensing with your Oracle EE. 

SQL> select METRIC_NAME,max(MAXVAL) as "IO Value"

from dba_hist_sysmetric_summary

where METRIC_NAME in (

'Physical Read Bytes Per Sec',

'Physical Read IO Requests Per Sec',

'Physical Read Total Bytes Per Sec',

'Physical Read Total IO Requests Per Sec',

'Physical Write Bytes Per Sec',

'Physical Write IO Requests Per Sec',

'Physical Write Total Bytes Per Sec',

'Physical Write Total IO Requests Per Sec'

) group by metric_name;

METRIC_NAME                                                        IO Value

---------------------------------------------------------------- ----------

Physical Read Bytes Per Sec                                      2998377909

Physical Read IO Requests Per Sec                                 31959.285

Physical Read Total Bytes Per Sec                                3015288207

Physical Read Total IO Requests Per Sec                          32542.5521

Physical Write Bytes Per Sec                                      130981144

Physical Write IO Requests Per Sec                               2426.26263

Physical Write Total Bytes Per Sec                                177724182

Physical Write Total IO Requests Per Sec                         2563.60269

8 rows selected.

When you see Total in the metric name, it includes - backup and recovery, and other utilities(stats reference). If you use snapshot for backup, use non  Physical%Total% metrics.

What if you don't have Diagnostics Pack licensing? use this AWS article. below is the excerpt of the script provided. 

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;

Amazon Web Services Determining the IOPS Needs for Oracle Database on AWS

Page 5

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)+((wtioprev_

wtio))/5,(rdbt-prev_rdbt)/5,(wtbtprev_wtbt)/5,((rdbt-prev_rdbt)/5)+((wtbtprev_

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;

/

Allocating IOPS correctly is crucial for Oracle databases, hope this article helps to find IOPS and throughput requirements for Oracle DBs. 

What if You have undersized, well, you can easily increase online  unless its throttled at instance class level which would require DB downtime to change the class.

References:

I/O Tuning with Different RAID Configurations (Doc ID 30286.1)

https://docs.aws.amazon.com/whitepapers/latest/determining-iops-needs-oracle-db-on-aws/determining-iops-needs-oracle-db-on-aws.html

https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-volume-types.html

https://blog.pythian.com/investigating-io-performance-on-amazon-rds-for-oracle/

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/statistics-descriptions-2.html#GUID-2FBC1B7E-9123-41DD-8178-96176260A639