Data retention for Clickhouse persistent data storage

FastNetMon creates all tables in Clickhouse with configuration to remove all data older than 7 days by default. It implemented using TTL capability in Clickhouse.

You may alter this value using this guide. Run Clickhouse client:

clickhouse-client

Then switch to database “fastnetmon” in clickhouse-client interface:

USE fastnetmon

FastNetMon uses multiple tables and you will need to repeat actions for all of them. List all available tables:

show tables

You will see following list, it’s just an example as you may have more or less tables (as it depends on configuration):

asn_metrics_ipv4
asn_metrics_ipv6
host_metrics
network_metrics
total_metrics
traffic

Optionally, you may find which table uses most of data using this query:

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

Example output will look this way:

┌─database───┬─table────────────┬─size──────┐
│ fastnetmon │ traffic          │ 4.81 GiB  │
│ fastnetmon │ host_metrics     │ 3.68 GiB  │
│ fastnetmon │ asn_metrics_ipv4 │ 2.01 GiB  │
│ fastnetmon │ total_metrics    │ 27.80 MiB │
│ fastnetmon │ network_metrics  │ 12.20 MiB │
└────────────┴──────────────────┴───────────┘

To check current retention setup you can use following command:

show create table total_metrics

And then check for TTL section in output:

CREATE TABLE fastnetmon.total_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `direction` String,
    `flows` UInt64,
    `packets` UInt64,
    `bits` UInt64
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (direction, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192 

In this case it means that Clickhouse will remove data after 7 days.

And then you can reduce or extend period to keep data using following query:

ALTER TABLE total_metrics MODIFY TTL metricDate + toIntervalDay(8);

Do not forget to repeat this command for all tables.