Skip to content

Query Insights

Find and optimize long-running queries in your application.

PlanetScale Insights, currently in beta, gives you a detailed look into the active queries running against your database. This in-dashboard tool allows you to identify queries that are running too often, too long, returning too much data, and more. You can scroll through the performance graph to detect the time that a query was impacted and, if applicable, the Deploy Request that affected it.

You can also see a list of all queries performed on your database in the last 24 hours. For further analysis, you can sort these by metrics like amount of rows read, time per query, and more.

This video shows an example Insights page. All explanations are covered in this doc.

Note

On the free Hobby plan, query insights are limited to the previous 24 hours. To unlock the full seven days of analytics, upgrade to a paid plan.

Insights page overview

To view Insights for your database, head to the PlanetScale dashboard, select your database, and click the "Insights" tab.

Below is an overview of what you'll find on this page. If you'd like to see a practical example of how to use Insights to debug a performance issue, check out our Announcing Insights blog post.

Branch selection

PlanetScale Insights branch selectionPlanetScale Insights branch selection

The dropdown on the top right lets you select which branch you want to analyze. The selection applies to the entire Insights page.

Insights graph

PlanetScale Insights graphPlanetScale Insights graph

This graph depicts your database activity over a 24-hour period. You'll also see links to any deploy requests at the time that you deployed them.

You can click the dates listed above the graph to scroll through the past seven days. If you're on our free Hobby plan, activity is limited to the previous 24 hours.

Graph metrics selection

PlanetScale Insights graph metrics selection - rows writtenPlanetScale Insights graph metrics selection - rows written

The dropdown on the top left lets you select what metric you want to see on the graph. You can hover over a specific time on the graph to see the metric(s) for that time period.

The available options are:

  • Rows read per second — Total count followed by rows read per second.
  • Rows written per second — Total count followed by rows written per second.
  • Query latency (ms) — Two line charts showing p50 and p90 latency. This means 50% and 90% of requests, respectively, completed faster than the time listed.
  • Queries per second — Total queries per second.

Queries list

PlanetScale Insights recent queries listPlanetScale Insights recent queries list

The table underneath the graph shows all queries performed on your database in the past 24 hours. To further narrow down query analysis, you can select a time range from the above graph to restrict the table to queries that happened in that time frame.

You can also sort the columns for quick analysis.

Insights queries selected time rangeInsights queries selected time range

You may see some placeholder values in the queries, such as :v1. This is because we consider the actual data private and normalize it away.

Available query statistics:

  • Count — The number of times this query has run.
  • Total time (ms) — The total time the query has run in milliseconds.
  • Time per query (ms) — The number of milliseconds each individual query takes to run. This is calculated from total time divided by count.
  • Table — The table(s) being queried or modified.
  • Rows returned — The total number of rows fetched by a SELECT statement. This includes all times the query has run in the displayed time frame.
  • Rows read — The total number of rows read. This includes all times the query has run in the displayed time frame. This is the number that directly affects your rows read billing calculation.
  • Rows affected — The total number of rows modified by an INSERT, UPDATE, or DELETE statement. This includes all times the query has run in the displayed time frame.

You can customize the metrics that show up on the Queries list by clicking the "Columns" dropdown.

Query deep dive

Clicking on a query in the Queries list will open a new page with more information about that query, such as:

  • The query pattern — The query with data normalized away. This query may run several times with different values, which Insights combines into a single query pattern.
  • Selected instances of the query — Below the query pattern, we surface any instances of the query that took longer than one second, read more than 10,000 rows, or produced an error.
  • Error messages — If any exist.
  • Query tags — If any of the selected queries have SQL comment tags attached, you'll see the key-value pairs in the table. Note, if you're sending queries with comments using the MySQL shell, make sure you have enabled comments with the -c flag.
  • Query EXPLAIN plan — If you toggle "Show explain plan", you can generate the execution plan for the selected query. You may have to fill in some sample values designated with placeholders like :v1. We use placeholders in the patterns both so you can look at whole patterns at once and so the literal values remain private.

If you'd like to further interact with the query, click "Open query in web console", and you'll be taken to your in-dashboard web console, where you can run the EXPLAIN plan.

Note, if you're viewing the EXPLAIN plan on a production branch, this button will be disabled unless you enable production web console access in your database Settings page.

PlanetScale Insights query pattern w/ explain planPlanetScale Insights query pattern w/ explain plan PlanetScale Insights explain planPlanetScale Insights explain plan

Need help?

Get help from the PlanetScale support team, or join our GitHub Discussion board to see how others are using PlanetScale.

Was this page useful?
Last updated on August 4, 2022
Help us improve this page
PrivacyTerms© 2022 PlanetScale Inc.