Optimize Looker Performance with System Activity Anti-Pattern Detection Dashboards
The challenge of Looker admins
Imagine this scenario: as a diligent Looker Admin, you notice a gradual degradation in Looker’s performance. Concerned about the impact on user experience, you delve into the Looker System Activity Explores in search of insights that could shed light on the root cause of the problem.
Looker’s System Activity Dashboards: Valuable, But Limited
Looker’s built-in System Activity dashboards offer valuable insights into instance usage and performance. However, the data presented may not give a comprehensive picture of Looker Performance or lack granularity visibility to pinpoint the exact issue plaguing the system.
So if you are in this case, this blog is for you!
Joon Solution’s action
Due to the facts above, we’ve helped clients implement Looker dashboards that function as anti-pattern detectors. These dashboards proactively monitor performance and alert administrators to potential issues, allowing swift intervention.
In the quest to conquer the performance challenges plaguing one of our esteemed clients, we embarked on a multi-faceted solution that combines innovation with strategic implementation.
- Extract data to a self-hosted data warehouse: Our first step was to extract the data from the Looker database and migrate it to a self-hosted data warehouse. This shift laid the groundwork for performance tracking and provided greater control such as keeping all historical data.
- Replicate Looker’s System activity explores & Enrich with customized fields: Leveraging the rich data stored in the data warehouse, we replicated the Looker’s System activity explores, infusing them with additional views and metrics tailored to the client’s needs.
- Build anti-pattern detection dashboards: Lastly, we built a monitoring tool that can also help Administrators enforce an automated process of preventing anti-patterns effectively
After successfully implementing the solution, we addressed the immediate performance issues, laid a foundation for sustained optimization, and enhanced both cost and user experience.
The anti-pattern detection dashboards you need
Four elements are attributed to Looker’s performance: Database Load, Instance Load, Browser Load, and Network Latency. However, I’ll delve into the anti-patterns that impact Database Load and Instance Load and haven’t been built into Looker System Activity dashboards.
Database Load: It takes time for a database to process a SQL query, especially if a query is large or if the database is processing several queries at once.
- Anti-patterns: If an Explore, a Look, or a dashboard takes a long time to return results, it can boil down to the complexity of queries, several queries running simultaneously, or unnecessary queries.
- Guardrails: Detect and prevent unnecessary queries — Overly frequent scheduled plan, Not maximized caching, Unused fields (enriched report); Enable run-on-load dashboard (built-in Looker report)
Instance Load: Your Looker instance serves visualizations and pages for everyone who uses the instance. The level of usage at any given time can potentially strain instance resources.
- Anti-patterns: custom fields, concurrent queries in a dashboard
- Guardrails: Detect and convert custom fields to the LookML layer (enriched report), Limit the number of elements within an individual dashboard to under 25 tiles (built-in Looker report)
1. Detect overly frequent scheduled plans
Each scheduled plan triggers database queries to fetch the latest data. While a single query might seem harmless, imagine dozens or even hundreds of scheduled plans firing off at once. This sudden surge creates a long queue for the database. When a scheduled plan refreshes more frequently than its source data updates, it becomes redundant. Every unnecessary refresh adds another query to the already overloaded queue, further increasing wait times for all queries — scheduled and user-initiated. Therefore, overly frequent scheduled plans fall into the classic “anti-pattern” category.
Data preparation:
Looker provides valuable out-of-the-box data on the scheduled frequency of Looker Schedule Plans.
However, this data alone might not provide the whole picture. To gain deeper insights, we can leverage ETL metadata. This metadata typically includes information about how often the underlying data sources (e.g., tables in BigQuery) are updated.
Anti-Pattern Detection Report:
Combining Looker schedule plan data with ETL metadata, we can compare the following:
- Scheduled Plans’ Frequency: How often Looker refreshes dashboards and Looks based on their schedule plans.
- Source Data Update Frequency: How often the actual data sources are updated based on ETL processes.
With this report, Looker Admin can gain insights:
- Which Dashboards/ Looks have scheduled plans updated more frequently than needed
- How to adjust the scheduled plans’ frequency
2. Identify caching policies that aren’t optimized
Looker reduces the load on your database and improves performance by using cached results of prior SQL queries by your caching policy. When the caching policy is maximized to the freshness of the source data, Looker is optimized for efficiency.
Data preparation:
Using the Looker API Method “Get All Datagroups“, we extract the datagroups policy in each LookML model & surface it to Looker System Activity Explores. Next, we source ETL metadata, such as the update frequency of underlying data sources.
Anti-Pattern Detection Report:
Merging the data, we build a monitoring report that compares the caching policies with refreshing schedules of the underlying data. It supports Looker Admin to easily identify the gaps and then properly maximize the caching policy.
3. Flag the most used custom fields to convert into LookML
The post-query processing such as merge results and custom fields (dimension, measure, table calculation) can serve as proof of concepts. However, excessive calculations can compete for Java memory on the Looker instance, causing the Looker instance to respond more slowly.
Therefore, if you use the same table computations, merged results, or custom fields in many Looks and dashboards, it’s recommended to hard-code them into your LookML model wherever possible.
Data preparation:
To achieve the goal, we build a derived table to parse query custom_field into tabular forms containing the following attributes:
- Type of custom fields: either custom measure, custom dimension or table calculation
- Calculation logic used in the custom fields
- Name, label, and description of the custom fields
Anti-Pattern Detection Report:
With this report, Looker Admin can take valuable actions:
- Detect the most used custom fields, ranked by the number of query runs and which dashboards or looks use those custom fields
- Decide whether to convert custom fields to LookML measures.
4. Find the least used and unused fields in the explores
When an explore is refreshed in Looker, it retrieves data for all fields defined in the explore, regardless of whether they are used in the final visualization or analysis. It results in unnecessary data retrieval, leading to query processing time increase and potentially slower performance.
Data preparation:
Extract all LookML fields from the new API endpoint.
Anti-Pattern Detection Report:
Similar to the usage of custom fields report, Looker Admin can request developers clean up unused fields.
Tired of Looker Slowness? Let’s chat!
