# Detect and terminate long-running queries

ClickHouse is a high-performance, column-oriented OLAP database, but poorly optimized or long-running queries can still impact performance especially in resource-constrained environments like Elestio. Because ClickHouse executes large queries across multiple threads and can consume high memory and disk I/O, monitoring and controlling slow or blocking operations is essential.

This guide explains how to <span class="s3">**detect**</span>, <span class="s3">**analyze**</span>, and <span class="s3">**terminate**</span> long-running queries using terminal tools, Docker Compose setups, and ClickHouse’s internal system tables. It also outlines <span class="s3">**prevention strategies**</span> to help maintain system health.

## **Monitoring Long-Running Queries**

ClickHouse exposes query execution data through system tables like <span class="s4">system.processes</span> and <span class="s4">system.query\_log</span>. These allow you to monitor currently executing and historical queries for duration, memory usage, and user activity.

##### **Check Active Queries via Terminal**

To list currently running queries and their duration:

```sql
SELECT
  query_id,
  user,
  elapsed,
  memory_usage,
  query
FROM system.processes
ORDER BY elapsed DESC;
```

- <span class="s1">elapsed</span> is the query runtime in seconds.
- <span class="s1">memory\_usage</span> is in bytes.
- This lets you pinpoint queries that are taking too long or consuming excessive memory.

##### **Monitor Query Load in Real Time**

ClickHouse doesn’t have a MONITOR-like command, but you can simulate real-time monitoring by repeatedly querying <span class="s2">system.processes</span>:

```
watch -n 2 'clickhouse-client --query="SELECT elapsed, query FROM system.processes ORDER BY elapsed DESC LIMIT 5"'
```

This updates every 2 seconds and shows the top 5 longest-running queries.

### **Terminating Problematic Queries Safely**

If you identify a query that is consuming too many resources or blocking critical workloads, you can terminate it by its <span class="s2">query\_id</span>.

##### **Kill a Query by ID**

```sql
KILL QUERY WHERE query_id = '<id>';
```

- The <span class="s1">&lt;id&gt;</span> can be found in the <span class="s1">system.processes</span> table.
- This forces termination of the query while leaving the user session intact.

To forcibly kill all long-running queries (e.g., &gt;60 seconds):

```sql
KILL QUERY WHERE elapsed > 60 SYNC;
```

> Use <span class="s1">SYNC</span> to wait for the termination to complete before proceeding.

### **Managing Inside Docker Compose**

If ClickHouse is running inside Docker Compose on Elestio, follow these steps:

##### **Access the ClickHouse Container**

```
docker-compose exec clickhouse bash
```

Then run:

```
clickhouse-client --user default
```

If authentication is enabled, add <span class="s1">--password &lt;your\_password&gt;</span>.

You can now run queries like:

```sql
SELECT query_id, elapsed, query FROM system.processes;
```

Or terminate:

```sql
KILL QUERY WHERE query_id = '<id>';
```

## **Analyzing Query History**

ClickHouse logs completed queries (including failures) in the <span class="s2">system.query\_log</span> table.

##### **View Historical Long-Running Queries**

```sql
SELECT
  query_start_time,
  query_duration_ms,
  user,
  query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
ORDER BY query_start_time DESC
LIMIT 10;
```

This helps identify patterns or repeat offenders.

### **Understanding Query Latency with Profiling Tools**

<span class="s2">ClickHouse provides advanced metrics via </span>system.metrics<span class="s2">, </span>system.events<span class="s2">, and </span>system.asynchronous\_metrics<span class="s2">.</span>

##### **Generate a Performance Snapshot**

```sql
SELECT * FROM system.metrics WHERE value != 0 ORDER BY value DESC;
```

- Use to analyze memory pressure, merge operations, disk reads/writes, and thread usage.

To examine detailed breakdowns of CPU usage or IO latency:

```sql
SELECT * FROM system.events WHERE value > 0 ORDER BY value DESC;
```

### **Best Practices to Prevent Long-Running Queries**

Preventing long-running queries is vital for maintaining ClickHouse performance, especially under high concurrency or on shared infrastructure.

- **Avoid Full Table Scans:** Use filters on primary key or indexed columns. Avoid queries without WHERE clauses on large tables.

```sql
SELECT count() FROM logs WHERE date >= '2024-01-01';
```

- **Limit Result Set Sizes:** Avoid returning millions of rows to clients. Use <span class="s2">LIMIT</span> and paginated access.

```sql
SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100;
```

- **Optimize Joins and Aggregations:** Use <span class="s1">ANY INNER JOIN</span> for faster lookups. Avoid joining two huge datasets unless one is pre-aggregated or dimensionally small.
- **Avoid High Cardinality Aggregates:** Functions like <span class="s2">uniqExact()</span> are CPU-intensive. Prefer approximate variants (<span class="s2">uniq()</span>) when precision isn’t critical.
- **Set Query Timeouts and Memory Limits:** Limit resource usage per query:

```sql
SET max_execution_time = 30;
SET max_memory_usage = 1000000000;
```

- **Use Partitions and Projections:** Partition large datasets by time (e.g., <span class="s1">toYYYYMM(date)</span>) to reduce scanned rows. Use projections for fast pre-aggregated access.