Query Performance Monitoring

UptimeDock provides comprehensive query performance monitoring for your ClickHouse databases. Track execution times, identify slow queries, and analyze resource consumption to optimize your database performance.

Overview

The Query Performance dashboard is divided into two main sections:

Query Profile – Aggregated view of all queries with execution statistics
Slow Queries – Individual slow query instances for detailed analysis

Both views help you understand how your queries are performing and identify opportunities for optimization.

Query Profile

The Query Profile tab shows an aggregated view of queries executed on your ClickHouse database. Queries are grouped by their normalized form (query hash), allowing you to see patterns and identify frequently executed or resource-intensive queries.

Understanding the Columns

Column	Description
Query	The SQL query text (truncated for display). Click the eye icon to view full query details.
Database	The database where the query was executed.
Executions	Total number of times this query pattern has been executed.
Avg Duration	Average execution time across all executions of this query.
Avg Read Rows	Average number of rows read per execution.
Avg Write Rows	Average number of rows written per execution (for INSERT/UPDATE operations).

Search & Filter

Use the search box to filter queries by text. Click "Show Settings" to access additional filtering options like date range and query type.

Query Details

Click on any query row to open the Query Details modal. This provides deeper insights into the query's performance characteristics:

Metric	Description
Database	The target database for this query.
Executed By	The ClickHouse user who executed the query.
Executions	Total execution count with query type indicator (Select/Insert).
CPU Time (Avg)	Average CPU time consumed per execution.
Memory Usage (Avg)	Average memory consumed per execution.
Full Query	Complete SQL query text with syntax highlighting.

The Query Details modal also includes tabs for:

Duration – Execution time distribution and trends
Read – Read operation statistics
Write – Write operation statistics

Slow Queries

The Slow Queries tab shows individual query executions that exceeded the slow query threshold. Unlike Query Profile which aggregates similar queries, this view shows each slow execution separately, helping you identify specific problematic instances.

Column	Description
Query	The SQL query text that was slow.
Database	The database where the query ran.
Duration	How long this specific execution took.
Execution Time	When the query was executed (timestamp).

Slow Query Threshold

By default, queries exceeding 10ms are considered slow. You can adjust this threshold in your check settings based on your performance requirements.

Slow Query Details

Click on a slow query to view detailed execution information:

Field	Description
Full Query	Complete SQL with syntax highlighting for easy reading.
Initial Query Id	Unique identifier for this query execution in ClickHouse.
Query Hash	Hash of the normalized query (links similar queries together).
Database	Target database.
User	ClickHouse user who ran the query.
User Agent	Client application that submitted the query.
Execution Time	Exact timestamp when the query was executed.
Affected Tables	Tables accessed by this query (e.g., system.parts).
Duration	Total execution time.
Memory Usage	Peak memory consumed during execution.
CPU Time	CPU time consumed by this query.

The Slow Query Details modal includes additional tabs:

Read – Rows and bytes read during execution
Write – Rows and bytes written (for mutations)
Query Profile – Link to the aggregated query profile
Alerts – Any alerts triggered by this query

Analyzing Slow Queries

When investigating a slow query, consider these factors:

Check the affected tables – Large tables without proper indexing can cause slow queries
Review the query structure – Look for missing WHERE clauses or inefficient JOINs
Compare memory and CPU usage – High memory usage may indicate full table scans
Check execution time patterns – Is this query always slow or only sometimes?

-- Example: Identify tables with many parts (can slow down queries)
SELECT 
    database,
    table,
    count() AS part_count,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE active = 1
GROUP BY database, table
HAVING part_count >= 100
ORDER BY part_count DESC

Key Metrics Explained

Understanding these metrics helps you identify performance issues:

Metric	What It Measures	Warning Signs
Duration	Wall clock time from query start to completion	Queries > 1 second should be investigated
CPU Time	Actual CPU processing time	High CPU with low duration = efficient; Low CPU with high duration = I/O bound
Memory Usage	Peak memory during execution	Queries approaching max_memory_usage limits
Read Rows	Number of rows scanned	High read rows with few results = missing indexes
Executions	How often a query runs	High-frequency queries should be highly optimized

Best Practices

Follow these best practices to maintain optimal query performance:

Optimization Tips

Use LIMIT clauses when you don't need all results
Filter by partition key columns in WHERE clauses
Avoid SELECT * – only select columns you need
Use materialized views for frequently aggregated data
Monitor queries with high execution counts first

Regular monitoring checklist:

Review the Slow Queries tab daily for new issues
Check Query Profile weekly for patterns in high-frequency queries
Investigate any query with avg duration > 100ms
Monitor memory usage trends to prevent OOM errors
Set up alerts for queries exceeding your thresholds

Troubleshooting

Common query performance issues and solutions:

Issue	Possible Cause	Solution
High read rows, low result count	Missing or ineffective index	Add appropriate primary key or index
High memory usage	Large GROUP BY or ORDER BY	Use `max_memory_usage` setting, optimize query
Slow INSERT queries	Too many small inserts	Batch inserts together, use async inserts
Queries timing out	Resource contention	Check concurrent query count, optimize heavy queries
Inconsistent query times	Background merges or mutations	Monitor system.merges, schedule heavy operations

Need More Help?

For advanced optimization techniques, see our Memory & Resources guide or contact support for personalized assistance.