Understanding and Improving Server Performance Alan Doerhoefer Timothy Hanson Tableau Software Inc. All rights reserved. © 2009 Tableau Software Inc. How To Improve Server Performance Steps to Great Performance 1. 2. 3. 4. 5. 6. 7. 8. 9. Learn the architecture Use appropriate hardware Use distributed server Use server extracts Tune Monitor Troubleshoot Learn the types of views Understand the load test results All rights reserved. © 2009 Tableau Software Inc. Understand Server Architecture Tableau Server Load Balancer Tableau Professional All rights reserved. © 2009 Tableau Software Inc. Application State Data Sources Web Web VizQL App App Processes Repository Application Web Web (WG) App App Processes Use Appropriate Server Hardware Good Server for performance: • Windows Server 2003 or 2008 64 bit • 16 GB RAM • Fast RAID-5 Drives • Gigabit Network Card • 8 Fast CPU cores All rights reserved. © 2009 Tableau Software Inc. Use Appropriate Server Hardware But Tableau Server will work on lower class hardware: • Windows Server 2003 32 bit • 2 GB RAM • Any type of drive • 100 Mb Network Card • 2 slow CPU cores All rights reserved. © 2009 Tableau Software Inc. Use Distributed Server Worker 1 VizQL oror VizQL VizQL or WG WG Application Processes Processes Processes Main Load Balancer Worker N Application State Repository Worker 2 VizQL oror VizQL VizQL or WG WG Application Processes Processes Processes All rights reserved. © 2009 Tableau Software Inc. Use Distributed Server Installation is fast and easy All rights reserved. © 2009 Tableau Software Inc. Use Distributed Server Installation All rights reserved. © 2009 Tableau Software Inc. Use Distributed Server Determining the number of server processes Use 2 X the number of processor cores Example 3 server cluster (2 workers) 8 cores per machine •Use 2 x 8 = 16 App processes on one worker •Use 2 x 8 = 16 VizQL processes on the other worker All rights reserved. © 2009 Tableau Software Inc. Use Server Extracts • New in 5.0 • A database that stores “extracts” of user data locally and efficiently • Performance is improved since data is local and indexed • Data can be filtered and aggregated as part of the extract • Can be set to refresh data sources automatically on a schedule • The following extract database types are supported: – Built-In – Postgres – MySQL – Microsoft SQL Server • Please attend Extracts: Fast Data To Go Thurs. 11 am for more extract details All rights reserved. © 2009 Tableau Software Inc. Tune the OS • Windows 2003 Server (SP1 or Later) TCP Setting Not an issue in Windows 2008 Server Set SynAttackProtect to zero: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\ Parameters] "SynAttackProtect"=dword:00000000 • Disable other processes All rights reserved. © 2009 Tableau Software Inc. Tune the Server Session Timeouts Can be changed in the configuration file Tableau Server\config\tabsvc.yml • WG Session Default is 4 hours To change it to 1 hour add this to tabsvc.yml: wgserver.session.idle_limit: 60 • VizQL Session Default is 2 hours To change it to 30 minutes add this to tabsvc.yml: vizqlserver.session.expiry.timeout: 30 All rights reserved. © 2009 Tableau Software Inc. Monitor Server Server Status Demo All rights reserved. © 2009 Tableau Software Inc. Monitor Server Server Activity Demo All rights reserved. © 2009 Tableau Software Inc. Monitor Server User Activity Demo All rights reserved. © 2009 Tableau Software Inc. Troubleshoot Server Apache logs Contains information about every request to server at the HTTP request level All rights reserved. © 2009 Tableau Software Inc. Troubleshoot Server WG logs and VizQL logs Contains details of each operation performed by the server All rights reserved. © 2009 Tableau Software Inc. Troubleshoot Server tabadmin ziplogs command collects all logs – even on a distributed cluster – into one archive All rights reserved. © 2009 Tableau Software Inc. Understand View Types and Performance Three Types Of Views: 1. Fast - Fast query - Fast rendering 2. Query-bound - Slow query - Fast Rendering - Same as Fast once query is cached 3. CPU-bound - Render-Intensive - Uses the most CPU - Example: text tables with thousands of cells All rights reserved. © 2009 Tableau Software Inc. Understand the Load Test Results Capabilities of: • 1 machine • 3 machines • 5 machines * Note that these tests show results from a specific test configuration and should not be taken as a guarantee of client response times. These benchmark results were returned in a controlled lab environment, without other applications running during execution. Actual results will vary based on load type, hardware, network speed, browser settings, and database performance. All rights reserved. © 2009 Tableau Software Inc. Understand the Load Test Results Initial View Load Times Effect of caching All rights reserved. © 2009 Tableau Software Inc. 5 servers in our load tests served 2,250 named users, or 225 concurrent users Understand the Load Test Results Caching - Pre-load views whenever possible to improve initial load times All rights reserved. © 2009 Tableau Software Inc. Summary Keys to Performance: - Understand view types - Understand and utilize the distributed architecture - Use server extracts - Learn how to monitor and investigate All rights reserved. © 2009 Tableau Software Inc.