This page gives an overview of the enhanced Query Insights tool. To start using the tool now, see Improve query performance using enhanced Query Insights.
Enhanced Query Insights offers advanced query performance diagnostics as well as all of the features available in standard Query Insights.
Enhanced Query Insights in AlloyDB is an assistive query performance diagnostics platform that lets you detect, troubleshoot, and prevent database and query performance problems in near real time. Based on the previous seven days of Query Insights data, you can monitor query performance at the application level and trace the source of problematic queries across the application stack by action, controller, framework, route, application, and database driver. Enhanced Query Insights captures query plans for all queries and samples up to 200 query plans per minute.
To help you proactively identify and troubleshoot root causes of query performance issues, enhanced Query Insights lets you do the following:
- Perform ad hoc analysis to diagnose difficult problems using near real-time query stats and correlation analysis across multiple dimensions like users, hosts, and databases.
- View your query execution plan using complete SQL statements, along with accompanying comments. You can opt in to view comments associated with a query, where applicable.
- Troubleshoot complex performance issues by analyzing telemetry, like wait events. A wait event is a component that causes a delay in processing a query-related operation. Wait event metrics, enabled by default, help you diagnose and debug performance issues for long-running queries and their associated wait times.
- View index advisor recommendations about creating indexes.
- Configure your query insight settings. For example, you can customize query lengths, enable wait event analysis, and store query comments and application tags. You can access historical and near real-time data.
- Analyze data for up to a 30-day period for a long term performance view.
Benefits of enhanced Query Insights
Enhanced Query Insights offers the following features to help you detect and fix query performance problems:
- Prebuilt dashboards that help you identify query performance problems early and let you identify, diagnose, and resolve issues in a single page on the Google Cloud console.
- Visual query plans that help you identify root causes and troubleshoot issues. You can trace the source of a problematic query with in-context, end-to-end application tracing.
- Insights based on Google Cloud's enterprise security, privacy and compliance offerings, which might reduce operational overhead.
- Application-centric monitoring and simplified performance diagnosis of applications built using object relational mappers (ORM). You can monitor database performance with an application-centric view by tagging queries with business logic. Sqlcommenter, an open source library, auto-instruments your ORMs to help identify the application code causing performance problems.
How enhanced Query Insights works
Enhanced Query Insights is fully managed, which means you don't need to install or actively manage the feature.
Enhanced Query Insights loads an extension that records query metrics and execution plans that are implemented using hooks. The metrics are stored in AlloyDB tables, and normalized execution plans are stored in Cloud Trace.
You can access these metrics in the AlloyDB Query Insights page of the Google Cloud console. Metrics are expected to be available in enhanced Query Insights within 30 seconds of query completion. Metrics are encrypted at rest in the same way as database storage.
Enhanced Query Insights stores and displays only normalized queries. All the constant values from the query string are removed. Query comments are preserved only on demand (it is an opt-in feature). Query plan traces don't collect and store constant values.
Storage
Enhanced Query Insights stores and displays only normalized queries. All the constant values from the query string are removed. Query plan traces don't collect and store any constant values. By default, enhanced Query Insights does not collect tags information. If enabled at a later stage it can be disabled from enhanced Query Insights per instance setting.
The Query Insights dashboard provides four weeks of data. The storage requirement for seven days' worth of data is approximately 36GB. Enhanced Query Insights uses up to 10 MB of RAM (shared memory). Metrics are expected to be available in Insights within 30 seconds of query completion. In addition, standard Query Insights metrics are available in system metrics in Cloud Monitoring to keep the functionalities of the standard Query Insights intact. For more information about the Cloud Monitoring system metrics retention policy, see Data retention.
Enhanced Query Insights traces are stored in Cloud Trace and have a 30-day retention period. For more information about the Cloud Trace data retention policy, see Quotas and limits.
Limitations
- The metrics exposed by enhanced Query Insights aren't available using a Cloud Monitoring API.
- Client IP address support isn't available in Preview.
- Enhanced Query Insights are not supported for cross-region replication.
- AlloyDB stores Query Insights data for its primary and read pool instances for 30 days. Storing data for primary instances consumes about 180 GB and might increase based on the number of read pool nodes present in the cluster. The maximum consumed storage size is 700 GB.
- You must enable enhanced Query Insights on a primary instance before enabling them on a read pool instance.