“PSAM”, NEC PCIe SSD Appliance for Microsoft® SQL Server® (Reference Architecture) September 11th, 2014 NEC Corporation 1. Overview of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 2 © NEC Corporation 2014 NEC PCIe SSD Appliance for Microsoft SQL Server - Customer’s Pain : Background of the Solution ▌System bottleneck from performance gap between in-box memory / storage and external disks l Performance tuning is getting more complicated, needs a lot of external storage and Host Bus Adapters to provide enough IO bandwidth for a high-performance system ▌Balanced CPU-IO configuration restricted by the latest QPI architecture-based processors l The architecture requires extra CPU sockets, even if customer just wants to have more IO slots (or memory). The extra CPU sockets contribute to increased power consumption and system cost 1PB Huge Performance gap QPI 100TB 10TB PCIe SSD <1us <150us ~2ms ~8ms ~20ms DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM DIMM IO Controller CPU Cache <ns Memory Controller SATA Disk Array (Access latency) PCIe slot DRAM QPI QPI PCIe slot PCIe slot 1TB SAS SSD SAS Disk Array CPU Memory Controller (Storage Capacity) QPI based CPU * QPI = Quick Path Interconnect (Interconnect between CPUs) “PCIe SSD (server-mounted flash storage)” and “Rich IO slots (per CPU)” are the keys to solve those issues Page 3 © NEC Corporation 2014 NEC PCIe SSD Appliance for Microsoft SQL Server - Overview Proven performance reference architecture for TCO optimization - Best fit for High-Performance & Scalable DWH/BI/OLTP system NEC Solution Platforms l 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 l Best practice configuration fully utilizing NEC’s knowledge of 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 AlwaysOn AG two node cluster model Express5800 Scalable Enterprise Server • Max. 4 Intel® Xeon® Processor E7 v2 family CPU • Max. 4TB memory • Max. 16 slots of PCI-Express 3.0 (Class No.1 rich IO slots) Page 4 © NEC Corporation 2014 10G Ether High band width Low Latency 2. Reference configuration of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 5 © NEC Corporation 2014 NEC PCIe SSD Appliance for Microsoft SQL Server - 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 loss by utilizing software-mirrored storage pool feature of Windows OS Ø 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 • 4CPU (3.4GHz, 6C) • 512GB RAM • 8x FM2 in mirrored storage pool Express5800 Scalable Enterprise Server FlashMAX II SW Mirroring FlashMAX II 128GB RAM 128GB RAM E78893 v2 E78893 v2 FlashMAX II SW Mirroring FlashMAX II QPI Scale Up Scale Up • 2CPU (3.4GHz, 6C) • 256GB RAM • 4x FM2 in mirrored storage pool • 3CPU (3.4GHz, 6C) • 384GB RAM • 6x FM2 in mirrored storage pool Scale Up Page 6 © NEC Corporation 2014 FlashMAX II SW Mirroring FlashMAX II Internal Drive E78893 v2 E78893 v2 128GB RAM 128GB RAM FlashMAX II SW Mirroring FlashMAX II Reference configuration as single node Data Warehouse system CPU and Memory Express5800 Scalable Enterprise Server Built-in DVD-ROM Drive Additional CPU （E7-8893 v2, 6 core） Memory Riser 16GB additional memory (8GB DIMM x2) 10KRPM 600GB HDD RAID SAs-Controller (1GB, RAID 0/1/5/6) 1000BASE-T Network Adopter (4 port) 1.1TB FlashMAX II # of card Maximum Physical Storage Capacity per node * •Max capacity mode •Mirrored Storage Pool 1CPU 128GB RAM 1 1 1 2 8 3 1 1 2CPU 256GB RAM 1 1 2 4 16 5 1 2 3CPU 384GB RAM 1 1 3 6 24 5 1 3 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/5 of logical user data capacity before compression Page 7 © NEC Corporation 2014 Reference configuration as single node OLTP system CPU and Memory Express5800 Scalable Enterprise Server 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 Flash MAXII • Max Performance mode •Mirrored Storage Pool Page 8 © 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 Reference configuration as two node OLTP system (configuration per node) CPU and Memory Express5800 Scalable Enterprise Server 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 10GBASE Network Adopter (SFP+/2 port) Page 9 © NEC Corporation 2014 2CPU 256GB RAM 1 3CPU 384GB RAM 1 4CPU 512GB RAM 1 1 2 4 16 5 1 2 4 1 3 6 24 5 1 3 6 1 4 8 32 7 1 4 8 1.7TB 2.6TB 3.4TB 1 1 1 3. Performance of NEC PCIe SSD Appliance for Microsoft® SQL Server® as Dara Warehouse system Page 10 © 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/5 of logical user data Capacity capacity before compression. per Transaction log ~0.2TB Usage tempdb ~0.3TB Page 11 © 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,234 3.50 8,000 7,000 3.00 Benchmark Logical Scan rate [MB/s] Read time to scan all user data [s] 2.50 6,000 4,820 5,000 4,000 4,759 3,422 1.50 3,000 2,000 1,000 0 1.00 1,425 0.50 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 12 2.00 © NEC Corporation 2014 0.00 Fast-track Data ware house 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 13 © NEC Corporation 2014 All-n-a-box Solution 4. Performance of NEC PCIe SSD Appliance for Microsoft® SQL Server® as single node OLTP system Page 14 © NEC Corporation 2014 Single Node OLTP Performance (1/2) OLTP performance comparison with “Express5800/A1080a-S + external FC storage system” using “Hammer DB” benchmark tool. Configuration for OLTP performance Comparison 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 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 15 © NEC Corporation 2014 Single Node OLTP Performance (2/2) Performance comparison using HammerDB benchmark tool Relative performance 200 Performance down rate Relative performance of Non-Optimized I/O scenario 150 Performance down rate between 2 scenarios （%） 50% 47% 100 131 46% 95 50 0 100% Relative performance of Optimized I/O scenario 96% 178 75% 49% 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 16 © NEC Corporation 2014 Benefit as single node 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 17 © NEC Corporation 2014 5. Performance of NEC PCIe SSD Appliance for Microsoft® SQL Server® as two node cluster OLTP system Page 18 © NEC Corporation 2014 Two Node OLTP Performance (1/2) OLTP performance comparison with “Express5800/A1080a-S + external FC storage system” using “Hammer DB” benchmark tool. Configuration (per node) for OLTP performance Comparison NEC PCIe SSD Appliance for Microsoft® SQL Server® Express5800/A1080a-S + External FC storage CPU E7-8893 v2 (6C, 3.4GHz) E7-8870 (10C, 2.4GHz) CPU socket count 2 3 4 2 Total core count 12 18 24 20 256GB 384GB 512GB 256GB Physical memory capacity 1.1TB FlashMAX II (Max Performance Mode) Storage configuration Mirrored storage pool configuration iStorage M100 x1 (24 450GB 10krpm SAS-HDD) RAID-10 6 pair 2 sets 3sets 4 sets Physical Storage Capacity 1.7TB 2.6TB 3.4TB 5.4TB Physical User data Capacity Transaction log per tempdb Usage ~1.0TB ~1.5TB ~2.0TB 3.6TB ~0.4TB ~0.6TB ~0.8TB 1.0TB ~0.3TB ~0.5TB ~0.6TB 0.8TB Page 19 © NEC Corporation 2014 Two Node OLTP Performance (2/2) Performance comparison using HammerDB benchmark tool Relative performance 200 Performance down rate 100% Relative performance of Optimized I/O scenario 98% Relative performance of Non-Optimized I/O scenario 150 Performance down rate between 2 scenarios (%) 124 100 50 29% 29% 165 113 88 80 75% 32% 50% 100 25% 57 2 0 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 20 © NEC Corporation 2014 Benefit as two node 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 98% 165% better performance 34% less impact of IO 165 32％ *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 21 © NEC Corporation 2014 6. Recommendation to improve availability of the system Page 22 © NEC Corporation 2014 EXPRESSCLUSTER X SingleServerSafe （SSS) To reinforce availability of the system, it is recommended to install EXPRESSSLUSTER X SSS ▌What is EXPRESSCLUSTER X SSS ? lEXPRESSCLUSTER X SSS is a program product which monitors abnormality of HW & SW, recovers them from failure, and improves availability of the system. lEXPRESSCLUSTER X SSS contributes to reinforce fault resiliency of 2 node AlwaysON AG system also. üAdditional to monitoring targets of the AlwaysOn, improve fault resiliency of the 2 node system by monitoring • Disk failure: Restarts the node, then fail over to the secondary node to continue operation. • LAN failure of the secondary “read only” node: Change routing of “read only access” from the faulty node to the other heathy node (in 2 node system, primary node) to continue system operation. Page 23 © NEC Corporation 2014 Router, other network devices Ping check monitoring Application layer App Presence monitoring Response monitoring Monitoring agent X SingleServerSafe OS layer Stall monitoring OS space Access Check Hardware layer System disk PCIeSSD NIC Link-down monitoring NIC EXPRESSCLUSTER X SSS’s target of monitoring ▌Accurate monitoring to check the actual status Router, other network devices Ping check monitoring Application layer • Restarts when process abnormality is detected App Response monitoring Presence monitoring Monitoring agent X SingleServerSafe OS layer Hardware layer • Restarts when abnormality in SQL, HTTP and so on is detected ▌OS layer l User space monitoring • Restarts when abnormality in AP layer such as hang-up is detected. • Restarts when disk/PCIeSSD access abnormality is detected. Access check System disk PCIeSSD Page 24 l Monitoring agent ▌Hardware layer l Access check Stall monitoring OS space ▌Application layer: l Presence monitoring of process © NEC Corporation 2014 NIC l Ping monitoring NIC l NIC Link UP/DOWN monitoring Link-down monitoring • Restarts when ping abnormality is detected. • Restarts when link-down is detected. • Availability can be improved by NIC duplication. 7. Summary Benefit of NEC PCIe SSD Appliance for Microsoft® SQL Server® Page 25 © 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 • Balanced and Proven CPU-IO Performance depending on number of Flash MAX II. • Consistent high-performance even under heavy IO load. Easy to introduce TCO reduction • Customer can choose best practice configuration depending on data size. • Cook book (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 26 © NEC Corporation 2014 Down Image of 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 27 © NEC Corporation 2014 End user NEC brings together and integrates technology and expertise to create the ICT-enabled society of tomorrow. We collaborate closely with partners and customers around the world, orchestrating each project to ensure all its parts are fine-tuned to local needs. Every day, our innovative solutions for society contribute to greater safety, security, efficiency and equality, and enable people to live brighter lives. Page 28 © NEC Corporation 2014 Page 29 © NEC Corporation 2014