Exalytics @ WellPointThe Fastest Essbase, Hyperion Planning, and OBIEE Performance Imaginable. Jonathon Eastman WellPoint, Inc.
AGENDA • Introduction. • Why Oracle Exalytics. • Business Case. • Extreme Performance Infrastructure. • Lessons Learned… including performance. • Questions???
About WellPoint • Health Benefits Company • 37 Million Members Q1 2014 • $71 Billion Revenue 10K 2013 • ≈ 50K Associates and Contractors.
About Me And Our Department • Jonathon Eastman, Director EPM/BI. • Center of Excellence – EPM. • Cross functional team of CPAs to technical experts. • We manage system tools and processes critical for the forecasting and budgeting, internal management reporting, and external GAAP and Statutory Reporting functions. • We report to CAO even though we perform many functions traditionally considered “IT”.
EPM @ WellPoint • Uber reliant on Essbase. • 2500 named Essbase users. • 40 Reporting Cubes, 500+ Production Cubes. • Reporting Tools. • Financial Reporting/Workspace • Excel Add-In/Smart View. • Interactive Reporting (Dashboards). • OBIEE with mobility. • Hyperion Planning. 300 users. • EPM 22.214.171.124.500 OBIEE 126.96.36.199
Consulting WellPoint’s Partner. Press Training • interRel Consulting. • WellPoint Consultants last 7 years. • We won the Oracle Innovation award together @ OpenWorld 2013 (EPM/BI). Infra structure Support
Why Oracle Exalytics – Part 1. • Over 1000 pages. • Expected acquisitions and new business requirements. • More data, metadata, and processes.
Why Oracle Exalytics – Part 2. • Aging and complex infrastructure. • 13 AIX servers and 5 windows servers. • OS Patching and upgrades costly and time consuming.
Why Oracle Exalytics – Part 3. • Tested, Certified, Packaged, Deployed, Upgraded, Managed, and Supported Together. • AIX Last Tested, Last Released. • Performance!!!!
WellPoint’s Business Case – Part 1. • Align with an initiative. • Amerigroup acquisition for initial 3 Exalytics boxes. • ACA Exchanges for 4thExalytics box. • Easier to find budget. • Replace aging infrastructure. • Easy way to get concrete numbers. • Priced internally against WellPoint Standard Offerings. • We did not shop numbers against outside vendors.
WellPoint’s Business Case – Part 2. • Roughly 25% cost savings. • Can’t release hard numbers. • Includes new software • Converted to BI Foundation Suite • Received Mobility and others. • Majority of our cost. • Better Servers. • More CPU (80 cores to 120 cores). • More RAM (640 GB to 3 TB).
WellPoint’s Business Case – Part 3. • Savings were enough to justify. • Normalize server CPU/RAM specs. • Take out the software portion. • Apply performance ratio to the cost. • Administrative savings. • Server consolidation (patching, upgrades, monitoring, etc.). • Subjective savings • Better lifecycles due to performance improvements.
WellPoint’s New Infrastructure – Part 1 • F5 – to load balance for to EPM web applications (Round Robin). • 3 Exalytics X2-3 boxes (No Flash Storage). • ZFS storage device. • EPM web application share (RM1, LCM). • Essbase App Directory
WellPoint’s New Infrastructure – Part 2 • Added 4th Exalytics Server. • Oracle VM. • DEV, TEST, and PROD • Fully utilize box.
Lessons Learned – Procurement Part 1. • Budget correctly. • Transceivers do not come with the Exalytics boxes. • 10G networking (We did not use Infiniband). • Required on the switch side and on the server side. • Fiber Channel cards and transceivers do not come with the ZFS appliance. • Virtualization and ASR (automated service request) require commodity OEL box (linux). • Load balancing/Fail over potential costs.
Lessons Learned – Procurement Part 2. Who does What?
Final Roles and Responsibilities. • Power, Networking, Disaster Recovery. • Hardware failure. • Information Technology – Governance, User Security. • EPM CEE – Everything EPM, Associate Support • Exalytics OS Patching, admin support.
Lessons Learned – Installation. • Not Like Apple computer all ready to go. • Now ships with Oracle Linux (5.5) pre-installed. Oracle Virtual Linux now option as fixed price install. Done on-site. • 1 week to install OBIEE/Time Ten on three Exalytics boxes. • Need to fill out Pre-Installation Questionnaire. • Host Names, static ip addresses, DNS Servers, gateways. • Logistics and Facility access. • Database configuration. • Create SS Schemas, Temporary dba access to run OBIEE RCU. • Port Configuration. • Oracle ACS performs installation. • OBIEE, TimesTen, Essbase, but not all of EPM. (Like Planning)
Lessons Learned – Configuration Part 1 • Exalytics comes with HugePages memory set really high to support TimesTen Oracle In-Memory Database. • Essbase/EPM does not use and it can potentially degrade performance.
Lessons Learned – Configuration Part 2 • Serious Error~(1013000)~Unable to Create Request Server Thread • JVM hs_err_pid.log • Check pid_max for linux OS. • cat /proc/sys/kernel/pid_max • Output: kernel.pid_max = 32768 • Change two ways. • sysctl -w kernel.pid_max=4194303 • echo 4194303 > /proc/sys/kernel/pid_max
Lessons Learned – Configuration Part 3 • 500 Gigabyte linux tmpfs for TimesTen. • This in combination with Hugepages greatly reduces available memory. • Load balancing requires a shared drives for LCM and RM1 directories. • Oracle Clustered file systems supported. • SAMBA/NFS not supported. • If using a ZFS, need technical expertise configuring Multipath. • Oracle VM – Guest Size Limitation (Patch Soon).
Lessons Learned – Performance!!! • Benchmark Hardware • IBM Power 595 (9119-FHA) • 32 Core 64-Bit Power6 Processers running @ 5.0 GHZ. • 4 cores dedicated to Virtual IO. • Split into 4 separate LPARS • 96 Gigabytes RAM • 2 Terabytes EMC Storage • AIX 6.1
Our Benchmark Process • ASO Reporting. • 9 Dimensions. • 4 Attributes. • 35K members • BSO Calcs. • 11K Block Size. • 8 Dimensions. • 5 Sparse.
Lessons Learned – BSO Performance. • Saw over 2-3X improvement across the board. • Serial calculations represented roughly 200,000 lines of calculation script code. • Export = 2 Gigabytes @ level 0.
Lessons Learned – BSO Performance • Optimizing data cache and index caches improved performance. • As today, must empirically optimize cache settings on a per application basis. • Performance could get worse if cache is set too high.
Lessons Learned – ASO Performance • Buffer loads saw a 2X increase. Already loading in memory. • Commits and aggs performed 3-4X better • Setting the Pending Cache Size Limit improved performance. Set too high and performance went down.
Lessons Learned – Query Performance. • MDX Query times had the biggest variability improvement – from 2-8x better • 95% of our queries are fast, so we looked for large dynamic Hierarchies with lots of formulas for test. • 2-4x performance improvement report batch bursting in workspace.
Lessons Learned – Storage Performance. • Local storage cannot handle multiple simultaneous batch processes (non-flash). • WellPoint originally looked at volatile RAMDISK before san was supported. • ZFS performance equaled RAMDISK. • Running 4 calc dims at once with 200,000 lines of calc script code. (Screenshot to follow).
Lessons Learned – ZFS Performance. 3000 IOPS
Lessons Learned – Batch Performance. • Midday Direct Admin and Headcount Batch Job to create two ASO databases. 5X Faster. • Midday P&L, Balance Sheet, Cash Flow, statistical metrics. Many bso calculations exported into an aso reporting cube. 2X Faster. • Nightly Batch process for all our “GAAP” databases. Cost Allocations, Direct Admin, Headcount, Enrollment, Quality Improvement, MLR Rebates, MLR Segments, P&L. 2.5X Faster.
Lessons Learned – Virtualization Performance. • Performance hit over our x2-3 servers connected to ZFS. • XEN overhead. • Exalytics X3-4 come with 22 93G flash drives. • LVM (Logical Volume Manager) • Oracle ACS coming by end of year to tune linux and zfs. • Contact me if you curious about the Oracle’s hypervisor’s impact on performance.
Lessons Learned – Performance Variability. • Can you realize even better gains? • Hardware • CPU – Number of cores, Processor Speed, Cache, etc…. • Hard Disk IO – Stand alone disk, performance RAID, Storage Area Networks (SAN), RAMDISK. • Cube Design – Index, Structure, and Cache optimization. • Script optimization – Calculating the minimum data necessary, utilizing CPU efficiently, etc… • Load Optimization – Buffers, parallel loads, etc…. • Batch process optimization – Take advantage of parallelism.
Takeaways. • Great Performance compared to high-end hardware 2-3x with cost savings. • You can see even better gains if using lessor hardware. • Exalytics has some configurations slanted towards OBIEE that may need to be altered if you focus more on EPM. • You still need to optimize you application caches, outlines, and data loads.
Questions? • Feel free to contact me. • Jonathon.firstname.lastname@example.org