Knowledge Drop

How can I investigate why my instance is so slow?

  • 4 June 2021
  • 2 replies
  • 185 views

Userlevel 1

The first thing you want to do is figure out which dashboards are the slow performers. Our System Activity Pages will give you performance statistics and help with your investigation. 

 

Below are some impactful changes you can make for optimizing Looker query performance:

  • Build explores using many_to_one joins.

  • Take advantage of datagroups. Applying datagroups within Explores and using the persist_with parameter, will allow Looker to integrate closely with your backend data pipeline. This will allow cache usage to be maximized without the risk of analyzing stale data. 

  • Use persistent derived tables (PDTs). Convert Explores with many complex or non performant joins, or dimensions with subqueries or subselects, into PDTs.

  • Learn about Aggregate Awareness logic to find the smallest, most efficient table available in your database to run a query while still maintaining accuracy.

  • Leverage the Explain in SQL Runner tool for benchmarking. EXPLAIN produces an overview of your database's query execution plan for a given SQL query, enabling us to detect query components that can be optimized. Learn more with our How to Optimize SQL with EXPLAIN article here.

  • Keep your dashboards at under 25 tiles. 

You can also find more best practices here. If you are still unclear what is happening, you can go to our support team for help to diagnose the issue.


2 replies

Userlevel 1

Hi Diegof,

 

Thank you for your inquiry! The best place for you to get detailed information on your instance and its performance is through the System Activity Pages. If you are having trouble narrowing it down via this information, open a chat support ticket. Our analysts are trained to help you narrow it down.

Hi there.

Do you know how to answer the following questions in sequence in a very precise way?

1) which queries are triggered most on our instance?
2) where those queries are being triggered from (i.e. what tiles? what dashboards?)
3) how much data those queries are fetching on every run?
4) know how long those queries take?

Thank you

Reply