Tableau 10.3 has a big feature for server admins: Automatic query caching refresh after extracts which is also called: Pre-compute workbooks viewed recently.
It is a new flag that can be enabled for each site without downtime. My Tableau server uses a lot of extracts, after we enabled this feature, the overall server render time improved 20% for all views which is a huge!
It sounds simple but it actually has a few other important factors to consider in order to make it work. To make it simple, here is what I will recommend other server admins to do step by steps:
- Get the baseline for your server overall render time perf. You can use ‘Performance of Views’ from Tableau admin view
- Enable this flag for the sites that have a lot of extracts. If you do no use site, it will be the ‘Pre-compute workbooks viewed recently’ in default site. Change this flag does not need any system downtime.
- Observe overall server ‘Performance of Views’ improvements after step 2.
- If there is no improvement at all, check what is your ‘server connection caching’ setting which is part of server configurations. If the setting is balanced, what is the minutes you have? This minutes have to be longer enough, otherwise after extract refresh, server pre-compute the cache but this ‘server connection caching’ just dump the cache. I had to change this ‘server connection caching’ to balanced 1440 mins to see the performance improvements. Please note, every change of ‘server connection caching’ setting needs server re-starting.
- You are not done yet. Unfortunately this ‘server connection caching’ impacts ALL server connections, includes live connections as well. When you change it to 12 or 24 hours, the million dollar question is if this is Ok for server live connections? 24 hours means that live connections will hold cache for 24 hrs as well, which maybe Ok if the source data does not change often, and may not be Ok at all for some other live connection use cases.
- Can you let ‘server connection caching’ setting by-pass all live connections but only apply to extracts? Unfortunately there is no such server setting options. Here are two workarounds:
- Workaround1 : Embed all live connection views to portal, or another Tableau workbook view with refresh=yes, which means by-pass all cache. It is a little bit work for each workbook but it works. If you have a lot of live connection workbooks that data changes often, you may have to go with workaround2.
- Workaround2 : Find a way to dump live connection workbook caches independent to server’s out of box ‘server connection caching’ setting. There is no simple way, likely you will need to work with Tableau Professional Service – they have a solution to change all live connection workbook’s last update timestamp, which will dump the cache. How it works? Whenever you publish a new version of workbook, server has to dump its cache. Behind scene, server uses workbook updated at timestamp to decide if cache should be dumped or not – an additional logic in additional to ‘server connection caching’ setting. If you can use Python to change the workbook timestamp hourly, then the workbook cache gets dumped hourly. This is not Tableau supported option since it needs to update Postgre DB directly but it works with relative low risk.
- Observe server CPU and memory impacts due to above changes. If high CPU or memory, increase the following threshold. If no CPU or memory impact, decrease the threshold to warm-up more workbooks:
- Config workbook caching threshold: tabadmin set backgrounder.externalquerycachewarmup.view_threshold 2 (# of views of the workbook last 7 days / # of refresh in next 7 days)
- Of course, if server CPU or memory is a lot of higher above enabled this feature, you may have to turn this feature off.
Read more @ Tableau 10.3 and 10.4 new feature demo for admins and download the attached workbook to decide if you should overwrite Tableau server’s default setting.