Boost Your Database Performance 10x with Oracle SecureFiles

Boost Your Database Performance 10x with

Oracle SecureFiles

Senior Product Manager Oracle

Aris Prassinos

Distinguished Member of Technical Staff

Agenda

•

Enterprise Data

•

Current Approaches to managing data

•

SecureFiles: Changing the paradigm

•

High Performance

•

Advanced Features

•

Out-of-the-Box benefits with SecureFiles

•

Motorola – Real world experience

•

SecureFiles Interfaces

Enterprise Data

Yearly Data Growth

Data Growth

Structured 15 - 20% Semi & Unstructured 50 - 100%

* Gartner & IDC Estimates

•

In a typical enterprise, Structured Data is ~20%

•

Semi & Unstructured Data represents the other 80%

Managing Information

• Organizations need to efficiently and securely manage all data

Structured StructuredSemi- Unstructured

XML PDF

• Simplicity and performance of file systems makes it attractive to store file data in file systems, while keeping relational data in DB

• Mainstream DBs support ANSI-standard LOB for storing file data inside DB – performance is a concern for many users

Files Belong with Relational Data

•

Many Enterprise applications manipulate both files

and relational data

• Rich user experience, compliance, business integration

•

This split compromises

security, robustness, and

management

• Disjoint security and auditing models

• Changes cannot be made atomically

• Backup and recovery are fragmented

• Search across relational data and files is difficult

• Space management is complicated

• Separate interfaces and protocols

• Application architecture more complex

Two data managers for one application

Oracle SecureFiles

Consolidated Secure Management of Data

• SecureFiles is a new database feature designed to break the performance barrier keeping file data out of databases

• Similar to LOBs but much faster, and with more capabilities

• Transparent encryption (with Advanced Security Option)

• Compression, deduplication (with Advanced Compression Option)

• Preserves the security, reliability, and scalability of database

• Superset of LOB interfaces allows easy migration from LOBs

• Enables consolidation of file data with associated relational data

• Single security model

• Single view of data

SecureFiles Innovations

•

SecureFiles is a major rearchitecture of how the

database handles unstructured (file) data

• Not a new SQL data type, but new storage type for file data

• Not an incremental improvement to LOBs

•

Entirely new:

• Disk format

• Network protocol

• Caching and locking

• Redo and undo algorithms

• Space and memory management

Designed for High Performance

• Write Gather Cache

• Cache above the storage layer buffers data up to 4MB during writes before flushing to disk

• Allows for large contiguous space allocation for LOB data and reduced write latency.

• Intelligent Pre-fetching

• Improves read performance by pre-fetching LOB data from disk

• Overlaps disk IO with network latency to improve throughput • New Space Management routine

• Automates new space allocation and “freed” space reclamation

• Optimized chunk size reduces fragmentation

• No more High Water Mark contention as with old LOBs

High Performance

SecureFiles Vs. NFS

• SQL+ File test, single stream, single host

• Using Secure Files is faster across the board

• 2x-3x faster for Queries, 6x for Inserts

• Tests run using both SecureFiles and NFS/ext3 in metadata journaling only (default for NFS)

• Filesystem-like performance

File Read Performance File Write Performance

0.01 0.1 1 10 100 1 10 100 1000 10000 File Size (KB) MB /s 0.1 1 10 100 1 10 100 1000 10000 File Size (KB) MB /s SecureFiles NFS

High Performance

SecureFiles Vs. BasicFiles

Writes: New Space

File Size (MB) 0 50 100 150 200 250 300 0.01 0.1 1 10 100 MB /s

Writes: Reused Space

File Size (MB)

SQL+ File Test: Concurrent Reads/Writes, OCI, 4 streams

• Adding Files using New Disk Space: Up to 2x faster

• Adding Files Reusing Space: Up to 22x faster

• Reads up to 3x faster 0 50 100 150 200 250 300 0.01 0.1 1 10 100 MB /s Read Performance File Size (MB) SecureFiles LOBs

Advanced Features - Compression

• SecureFiles introduces Compression for unstructured data

• Huge storage savings

• Industry standard compression algorithms

• 2-3x compression for typical files (doc, pdf, xml)

• Automatically detects if SecureFile data is compressible • Skips compression for already compressed data

• Auto-turn off compression when space savings are minimal or zero

• Server-side compression

• Allows for random reads and writes to SecureFile data

• Two levels of compression provide different compression ratios • Compression Levels: MEDIUM (default), HIGH

• Higher the degree of compression, higher the latency and CPU overhead incurred

Advanced Features - Deduplication

• Enables storage of a single physical image for duplicate data

• Significantly reduces space consumption

• Dramatically improves writes and copy operations

• No adverse impact on read operations

• May actually improve read performance for cache data

• Specially useful for content management, email applications and data archival applications

• Part of the Advanced Compression Option

Advanced Features - Encryption

•

Extends Transparent Data Encryption (TDE)

functionality to SecureFile data

• Data encrypted on disk

• Key management completely transparent to applications

•

Support for industry-standard encryption algorithms

• 3DES168

• AES192 (default)

• AES256

•

Unified security level for both file and relational data

Out of the Box Benefits

•

Easy conversion from LOBs to SecureFiles using Online

Redefinition

• Existing LOB applications benefit with no changes

•

SecureFiles can be enabled only on new partitions

• Requires no data migration

• New data can benefit from high performance and other advanced features of SecureFiles

•

New installations

• By setting db_securefiles= ALWAYS

•

SecureFiles is fully integrated with

Motorola – Real World Experience

Aris Prassinos

Motorola Biometrics

• Trusted developer and integrator of Biometrics solutions for over 30 years

Motorola Printrak

Biometric Identification Solution

• Capture, Processing, Search and Storage of multiple biometrics

• Criminal and Civil applications

• Criminal investigation, background checking, border control, biometric documents, welfare disbursement, voter registration

Some Motorola Biometrics customers

• Motorola Supplies Mobile Fingerprint System for UEFA EURO 2008

http://www.motorola.com/mediacenter/news/detail.jsp?globalObjectId=9822_9751_23&pageLocaleId=2026

• Motorola Plays Key Role in UK Biometric Visas Pilot Programme

http://www.motorola.com/mediacenter/news/detail.jsp?globalObjectId=8778_8707_23

• Motorola Biometric Technology Wins Again at Super Bowl XLI

http://www.motorola.com/mediacenter/news/detail.jsp?globalObjectId=7887_7816_23

• Motorola’s Biometric Technology Integral to the Registered Traveler Program

… and of course the CSI crime labs

• Motorola Provides Miami Dade Police with Enhanced Biometric Identification Tools

http://www.motorola.com/mediacenter/news/detail.jsp?globalObjectId=7299_7240_23

A Motorola Printrak BIS system

•

12TB (projected 40TB)

• 90% is LOBs (biometric images, XML metadata, files)

• LOBs range from 5K to 5MB

• 500 million LOBs (projected 1.5 billion)

•

OLTP

• All data current and may be read / updated / deleted

•

100 concurrent users

•

Java-based Service Oriented Architecture

•

2-node Oracle 11g RAC

Images on the file system?

•

Not an option for our customers!

• Two data stores will diverge over time

• Non-transactional – Limited rollback capabilities

• Almost certain data loss

• Incomplete recovery

• Costly and complex high availability options

• Chain of custody

• File system security and auditing limitations

• No onsite DBA or administrator

• Manageability of too many moving parts

Images inside the database

•

Storing images inside database as BasicFile LOBs

since 2001 (starting with Oracle 8i)

•

Over 80 Printrak BIS installations with BasicFiles

• 500GB to 3TB in size

• Resolved problems of using two data stores

• Acceptable performance given the benefits of this approach

• Market success and industry recognition

• Frost & Sullivan Award for

Competitive Strategy Leadership (2004)

Metadata stored as XML

•

Each customer requires a custom schema

• Different applications (criminal, civil)

• Different customer requirements for the same application

• From basic demographics to full criminal history

• Schema to evolve over time with no application changes

•

Storing metadata as schema-less XML

• Tables same across installations (e.g. Person, Event etc)

• Columns differ across installations and stored as XML

• Relying heavily on XML indexing (Oracle Text, XMLIndex)

Files also inside the database

•

Storing up to 200,000 regular files inside the database

• Stored as LOBs in the XMLDB repository

• XMLDB still using BasicFiles in present release

• Access via HTTP, Native Web Services, Windows Explorer

• More reliable than NFS for concurrent access

• RMAN Backup / Recovery - Disaster Recovery via DataGuard

• Security and auditing

•

SecureFiles file system (in upcoming release)

• Filesystem API allows integration with existing applications

• Addresses limitations of HTTP, WebDAV access

Challenges with BasicFiles

•

Low concurrency / RAC scalability due to contention

• Hash partitioning helps (but with extra licensing costs)

•

Data encryption customer requirements

• BasicFiles not supported by TDE

•

Significant disk space wastage (as much as 25%)

• Inefficient CHUNKing due to widely varying image sizes

• Large blocksize for performance at the expense of space

•

Inefficient space reclamation

• OLTP nature of application causes frequent deletes / updates

Why are we excited with SecureFiles?

•

SecureFiles allow high concurrency / RAC scalability

• Hash partitioning still beneficial but not mandatory

•

SecureFiles support Transparent Data Encryption

•

Very efficient space management

• Dynamic CHUNKing

• Large blocksize no longer necessary for performance

•

Very efficient space reclamation

• Very pronounced effect in multi-terabyte OLTP databases

•

No other RDBMS matches speed of SecureFiles

• Observed 2x – 3x out of the box speedup over BasicFiles for LOBs up to 5MB and full logging enabled

Tips on using SecureFiles

•

Test with ARCHIVELOG

• Behavior different

• Performance drop larger than with standard datatypes

•

Additional performance drop with FLASHBACK

• Consider impact of FLASHBACK if using it for Data Guard

•

Works out of the box without excessive tuning

• Tune redo logs before anything else

Tips on using SecureFiles

(cont.)

•

Tune network parameters

• Very important if accessing SecureFiles over slow WANs

• Less noticeable effect on fast LANs

• sdu / tdu

• 32767 a good value for sdu / tdu

• send_buf_size / recv_buf_size

• depending on the bandwidth delay product of network

• OS kernel network parameters e.g. rmem, wmem

• Very important to also set network parameters on the client

Tips on using SecureFiles

(cont.)

•

Carefully evaluate old tuning practices for BasicFiles

• Not necessarily applicable or as important to SecureFiles e.g. large blocksize, separate tablespaces, caching writes

•

Performance benefits come from many places

e.g. concurrency, pre-fetching, space reclamation

• Test a realistic application scenario, not just single thread performance

• Efficient space reuse during insert-delete-reclaim processing may result in a dramatic speedup over BasicFiles

SecureFile Interfaces

•

SecureFiles is

100% backward compatible

with ANSI

SQL 92 LOB interfaces

•

SecureFiles can be accessed by both database

clients and file system clients

•

Database clients use standard LOB interfaces

• JDBC, ODBC, OCI, OCCI, .NET, PL/SQL

•

File system clients use the file system protocols

• Implemented using the Oracle Secure File System

Opening the floodgates to files

File systems

Database

Oracle Secure File System

• SFS is file system in the database, uses database for storage and brings all of database technology to file systems

• SFS implements the file system interfaces:

• 2 methods (getpath, list) for a readonly file system

• 5 methods for an a file system with read and write support

• 15 methods for fully functional POSIX file system

• SFS interface is extensible for easily defining special purpose implementations (providers)

• SFS can surface one or more DB tables as a filesystem or a single table through multiple file systems

• Example, a CheckImages table can have 2 filesystems on it:

• /CheckImages_by_customer/CustomerName/check.jpg

Linux Client for Oracle Secure File System

• Conceptually like NFS Client, but uses DB instead of NFS Server

• User mode filesystem, no changes to Linux kernel • Standards based POSIX compatible

• Transparent to file system applications

• Mount SFS at a Linux mount point at some path /mnt/sfs/…

• mount.lcos user@server:port/service /mnt/sfs

• What can you do with it?

• Run E-Business Suite on SFS

• MAA for mid-tier to host Log and Report directories on SFS • Run Apache Web Server on SFS

• Run virtual machines from SFS

• Run YOUR custom application on the database

• Benefit from Security (encryption, SSL), HA (standby, hot backup), RAC (scale on commodity h/w), Compression/De-duplication,…

Secure File System performance

Secure File System Performance (4 way parallel)

0 100 200 300 400 500 600 1MB 10MB 100MB Fi le S iz e MB/s

Secure File System Write Secure File System Reads

Quiz

You accidentally deleted one file yesterday. How do

you recover it?

How do you convert the file system into a compressed

file system online?

How do you get a tamper-proof file system?

What technology can you use to Secure the file

system?

The Best of Files and Databases

• SecureFiles have all the leading-edge file system capabilities

• Deduplication, Encryption, Compression, Logging, Versioning

• SecureFiles have advanced database features not in file systems

• Transactions, Read Consistency, Flashback

• Readable Standby, Consistent Backup, Point in Time Recovery

• Fine Grained Auditing, Label Security

• Sliding Inserts

• XML indexing, XML Queries, XPath

• Real Application Clusters

• Automatic Storage Management

• Partitioning and ILM

• Search across meta-data and file content

• Capabilities go far beyond any other database or file system

The Best of Files and Databases

• SecureFiles have all the leading-edge file system capabilities

• Deduplication, Encryption, Compression, Logging, Versioning

• SecureFiles have advanced database features not in file systems

• Transactions, Read Consistency, Flashback

• Readable Standby, Consistent Backup, Point in Time Recovery

• Fine Grained Auditing, Label Security

• Sliding Inserts

• XML indexing, XML Queries, XPath

• Real Application Clusters

• Automatic Storage Management

• Partitioning and ILM

• Search across meta-data and file content

• Capabilities go far beyond any other database or file system

Resources Available

•

Oracle Technology Network (OTN)

• http://www.oracle.com/technology/products /database /

securefiles/index.html

• Technical White paper

• Performance White paper

• Data Sheet

• Internet Seminar

• SecureFiles Test Kit

•

SecureFiles Tutorial

• http://www.oracle.com/technology/obe/11gr1_db/datamgmt/se

Sessions From Oracle Development

• S298741 - Oracle Database 11g: Next-Generation Performance and Scalability, 11:30 - 12:30 pm, Moscone South 103

• S298792 - Real-World Database Performance Techniques and Methods, 12:00 - 1:00 pm, Moscone South 104

• S298786 – Current Trends in Real-World Database Performance, 1:00 - 2:00 pm, Moscone South 103

Monday, Sep 22

Wednesday, Sep 24

Thursday, Sep 25

• S298759 – Oracle Advanced Compression: Throw Away Half of Your Disks, and Run Your Database Faster, 9:00 - 10:00 am, Moscone South 103

• S298756 - Boost Your Database Performance 10x with Oracle SecureFiles, 11:30 - 12:30 pm, Moscone South 104

• S298785 – Real-World Database Performance Roundtable, 1:30 - 2:30 pm, Moscone South 104

Tuesday, Sep 23

• S298757 – Time-Travel Through Your Data with Oracle Total Recall, 11:30 - 12:30 pm, Moscone South 305

• S298754 – Using Oracle Database in the Cloud , 1:00 - 2:00 pm, Moscone South 305

• S298765 – Oracle Database Performance on Flash Drives: Insights from Oracle Development , 9:00 -10:00 am, Moscone South 104

Labs/Demos From Oracle Development

Total Recall

Oracle DEMOgrounds, Moscone West Monday, Sep 22 – Thursday, Sep 25

Hands-on Labs

Marriott Golden Gate A3

Monday, Sep 22 2:30 – 5:00 pm Advanced Compression Tuesday, Sep 23 5:00 – 6:00 pm Total Recall Thursday, Sep 25 1:30 – 4:00 pm Advanced Compression

Q U E S T I O N S

A N S W E R S

For More Information

search.oracle.com

or

<Insert Picture Here>

SecureFiles Best Practices

•

CHUNK parameter no longer needs to be specified

• SecureFiles automatically optimizes writes to use maximum sized contiguous allocations on disk – reduces fragmentation

•

Write Gather Cache

• Buffers writes across calls for large, contiguous I/O

• Recommend inserting multiple rows between commits,

especially for applications inserting small/medium sized LOBs

•

New logging level, FILESYSTEM_LIKE_LOGGING

provides logging similar to most journaled file systems

• Only SecureFiles metadata is logged, not SecureFiles data

SecureFiles Best Practices

•

Recommend dedicated tablespaces and devices for

SecureFiles segments

• Allows for large, contiguous allocations for writes

• Efficient pre-fetching for reads

•

Database parameters

• SDU/TDU Size – set to maximum possible value for more efficient streaming of SecureFiles data

• Recv_buf_size/Send_buf_size – Tune to higher value to have significant impact on network throughput

•

Kernel parameters

• Increasing network buffer sizes greatly increases SecureFiles throughput