Isolating Tableau Server Performance Issues

In this post, I’ll be describing a set of steps to follow to isolate the causes of performance issues on Tableau Server.

Here are the basic steps:

  1. Test the workbook in Tableau Desktop. Does it perform well? If yes:
  2. Test the workbook in Tableau Desktop on the Tableau Server machine. Does it perform the same as it did on the previous machine? If yes:
  3. Publish the workbook to Tableau Server, and find a time when there is low-to-no usage on the Tableau Server. Go to the published workbook. Did it perform relatively the same as the test in Step 2 (within 1-3 seconds)?  If yes:
  4. Test the workbook during a time of high usage on the Tableau Server (either natural or do load testing using TabJolt).

If it is slow in Tableau Desktop, it will be slow in Tableau Server

The main principle we use to isolate the issue is “If it is slow in Tableau Desktop, it will be slow in Tableau Server“. You might alternatively think of this in terms of “The maximum load speed of a given viz on Server is less than or equal to the load speed of the same workbook in Tableau Desktop”.

This means that the first testing stage is always in Tableau Desktop.

Isolating to Desktop

If you are using a workbook that has Published Data Sources, your queries will still be going through Tableau Server. To get all the processing moved to Desktop, follow these steps:

  1. Download the workbook file from Tableau Server to the local machine.
  2. Open the TWB or TWBX file in Tableau Desktop
  3. If there is a Published Data Source (the Tableau icon is next to the data source rather than a database cylinder icon), follow this KB article to make it a local data source.
  4. You will need the database credentials to you can log into the database once it the source is local. These should be the same credentials used on Tableau Server. Some databases have resource limits in place for certain users, so at this point we want to be testing with the exact same credentials.

Testing the Performance

Run a Performance Recording in Desktop. The best instructions are from the Tableau Online Help.  If you already have the workbook open, you can go up to the Data menu at the top, find the data source name and then choose Refresh. Once it has reloaded, click around or change filters (whatever actions have been slow on the Server). Now end the performance recording.

The Performance Recording will be a new workbook which will show which steps are taking the most time.

“Executing Query”

A query typically takes long time for one of these reasons (it can be a combination though):

  1. The query itself is complex and the database is not optimized to answer it in the form Tableau is sending
  2. The query isn’t difficult to compute, but returns an extremely large amount of data
  3. The query is neither complex nor results in much data, but still takes a long time to return.

For #1: Send the query to your DBA and have them run an EXPLAIN to understand why the query might be taking a long time to run. They should be able to make changes or recommendations that will optimize the query at the database level. If they have reached a state where they feel like things are as optimized as possible, but Tableau is still doing some things that will necessarily take a long time, consider altering the worksheet where the inefficient query is running to see if Tableau will write a simpler query.

To investigate #2: Run the Tableau query in your direct querying tool. How many results come back (you want a full row count, usually displayed somewhere). Most SQL querying tools limit the actual number of results they return back. If the limited amount returns quickly, then that indicates the dashboards and sheets need to be optimized to return less data until a user has filtered down. Good examples of these processes are in the Best Practices For Designing Efficient Workbooks whitepaper or Interwork’s Performance Checklist

To determine #3:

  1. Have your DBA run the exact same query from Tableau directly on the database (or as close to directly as the can).
  2. Run the query from the same machine as Tableau Desktop through a SQL querying tool. This tool should be using the same driver as Tableau Desktop (it should be using the ODBC driver installed on the desktop machine). If you cannot get an ODBC querying tool, a JDBC based tool can be used in a pinch.

If the results are the relatively the same from the direct query on the database and using the SQL querying tool, but much slower in Tableau Desktop, open up a Support case and provide all of the testing steps, the results you are seeing, and any logs from Tableau Desktop so that the issue can be investigated.

If the results are slow in the SQL querying tool and Tableau Desktop, ask your DBA and networking team to investigate what might be causing the delay.

“Computing Layout”, “Sorting Data” or “Computing Table Calculations”

If the sheer amount of results coming back from the queries is very large, rendering views and table calculations will take more effort, so improvements to the query to limit total data will improve performance in this stage as well.

Look at the Best Practices For Designing Efficient Workbooks whitepaper or Interwork’s Performance Checklist for other aspects of the design that can be improved

Moving to the Tableau Server machine

Once you have achieved acceptable performance in Tableau Desktop, it’s time to test again in Desktop, but on the Tableau Server machine. This step helps determine if there is something amiss in the actual configuration of the Tableau Server machine.

Copy the workbook file over to the Tableau Server machine and install the exact same version of Tableau Desktop that you tested on previously. Open up the workbook, and run a Performance Recording just like you did earlier.

If your querying is running significantly different than previously, then you have the #3 situation from before. Take the same steps to isolate if the issue is specific to Tableau or just to that machine. If it is Tableau specific, open a Support case. If machine specific, have your local IT team investigate what is causing the difference on that machine.

Publishing to Tableau Server

If everything has performed well so far, it’s time to see why things are different in the Tableau Server software. You will need to publish the workbook in two variations:

  1. The version with the “embedded” data source you have been testing so far
  2. A version where you have Published the Data Source, and connected the workbook to that Data Source

Test the “embedded data source” version first. Tableau Server has Performance Recorder as well, and the Server Guide describes exactly how to use it.

It’s important to test at a time of low server load. You are basically looking for any differences from Desktop to a single load of the workbook in Server. If the performance is relatively the same as it was in Desktop, then the issue most likely occurs during heavy load.

If the workbook immediately performs poorly, then it’s time to look at the performance recorder to see what is taking the time. If everything seems to be taking longer, or the queries are the same length but everything else is taking longer, then there may be an issue with the Tableau Server software.

A way to confirm that there are issues with Tableau Server overall would be to publish a workbook that uses an embedded extract from a CSV or Excel file (so, a TWBX file that does not use a Published Data Source). If this workbook performs poorly as well, with no other load on the, then there is something wrong with the Tableau Server software or its interaction with the underlying machine directly.

One hidden place to look for performance issues is anti-virus actively scanning on the machine. Once that has been ruled out, you may want to look at Windows Performance Monitor to see what is happening. You can also use TabMon to bring all of the various aspects of the Tableau Server software in view at once.

Testing Tableau Server Under Load

If the workbook only seems to be performing poorly under load, it is time to look at a performance recording while the server is busy. You can also investigate this from the other side, by having your DBA look at the number of incoming queries at a time.

If you need to simulate a lot of load (on a testing database), then you should use TabJolt .

Tableau sends queries in parallel to a database. If your DBA says that the number of simultaneous queries is too much for the database to handle, you can place limits on the number of connections per process using this KB article. If query times are not the issue, then it is time to look at the TabMon results to see what part of the system is getting overloaded.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s