Overview of Query Insights

This page gives an overview of the Insights tool. To start using the tool now, see Troubleshooting query performance problems using Query Insights.

Introduction

Query Insights helps you detect, diagnose, and prevent query performance problems for Cloud SQL databases. It provides self-service, intuitive monitoring, and diagnostic information that goes beyond detection to help you to identify the root cause of performance problems.

With Query Insights, you can monitor performance at an application level and trace the source of a problematic query across the application stack by model, view, controller, route, user, and host. The Query Insights tool can integrate with your existing application monitoring (APM) tools and Google Cloud services using open standards and APIs. This way, you can monitor and troubleshoot query problems using your favorite tool.

You view metrics for queries on the Query Insights dashboard. The dashboard provides a series of filters that allows you to view database load for queries by user, database, IP address, time range, CPU capacity, CPU and CPU wait, IO Wait, and Lock Wait.

Benefits of using Query Insights

Query Insights provides monitoring and diagnostics that let you detect and fix query performance problems. Query Insights dashboards help you identify query performance problems early and let you move from detection to resolution using a single interface. Built-in, visual query plans assist you in troubleshooting issues to find the root cause. You can also use in-context, end-to-end application tracing to find the source of a problematic query.

Query Insights enables you to tag queries with business logic that the query is associated with, such as payment, inventory, business analytics, or shipping so that you can monitor query performance from an application-centric perspective. Also, Query Insights provides application-centric monitoring that helps you diagnose performance problems for applications built using object-relational mappings (ORMs). The Query Insights tool uses SQL Commenter, an open source library, to automatically instrument your ORMs. This instrumentation helps you identify which application code is causing problems.

Query Insights can integrate with your existing APM tools, letting you monitor and troubleshoot query problems using your favorite tool. Query Insights supports the OpenCensus and OpenTelemetry standards and makes the query metrics and traces data available for your APM tools through the Google Cloud's operations suite API. Query Insights integrates with Cloud Monitoring, letting you create custom dashboards and alerts on query metrics or tags and receive notifications using email, SMS, Slack, PagerDuty, and more.

Pricing

There is no additional cost for this feature. You can access one week of data in the Query Insights dashboard. For API requests, see the Cloud Monitoring Pricing. Cloud Monitoring has a tier you can use at no additional cost.

Security

Users who have access to the Cloud SQL dashboard can access Query Insights metrics on the Query Insights dashboard. If you have permission to update instances, you can configure Query Insights. For a list of permissions required for Cloud SQL instances, see Cloud SQL project access control.

Query Insights metrics are encrypted at rest.

Privacy

Query Insights stores and displays only normalized queries. All the constant values from the query string are removed. Query plan traces do not collect or store any constant values. By default, Query Insights does not collect IP addresses or tags information, although you can enable Query Insights to collect this information. If you enable the Query Insights settings to collect this information, at a later stage you can disable the setting to stop Query Insights from collecting the information.

Performance

Query Insight's PostgreSQL extension records query metrics and execution plans implemented using postgres hooks. The metrics, and execution plans, are stored as a postgres table in a shared memory table. By avoiding using expensive Disk IO operations, Query Insights is always on, and its performance overhead is expected to be small. Query Insights uses 12 MB of RAM for a default query string size of 1024 bytes. RAM usage increases with an increase in the query string size. Metrics are expected to be available in Query Insights within minutes of query completion.

Storage

Query Insights does not occupy any storage space in your Cloud SQL instance storage space. Metrics are stored in system metrics in Cloud Monitoring. It's important to review the Cloud Monitoring data retention policy.

Query Insights traces are stored in Cloud Trace. It's important to review the Cloud Trace data retention policy.

Compatibility

Query Insights is supported on all PostgreSQL for Cloud SQL machine types and available on all GCP regions.

Limitations

Query Insights is not supported on read replicas. It's possible to enable Insights on a read replica, but if that happens, you see an error in the log.

What's Next