engineered systems what is it for dba’s systems - what is it... · exadata smart scans •...
TRANSCRIPT
© Copyright 2015. Apps Associates LLC. 1
Engineered Systems – What is it for DBA’s
February 23, 2015
© Copyright 2015. Apps Associates LLC. 2
Satyendra Kumar Pasalapudi
Associate Practice Director – IMS @ Apps Associates
Co Founder & President of AIOUG
@pasalapudi
© Copyright 2015. Apps Associates LLC. 3
Engineered Systems – What is it for DBA’s ?
© Copyright 2015. Apps Associates LLC. 4
How does the database process OLTP Caching
• Cache data in memory from disk to achieve fast query response
• For OLTP/ DSS workloads, memory should be big enough to hold as much as possible
SGA
Buffer Cache
100% cache hit ratio is ideal for
OLTP workloads
© Copyright 2015. Apps Associates LLC. 5
How does the database process OLTP?
• CPU time is mainly consumed by I/O wait
Cache miss Cache Hit
© Copyright 2015. Apps Associates LLC. 6
OLTP performance issues
2. User Volume is
increasing...
1. Data Size is
increasing...
SGA
1. Huge amounts of data
2. Not possible to cache
all data
3. Many I/O operations
Buffer
Cache
© Copyright 2015. Apps Associates LLC. 7
0.00
2.00
4.00
6.00
8.00
10.00
12.00
14.00
16.00
0
100
200
300
400
500
600
700
800
100 200 300 400 500 600 700 800
Response T
ime(N
orm
al-
100T
hre
ad=
>1.0
0)
TP
S(N
orm
al-
100T
hre
ad=
>100)
Thread
TPS RES
Typical Problem of busy systems
I/O bottleneck
Low Throughput and Slow Response
Low CPU usage
Why? • 3rd Platform drives new
demands on the database: – Global High Availability – Data volumes – Unstructured data – Transaction rates – Latency
• A single architecture cannot meet all those demands
Are traditional systems sufficient to handle workloads?
Complex technical support structure involving different vendors
Drove delays in resolving system outages Vendor finger pointing
Objective / Problem Statement
10
The current platform did not meet performance expectation Not meeting SLA’s Customers waiting in line
Database,OS,Siebel upgrade was overdue
Did not have room for growth or enough
capacity for additional users
Release process took up to 8 hrs of down time
No disaster recovery environment
Hardware was overdue to be replaced
1000+ IP’S 100+ Application Zones 16 Databases SOA ,Siebel ,OBIEE,OPA
Solution Approach
11
Data replication using Data Guard
Data replication using Data Guard
File replication using Zfs
File replication using Zfs
Dallas,TX Tulsa,OK
PROD STANDBY
STAGE PRIMARY
STAGE STANDBY
PROD PRIMARY
Near Zero down time using Golden Gate
1
2
Data replication using Data Guard
File replication using Zfs
Dallas,TX Tulsa,OK
Enable Golden Gate Capture
Code Deployment
Start Replicat
Trail
Files
Trail
Files
Extract
Dallas,TX Tulsa,OK
Data replication using Data Guard
File replication using Zfs Deployment Completed
Replicat Completed
Validation Passed
Oracle Engineered Systems
© Copyright 2015. Apps Associates LLC. 14
Hadoop Design Principles
• System shall manage and heal itself
– Automatically and transparently route around failure
– Speculatively execute redundant tasks if certain nodes are detected to be slow
• Performance shall scale linearly
– Proportional change in capacity with resource change
• Compute should move to data
– Lower latency, lower bandwidth
• Simple core, modular and extensible
© Copyright 2015. Apps Associates LLC. 21
Oracle Engineered Systems
Engineered for Database Physical I/O
Database CPUs Memory HBAs Switches Controllers
An unbalanced configuration
Exadata configuration
< 20% Possible
Efficiency
100% Possible
Efficiency
Disks
Database CPUs Memory HBAs Switches Controllers Disks
Designed To Avoid Bottlenecks
© Copyright 2015. Apps Associates LLC. 23
Exadata Eliminates Communication Bottlenecks
© Copyright 2015. Apps Associates LLC. 24
Exadata Evolution V1 to X5
2015
© Copyright 2015. Apps Associates LLC. 25
What’s Inside Exadata?
• Infiniband Switches
• Database Servers
• Storage Servers
© Copyright 2015. Apps Associates LLC. 26
Exadata Architecture
© Copyright 2015. Apps Associates LLC. 27
Exadata Smart Scans
• Exadata cells implement smart scans to greatly reduce the data that needs to be processed by database
– Only return relevant rows and columns to database
– Offload predicate evaluation
• Data reduction is usually very large
– Column and row reduction often decrease data to be returned to the database by 10x
• Join Filtering – Bloom filters used for join filtering in storage
• Telco wants to identify customers that spends more than Rs 200 per phone call
• The information about these customer occupies 2 MB in a Tera Byte Table
© Copyright 2015. Apps Associates LLC. 28
Traditional Scan Processing
© Copyright 2015. Apps Associates LLC. 29
Exadata Smart Scan Processing
© Copyright 2015. Apps Associates LLC. 30
Wait Event Description
Cell interconnect retransmit during physical read Database wait during retransmission for an I/O of a single-block or multiblock read
Cell single block physical read Cell equivalent of db file sequential read
Cell multiblock physical read Cell equivalent of db file scattered read
Cell smart table scan Database wait for table scan to complete
Cell smart index scan Database wait for index or IOT fast full scan
Cell smart file creation Database wait for file creation operation
Cell smart incremental backup Database wait for incremental backup operation
Cell smart restore from backup Database wait during file initialization for restore
Cell statistics gather Wait during query of v$cell views
Exadata Storage Server Wait Events
© Copyright 2015. Apps Associates LLC. 31
Smart Scan Execution Plan Example
© Copyright 2015. Apps Associates LLC. 32
Smart Scan Execution Plan Example
© Copyright 2015. Apps Associates LLC. 33
Unique Exadata Benefits are Increasing
New F40 Flash PCI Card in Storage
• New F40 eMLC card with 4X capacity
• eMLC is Enterprise grade Multi-level Cell. 2-bits per flash cell doubles capacity
– eMLC has excellent lifetime. As always, flash lifetime is guaranteed by Oracle. Any failed cards are replaced under Oracle Support contract
– Note this is true Enterprise MLC flash. Other vendors claim to use lower cost cMLC (consumer MLC) and extend its lifetime. This is not guaranteed by the flash manufacturer and performance towards end of lifetime is potentially degraded.
• Total read IOPs at the flash card level are much higher than quoted IOPs for DB Machine. We measure end-to-end SQL IOPs, not low level hardware IOPs.
• Read and Write latency improved by 40% or more
• Reduces Maintenance by replacing Energy Storage Module (ESM) with much longer lifetime conventional capacitors. Both guarantee writes are never lost.
Current F20 Card New F40 Card Improvement
Capacity* 96GB 400GB 4 X
Data Scan Rates 1 GB/s >1.4 GB/s 1.4X
Flash Cache Advantages over Flash Tiering
• All the existing Exadata flash advantages also apply
– Scale-out architecture
– InfiniBand 40Gb/sec connectivity
– Flash PCI Cards are much faster than flash disk
– EHCC compression enhances flash capacity
– Smart Scans on data in flash
– Smart Flash Logging
– Smart Caching avoids churning of cache by backups, loads, etc.
– Flash is shared across servers and works with RAC
• Unlike server flash cards
– Optionally Keep specific tables/indexes/partitions in flash with simple command
– Benefits from storage index
– Decompression and decryption in cell CPUs during scans
Faster Flash Scans for Data Warehousing
• Updated flash technology also enables faster Exadata Smart Scans
– Scan rate impoved 33% from 75 GB/sec to 100 GB/sec
• As always, Exadata flash cache technology enables scans to run simultaneously from disk and flash
– Delivers sum of the scan speeds of both
– Another unique benefit of flash cache over flash tiering
• Amazing Capacity when combined with compression
– Hybrid Columnar 10x Compression enables up to 200 TB of user data in the 20TB of physical flash capacity
– Advanced OLTP compression enables up to 80 TB of user data in flash
100 GB/sec
Scans
200 TB of
User Data
in Flash
© Copyright 2015. Apps Associates LLC. 37
I O Resource Manager – IORM
© Copyright 2015. Apps Associates LLC. 38
I/O Scheduling, the Traditional Way
Report Report Report Report
OLTP OLTP
Report
With traditional storage, disks service I/Os in FIFO order.
I/Os are reordered only to improve disk efficiency.
You cannot influence their behavior!
A burst of Report I/Os will be queued ahead of OLTP I/O.
And serviced ahead of OLTP I/Os!
© Copyright 2015. Apps Associates LLC. 39
I/O Scheduling, the Exadata Way
Report Report Report Report
OLTP OLTP
Report
I/O Resource Manager controls order that I/Os are issued to disk.
IORM issues enough I/Os to keep disks busy and efficient.
I/Os are queued per database, as necessary, within Exadata.
Uses Resource Plan to determine the order of I/O requests
Prevents a database from flooding the disk
I/O Resource Manager
OLTP
OLTP
OLTP
Exadata Storage Cell Resource
Plan
© Copyright 2015. Apps Associates LLC. 40
How Strong you are at ASM?
© Copyright 2015. Apps Associates LLC. 41
Oracle Database Exadata Machine X5
© Copyright 2015. Apps Associates LLC. 42
Exadata X5 is Sixth Generation Database Machine
© Copyright 2015. Apps Associates LLC. 43
New X5-2 Extreme Flash (EF)Storage Server
© Copyright 2015. Apps Associates LLC. 44
Elastic Configurations Scale Compute and Storage
© Copyright 2015. Apps Associates LLC. 45
Elastically Optimize Exadata for Each Workload
Operational RDBMS
(Oracle, SQL Server, …)
In-memory Analytics (HANA,
Exalytics …)
In-memory processing
(Spark)
Hadoop
Web DBMS (MySQL, Mongo,
Cassandra)
ERP & in-house CRM
Analytic/BI software
(SAS, Tableau
Web Server Data
Warehouse RDBMS
(Oracle, Terradata …)
Sample Enterprise Big data Architecture
© Copyright 2015. Apps Associates LLC. 47
Big Data Architecture
D A T A
S O U R C E S
DATA LAKE – On AWS Big Data Infra (Optrion2)
DATA CONNECTORS
A N A L Y T I C S
DATA LAKE on Oracle Big data Appliance (Option1)
DATA LAKE – On Premise Hadoop Infra(Option3) D A T A L A K E
© Copyright 2015. Apps Associates LLC. 48
Administration Skills Needed
System Administration 15%
Storage/Cell Administration
20%
Network Administration 5%
Database Administration 60%
DBA Mandatory Skills
Real Application Clusters (RAC)
Automatic Storage Management (ASM)
Recovery Manager (RMAN)
Partitioning, Oracle Secure Backup & Database Vault (Optional)
Skills Required
DBA should wear multiple hats ( Architect, DBA, Sys Admin, Storage Admin, Network Admin) +
Write back Flash cache, IORM, Smart Scan, Storage Indexes, Smart Flash Logging, Cell CLI)
© Copyright 2015. Apps Associates LLC. 49
DMA
© Copyright 2015. Apps Associates LLC. 50
Thank You!