ClickHouse QAN Storage
Relevant source files
The following files were used as context for generating this wiki page:
ClickHouse QAN Storage serves as the primary analytical database backend for Percona Monitoring and Management (PMM) Query Analytics (QAN). It is responsible for storing, aggregating, and retrieving highly granular query performance data collected from various database engines including MySQL, PostgreSQL, MongoDB, and ClickHouse itself.
The storage system is designed to handle high-volume telemetry through a "bucket" based ingestion model. Data is collected by agents, aggregated into time-based metrics buckets, and then pushed via the qan-api to ClickHouse. This architecture enables PMM to provide a "single pane of glass" for monitoring database performance across different environments.
Sources: README.md, CONTRIBUTING.md:28, api/qan/v1/service.proto:43-157
Data Ingestion Architecture
The ingestion flow follows a multi-stage path from the monitored database instance to the central ClickHouse storage. The PMM Agent collects raw query data and aggregates it into MetricsBucket messages to reduce network overhead and storage pressure.
The diagram above illustrates the path of query metrics from collection to long-term storage and eventual visualization.
Sources: CONTRIBUTING.md:21-42, api/qan/v1/collector.proto:11-15
Metrics Collection Service
The CollectorService is the primary entry point for telemetry data. It exposes a Collect RPC method that accepts a list of metrics buckets.
- Service:
qan.v1.CollectorService - Method:
Collect(CollectRequest) returns (CollectResponse) - Data Structure:
MetricsBucketcontains md5 query IDs, fingerprints, dimension groups (service name, database, user), and performance metrics.
Sources: api/qan/v1/collector.proto:11-20
Data Model and Schema
ClickHouse QAN Storage utilizes a structured schema designed for efficient time-series analysis. Data is organized into "buckets" representing a specific query class within a defined period.
Metrics Bucket Structure
Each bucket contains a combination of dimensions and metrics. Dimensions allow for filtering and grouping, while metrics provide the numerical data for analysis.
| Category | Fields | Description |
|---|---|---|
| Identity | queryid, fingerprint | MD5 hash and sanitized query text. |
| Dimensions | service_id, database, username, client_host | Environmental context for the query. |
| Time | period_start_unix_secs, period_length_secs | Temporal window of the bucket. |
| Execution | num_queries, m_query_time_sum, m_query_time_max | Count and latency statistics. |
Sources: api/qan/v1/collector.proto:24-144, api/agent/v1/collector.proto:25-115
Engine-Specific Metrics
The storage schema supports specialized metrics for different database engines, ensuring deep observability for each supported platform.
- MySQL: Tracks lock time, temporary tables on disk, and InnoDB-specific I/O metrics (e.g.,
m_innodb_io_r_ops_sum). - PostgreSQL: Includes shared buffer hits/reads, WAL (Write-Ahead Log) records, and parallel worker statistics.
- MongoDB: Monitors document scans, index key usage, and storage read times.
- ClickHouse: Self-monitoring metrics including rows/bytes read and memory usage for its own queries.
Sources: api/qan/v1/qan.proto:40-165, api/agent/v1/collector.proto:117-380
Query and Retrieval API
The QANService provides a high-level API for the PMM UI and external integrations to query the ClickHouse storage.
Key API Endpoints
The service implements several RPCs to retrieve reports and specific query details:
| RPC Method | Purpose | Source File |
|---|---|---|
GetReport | Returns metrics grouped by query ID or other dimensions. | service.proto:44 |
GetMetrics | Provides a map of metrics for specific filtering. | service.proto:73 |
GetHistogram | Returns latency distribution for specific queries. | service.proto:91 |
GetQueryExample | Retrieves actual query samples (random, slowest, or with errors). | service.proto:136 |
ExplainFingerprintByQueryID | Provides an explain plan fingerprint for a query ID. | service.proto:100 |
Sources: api/qan/v1/service.proto:43-145
Server Configuration and Optimization
The ClickHouse server is configured for high concurrency and significant data volumes. The clickhouse-config.xml defines critical limits and storage paths for the PMM environment.
Performance Tuning
- Memory Management:
max_server_memory_usage_to_ram_ratiois set to 0.9, allowing the server to utilize up to 90% of available RAM. - Cache Sizes:
- Uncompressed cache: 8GB (
uncompressed_cache_size) - Mark cache: 5GB (
mark_cache_size)
- Uncompressed cache: 8GB (
- Networking: Configured to listen on ports 8123 (HTTP) and 9000 (TCP), with a maximum of 4096 concurrent connections.
- Storage Policy: Data is stored in
/srv/clickhouse/with specific TTL (Time To Live) policies for logs, such asevent_date + INTERVAL 30 DAY DELETEfor thequery_log.
Sources: dev/clickhouse-config.xml:182-184, dev/clickhouse-config.xml:230-244, dev/clickhouse-config.xml:331-355, dev/clickhouse-config.xml:671-678
Security and Access
ClickHouse QAN Storage enforces security through several mechanisms:
- Password Complexity: Supports SHA256 and BCrypt password types.
- Masking Rules: Sensitive data in query logs is redacted using regex patterns (e.g., hiding arguments for
encrypt/decryptfunctions). - LDAP/Kerberos: Configuration supports integration with external directory services for user authentication.
Sources: dev/clickhouse-config.xml:369-380, dev/clickhouse-config.xml:433-510, dev/clickhouse-config.xml:973-984
Summary
The ClickHouse QAN Storage is a specialized analytical component of PMM. It transforms raw query telemetry into actionable insights through a structured aggregation model and a high-performance database backend. By utilizing ClickHouse's columnar storage and PMM's bucketed ingestion, the system provides efficient long-term storage and low-latency retrieval of complex database performance metrics across diverse fleet environments.
Sources: README.md, CONTRIBUTING.md:28-30