Improve query performance using Enhanced Query Insights for AlloyDB

This page describes how to use enhanced Query Insights to help you detect and analyze query performance problems in near real time for both primary and read pool instances. For an overview of enhanced Query Insights, see Enhanced Query Insights overview.

To learn how to use standard Query Insights, see Improve query performance by using Query Insights.

You can use Gemini in Databases assistance to help you monitor and troubleshoot your AlloyDB resources. For more information, see Monitor and troubleshoot with Gemini assistance.

Before you begin

To access the Query Insights dashboard, enable access to AlloyDB for PostgreSQL in your Google Cloud project.

Required roles

To use enhanced Query Insights, you need permissions to do the following:

To access enhanced Query Insights, you need permissions to access the AlloyDB Query Insights dashboard.
To edit enhanced Query Insights settings, you need permissions to update AlloyDB instances.

To get these permissions, ask your administrator to grant you one of the following roles:

Basic viewer (roles/viewer)
Database Insights viewer (roles/databaseinsights.viewer)

For more information about granting roles, see Manage access.

Enable enhanced Query Insights

Set up Gemini in Databases.
In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Click Edit Settings.
Enable Enhanced Query Insights.
If you secure your Google Cloud project with VPC Service Controls, add the databaseinsights.googleapis.com API to the service perimeter as a restricted service.
Save your changes. This restarts your instance.

After enhanced Query Insights is enabled, you can edit your enhanced Query Insights settings.

Edit enhanced Query Insights settings

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Click Edit Settings and then select or de-select any of the following settings:
- Enable wait event analysis. Wait event analysis can help you to identify and resolve performance issues in your database. A wait event is a component that causes a delay in processing a query related operation.
- Store query comments. Learn about query context by seeing the comments associated with any query you investigate, as part of query length. No additional memory needed. If your comments contain sensitive information, be mindful of user access.
- Store application tags. Enable to learn which APIs and model-view-controller routes are making requests, and group that data to run metrics against it.
- Customize query lengths. You can change the default limit on query lengths, from 256 bytes up to 100 KB. Higher query lengths require more memory. Changing query length requires instance restart.
- Enable query plans and configure sampling rate. Query plans visualize the operations used to complete a sample of a query. The sampling rate determines how many query samples can be captured per minute across your databases. Higher rates require more memory.
- Enable recommendations. Surface index creation recommendations on a cluster's Query Insights page.
Save your changes.

Detect and diagnose query performance problems

You can use enhanced Query Insights to detect and diagnose performance problems. The enhanced Query Insights dashboard displays the database load, which is a measure of the work (in CPU seconds) that executed queries in your selected database perform over time. Each running query is either using or waiting for resources. The database load is the ratio of the amount of time taken by all the queries that are completed in a given time window, in elapsed real time.

View the measure of work done by executed queries

The database load graph gives the measure of work done (in CPU seconds) by all executed queries in your database, sliced by the selected dimension. The database load is divided into the distribution data that you select from the chart drop-down.

You can break down the database load by any of the following dimensions:

Query
Wait event type
Wait event
Database
User

To view work completed by executed queries in your database, follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Choose the total execution time for which you want to display data, for example, 1 or 6 hours, or a custom time.
In the Database Load by execution time drop-down, choose the dimension you want to investigate. Depending on the dimension you choose, the graph displays how the chosen dimension contributes to the overall database load. For example, if you select the Wait events dimension from the database load chart drop-down, the breakdown of all wait events that occurred during the selected time range for the selected database and user is displayed in the database load chart.

The database load chart displays data for all nine wait event types and the wait events supported in PostgreSQL. Depending on the number of wait events and types, the top ten contributing wait events and types are displayed. If there are more than ten wait events, the top nine wait events are displayed, and the rest are grouped into the Others category. This approach also applies to other dimensions like Queries, Databases, and Users.

View the top contributors to database load

Using the Top dimensions by database load table, you can view top contributors to the database load for the chosen time range and dimension selected in the Database Load chart. Depending on the dimension you choose in the Top dimensions by database load table, you can find the top values for the selected time window.

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Choose the total execution time for which you want to display data, for example, 1 or 6 hours, or a custom time.

In the Top dimensions by database load table, you can click any of the following:

Dimension	Description
Queries	The table displays the top normalized queries by total execution time. For each query, the data shown in columns are listed as follows: Avg Exec Time (ms). Average time for the query to execute. Total execution time (ms). The total execution time taken by the specific query. Rows Returned. The average number of rows fetched for the query. Times Called. The number of times the query was called by the application. %load by chart dimension. The pill chart depicts how the chart's dimension is distributed across for the specific query.
Wait event types	The table displays the list of top wait event types that occurred during the selected time range. Avg time spent in wait (ms). Average time the queries spent in the specific wait event type. Total time spent in wait (ms). The total execution time the queries spent in the specific wait event type. Wait event type count. The number of times a specific wait event type occurred in the chosen time range. %load by chart dimension. The pill chart depicts how the chart's dimension selected in the database load chart drop-down is distributed across the specific wait event type.
Wait events	The table displays the list of top wait events that occurred during the selected time range. Avg time spent in wait (ms). Average time the queries spent in the specific wait event. Total time spent in wait (ms). The total execution time the queries spent in the specific wait event. Wait event count. The number of times a specific wait event occurred in the chosen time range. %load by chart dimension. The pill chart depicts how the chart's dimension selected in the database load chart drop-down is distributed across the specific wait event.
Database	The table shows the list of top databases that contributed to the load during the chosen time range across all executed queries. Avg time spent in database (ms). Average time the queries spent in the specific database. Total time spent in database (ms). The total execution time the queries spent in the specific database. %load by chart dimension. The pill chart depicts how the chart's dimension selected in the database load chart drop-down is distributed across the specific database.
User	The table shows the list of top users for the chosen time range across all executed queries. Avg time spent in user (ms). Average time the queries spent on the specific user. Total time spent in user (ms). The total execution time the queries spent in the specific user. %load by chart dimension. The pill chart depicts how the chart's dimension selected in the database load chart drop-down is distributed across the specific user.

View query execution time

Enhanced Query Insights displays the top ten queries contributing to database load in the Top dimensions by database load table. The top query is the one that contributed the most database load for the selected time range. The subsequent queries are a percentage of the longest execution time.

To learn how the execution time of a parameter in the Top dimensions by database load is affected by different values of the selected chart dimension (queries, wait event types, wait events, databases, and users), follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
View % load by chart dimension in the Top dimensions by database load table to learn how a parameter in the table is impacted by parameters in the Database Load chart.

For example, when you select All Queries in the Database Load chart and you click the Queries tab in the Top dimensions by database load table, % load by Queries displays a list of the queries that contributed the most database load in the selected database during the given time period.

View details about query execution time

You can view details about query execution time in More details, including the dimensions that you selected in the database load chart and table, the definition of each wait event, and data that is encapsulated in the Others category.

More details also displays details of complex information that isn't shown elsewhere in the query details screen. This information includes the definition of each wait event.

To view details about query execution time, follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
In the Top dimensions by database load table, click More details for a query.

Enhanced Query Insights displays normalized queries ($1, $2, and so on) to replace the literal constant values. For example:

UPDATE
  "demo_customer"
SET
  "customer_id" = $1::uuid,
  "name" = $2,
  "address" = $3,
  "rating" = $4,
  "balance" = $5,
  "current_city" = $6,
  "current_location" = $7
WHERE
  "demo_customer"."id" = $8

The constant value is ignored so that enhanced Query Insights can aggregate similar queries and remove any Personally Identifiable Information (PII) information that the constant might show.

Determine database load for normalized queries

To display a measure of the time and resource usage by query execution (in CPU seconds) that your selected normalized query performed in the selected database over time, follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Click Queries in the Database Load chart. To calculate database load, the Database Load chart uses the amount of time taken by the normalized queries that are completed in the minute boundary to the elapsed real time.

At the top of the Database Load chart, the first 10KB characters of normalized query are displayed, where literals are removed for aggregation and personally identifiable information (PII) reasons.

To help determine the distribution of time spent in each of these states, enhanced Query Insights distributes per normalized query loads into wait events and wait event types.

You can study query latency using the Latency charts. Latency is the time taken for the normalized query to complete in elapsed real time. Latency of parallel queries is measured in elapsed real time even though database load can be higher for the query due to multiple cores being used to run part of the query.

You can filter by percentile to view the 50th, 95th or 99th percentile to detect queries that don't adhere to the expected execution time. To analyze historical latency for the normalized query, change the time window.

Analyze normalized queries

Query plans help you understand and analyze normalized queries by providing a breakdown of the different operations in a sample of your query.

The sample query plan provides an EXPLAIN ANALYZE view for the query plan samples that are related to the normalized query. These are executed query plans that provide a breakdown of the active time taken by each operation in the query plan.

To view a sample query plan, follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Choose the total execution time for which you want to display data, for example, 1 or 6 hours, or a custom time.
In the Top dimensions by database load table, click a query.
In Query plan samples, click any of the dots on the chart to view a query plan for that query.

Monitor and troubleshoot application performance using tags

Enhanced Query Insights provides application-centric monitoring and simplifies performance diagnosis of applications built with object relational mapping (ORM). Query tagging helps you find issues at higher-level constructs, such as using the business logic, a microservice, or some other construct. You can use tags in your application to help you do the following:

Monitor database performance.
Use Sqlcommenter to identify performance issues caused by application code.
Analyze database load.
View the per-tag load.

To learn how to use tags in Query Insights, see Improve query performance by using Query Insights.

Troubleshoot applications at the database layer

Enhanced Query Insights provides an in-context, end-to-end trace view to help you understand issues with the application at the database layer for a specific request. In-context end-to-end application traces help you determine the source of the problematic query, for example, by model, view, controllers and route.

When you enable OpenTelemetry, span information is sent to the database along with the tag information inside SQL comments. Traces from the application to Cloud Logging are linked with database query plan traces to identify the source of the problem.

To view an in-context trace, follow these steps:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
From the list of clusters and instances, click an instance.
Click Query Insights.
Choose the database and user.
Click any entry in the Top dimensions by database load table.
In the Query Insights details page, click END to END.

You can also use Cloud Trace to see end-to-end tracing for each step in the query plan. To access Cloud Trace, select Operations > Trace.

Frequently Asked Questions

This section provides answers to frequently asked questions about enhanced Query Insights.

Why is the query string truncated?

By default, enhanced Query Insights displays 10 KB in query strings. You can increase the display to 100KB.

Why is my CPU load above the max core line in the Database Load chart?

CPU load accounts for both run time and time waiting for the Linux scheduler to schedule the server process running, so as a result the CPU load can go beyond the max core line.

Can I use standard Query Insights metrics?

Yes. Standard Query Insights generates counters that can be accessed through stackdriver. While enhanced Query Insights is in Preview, these metrics are available using Cloud Monitoring API with enhanced Query Insights. For more information, see Google Cloud metrics.

What is the max query string length?

For the standard Query Insights plugin, the max query string length is limited to 4.5 KB characters. For enhanced Query Insights, the default string length is 10KB and the maximum length is limited to 100KB.