“PSAM”, NEC PCIe SSD Appliance for Microsoft® SQL Server® (Reference Architecture) July 2014 NEC Corporation 1. Overview of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 2 © NEC Corporation 2014 Background of the solution (1/2) Issue#1: Performance gap between memory and storage ▌Customers are faced with performance bottleneck from performance gap between in-box memory / storage and external disks. Performance tuning is getting more complicated, needs many external storages and host bus adapters to provide enough IO bandwidth for high-performance DWH/BI system. (Storage Capacity) 1PB Huge Performance gap 100TB SW visibility gap 10TB 1TB DRAM PCIe SSD <1us <150us SAS SSD SAS Disk Array SATA Disk Array CPU Cache <ns Page 3 © NEC Corporation 2014 ~2ms ~8ms ~20ms (Access latency) Background of solution (2/2) Issue#2: CPU, Memory and IO Balanced Scalability ▌Customer’s expectation: Avoid excessive initial investment, CPU DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM IO Controller PCIe slot PCIe slot PCIe slot The latest QPI architecture-based processors have an integrated memory controller and IO controller on die. The architecture requires extra CPU sockets, even if customer just wants to have much IO slots (or memory). The extra CPU sockets contribute to increased power consumption and system cost QPI QPI QPI Memory Controller ▌Customer’s pain : Intel QPI* architecture Memory Controller System performance should scale up seamlessly by adding HW resources without modifying system (DB) configuration. QPI based CPU * QPI = Quick Path Interconnect (Interconnect between CPUs) “Rich IO slots” of NEC Express5800 Scalable Enterprise Server and “PCIe SSD (server-mounted flash storage)” will solve those issues “NEC PCIe SSD Appliance for Microsoft® SQL Server®” is the answer Page 4 © NEC Corporation 2014 NEC PCIe SSD Appliance for Microsoft® SQL Server® Overview Proven performance reference architecture for TCO optimization NEC Solution Platforms High-performance and high availability appliance system (reference の露出用 architecture) with no external storage. 記載文言は検討中 NEC Express5800 Scalable Enterprise Server-based “all in a box” solution combined with high-performance HGST FlashMAX II PCIe server-mounted flash storage. Best practice configuration fully utilizing NEC’s knowledge for SQL Server. Support the latest Microsoft SQL Server “SQL Server 2014”. Balanced CPU-IO performance depending on number of Flash MAX II (i.e., depending on data size ). No data lost by utilizing mirrored storage pool feature of Windows OS. Single Node model PCIe SSD AlwaysOn AG two node cluster model PCIe SSD PCIe SSD InfiniBand High band width Low Latency Page 5 © NEC Corporation 2014 2. Reference configuration of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 6 © NEC Corporation 2014 Reference configuration per Node Optimized performance combining fast clock CPU (E7-8893 v2:6C, 3.4GHz) and high-performance HGST FlashMAX II PCIe server-mounted flash storage (FM2).. No data lost by utilizing software-mirrored storage pool feature of Windows Operating System Balanced CPU-IO performance depending on number of FM2 (i.e., data size ). • 1CPU (3.4GHz, 6C) • 128GB RAM • 2x FM2 in mirrored storage pool Page 7 • 2CPU (3.4GHz, 6C) • 256GB RAM • 4x FM2 in mirrored storage pool © NEC Corporation 2014 • 3CPU (3.4GHz, 6C) • 384GB RAM • 6x FM2 in mirrored storage pool • 4CPU (3.4GHz, 6C) • 512GB RAM • 8x FM2 in mirrored storage pool Block Diagram Express5800 Scalable Enterprise Server SW Mirror FlashMAX II 1.1TB FlashMAX II 1.1TB 128GB RAM 128GB RAM E7-8893 v2 E7-8893 v2 Intel® Xeon® Processor E7-8893 v2 (6core 3.4GHz) FlashMAX II 1.1TB SW Mirror FlashMAX II 1.1TB PCIeGen2 x8 QPI SW Mirror FlashMAX II 1.1TB FlashMAX II 1.1TB Internal Drive Page 8 © NEC Corporation 2014 E7-8893 v2 E7-8893 v2 128GB RAM 128GB RAM FlashMAX II 1.1TB FlashMAX II 1.1TB SW Mirror Reference configuration as single node Data Warehouse system Reference Configuration for Single Node Data Warehouse system 1CPU 2CPU 3CPU CPU and Memory 128GB 256GB 384GB RAM RAM RAM Express5800 Scalable Enterprise Server 1 1 1 Built-in DVD-ROM Drive 1 1 1 Additional CPU （E7-8893 v2, 6 core） 1 2 3 Memory Riser 2 4 6 16GB additional memory (8GB DIMM x2) 8 16 24 10KRPM 600GB HDD 3 5 5 RAID SAs-Controller (1GB, RAID 0/1/5/6) 1 1 1 1000BASE-T Network Adopter (4 port) 1 2 3 1.1TB FlashMAX II # of card Maximum Physical Storage Capacity per node * •Max capacity mode •Mirrored Storage Pool 4CPU 512GB RAM 1 1 4 8 32 7 1 4 2 4 6 8 1.1TB 2.2TB 3.3TB 4.4TB * Data capacity after compression. About 1/3.5 of logical user data capacity before compression Page 9 © NEC Corporation 2014 Reference configuration as single node OLTP system Reference Configuration for Single Node OLTP system CPU and Memory Express5800/A1040b Built-in DVD-ROM drive Additional CPU （E7-8893 v2, 6 core） Memory Riser 16GB additional memory (8GB DIMMx2) 10krpm 600GB SAS-HDD RAID SAS-Controller (1GB, RAID 0/1/5/6) 1000BASE-T Network Adopter (4 port) # of card Maximum Physical Storage 1.1TB Capacity per node FlashMAXII • Max Performance mode •Mirrored Storage Pool Page 10 © NEC Corporation 2014 1CPU 128GB RAM 1 2CPU 256GB RAM 1 3CPU 384GB RAM 1 4CPU 512GB RAM 1 1 1 2 8 3 1 1 2 1 2 4 16 5 1 2 4 1 3 6 24 5 1 3 6 1 4 8 32 7 1 4 8 0.9TB 1.7TB 2.6TB 3.4TB 3. Performance of NEC PCIe SSD Appliance for Microsoft® SQL Server® as Dara Warehouse system Page 11 © NEC Corporation 2014 Single Node DWH Performance (1/2) DWH performance comparison with Express5800/A1080a based FTRA DWH using Microsoft Fast-track Data Warehouse Benchmark tool. （FTRA DWH =Microsoft SQL Server Fast Track Data Warehouse) Configuration for DWH performance comparison CPU NEC PCIe SSD Appliance for Microsoft® SQL Server® Express5800/A1080a-S FTRA DWH E7-8893 v2 (6C, 3.4GHz) E7-8870 (10C, 2.4GHz) CPU socket count 1 2 3 4 2 Total core count 6 12 18 24 20 Physical memory capacity 128GB 256GB 384GB 512GB 128GB Storage configuration 1.1TB FlashMAX II (Max Capacity Mode) Mirrored storage pool configuration 1set Physical Storage Capacity 1.1TB User data* * Physical Data capacity after compression ~0.5TB About 1/3.5 of logical user data Capacity capacity before compression. per Transaction log ~0.2TB Usage tempdb ~0.3TB Page 12 © NEC Corporation 2014 2 sets 3sets 4 sets iStorage M100 x3 (72 450GB 10krpm SAS-HDD) RAID-10 18 pair 2.2TB 3.3TB 4.4TB 16.2TB ~1.0TB ~1.5TB ~2.0TB 8.0TB ~0.4TB ~0.6TB ~0.8TB 3.5TB 0.6TB ~0.9TB ~1.2TB 4.5TB Single Node DWH Performance (2/2) Performance comparison using Microsoft Fast-track Data Warehouse benchmark tool 8,000 7,000 3.50 8,234 Benchmark Logical Scan rate [MB/s] 3.00 Lead time to scan all user data [s] 2.50 6,000 4,820 5,000 4,000 4,759 3,422 2.00 1.50 3,000 2,000 1.00 1,425 0.50 1,000 0 0.36 0.30 0.32 0.25 1.72 1P6C 2P12C 3P18C 4P24C 2P20C NEC PCIe SSD Appliance for Microsoft® SQL Server® （E7-8893v2 3.4GHz） Page 13 © NEC Corporation 2014 0.00 Fast-track Data Warehouse NEC Express5800 A1080a-S （E7-8870 2.4GHz） Benefit as Data Warehouse system Performance boost with significant TCO saving Express5800 A1080a-S base Microsoft Fast-track Data Warehouse NEC PCIe SSD Appliance for Microsoft® SQL Server ® 2P20C (2.4GHz) + 128GB RAM 4P24C (3.4GHz) + 512GB RAM To Balance CPU power and IO bandwidth, 12 FC channels and 72 HDDs are required 8G-FC channels x12 (2p 8G-FC HBA x6) Total 72 SAS-HDD drives 13U 69% less rack space 4U 2,125W 27% less power consumption 1,551W 4,759MB/s 173% better performance 8,234MB/s Rack space Power Consumption Benchmark logical scan rate • Microsoft Fast-track Data ware house benchmark tool Page 14 © NEC Corporation 2014 All-n-a-box Solution 4. Performance of NEC PCIe SSD Appliance for Microsoft® SQL Server® as OLTP system Page 15 © NEC Corporation 2014 Single Node OLTP Performance (1/2) OLTP performance comparison with “Express5800/A1080a + external FC storage system” using “Hammer DB” benchmark tool. Configuration for OLTP performance Comparison CPU NEC PCIe SSD Appliance for Microsoft® SQL Server® Express5800/A1080a-S + External FC storage E7-8893 v2 (6C, 3.4GHz) E7-8870 (10C, 2.4GHz) CPU socket count 1 2 3 4 2 Total core count 6 12 18 24 20 128GB 256GB 384GB 512GB 256GB Physical memory capacity 1.1TB FlashMAX II (Max Performance Mode) Mirrored storage pool configuration 1set 2 sets 3sets 4 sets iStorage M100 x1 (24 450GB 10krpm SAS-HDD) RAID-10 6 pair Physical Storage Capacity 0.9TB 1.7TB 2.6TB 3.4TB 5.4TB Physical User data Capacity Transaction log per tempdb Usage ~0.5TB ~1.0TB ~1.5TB ~2.0TB 3.6TB ~0.2TB ~0.4TB ~0.6TB ~0.8TB 1.0TB ~0.2TB ~0.3TB ~0.5TB ~0.6TB 0.8TB Storage configuration Page 16 © NEC Corporation 2014 Single Node OLTP Performance (2/2) Performance comparison using HammerDB benchmark tool Relative performance 200 150 Performance down rate Relative performance of Optimized I/O scenario Relative performance of Non-Optimized I/O scenario Performance down rate between 2 scenarios （%） 50% 100 47% 131 0 96% 178 75% 49% 46% 95 50 100% 91 50% 100 71 54 25% 50 27 1P6C 4 2P12C 3P18C NEC PCIe SSD Appliance for Microsoft® SQL Server® （E7-8893v2 3.4GHz） 4P24C 0% 2P20C NEC Express5800 A1080a-S (E7-8870 2.4GHz） + iStorage M100 x1 NEC Express5800 A1080a-S performance of optimized I/O scenario is the base performance of the relative performance comparison Optimized IO scenario ： Minimize IO by enabling Server side memory caches and Storage side cache. Non-optimized IO scenario： Maximize IO by disabling Server side memory caches and Storage side cache. Page 17 © NEC Corporation 2014 Benefit as OLTP system Achieve high performance even in heavy IO stress, requires less DB tuning cost (i.e., less system integration cost) Express5800 A1080a-S +iStorage M100 2P20C (2.4GHz) + 256GB RAM NEC PCIe SSD Appliance for Microsoft® SQL Server ® 4P24C (3.4GHz) + 512GB RAM 8G-FC channels x 4 (2p 8G-FC HBA x 2) All-in-a-box solution Total 24 SAS-HDD drives Relative OLTP performance (TPS) comparison for optimized I/O scenario *1, *2 100 OLTP performance down ratio between the optimized IO scenario And Non-optimized IO scenarios ＊1,3 96% 178% better performance 53% less impact of IO 178 49％ *1 Relative performance comparison using HammerDB benchmark tool *2 Optimized IO scenario ： Minimize IO by enabling Server side memory caches and Storage side cache. *3 Non-optimized IO scenario： Maximize IO by disabling Server side memory caches and Storage side cache. Page 18 © NEC Corporation 2014 5. Summary Benefit of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 19 © NEC Corporation 2014 Benefit of NEC PCIe SSD Appliance for Microsoft® SQL Server ® Express5800 Scalable Enterprise Server High-performance PCIe server-mounted flash storage Ｘ Balanced and Predictable Performance Easy to introduce TCO reduction • Balanced and Proven CPU-IO Performance depending on number of Flash MAX II. • Consistent high-performance even under heavy IO load. • Customer can choose best practice configuration depending on data size. • Configuration guide, including best parameter setting will be available * • Less platform integration cost • Less Rack apace • Less Power Consumption Initial Cost Operating Cost Time to introduction * Pre-configuration and Pre-installation Are subject of discussion Page 20 © NEC Corporation 2014 Down Big Data Analytics Solution built on NEC PCIe SSD Appliance for Microsoft® SQL Server® “Microsoft SQL Server 2014” equipped with ETL, DB, BI, Data Mining, easy-to-use “Excel & Power BI” and “NEC PCIe SSD Appliance for Microsoft® SQL Server®” provide Low-Cost All-in-One Big Data Analysis Platform INPUT Big Data Analytics Platform Data Extract Process & Aggregation Store data into High-Performance PCIe server-mounted flash storage Enterprise System TARO SUZUKI 08/07 Customer Data NEC PCIe SSD Appliance for Microsoft® SQL Server® Web site Blog Data Scientist SMS SQL Server 2014 Analysis Services Smart Device Page 21 © NEC Corporation 2014 End user Page 22 © NEC Corporation 2014