Introduction
When administrators install Percona Monitoring and Management (PMM), they usually focus on the dashboards.
- Beautiful Grafana panels.
- Historical metrics.
- Query Analytics (QAN).
- Performance insights.
However, behind those dashboards is a database that quietly handles millions of monitoring records every day. That database is ClickHouse. Many engineers only notice ClickHouse when something goes wrong:
- Query Analytics stops updating
- PMM becomes slow
- Disk usage suddenly increases
- The ClickHouse container consumes high CPU
- Storage fills unexpectedly
At that point, one question arises What exactly is ClickHouse doing inside PMM?
This article explores ClickHouse from a PMM administrator’s perspective not as a database developer, but as an SRE or DBA responsible for keeping the monitoring platform healthy.
By the end of this article, you’ll understand:
- Why PMM uses ClickHouse
- How Query Analytics (QAN) works
- What data ClickHouse stores
- Internal operations of ClickHouse
- Common operational issues
- Useful troubleshooting commands
- Best practices for maintaining a healthy PMM server
Why Doesn’t PMM Store Everything in MySQL?
A common misconception is that PMM stores all monitoring data in MySQL. It doesn’t. Monitoring systems generate an enormous amount of data. Consider a medium-sized environment:
- 100 database servers
- Metrics collected every second
- Thousands of queries per minute
- Query execution statistics
- Histograms
- Performance counters
This easily results in millions of records every day. Traditional transactional databases are not optimized for this workload. Monitoring data is different from application data. Monitoring requires:
- Fast writes
- Time-series storage
- Large-scale aggregation
- Compression
- Analytical queries
ClickHouse was designed specifically for these workloads.
What is ClickHouse?
ClickHouse is an open-source column-oriented analytical database developed to process massive volumes of data with exceptional speed.
Unlike traditional row-based databases such as MySQL or PostgreSQL, ClickHouse stores data column by column rather than row by row.
Imagine a table like this:
| Server | CPU | Memory | Timestamp |
|---|---|---|---|
| DB01 | 65 | 78 | 10:01 |
| DB02 | 81 | 60 | 10:01 |
A row-oriented database stores complete rows together.
A column-oriented database stores:
| Server: DB01 DB02 CPU: 65 81 Memory: 78 60 Timestamp: 10:01 10:01 |
Why? Because analytical queries often read only a few columns.
Example:
| SELECT avg(cpu) FROM metrics WHERE timestamp > now() – INTERVAL 1 HOUR; |
ClickHouse only reads the CPU column, making such queries extremely efficient.
Why Did Percona Choose ClickHouse?
PMM continuously receives data from:
- MySQL
- MongoDB
- PostgreSQL
- MariaDB
- ProxySQL
- HAProxy
- Operating systems
This telemetry includes:
- CPU
- Memory
- Disk I/O
- Query execution times
- Slow queries
- Lock statistics
- Wait events
- Replication metrics
The volume grows rapidly. ClickHouse provides:
- High compression
- Fast inserts
- Fast aggregations
- Excellent time-series performance
- Low storage overhead
It is an ideal fit for observability platforms.
How PMM Uses ClickHouse
The simplified architecture looks like this:
Notice something interesting. ClickHouse is not storing every monitoring metric. Metrics are primarily stored in VictoriaMetrics. ClickHouse mainly stores Query Analytics (QAN) data. This distinction is important because many administrators mistakenly assume ClickHouse contains all monitoring information.
Understanding Query Analytics (QAN)
Query Analytics is one of PMM’s most valuable features.
Whenever a monitored database executes SQL statements, PMM collects information such as:
- Query fingerprint
- Execution count
- Average execution time
- Maximum latency
- Rows examined
- Rows returned
- Lock time
- Query plan statistics
Instead of storing every SQL statement separately, PMM groups similar queries using fingerprints.
For example:
| SELECT * FROM users WHERE id=1 SELECT * FROM users WHERE id=2 SELECT * FROM users WHERE id=500 |
All three become:
| SELECT * FROM users WHERE id=? |
This dramatically reduces storage requirements while preserving valuable performance information.
Journey of a Query
Let’s follow a query from execution to the PMM dashboard.
Step 1
Application executes:
| SELECT * FROM orders WHERE customer_id = 120; |
Step 2
The database records execution statistics. PMM Agent collects those statistics and processes the incoming data.
Step 3
Query fingerprints and metadata are written into ClickHouse. Grafana reads aggregated results. QAN Dashboard displays:
- Total executions
- Average latency
- Maximum latency
- Load contribution
- Historical trends
Internal ClickHouse Operations
Inside PMM, ClickHouse continuously performs several background activities.
Data Ingestion
Incoming query statistics are inserted continuously. This workload is write-heavy.
Compression
ClickHouse automatically compresses data. Compression ratios of 5x–10x are common. This significantly reduces storage requirements.
Merge Operations
One of ClickHouse’s most important internal activities is merging.
Small data parts are periodically merged into larger optimized parts.
This improves:
- Read performance
- Compression efficiency
- Query speed
You can inspect merges using:
| SELECT * FROM system.merges; |
Background Cleanup
Old Query Analytics data is automatically removed according to retention policies. This prevents unlimited disk growth.
Useful System Tables
ClickHouse exposes valuable operational information through system tables.
Running Queries
| SELECT * FROM system.processes; |
Useful for investigating:
- Long-running queries
- Heavy resource usage
- Active sessions
Active Merges
| SELECT * FROM system.merges; |
Storage Information
| SELECT database, table, rows, bytes FROM system.parts; |
Background Activity
| SELECT * FROM system.metrics; |
Server Events
| SELECT * FROM system.events; |
These tables are invaluable when troubleshooting PMM performance issues.
Common Operational Issues
High CPU
Often caused by:
- Heavy QAN activity
- Large merge operations
- Complex dashboard queries
High Disk Usage
Usually caused by:
- Long retention periods
- Large QAN history
- Excessive monitored instances
High Memory
Large analytical queries may temporarily consume significant memory.
Slow Dashboards
Possible causes include:
- Large datasets
- Insufficient CPU
- Disk bottlenecks
- ClickHouse merge backlog
Practical Troubleshooting Checklist
When ClickHouse behaves unexpectedly, these Linux commands are a good starting point:
| docker stats docker logs pmm-server top htop iostat -x vmstat df -h free -h |
Inside ClickHouse:
| SELECT * FROM system.processes; SELECT * FROM system.parts; SELECT * FROM system.merges; |
These provide quick visibility into the health of the ClickHouse engine.
Best Practices
A few operational practices can keep PMM responsive:
- Monitor available disk space regularly.
- Review retention policies for Query Analytics.
- Allocate sufficient CPU and memory to the PMM server.
- Periodically inspect ClickHouse system tables.
- Monitor merge activity during periods of high ingestion.
- Keep PMM updated to benefit from performance improvements and bug fixes.
Conclusion
ClickHouse is one of the key technologies that enables PMM to provide rich Query Analytics at scale. While it often operates quietly in the background, it plays a critical role in collecting, compressing, storing, and serving analytical data.
Understanding how ClickHouse works within PMM helps administrators troubleshoot issues more effectively, optimize resource usage, and make informed decisions about scaling their monitoring infrastructure.
As environments grow larger and observability data continues to increase, having a solid understanding of ClickHouse is becoming an increasingly valuable skill for database administrators and Site Reliability Engineers.
The next time you open the QAN dashboard and instantly identify a slow query, remember that ClickHouse is the analytical engine making that insight possible.