Optimize for Query Heavy Environments
This configuration has been tested by the Tableau team using Tableau Server Data Engine powered by Hyper technology and is recommended for Tableau Server versions 10.5 and later.
When to use this configuration
There are several factors that affect query performance, including, but not limited to: large extract size, complex calculations, or multiple views in a dashboard. Use this configuration to improve workbook performance if one of the two following conditions apply to your environment:
- Your workbooks use extracts and you are seeing long-running queries. In this context long-running queries means that the query execution time on extracts is taking more than 5 seconds.
- You are seeing resource contention between Data Engine and VizQL Server when File Store is present on the box.
Note: Use performance recording to determine query execution times. To determine resource usage of Tableau use Performance monitor for Windows installations, and sysstat or vmstat tools for Linux installations.
Important: Network bandwidth is an important consideration for this configuration. Once a query result is generated, it is sent from the node with the File Store to the node with VizQL Server. Or when publishing an extract, the extract must be sent over the network to the File Store node. Both of these may take additional time depending on the size of data and network bandwidth available.
When not to use this configuration
If you are not seeing a large number of slow running queries (queries taking more than 5 seconds to complete), are unsure of your workload type, or do not see resource contention between Data Engine and VizQL Server when File Store is present, then this configuration may not be optimal for you. Before implementing this configuration, it is highly recommended to evaluate your CPU usage for VizQL Server and Data Engine installed with the File Store.
Furthermore, you should also consider the overall mix of workbooks in your environment. Tableau team tested this configuration using workbooks that have slow running queries (taking more than 5 seconds to complete) and workbooks with queries that take less than 5 seconds to complete. During that testing, Tableau team found that queries that take less than 5 seconds may get slightly slower. Depending on your workload mix of slow and fast running queries, you should evaluate whether this configuration will result in overall better performance for your environment.
The guiding principle for this configuration is to separate the node with File Store from the node that has VizQL Server installed. This is because of the following reasons:
On Tableau Server, the Data Engine co-located with File Store is used for querying extracts.
VizQL Server consumes noticeable resources when loading views and interactive use from a web browser. If both File Store and VizQL Server are on the same node, there can be contention for resources between VizQL Server and Data Engine, especially if you are running heavy queries (for example, workbooks with complex calculations) on extracts to load the views.
Thus, by separating VizQL Server and File Store processes, the load between querying extracts and loading or interacting with views can be balanced and better managed. This configuration is targeted at making slow queries faster. By slow queries, we mean queries that take more than 5 seconds to complete.
Here is a visual representation of how to configure your Tableau Server installation to optimize the performance of workbooks that use extracts and have long query execution times:
In this example, all computers are assumed to be at least 64-bit, 16 core, 16+GB systems.
General configuration notes
These configuration guidelines apply to all configurations for Tableau Server installations.
To calculate the minimum number of Backgrounder processes to run, divide the computer’s total number of physical cores by 4. To calculate the maximum number, divide the computer's total physical cores by 2.
Run two VizQL Server processes, two Cache Server processes, and two Data Server processes on the nodes that are not running File Store processes.
Special configuration notes
These configuration recommendations are specific to this configuration.
For this configuration, typically 16 cores are recommended for each node, with 16 GB of RAM per core. Adding more cores to the node where File Store is running will improve performance since Data Engine is designed to maximize and leverage available cores on the machine. In tests performed by the Tableau team indicates that the File Store/Data Engine dedicated nodes benefited from adding more cores. Pursuing this configuration will allow for more flexibility when scaling your deployment as you are now able to scale the File Store node independently of other processes.
Run one process of File Store on a separate node that is not running VizQL or Backgrounder processes. Tableau also strongly recommends that you compare the CPU usage for Data Engine to the CPU usage for VizQL Server, and distribute the cores for each of the processes accordingly. The exact ratio can vary depending on your workload type, but a starting point would be to have one File Store/Data Engine node for every 2 VizQL Server nodes.
The default of this configuration is 4 nodes for high availability considerations.
Here are the general benefits you can expect to see from this configuration and the trade-offs you should consider when adopting this configuration:
|More consistent performance in user experience when loading extract-dependent workbooks*||Fast-running queries may potentially get slightly slower|
|Better response times on workbooks with long-running queries*||You may have fewer VizQL Server processes moving to this topology without growing your deployment|
|More scalability options to grow your deployment|
|Reduction in resource consumption on nodes with VizQL Server|
|Reduction in CPU spikes above 95%|
*While under moderate (<80%) CPU usage
In the tests done by the Tableau team, we found that for the same number of users, the more the number of query intensive workbooks (which use extracts) in an environment, the faster the time it took to reach the saturation point. If you want to make these workbooks more performant, or service more users using these workbooks, this configuration will help you achieve these objectives.
This configuration also opens a new scalability option for your installation. You now have the option to scale horizontally or vertically with respect to querying on extracts. Unlike other Tableau processes, the Data Engine leverages as many cores as available on the machine. Given this, you have the flexibility to add more resources to the standalone File Store nodes to reduce query response time and variability on expensive extract queries or add more File Store nodes to get more extract query throughput in your Server.