Clickhouse excessive disk usage with log tables

Old installations of FastNetMon (pre-2023) may consume an excessive amount of disk space as the old Clickhouse configuration had logic which logged every single query in a 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

An example affected instance may look like the following:

┌─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 a lot of disk space, and they can be removed.

To disable such excessive logging, create a file /etc/clickhouse-server/users.d/disable_logging.xml with the 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 a 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 the 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 the 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 the disk space has been freed.

To automate this task, you can usea special command from our installer:

wget https://install.fastnetmon.com/installer -Oinstaller
sudo chmod +x installer
sudo ./installer -configure_clickhouse_logging