Technical Tip: FortiSIEM ClickHouse disk usage growth analysis

Description

This article describes how to identify the primary contributors to ClickHouse disk utilization and validate whether the growth is expected based on event volume and retention settings.

Scope

FortiSIEM.

Solution

In FortiSIEM environments utilizing ClickHouse as the event database, administrators may observe continuous disk utilization growth on Supervisor or Worker nodes hosting ClickHouse services. If not monitored properly, increasing disk consumption can lead to reduced query performance, ingestion delays, event retention issues, or disk exhaustion.

Step 1: Verify overall disk utilization:

Check filesystem utilization:

df -h

Identify directories consuming the most space:

du -sh /data/*

For ClickHouse deployments, database files are typically stored under:

• /opt/clickhouse/

• /data/clickhouse/

Depending on the deployment architecture.

Step 2: Verify ClickHouse database size.

Connect to ClickHouse:

clickhouse-client

Display database size information:

```sql
SELECT
database,
formatReadableSize(sum(bytes)) AS size
FROM system.parts
GROUP BY database
ORDER BY sum(bytes) DESC;

This identifies which database is consuming the most storage.

Step 3: Identify the largest tables.

Run:

```sql
SELECT
table,
formatReadableSize(sum(bytes)) AS size
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(bytes) DESC;
```

Sample output:

```text
events 2.5 TB
eventattrs 750 GB
incidents 50 GB
```

This helps determine whether event ingestion or another table is responsible for growth.

Step 4: Check event volume.

Review event ingestion statistics from the FortiSIEM GUI: ADMIN -> Health -> Event Rate, or Analytics -> Event Query.

A sudden increase in EPS can significantly increase ClickHouse storage requirements.

Common causes include:

• New log sources added.

• Debug logging is enabled on monitored devices.

• Syslog loops.

• Duplicate log forwarding.

• Excessive Windows Event collection.

Step 5: Validate event retention settings.

Navigate to ADMIN -> Settings -> Storage.

Review:

• Event Retention Period.

• Raw Event Retention.

• Aggregated Event Retention.
  
  

Long retention periods combined with high EPS rates are the most common reason for expected database growth.

Example: 10,000 EPS, 90 Day Retention.

Step 6: Check for Old ClickHouse Partitions

Connect to ClickHouse and review partition usage:

```sql
SELECT
table,
partition,
count()
FROM system.parts
WHERE active
GROUP BY table, partition
ORDER BY partition;
```

A large number of active partitions may indicate retention cleanup is not occurring as expected.

Step 7: Identify top space-consuming directories.

From the operating system:

du -h /data/clickhouse --max-depth=2 | sort -hr | head -20

This helps identify:

• Large partitions.

• Temporary merge files.

• Failed cleanup operations.

• Unexpected storage growth locations.

Step 8: Check ClickHouse health.

Verify FortiSIEM services:

phstatus

Confirm ClickHouse processes are healthy:

systemctl status clickhouse-server

Potential issues include:

•  Failed merges.

•  Insufficient disk space.

•  Corrupted parts.

•  Retention cleanup failures.

Summary:

ClickHouse disk utilization growth is most commonly caused by increased event ingestion volume, retention configuration changes, or retention cleanup issues. Administrators should regularly monitor EPS trends, retention settings, database table growth, and ClickHouse partition cleanup activity to prevent unexpected storage exhaustion and maintain optimal FortiSIEM performance.