Transcript Slide 1
Oracle Database Performance: Latest Developments, What’s Next
Amit Ganesh Vice President, Oracle Corporation
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.
The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Agenda
• High-Performance Today • Offloading and Caching for High Performance • High Performance with Large Data Volumes • End-to-end Performance Architecture
Quiz Question 1: Does Exadata utilize RAC for scale-out or SMP for scale-up?
• • • • RAC SMP Both None
High Performance Today Database Scaling
SMP Scale-Up
• Very mature – 20 years of experience • Many customers with largest SMPs on the market – – 64 to 256 CPUs Sun M9000, HP Superdome, IBM Regatta • Single System Image – – Easy to manage Easy to design applications • Works great, but eventually hits a wall • Need at least two for availability
RAC Scale-Out
HR
• • • • • Runs all Oracle database applications Highly available and scalable No Idle Resources Single System Image Thousands of production customers
ERP
Leader in Industry Benchmarks
Benchmark
TPC-C Price/Performance TPC-H @1000GB Non-Clustered TPC-H @ 10,000GB Non-Clustered TPC-H @ 30,000 GB SAP Sales and Distribution Parallel SAP Business Intelligence (BI-D) Data Mart
World Record Leadership
Oracle Oracle Oracle Oracle Oracle Oracle As of September 20, 2010: Source: www.tpc.org
& www.sap.com/benchmark (SAP details on notes page): HP ProLiant ML350 G6, 290,040 tpmC, $.39/tpmC, 4.22 watts/KtpmC, available 8/16/10 (world record TPC-C price/performance). HP Integrity Superdome 2, 140,181 QphH@1000GB , $12.15/QphH@1000GB , available 10/20/10. HP Integrity Superdome server, 208,457.7 QphH@10000GB $27.97/QphH@10000GB, available 9/10/08 (world record TPC-H 10TB non-clustered). HP Integrity Superdome Server, 150,960 QphH@30000GB, $46.69/QphH@30000GB, available 6/18/07 (world's first TPC-H 30TB result, world record TPC-H 30TB). Source: Transaction Processing Performance Council (TPC), www.tpc.org
. Please see notes page for SAP
Best OLTP Price-Performance
Value Leadership Over Microsoft 0.49
Best Single Processor Result 0.39
0.50
0.45
0.40
0.35
0.30
0.25
0.20
0.15
0.10
0.05
0.00
Oracle SQL Server
As of September 20, 2010: HP ProLiant ML350 G6, 1 processor, 6 cores, 290,040 tpmC, $.39/tpmC, 4.22 watts/KtpmC, Oracle Database 11g Standard Edition One with OEL, available 8/16/10 (world record TPC-C price/performance). HP ProLiant DL580 G7, (4 processors, 32 cores) 1,807,347 tpmC, .49/tpmC, available 10/15/10 Source: Transaction Processing Performance Council (TPC), www.tpc.org
Best Scalability and Performance
World Record SAP SD-Parallel Benchmark 50,000 40,000 37,000 Near Perfect Scaling 40,000 4-Node RAC Oracle Sun Fire x4470 30,000 2-Node RAC Oracle Sun Fire x4470 21,000 20,000 10,000 DB2 on P780 0 Oracle Oracle
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark . Please see notes page for SAP benchmark certification details for the above results.
Best Business Intelligence Performance
SAP BI-Data Mart Benchmark 4-Node RAC Fujitsu RX300 1,165,742 1,200,000 Near Perfect Scaling 1,000,000 3-Node RAC 900,309 800,000 2-Node RAC 609,349 600,000 400,000 Single Node SMP 320,363 182,112 200,000 IBM DB2 Oracle Oracle Oracle Oracle 0 8 8 16 # of CPU Cores 24 32
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark . Please see notes page for SAP benchmark certification details for the above results.
Best Business Intelligence Performance
World Record SAP BI-Data Mart Benchmark 2-Node RAC Fujitsu RX600 S5 Near Perfect Scaling 1,624,629 1800000 1600000 1400000 1200000 1000000 800000 600000 400000 200000 0 182,112 854,649 DB2 Oracle Oracle
These results as of September 20, 2010: have been certified by SAP AG, www.sap.com/benchmark . Please see notes page for SAP benchmark certification details for the above results.
What Oracle Runs
Storage
Example: Oracle Central e-Business DB
Texas Colorado 4 Node RAC Data Guard Sun E25K 36 CPU 2 Cores/CPU Total = 288 Cores 76 TB Primary 76 TB Standby
• • • Worldwide Central E-business database for Fortune 200 company ERP, HR – Payroll, orders, contracts, procurement, expense reports, hiring… Consolidated 70 separate Applications databases – Estimated cost savings of over $1B
Oracle Beehive OLTP using Exadata
• Runs Oracle Email, Calendar, Contacts, Chat, Documents, Web Conferencing • 16-node production system – Remote standby, testing system • 1 PB of disk per system – – 50 SAS cells, 48 SATA cells 3 PB of total storage Each of 3 Configurations: 16 Node RAC Cluster 2 quad-core Intel CPUs per Node Infiniband
17 Switches
• Complete Oracle Software Stack – RAC, Streams, Active Data Guard, Secure Backup, RMAN, Flashback Database, ASM, Partitioning – 2X space saved with compressed SecureFiles 98 HP Exadata Storage Cells
1 PB Raw Storage
Offloading and Caching for High Performance
Database, Client, Remote
Quiz Question 2: What is the maximum total server memory with Exadata
• • • < 10GB > 100GB, < 1 TB > 1TB
Server SQL Results Cache
• • • • • Database caches results of queries, sub-queries, or pl/sql function calls – Cache is shared across statements and sessions on server – Full consistency and proper semantics 2x speedup on hit for worst case of trivial query 100x speedup on hit for complex queries Statement hints specify caching -
/*+ result_cache +*/
Only for very read intensive tables
In-Memory Parallel Execution
• Database release 11.2 introduces parallel query processing on memory cached data – – Queries run from tables in database buffer cache Harnesses memory capacity of entire database cluster for queries • An affinity algorithm places fragments of a object (partitions) in memory on different RAC nodes • Data is kept compressed in memory
Memory has 100x more bandwidth than Disk
Database Smart Flash Cache
Buffer Cache Many I/O’s Enterprise Storage Multiple Cabinets Buffer Cache Few I/O’s Database Smart Flash Cache
• • Database Smart Flash Cache transparently extends buffer cache – 10x Larger – Uses flash disks or cards in database host – – Cache eliminates most I/Os Available on Solaris and Oracle Linux
Mid-Range Storage Few Shelves
Benefits – – – – – Fewer disks needed Less powerful array needed Better response time Big jobs run faster Lower Power High ROI
OCI Consistent Client Cache
Application Server Consistent Caching Database
• •
Simplest Queries can speedup: 50x in elapsed time 20x in CPU time
• • • Caches query results on client Primarily for caching small (10s or 100s of KB) read-intensive tables – – Queries where network overhead dominates e.g. lookup tables Cache is fully consistent – Coherence messages bundled into responses to DB calls ensure cache remain consistent – Like Cache Fusion extended out to clients
Oracle TimesTen In-Memory Database Cache
Accelerates Oracle Database Applications
Telco Services Financial Services Application In-Memory Database Cache CRM, Portal, SaaS, Customer-facing Applications Application In-Memory Database Cache Real-Time BAM & BI Application In-Memory Database Cache
• • • • • • • • • Runs in the middle-tier Caches subset of Oracle DB Full featured in-memory RDBMS with standard SQL and PL/SQL Accelerates applications with micro-second response time Scale up on SMP Scale out on commodity hardware Supports read/write caching Automatic synchronization with Oracle DB Built-in high availability
Order Matching Application
• • • • Three different types of transactions: – – – Place market order Place limit order Process quote Business logic implemented in PL/SQL stored procedures Application written in Java Execute 1000 times in one thread – – Place an order Process a quote
PL/SQL Executed on Oracle DB Trading Application Trading Application In-Memory Database Cache PL/SQL Executed in IMDB Cache
Accelerate Order Matching Application
Lower Response Time and Higher Throughput
Lightning Fast Response Time (run on Exalogic X2-2 server)
Average Response Time TimesTen In-Memory Database 12 8 4 0 2.5
Millionths of a second Read Transaction 10 Millionths of a second Update Transaction Oracle TimesTen In-Memory Database 11g Intel Xeon 5670, 2 CPUs, 6 cores per CPU Solaris 10
TimesTen 11g – Read Throughput Scaling
Scale Up on Multi-Processor / Multi-Core Hardware
TimesTen 11g - Read Throughput
3,500,000 3,112,020 3,000,000 2,429,709 2,500,000 2,000,000 1,258,811 1,500,000 1,000,000 500,000 396,816 0
1 4 8 Concurrent Processes 12 Oracle TimesTen In-Memory Database 11g Intel Xeon 5670, 2 CPUs, 6 cores per CPU Solaris 10
TimesTen 11g – Write Throughput Scaling
Scale Up on Multi-Processor / Multi-Core Hardware
TimesTen 11g - Update Throughput
500,000 450,000 400,000 350,000 300,000 250,000 200,000 150,000 100,000 50,000 0 98,106 270,369 338,511 449,772 1 4 8
Concurrent Processes
12
Oracle TimesTen In-Memory Database 11g Intel Xeon 5670, 2 CPUs, 6 cores per CPU Solaris 10
Active Data Guard Query Offload
• • • Users want to performance protect their production DBs – Active Data Guard offloads high risk reporting & backup from OLTP Current approaches – Physical Copy Reporting DB (e.g. split mirror) • Solution is simple but data is stale (day old) – Logical Replica Reporting DB (e.g. replication) • Replication provides real-time updates but is complex
Active Data Guard
– enables a unique real-time solution Reporting using physical standby technology
Real Time
– Real-time, simple, and fast – also provides DR
Simple
Production Database
Continuous Redo Shipment and Apply
Concurrent
Real-time Queries
Reporting Database
Web Scale Highly Available Reader Farm
Reporting, web content browsing
Reader Databases Updates Redo Shipping
• • • Reader farm implemented using Active Data Guard – – – – Scale-out read queries Isolate faults to each DB High performance Supports all types & DDL Automatic, zero loss failover – Readers follow automatically RAC can scale-out updater, or centralize storage of readers
Primary Database Redo Shipping Designated Fast-Start Failover DB
High-Performance with Large Data Volumes
Data Growth Challenges
• IT must support exponentially growing amounts of data – With improved performance – With lower cost • Powerful and efficient compression is key
Advanced OLTP Compression
• • • Compress large application tables – Transaction processing, data warehousing – Transparent to application Compress all data types – Structured and unstructured data types Improve query performance – Cascade storage savings throughout data center Up To 4X Compression
0.4
0.3
0.2
0.1
0
Real World Compression Results - ERP Database 10 Largest Tables
Table Scan Performance Storage Utilization
2.5x Faster
500 DML Performance
Less than 3% Overhead
0 2500 2000 1500 1000 40 10 0 30 20
3x Smaller
Exadata Hybrid Columnar Compression
Two Modes
• •
Warehouse Compression
10x average storage savings 10x reduction in Scan IO
Optimized for Speed Archive Compression
• • • 15x average storage savings – Up to 50x on some data Some access overhead For cold or historical data
Optimized for Space Smaller Warehouse Faster Performance Reclaim 93% of Disks Keep Data Online
Can mix OLTP and Hybrid Columnar Compression by partition for ILM
50 45 40 35 30 25 20 15 10 5 0
Real-World Compression Ratios
Oracle Production E-Business Suite Tables OLTP Compression (avg=3.3) Query Compression (avg=14.6) Archive Compression (avg=22.6) 10 10 10 11 16 19 19 19 20 21 29 43 52
© 2009 Oracle Corporationl •
Columnar compression ratios
•
Query = 14.6X
• •
Archive = 22.6X
Vary by application and table
35
Files in the Database Reinvented
• • • Best of Both Worlds File Capabilities – – – – – – File System Interface High Performance Compression Encryption Deduplication HSM Database Capabilities – – – Transactions Query Consistency Advanced Backup and Recovery – – – – Powerful Security Flashback Scale up SMPs Scale out Clusters • Files are an integral part of modern database applications – Product images, contracts, XML, ETL files, manuals, etc.
• Application developers want to store business data files in the database to benefit from transactional consistency, and unify HA and Security – Poor performance, limited functionality, and lack of access by existing file based tools have held them back • Oracle Database 11g reinvents files in the database • SecureFiles provides super fast and powerful file storage – Removes performance barrier to storing files in the database • DBFS provides a file system interface to files in the DB – Enables existing file based tools to easily access DB files
SecureFiles Performance
• Performance compared to Linux FS – Tests run using both SecureFiles and ext3 in metadata journaling only, no network
100 80 60 40 20 0 File Reads
(MB/second)
SecureFiles Linux Files LOBs 0.01
0.10
1
File Size (MB)
10 100 File Writes
(MB/second)
20 10 0 60 50 40 30 SecureFiles 0.01
0.10
1 10
File Size (MB)
Linux Files LOBs 100
Database File System - DBFS
• • • Shared Linux file system – Shared storage for ETL staging, scripts, reports and other application files Files stored as SecureFiles in database tables – Protected like any DB data – mirroring, DataGuard, Flashback, etc.
5 to 7 GB/sec file system I/O throughput on Database Machine • Example use case: Load into database using External Tables
ETL Files in DBFS ETL More File Throughput than High-End NAS Filer
End-to-end Performance Architecture
Exadata Hardware Architecture
•
Scaleable Grid of industry standard servers for Compute and Storage Eliminates long-standing tradeoff between Scalability, Availability, Cost Database Grid
• 8
compute
servers (1U)
Storage Grid
• 14
storage
servers (2U) • 64 Intel cores
InfiniBand Network
•
Redundant 40Gb/s switches
•
Unified server & storage net
•
112 Intel cores in storage
• 100 TB SAS disk, or 336 TB SATA disk •
5 TB PCI Flash
•
Data mirrored across storage servers
© 2010 Oracle Corporation
40
Scales to 8 Racks by Just Adding Cables
Full Bandwidth and Redundancy
41
© 2010 Oracle Corporation
Exadata Flash Warehousing
Fastest Query Throughput
50 GB/sec!
•
50 TB of data fits in Flash
– Using 10x Query Compression •
Easily keep recent data in flash, older data on disk Query Throughput GB/sec Uncompressed Data Single Rack Flash Disk Teradata 2580 Netezza TwinFin 12 Exadata V2 Business answers in seconds, not hours © 2010 Oracle Corporation 42
Quiz Question 3: Can Exadata help scan even more than 50 GB/s per rack
• • Yes No
Exadata Flash Warehousing
Comparison to Storage Arrays
• Storage Arrays bottleneck on back-end connectivity and controller performance – Flash provides no bandwidth increase •
Exadata is fastest
–
and scales with more racks
• Arrays don’t scale and: – – – – No CPU offload No Columnar Compression No InfiniBand Expensive
Storage Data Bandwidth (Uncompressed GB/sec) 50 GB/sec!
Exadata V2 Multiple Racks 1 Rack 44 © 2010 Oracle Corporation
High Performance Backup with Exadata
•
Backup runs at 7 TB/hr
• • Both to tape and disk Less than 10% of Server CPU utilized during backup • • OLTP Compression triples the effective backup rate EHCC gets an effective rate of 70 TB/hr
Load Performance
• • Reporting queries continue to run uninterrupted during loading data into the database – – This is accomplished using Oracle’s unique Multi Version Read consistency Transaction model.
Oracle even has customers already doing “continuous loads” 24x7 while reporting queries are running in parallel Data loaded into Exadata as tables – – By loading from external files into tables in the database
Loads into tables on Exadata runs over 5 TB/hour
• Data loaded into Exadata into external tables on DBFS – – By loading ETL flat files into DBFS using ftp/scp Data can be immediately queried using SQL on external tables –
Loads into DBFS on Exadata runs over 90 TB/hour
Oracle is Ready for the Future
• High-Performance Today • Offloading and Caching for High Performance • High Performance with Large Data Volumes • End-to-end Performance Architecture