Clickhouse excessive disk usage with log tables

Old installations of FastNetMon (pre 2023) may consume excessive amount of disk space as old Clickhouse configuration had logic which logged every single query in separate database. It may easily use tens of gigabytes of disk space.

You may easily confirm that it’s the root cause by running this query in clickhouse-client:

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

Example affected instance may look this way:

┌─database───┬─table───────────────────┬─size───────┐
│ system     │ trace_log               │ 32.84 GiB  │
│ system     │ part_log                │ 26.51 GiB  │
│ system     │ metric_log              │ 2.83 GiB   │
│ system     │ asynchronous_metric_log │ 2.80 GiB   │
│ fastnetmon │ host_metrics            │ 664.83 MiB │
│ fastnetmon │ host_metrics_ipv6       │ 34.97 MiB  │
│ fastnetmon │ total_metrics_ipv6      │ 6.87 MiB   │
│ fastnetmon │ network_metrics_ipv6    │ 5.14 MiB   │
│ fastnetmon │ asn_metrics_ipv4        │ 4.31 MiB   │
│ fastnetmon │ network_metrics         │ 4.13 MiB   │
│ fastnetmon │ asn_metrics_ipv6        │ 3.18 MiB   │
│ fastnetmon │ total_metrics           │ 2.60 MiB   │
│ system     │ query_log               │ 13.95 KiB  │
│ system     │ crash_log               │ 2.14 KiB   │

You may notice that tables with names trace_log, part_log, metric_log, asynchronous_metric_log use quote a lot of disk space and they can be removed.

To disable such excessive logging please create file /etc/clickhouse-server/users.d/disable_logging.xml with following content:

<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <log_queries replace="replace">0</log_queries>
            <log_query_threads replace="replace">0</log_query_threads>
        </default>
    </profiles>
</yandex>

Then you need to create another file /etc/clickhouse-server/config.d/z_completely_disable_logging.xml with few more options to disable logging:

<?xml version="1.0"?>
<yandex>
    <asynchronous_metric_log remove="1"/>
    <metric_log remove="1"/>
    <query_thread_log remove="1" />
    <query_log remove="1" />
    <query_views_log remove="1" />
    <part_log remove="1"/>
    <session_log remove="1"/>
    <text_log remove="1" />
    <trace_log remove="1"/>
</yandex>

And lest one /etc/clickhouse-server/config.d/disable_verbose_file_logging.xml with following content:

<?xml version="1.0"?>
<clickhouse>
    <logger>
        <level replace="replace">warning</level>
    </logger>
</clickhouse>

After that please restart Clickhouse to apply changes:

sudo systemctl restart clickhouse-server

Finally, you can open clickhouse-client, switch to system database that way:

use system;

And then drop these log tables manually one by one:

drop table system.trace_log;
drop table system.part_log;
drop table system.metric_log;
drop table system.asynchronous_metric_log;

Clickhouse may need around ten minutes to remove data from disk and after that you will see that disk space was freed.