Accelerating Oracle OLTP Database with Dell Flash based storage: Case Studies

1. Accelerating Oracle OLTP Database with Dell Flash based storage: Case Studies Guy Harrison, Kai Yu, Naveen Iyengar

2. Agenda

3. Agenda • Why should you care about Flash based storage disks? • Types of Flash based storage disks • Dell Flash based storage solutions • Case studies – performance analysis • Q&A

4. Why should you care about Flash based storage disks?

5. Why should you care about flash based storage drives? • HDDs are performance limited by the spinning speed of the disk (15K rpm) and its mechanical head movement • Compared to HDDs flash based storage or Solid State Drives (SSDs) • Have no moving parts • Extremely fast, low-latency, and high IOPS • Single SSD can replace many HDDs • Consolidate rack space • Save on power and cooling Flash SSD Technologies FASTER SLOWER

6. Types of Flash based storage disks

7. Types of flash based storage disks or SSDs • SAS/SATA based SSDs • Use the same SAS/SATA based interface as HDDs • Accessed by the system through OS driver stack • Performance better than HDDs but still limited by OS driver stack • PCIe based SSDs • Directly connected to PCIe bus • Directly connected to CPU and system memory through PCIe bus • Faster than HDDs and SAS/SATA based SSDs • Two types • Single-level cell (MLC) NAND • Best suited for performance intensive applications • Smaller capacity • Multi-level cell (SLC) NAND • Suited for applications having high capacity requirements • Lower cost alternative to SLCs Flash SSD Technologies FASTER SLOWER

8. Dell Flash based storage solutions

9. Dell Flash based storage solutions • Server-side PCIe SSD based solutions • Database directly on Dell PowerEdge Express Flash PCIe SSDs • Database cached on Dell PowerEdge Express Flash PCIe SSDs using Fluid Cache for DAS • Storage-side PCIe SSD based solutions • Dell Compellent All-Flash array PCIe-SSD SAS/SATA

10. Case studies –Dell Flash based Storage Solutions Performance analysis

11. Case Study #1Oracle OLTP Database directly on Dell PowerEdge Express Flash PCIe SSDs

12. Introduction to Express Flash PCIeSSD • Directly connected to PCIe bus • Up to four PCIe SSDs per server • 2.5 inch plugged into a device bay • Capacity: 175GB/350GB • Hot Swappable: add or remove a device without halting or rebooting the system

13. Use Cases of PCIe SSDs for Oracle Database • Use PCIe SSDs stores the entire database schema. • PCIe SSDs stores portions of a database schema four PCIe SSDs combining with MD3220 external storage with 24 HDDs • PCIeSSD used as Oracle Smart flash cache in a 11g R2 RAC Database two PCIe SSDs combining with MD3220 external storage with 24 HDDs

14. Performance Testing Results • Use PCIe SSDs stores the entire database schema: comparison of two configurations: • Baseline: the entire database schema stored in Hard Disk Drives (HDDs) • Compared group: the entire database schema stored in PCIe SSDs TPS Comparison: 569% increase Response time (MS) Comparison: 250% increase of # of users for 1 second time 99 times reduce of query response time for 600 users

15. Performance Testing Results • User case 2: PCIe stores partial database: • Config1 : all the database objects stored in HDDs. (Base Line) • Config2: all indexes stored in PCI-e SSD Drives , • Config3: all indexes plus one most active table stored in PCI-e SSDs • Config4: all indexes plus four active tables stored in PCI-e SSD Response time Comparisons: TPS Comparisons: 12%-200% increase of # of users in 1 sec mark 14%-310% TPS Increases 2-16 times decrease of response time for 800 users

16. Performance Testing Results • Use PCIe as the smart flash cache for RAC --- an extension of database buffer cache • Configure PCEI SSD as Oracle Smart cache: • Set udev rule in /etc/udev/rules.d KERNEL=="rssda1",OWNER="oracle", GROUP="dba", MODE="0775"

17. Performance Testing Results SELECT name, value FROM v$sysstat WHERE name IN ('physical read flash cache hits', 'physical reads', 'consistent gets', 'db block gets', 'flash cache inserts'); NAME VALUE ---------------------------------------------------------------- ---------- db block gets 10912661 consistent gets 194736483 physical reads 4514407 physical read flash cache hits 2047180 flash cache inserts 246469 • 44.8% increase of TPS (Transactions Per Second)

18. Performance Testing Results • 5.8 times decrease of Query Average Response Time

19. Performance Testing Results • Observed different database wait patterns

20. Performance Testing Results • Free buffer wait event • Process A: Fast data read from smart flash cache • Process B: Slow process to free a slot in memory due to the slow writing a dirty block to disk • Fast process A has to wait for slow process B  Free buffer wait

21. Oracle Deployments: Challenges • Technical Challenges: • Increase performance and availability • Increase Automation • Reduce Management Complexity • Increase online data accessibility • Business Challenges: • Structured Data Growth at CAGR > 50% • Flat or declining IT budgets • Do more with less • Support aggressive Goto market requirements

22. Case Study #2Oracle OLTP Database on Dell Compellent All-Flash Storage Array

23. Solving Database challenges • Common Approaches: • Add more processing/ memory • Add more storage • Add Server Flash • High touch database tuning and management • Limitations: • Limited effectiveness • Storage overprovisioning not sustainable • Point fix/Capacity Constrained • Limited effectiveness

24. Better Solution: Automated Tiered Storage • Benefits of Automated Tiered Solution: • Increase performance without increasing cost • Scale performance/capacity independently • Right data in the right place at the right time – at the right cost • Automation reduces management overhead • Increase amount of data that is online • Leverage existing investments

25. New Compellent All Flash Array Solution • Flash Tiering Economics: • Improve cost per GB and cost per I/O • Reduce HW requirements • Reduce SW Licensing • Reduce maintenance/support costs • Reduce environmental cost factors >300K IOPS >100,000 IOPS and sub-millisecond latency running OLTP workload 75% Lower Cost Increase SSD Capacity Than most All-Flash solutions Reduce SSD costs 5X 80%

26. SSD Tiering • How it works: • Flash Optimized Tiering • Write Intensive Tier for Reads/Writes • Read Intensive Tier for Read Only Data • On Demand data progression • Flash Optimized Storage Profile • Enhanced Flash Wear monitoring • Capacity addition through NL-SAS Tier 1 $$$ RAID 10 Tier 2 $$ RAID 5 Tier 3 Write-Intensive Enterprise SAS SSD (400GB SLC) $ RAID 6 RAID 5 Read Intensive Enterprise SAS SSD (1.6TB MLC) RAID 6 HDD (15K, 10K, 7.2K) 1. Dell case study: 7-Eleven; 2, Salesforce.com

27. Performance Test Environment • Test Hardware: • 2 X M820 Blade Servers • Blade IO modules: • A: M8024 10GbE Switch • B: Force 10 10GbE Switch • C: M6505 16GB FC Switch • Storage : 2 X SC8000 Compellent Controllers • 4 X SC220 enclosures: • 72 X 15k SAS disks • 6 Write Intensive (SLC)SSDs • 6 Read Intensive (MLC) SSDs • Test Software: • Oracle Linux 6.4 UEK kernel • Oracle Database Enterprise • Edition 11. 2.0.4 • Orion • Dell Quest Benchmark Factory 100% 86%

28. Performance Test Environment 100% 86%

29. Orion Test Results Same Cost ORION: OLTP All Flash Vs Spinning Media (15k) Flash Performance 6XWI (400G) + 6XRI (1.6T) 72X146G 15k 100% Vs Flash Latency 86%

30. TPCC Test Results

33. Case Study #3Oracle OLTP Database Cached on Dell PowerEdge Express Flash PCIeSSDs using Fluid Cache for DAS

34. What is Fluid Cache for DAS? • Server-based caching technology • Leverages Dell’s hot pluggable PowerEdge Express Flash PCIe SSDs • Transparent to the Application • Reduces latency & accelerates response times for any Linux-based application needing improved random IO reads and writes (i.e. Oracle OLTP) or sequential reads • Consists of software, PowerEdge w/ Express Flash and optional PowerVault

35. Fluid Cache for DAS Differentiators • Write-back cache accelerates data writes as well as reads • Pools up to 4 Express Flash PCIe SSDs in a server • Protects write cache data with efficient block replication technologies • Combines performance enhancements with data integrity • Cache stays warm on reboot SAS/SATA PCIe-SSD

36. How Fluid Cache works:Enables High Speed Flash Performance for Direct Attached Storage Shared Read/Write Cache Pool Fluid Cache for DAS Server PCIe SSDs >50x IOPS 1x IOPS 1X IOPS Shares PCIe Flash Cache Pool for VDs across up to 3 RAID Controllers (only 2 used here) RAID cont. Internal Storage Oracle Database RAID cont. RAID cont. External PowerVault JBOD Storage External PowerVault JBOD Storage Dell (Server + Storage+ Flash) + Fluid Cache  Application Acceleration

37. Fluid Cache for DAS: Oracle OLTP Database reference architecture • Fluid Cache for DAS 1.0 • PowerEdge R720 • Database & OS • Oracle Single Node 11gR2 Enterprise or Std. Edition DB SW • Oracle Linux 6.2(RedHat Compatible Kernel) • PowerVault MD1220 • H810 connected to MD1220s in balanced mode • MD1220s connected in a daisy chain • 8 x R10 Data Volumes, 4 x R5 FRA* Volumes, 12 Global Hot Spares * FRA – Flash Recovery Area (contains Archive Logs, Backups, etc.)

38. TPC-C Performance: Transactions per second (TPS)

39. TPC-C Performance : Average Response Time (ART)

40. TPC-C Performance : Max User load Scalability

41. Fluid Cache for DAS Resources • Dell Express Flash PCIe SSDs – www.dell.com/poweredge/expressflash • Fluid Cache for DAS user’s guide - ftp://ftp.dell.com/Manuals/all-products/esuprt_electronics/esuprt_software/esuprt_cache_solutions/dell-fluidcache-das_User's%20Guide_en-us.pdf • Oracle OLTP Fluid Cache for DAS reference architecture white paper – ftp://ftp.dell.com/Manuals/all-products/esuprt_electronics/esuprt_software/esuprt_cache_solutions/dell-fluidcache-das_White%20Papers90_en-us.pdf

42. Q & A GlobalMarketing