Document

Download Report

Transcript Document

Automatic Data Optimization and Oracle Intelligent Storage Protocol

Kevin Jernigan Senior Director Product Management Oracle Product Development

Program Agenda



Data Growth and Information Lifecycle Management



Database Partitioning and Compression



Heat Map and Automatic Data Optimization



Automatic Data Optimization Use Cases



Oracle Intelligent Storage Protocol and ZFS Storage

Growth in Data Diversity and Usage

1,800 Exabytes of Data in 2011, 20x Growth by 2020

Today’s Drivers Emerging Growth Factors

Mobile

#1 Internet access device in 2013

Enterprise

45% per year growth in database data

Cloud

80% of new applications and their data

Regulation

300 exabytes in archives by 2015

Big Data

Large customers top 50PB

Social Business

Information Lifecycle Management

Managing Data Over its Lifetime

High Value Medium Value Low Value $$ $ $$$

Total Cost of Ownership (TCO)

“The policies, processes, practices, and tools used to align the business value of information with the most appropriate and cost effective IT infrastructure from the time information is conceived through its final disposition.”

Storage Networking Industry Association (SNIA) Data Management Forum

Information Lifecycle Management

• • Improve DW and batch performance by 10X or more with Partitioning and Compression • • Define Data Classes *Structured and Unstructured Improve OLTP performance with Compression and Flash Cache • Reduce TCO for database storage by 10X or more with Compression • Automate tamper proof history • tracking with Flashback Data Archive Define and Enforce Compliance Policies Mainframe Desktop Data Optimization Servers Hardware Optimization Software Optimization Application Optimization

Active Less Active Historical

• Create Storage Tiers for the Data Classes •   Reduce Storage Requirements with Compression: 2X – 4X with ACO 10X – 50X with HCC • Create Data Access and Migration Policies Use ZFS, SAM, and StorageTek tape solutions 6 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Database Partitioning

The Concept of Partitioning

Simple Yet Powerful

ORDERS ORDERS Large Table Difficult to Manage JAN Partition FEB Divide and Conquer Easier to Manage Improve Performance

Transparent to applications

ORDERS USA EUROPE JAN FEB Composite Partition Better Performance More flexibility to match business needs

What Can Be Partitioned?

 Tables – Heap tables – Index-organized tables  Indexes – Global Indexes – Local Indexes  Materialized Views Global Non-Partitioned Index Global Partitioned Index Table Partition Table Partition Table Partition Table Partition Local Partitioned Index 9 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Partition for Performance

Partition Pruning Q : What was the total sales for the weekend of May 20 22 2008?

SELECT sum(sales_amount) FROM sales WHERE sales_date BETWEEN to_date(‘05/19/2008’,’MM/DD/YYYY’) AND to_date(‘05/22/2008’,’MM/DD/YYYY’); • • Only relevant partitions will be accessed • • Static pruning with known values in advance Dynamic pruning at runtime Minimizes I/O operations • Provides massive performance gains 10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Sales Table May 18 th 2008 May 19 th 2008 May 20 th 2008 May 21 st 2008 May 22 nd 2008 May 23 rd 2008 May 24 th 2008

Partition for Tiered Storage

2013

5% Active

High End Storage Tier

Database Compression

Compression Techniques

COMPRESSION TYPE: SUITABLE FOR: Basic Compression Advanced Row Compression Advanced LOB Compression and Deduplication

“Mostly read” tables and partitions in Data Warehouse environments or “inactive” data partitions in OLTP environments Active tables and partitions in OLTP and Data Warehouse environments Non-relational data in OLTP and Data Warehouse environments All environments

Advanced Network Compression and Data Guard Redo Transport Compression

All environments

RMAN/Data Pump Backup Compression Index Key Compression Hybrid Columnar Compression – Warehouse Level Hybrid Columnar Compression – Archive Level

Indexes on tables for OLTP and Data Warehouse “Mostly read” tables and partitions in Data Warehouse environments “Inactive” data partitions in OLTP and Data Warehousing environments 13 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Advanced Row Compression

• • •

Partition/table/tablespace data compression

– Support for conventional DML Operations (INSERT, UPDATE) –

Customers indicate that 2x to 4x compression ratio’s typical

Significantly eliminates/reduces write overhead of DML’s

– Batched compression minimizes impact on transaction performance

“Database-Aware” compression

– Does not require data to be uncompressed – keeps data compressed in memory – Reads often see improved performance due to fewer I/Os and enhanced memory efficiency 14 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Hybrid Columnar Compression

Compression Unit

•

Hybrid Columnar Compressed Tables

– Compressed tables can be modified using conventional DML operations – Useful for data that is bulk loaded and queried 10x to 15x Reduction •

How it Works

– Tables are organized into Compression Units (CUs) • CUs are multiple database blocks – Within Compression Unit, data is organized by column instead of by row • Column organization brings similar values close together, enhancing compression 15 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Data Compression

Reduce storage footprint, read compressed data faster

Hot Data 111010101010101 001101010101011 010001011011000 110100101000001 001110001010101 101001011010010 110001010010011 111001001000010 001010101101000 3X

Advanced Row Compression

Warm Data 101010101110101 001101011100001 010001011011101 010100101001001 000010001010101 101001011010011 100001010010010 100001001000010 001010101101001 10X

Columnar Query Compression

Archive Data 101010101110101 001101011100001 010001011011101 010100101001001 000010001010101 101001011010011 100001010010010 100001001000010 001010101101001 15X

Compression Benefits



Transparent: 100% Application Transparent



Smaller: Reduces Footprint

– – CapEx: Lowers server & storage costs for primary, standby, backup, test & dev databases … OpEx: Lowers heating, cooling, floor space costs …  Additional ongoing savings over life of a database as database grows in size 

Faster: Transactional, Analytics, DW

– Greater speedup from in-memory: 3-10x more data fits in buffer cache & flash cache – Faster queries – Faster backup & restore speeds 

End-to-end Cost / Performance Benefits across CPU, DRAM, Flash, Disk & Network

Heat Map and Automatic Data Optimization

Understanding Data Usage Patterns

Database ‘heat map’

1 0 1 1 0 0 1 0 0 0 0 1 0 1 0 0 0 1 0 1 0 0 1 0 1 0 0 1 0 0 1 0 0 0 0 0 0 1 0 0 1 0 1 1 1 1 1 1 1 0 0 0 1 0 0 0 1 0 1 0 1 0 1 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0 1 0 1 0 1 0 1 0 0 0 0 0 0 0 1 1 0 0 1 0 0 0 1 1 1 0 1 0 1 0 1 0 1 0 0 0 1 0 0 0 0 1 0 1 0 0 0 1 0 1 1 0 0 0 1 0 1 0 0 1 0 1 1 1 1 1 1 1 0 0 1 0 0 0 1 0 0 0 0 0 1 0 1 0 1 0 1 1 0 0 1 0 0 1 0 0 0 1 0 1 0 1 0 1 0 1 1 0 1 1 1 1 1 1 1 0 0 1 1 1 0 0 1 0 0 0 0 1 0 1 0 0 1 0 1 0 1 0 1 1 1 1 1 1 1 1 0 0 0 1 0 1 0 1 0 0 0 0 1

Understanding Data Usage Patterns

Database ‘heat map’

Heat Map

What it tracks

Active Frequent Access Occasional Access Dormant

HOT WARM COLD 

“Heat Map” tracking

– Access and modification times tracked for tables and partitions – Modification times tracked for database blocks 

Comprehensive

– Segment level shows both reads and writes – Distinguishes index lookups from full table scans – Automatically excludes stats gathering, DDLs, table redefinitions, etc 

High Performance

Heat Map

Enterprise Manager Visualization

Automatic Data Optimization

Usage Based Data Compression

01110101010010 10000100010101 01011100001010 Hot Data 10101010111010100110101 11000010100010110111010 Warm Data 1010101011101010011010111000010100 0101101110101010010100100100001000 1010101101001011010011100001010010 1110010100100101001010110111011010 10100101001001000010001 Archive Data 101010101110101001101011100001010001011011 101010100101001001000010001010101101001011 010011100001010010010100001001000010001010 101010101110101001101011100001010001011011 101010101110101001101011100001011101011001 3X

Advanced Row Compression

10X

Columnar Query Compression

15X

Confidential – Oracle Restricted

Automatic Data Optimization

Simple Declarative SQL extension

Active

 Advanced Row Compression (2-4x)  Affects ONLY candidate rows  Cached in DRAM & FLASH

ALTER TABLE sales ILM add

row store compress advanced row

after 2 days of no update Frequent Access

  Warehouse Compression(10x) High Performance Storage column store compress for query low

after 1 week of no update Occasional Access

  Warehouse Compression(10x) Low Cost Storage tier to SATA Tablespace  Archive Compression(15-50X)  Archival Storage column store compress for archive high

after 6 months of no access Dormant

Scheduled Policy Execution

Automatic Data Optimization 

Immediate and background policy execution

– Policies are executed in maintenance windows – Can be manually executed as needed 

Policies can be extended to incorporate Business Rules

Automatic Data Optimization Use Cases

Automatic Data Optimization

OLTP Reporting 10x compressed This Quarter Row Store for fast OLTP This Year Compressed Column Store for fast analytics Compliance & Reporting 15x compressed Prior Years Archive Compressed Column Store for max compression As data cools down, Automatic Data Optimization automatically converts data to columnar compressed

Online

Up to 15x Smaller Footprint & Faster Queries

Automatic Data Optimization for OLTP  Both Columnar & Archive Compression now complement Advanced Row Compression  Best Practice: – Step 1:

Use Advanced Row Compression for entire DB

and then – Step 2:

ADO automatically converts into columnar compressed

once the updates cool down, and is used mainly for reporting => Query speed of Columnar & 10x smaller footprint – Step 3:

ADO automatically converts into archive compressed

Optimizes Data Based on Heat Map

Automatic Data Optimization for DW  Data generally comes in via Bulk Loading  Workload dominated by queries, even during loading

Step 1: Bulk Load directly into Columnar Compressed

– 10x smaller footprint, Query speed of Columnar

Step 2: ADO automatically converts to Archive Compressed and moves to Lower Cost Storage

Fast, Flexible Loads & Queries on Columnar

Automatic Data Optimization – Mixed Use  Fastest Load with uncompressed & Fastest Queries with columnar – Mixed workloads often use Java app or 3rd party tools to insert and update data that does not use Bulk Loads, so cannot use Columnar  Step 1:

Load into uncompressed

, conventional inserts & updates – Fast loading, & flexibility of using a regular OLTP app for loading  Step 2:

ADO moves to Row Compressed or Columnar Compressed or Low Cost Storage

Oracle Intelligent Storage Protocol (OISP)

Introduction to NFS

 NFS means Network File System – Originally developed by Sun Microsystems in 1984, built on RPC  NFS is a distributed file system protocol – NFS is an open standard, anyone can implement it – Secure data access anywhere in the enterprise – Commonly implemented with TCP over IP, can use RPC/RDMA  Thanks to Moore’s Law – Block and file protocol is a matter of convenience, not performance 32 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Direct NFS Client

 New feature introduced in Oracle Database 11

Release 1  NFS client embedded in Oracle database kernel  Improves NAS storage performance for Oracle database  Improves high availability of NAS storage  Optimizes scalability of NAS storage  Vastly reduces system CPU utilization  Significant cost savings for database storage  Simplifies client management and eliminates config errors  Consistent configuration and performance across different platforms – Even Microsoft Windows 33 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Direct NFS Architecture

Database …

LGWR

I/O queue …

DBWR

I/O queue Direct NFS client

LGWR

TCP connection

DBWR

TCP connection …

PQ slave

I/O queue

RMAN

I/O queue …

PQ slave

TCP connection

RMAN

TCP connection Parallel network paths for scalability and failover Direct NFS can issue 1000s of concurrent operations due to the parallel architecture NAS Storage Every Oracle process has its own TCP connection 34 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Oracle Database on ZFS

 Database files – Control – Data – Online Redo  Recovery files – Archived redo – Backup sets – Image copies

Oracle Intelligent Storage Protocol

Setting Up An Oracle Database Without OISP

Many Steps to Configure

21 7 Shares + 7 Record Size Tunes + 7 LogBias Tunes = 21 Steps Test

21

7 Shares + 7 Record Size Tunes + 7 LogBias Tunes = 21 Steps 21 7 Shares + 7 Record Size Tunes + 7 LogBias Tunes = 21 Steps Dev Production Database 12 4 Shares + 4 Record Size Tunes + 4 LogBias Tunes = 12 Steps BU&R

Oracle Intelligent Storage Protocol (OISP)

Only on Oracle: Simplify Setup and Tuning for Oracle Database 12c Database data Oracle Database 12c IO metadata exchanged with ZFS Storage Appliance Reduced ZFS Storage Appliance dynamically tunes critical storage parameters Administration Time OISP will dynamically tune 65% of the critical factors for optimal database performance ZFS Storage Appliance

Setting Up An Oracle Database

With

Fewer Steps to Configure

OISP

2

2 Shares + 0 Record Size Tunes + 0 LogBias Tunes = 2 Steps Test

2

2 Shares + 0 Record Size Tunes + 0 LogBias Tunes = 2 Steps

2

2 Shares + 0 Record Size Tunes + 0 LogBias Tunes = 2 Steps Dev Production Database

2

2 Shares + 0 Record Size Tunes + 0 LogBias Tunes = 2 Steps BU&R

Auto Tuning of Record Size, LogBias

Without OISP NFS Server Multiple shares, each with its own Record Size and LogBias setting

mnt/dbname/redo

(Admin sets RS, LB)

mnt/dbname/control

(Admin sets RS, LB)

mnt/dbname/pfile

(Admin sets RS, LB)

mnt/dbname/datafile

(Admin sets RS, LB)

mnt/dbname/tempfile mnt/dbname/chgtrack

(Admin sets RS, LB) (Admin sets RS, LB)

mnt/dbname/backup

(Admin sets RS, LB) With OISP

OISP Tunes

Logfile share

NFS Server

Datafile share

Two shares: logfile and datafile

mnt/dbname/logfile 

redo

(OISP sets RS, LB)

mnt/dbname/datafile 

control



pfile



datafile



tempfile



chgtrack



backup

(OISP sets RS, LB)

Architecture

Oracle Instance

Hints

Oracle Direct NFS RPC TCP IP

Hints on I/O requests

NIC LINK NIC ZFS File System

Hints

NFS Server RPC TCP IP

Use Case Example: RMAN Backup

Non-OISP/Un-Tuned

420 MBPS

OISP-Enabled

734 MBPS

1.7x Improvement

Over Un-Tuned / Non-OISP

Summary

Oracle Advanced Compression

Oracle Database 12c Advanced Row Compression Advanced LOB Compression Advanced LOB Deduplication Heat Map (Object and Row Level) Automatic Data Optimization Temporal Optimization Hybrid Columnar Compression (Exadata, ZFSSA, Axiom)

Summary



Partitioning:

An enabler to allow data classification 

Compression:

Reduce data storage footprint and improve performance 

Tiered Storage:

Key ILM strategy to lower storage usage and costs 

Heat Map:

Automatically tracks data access patterns 

Automatic Data Optimization

– – Automates data movement for compression and storage tiering Enables ILM for Oracle Database data 

ZFS Storage Appliance:

database-aware storage 

Oracle Intelligent Storage Protocol: