Query Analytics (QAN)

Relevant source files

The following files were used as context for generating this wiki page:

• api/qan/v1/service.proto
• api/qan/v1/collector.proto
• api/qan/v1/object_details.proto
• qan-api2/services/analytics/profile.go
• managed/services/qan/client.go
• qan-api2/README.md
• CONTRIBUTING.md

Query Analytics (QAN) is a core component of Percona Monitoring and Management (PMM) designed to provide deep visibility into database query performance. It allows users to analyze query behavior across various dimensions, identify slow queries, and understand the impact of queries on database resources. QAN supports multiple database engines, including MySQL, PostgreSQL, MongoDB, and ClickHouse, by collecting and aggregating query metrics into a centralized analytics backend.

The system operates by receiving aggregated "metrics buckets" from agents running on monitored nodes, processing these buckets within the PMM Server, and exposing an API for the PMM UI to generate reports, sparklines, and detailed query breakdowns.

Sources: CONTRIBUTING.md:27, api/qan/v1/service.proto, qan-api2/services/analytics/profile.go

Architecture and Data Flow

The QAN architecture follows a pipeline from data collection at the agent level to aggregation in the PMM Server and eventual visualization in the UI.

Data Collection Pipeline

1. PMM Agent: Collects raw query data from the database (e.g., via Performance Schema, Slow Log, or Profiler).
2. Aggregation: The agent aggregates queries into MetricsBucket structures based on fingerprints (summarized query text without values).
3. Transmission: The agent sends these buckets to pmm-managed, which uses the qan-api client to forward data to the CollectorService.
4. Storage: The qan-api2 service processes the buckets and stores them in ClickHouse for high-performance analytical querying.

The diagram shows the flow of query metrics from the source database to the analytics storage and UI.
Sources: managed/services/qan/client.go:138-232, api/qan/v1/collector.proto:14-25, CONTRIBUTING.md:23-45

Key Components

Collector Service

The CollectorService is the entry point for query data into the PMM Server. It accepts CollectRequest messages containing repeated MetricsBucket objects. Each bucket includes:

• Query Metadata: queryid (MD5 of fingerprint), fingerprint, and explain_fingerprint.
• Dimensions: service_name, database, schema, username, and client_host.
• Unified Labels: Environment, cluster, node, and service identifiers.
• Metrics: Aggregated statistics like num_queries, query_time_sum, lock_time_sum, and specific engine metrics (e.g., innodb_io_r_ops for MySQL or docs_scanned for MongoDB).

Sources: api/qan/v1/collector.proto:27-144, managed/services/qan/client.go:174-210

Analytics Service (QANService)

The QANService provides the public API for retrieving analyzed query data. It implements complex logic for grouping, filtering, and calculating performance statistics.

Method	Purpose
GetReport	Returns a list of metrics grouped by dimensions (e.g., queryid, host) with sparklines.
GetMetrics	Provides detailed metrics for a specific dimension value (e.g., a specific query ID).
GetQueryExample	Retrieves actual query samples (e.g., slowest, random, or with error) for a query class.
GetHistogram	Provides frequency distribution of query execution times.
ExplainFingerprint	Returns explain plans or placeholders count for a given query ID.

Sources: api/qan/v1/service.proto:26-118, api/qan/v1/object_details.proto:7-140

Data Structures and Metrics

Metrics Aggregation

Metrics are aggregated in buckets representing a specific time period (period_start_unix_secs and period_length_secs). For each metric root (e.g., query_time), the system tracks:

• Sum: Total value (used for calculating load).
• Cnt: Number of times the metric was recorded.
• Min/Max: The boundary values in the bucket.
• P99: The 99th percentile value.

Sources: api/qan/v1/collector.proto:55-59, qan-api2/services/analytics/profile.go:176-218

Engine-Specific Metrics

QAN collects specialized metrics depending on the database type:

Engine	Key Metrics Collected
MySQL	innodb_io_r_ops, lock_time, rows_examined, tmp_tables_on_disk, select_full_join
PostgreSQL	shared_blks_hit, blk_read_time, wal_records, cpu_user_time, plans_calls
MongoDB	docs_returned, response_length, docs_examined, locks_global_acquire
ClickHouse	read_rows, read_bytes, result_rows, memory_usage, written_rows

Sources: api/qan/v1/collector.proto:90-345, managed/services/qan/client.go:242-365

API Implementation Details

Report Generation Logic

The GetReport implementation in qan-api2 performs the following steps:

1. Validation: Checks that the start and end dates are provided and logical.
2. Dimension Mapping: Maps incoming labels to internal ClickHouse dimensions.
3. Metric Categorization: Splits requested columns into sumColumns, commonColumns, and specialColumns (e.g., boolean metrics).
4. Data Selection: Calls the repository manager to perform the ClickHouse Select and SelectSparklines.
5. Statistics Calculation: Computes SumPerSec (rate), Avg, and Load for the final response.

This diagram illustrates the internal processing of a report request within the PMM Server.
Sources: qan-api2/services/analytics/profile.go:32-156, managed/services/qan/client.go:215-230

Sorting and Ranking

The API supports ordering results via the order_by parameter. Prefixing a field with - indicates descending order.

• count maps to num_queries.
• load maps to m_query_time_sum.
• Time-based metrics (e.g., query_time) default to ordering by average (m_metric_avg).
• Common metrics default to ordering by sum (m_metric_sum).

Sources: qan-api2/services/analytics/profile.go:221-251

Conclusion

Query Analytics (QAN) provides a unified interface for database performance analysis by abstracting the differences between database engines into a consistent set of metrics and dimensions. Through the collaboration of PMM Agents, the managed service, and the specialized QAN API, it enables real-time and historical query profiling necessary for database optimization and troubleshooting.