<Insert Picture Here>

Set It and Forget it: Automatic Storage Management Best PracticesAra ShakianPrincipal Product ManagerOracle Server Technologies

Agenda

• Storage design challenge• Top down I/O design methodology

• Five easy steps • ASM best practices• Customer adoption

Storage Design Challenges

• Customers often ask:• How much storage do I need to meet my

database IO requirements?• Is there a simple methodology to follow?

• And BTW, I need to:• Reduce complexity• Simplify my solution stack• Increase availability and performance• Get one vendor to support me• Reduce my total cost of ownership

Top Down Design Methodology

Characterize Workload

Estimate Storage Reqs

Simulate Workload

Set It and Forget it with

ASM

Deploy & Troubleshoot

Step 1

Step 2

Step 3

Step 4

Step 5

Step #1

Characterizing Your Database IO Workload

Ask the Important Questions

• Workload mix: Will IO request be primarily single block (OLTP) or multi-block (DSS)?

• What is your average and peak IOPS requirement?• What is the average and peak throughput MBPS

requirement?• Read vs. write percentage• Needed response time?

o What is the IO latency?

Answer Are in an AWR Report

What is Automatic Workload Repository (AWR) Report?

• A built-in repository in every Oracle Database • Snapshot of all vital statistics and workload

information captured at regular intervals• One week intervals

• Generates reports extracting relevant information

Workload Mix, MB/S and IOPS116 MB/s

Total IOPS=784

Workload mix:Total Large IO=171(or 22%)

Total small IO=613(or 78%)

% Read vs. Writes and Block Size

97% read

Block size

Response Time

Weighted Avg= 18.2

V$SYSSTAT Statistics – An Alternative

• Examine the V$SYSSTAT database statisticsCumulative values that should be sampled during peak and typical operations

• Single-block read: V$FILESTAT.SINGLEBLKRDS• Multi-block reads: V$FILESTAT.PHYRDS or

V$FILESTAT.SINGLEBLKRDS• Single block write: V$FILESTAT.PHYWRTS• Multi-block writes: V$FILESTAT.PHYBLKWRT• Redo log writes: V$SYSSTAT• Bytes written: IO_COUNT

Future… Automating The Process An AWR Repository Database

• AWR repository gathers and maintains statistics for the cluster• More comprehensive statistics• Synchronized cluster-wide snapshots

• All relevant application level IO statistics collected• IOPS, MB/s, Latency, etc…

• Repository schema can be queried• Aggregation, trending, mining, historical• Queries per db, instance, per time series• Query by IOPS, bandwidth, latency, etc…

• Database statistics consolidated into a data warehouse• Multiple databases can be consolidated

Step #2

Estimating Storage Requirements

Typical Storage Configurations

Controller

S A N Network

HBA

LUN

Estimating storage requirements depends on several factors

JBOD Storage ArrayStorageSystem

Estimating Storage Requirements• Consult your storage vendor

• Manufacturer specs not always = actual numbers • Get estimates based on RAID levels chosen

• Decide between RAID 10, 5 or non• Consider tradeoff carefully

• Beware of theoretical measurements• Safe practice per drive

• 30 MByte/s• 60 IOPS

• Account for IO latency• 10 ms SCSI• 20 ms SATA

Note: These are examples and not meant to be actual specs

Three Way to Size Storage

• Sizing by capacity• Sizing by bandwidth (MB/sec)• Sizing by IO operations per second (IOPS)

Sizing by Capacity

• Let’s consider a 1TB database• Assumptions:

• 300GB disk drives• 30 MByte/s per drive• 60 IOPS per drive

• Let’s allow for resilience • RAID 10 4 x 2 (mirror) = 8 disks total (2.4TB)

• RAID5 (4+1) 5 disks total (1.5TB)

Sizing by IOPS

• OLTP and/or batch workload• Workload req = 20 TPS @ 50

reads/trans• 1000 physical reads/s

• Assume 60 IOPS per disk• 1000 / 60 ~ 16 disks

• OLTP and/or batch workload• Workload req = 20 TPS @ 10

writes/trans• 200 writes/s + 200 redundant

writes/s• Assume 60 IOPS per disk

• 400 / 60 ~ 8 disks

Total disks = 24 disks

80% read and 20% write workloadSame 1TB Database

Reads Writes

Sizing by Bandwidth & HBA

• Same database• Entry-level Data Warehouse• DW bandwidth req = 800 MByte/s• Assume 30MB/s per disk

• 800 / 30 ~ disks 26

• Don’t forget your HBA bandwidth• Assume 1GBit/s HBA = 125 Mbyte/s• 800/100 (to be safe) = 8 HBA

Step #3

Simulate Database Workload Using ORION

Oracle ORION Calibration Tool

• Simulates Oracle database IO using same Oracle IO stack

• Predicts the Oracle DB performance before creating a database

• Workload typesSmall random IO (OLTP)Large Sequential IO (DW)Large random IO (real world DW)Mixed workloads

Available on OTN: http://www.oracle.com/technology/software/tech/orion/ index.html

ORION Calibration Tool

$ orion –run advance –testname mytest -num_disk 24 -size_small 16 -size_large 1024-write 3 -type rand (large random IO) -matrix detailed

• Generate combination of 16kb and 1MB random IO with 97% read ratio

Validation with ORION

IOPS

IO Latency m

sM

BPS

Outstanding IOs

Only small IO

Only large IO

OLTP

DSS

• Determines storage IO boundaries

Mixed Workload

Mixed Workload

Step #4

Set It and Forget It Configure Storage for ASM

Best Practices

• 2 ASM disk groups• Same capacity ASM disks• Same performance characteristics within a DG

• Use whole disks if possible• Hardware RAID10/RAID5 (striping) complements

ASM striping• 1MB AU for OLTP• 8MB AU for DW

Good Practice

ASM DISK GROUP ASM DISK GROUP

Do not use LUNs created out of same disk drives

Consolidate Databases into ASM Storage Pools

• Shared storage across several databases• RAC and Single Instance

• Benefits:• Simplified and Centralized

management • Higher storage utilization• Higher performance

Local Area Network

ERP Database

HR Database

CRM Database

RACASM

ASM ASM

ASM Disk Group

ERP CRMFIN HR

FIN Database

Data Diskgroup (1TB) FRA Diskgroup (2TB)

• Give whole disks and allow ASM to manage it for you• Drives evenly distributed for Data & FRA• No drive contention• Simplest configuration

ASM Striping Only

LUN 1 2 3

Data DiskgroupLUN 4 5 6 7 8

FRA Diskgroup

• Fastest region for Data DG • Balanced data distribution• Fewer LUNs to manage while maximizing spindles• Highest availability

Hardware RAID Striped LUNs

Step #5

Deploy and Troubleshoot

Choose ASM to Deploy Your Database

Measure (AWR)Change Storage Configuration

Configure test DB

ASM

Troubleshoot with OS/Storage Tools

Why Choose ASM as Your Platform

• Forgiving of poor estimates• Provision or de-provision storage while DB is up

• Distributes DB data evenly across the storage pool• No hot spots despite configuration changes• Best performance

• Simple to monitor and manage• No additional cost even in cluster configurations• Take advantage of the ASM 11g Release 1 features

• Sys admin friendly• More automation• Rolling upgrades• Improved scalability and performance

In Summary…

One Integrated SolutionAlways On-Line

Optimal UtilizationScalableHigh PerformanceLow CostSimple

Cross Platform Linux, Windows, Solaris,

HP-UX, AIX

Structured Data

One Vendor for Support

One Management Interface

One Clusterware Framework

One Install and Configure

ASM

Oracle Clusterware

Oracle Database

&RAC

ACFS Snapshot

ASM Cluster FS&

Dynamic Volumes

Un-structured Data

ASM adoption

• De-facto standard for RAC and grid deployments

• De-facto standard for VLDB deployments

• Large and growing adoption for single instance deployments

• Thousands of customers using ASM• One of the most popular features in

the database

Some ASM Reference Customers In Production

Visit us at

ASM Demo Grounds - L33 (Moscone South)

For an ASM Cluster File System (ACFS) demo.

ORION Workload Matrix