Reaching out to other Looker customers and developers! Any of you out there using Redshift, and experiencing slow performance with first-run queries?
We have several large dashboards with up to 20 looks each, generating fairly hefty SQL queries, and using filters to customize the readout for various contexts.
We’ve noticed these dashboards, when first loaded, can take at worst 60 seconds or more to load the first time. On subsequent loads, the looks render in only a few seconds. The high query times were found on the AWS Redshift console so we don’t think it’s an issue on the Looker side.
tl;dr - Our questions for all of you
- Has anyone else out there experienced problems with Redshift first-run query performance?
- Has anyone else tried (in response to the problem):
- Resizing their Redshift cluster
- Pre-loading dashboards to warm the cache
- Having Looker do the caching (i.e. data groups)
… something else? (including lateral workarounds - we’re interested in any creative approach people have taken.)
As far as we can tell it looks like we have slow query compile times. See AWS document on the subject (see under “Code Compilation”).
According to various posts and articles, it seems that:
- The Redshift compilation cache has a finite size.
- We developers have no control over when cache items are invalidated.
- Amazon doesn’t even want to give us more details about it - the technology is too quickly changing, apparently - they’d like us to consider it black box.
- There could be a performance hit even when querying empty tables.
Why do we think it’s a compilation issue?
We ran various tests to diagnose the issue. We tried, for example, finding large queries, and then altering small parts of them and running them again.
Our results (details available if you’re interested) do seem to indicate that query compilation is the culprit.
Resizing cluster had no effect
Acting on advice, we resized our cluster from one to two nodes, but this did nothing to improve query performance. We were not surprised to see that, because we know that compilation only occurs on the leader node of a cluster.
Our Redshift specs
- Cluster size: one node at the beginning of this - we now have two nodes (no effect on query performance.)
- Node type:
What we have already tried
See above. No effect resizing from 1 to 2 nodes.
Install the Redshift Block on Looker
On Looker’s advice we have installed the Redshift Block - it is helpful, but we’ve found no insight for this problem.
Use Looker Data Groups
We’re looking at possibly using Looker data groups to have Looker cache as much as possible on its end. This would effectively be a workaround.
Has anyone else used this as a workaround? Any success?
Schedule daily loads of the dashbards to “warm the cache”
This probably won’t work for us:
- It’s labor-intensive.
- Not scalable.
- Doesn’t work well for dashboards with filters (which all of ours have).