After 7 days of running: 100 GB flows table (Clickhouse)

[infinitynet2011]

Hello,

We own around /18 IPv4 and /29 IPv6
We have around 60 Gbps connections, using 15% (15 Gbps).

ntopng -Version
07/Jul/2024 21:14:00 [Prefs.cpp:1653] ntopng will use redis sion@0
Version: 6.1.240706 [Enterprise/Professional build]
GIT rev: dev:3d835328121b0a98e5d95a31ae6bbcfd610c0adf:20240706
Pro rev: r6637
Built on: Rocky Linux release 9.3
Platform: x86_64
Edition: Enterprise M
License Type: Permanent

SELECT
table,
formatReadableSize(sum(bytes)) AS size,
min(min_date) AS min_date,
max(max_date) AS max_date
FROM system.parts
WHERE active
GROUP BY table

Query id: f966068f-6fdd-43e5-acb6-c0434e35d0e2

┌─table───────────────────┬─size───────┬───min_date─┬───max_date─┐

1. │ flows │ 99.83 GiB │ 1970-01-01 │ 1970-01-01 │
2. │ part_log │ 16.82 MiB │ 2024-04-30 │ 2024-07-07 │
3. │ metric_log │ 120.88 MiB │ 2024-04-30 │ 2024-07-07 │
4. │ trace_log │ 2.02 GiB │ 2024-04-30 │ 2024-07-07 │
5. │ system_alerts │ 73.49 KiB │ 1970-01-01 │ 1970-01-01 │
6. │ crash_log │ 1.83 KiB │ 1970-01-01 │ 1970-01-01 │
7. │ interface_alerts │ 56.02 KiB │ 1970-01-01 │ 1970-01-01 │
8. │ trace_log_0 │ 194.96 MiB │ 2024-06-19 │ 2024-06-28 │
9. │ query_log │ 32.37 MiB │ 2024-06-19 │ 2024-07-07 │
10. │ metric_log_0 │ 90.95 MiB │ 2024-06-19 │ 2024-06-28 │
11. │ user_alerts │ 14.41 KiB │ 1970-01-01 │ 1970-01-01 │
12. │ processors_profile_log │ 53.94 MiB │ 2024-06-19 │ 2024-07-07 │
13. │ vulnerability_scan_data │ 2.10 MiB │ 1970-01-01 │ 1970-01-01 │
14. │ asynchronous_metric_log │ 345.17 MiB │ 2024-04-30 │ 2024-07-07 │
    └─────────────────────────┴────────────┴────────────┴────────────┘

14 rows in set. Elapsed: 0.004 sec.

SELECT *
FROM system.query_log
WHERE event_time >= now() - interval 1 day
ORDER BY event_time DESC
LIMIT 100;

100 rows in set. Elapsed: 0.064 sec. Processed 110.06 thousand rows, 37.01 MB (1.72 million rows/s., 579.04 MB/s.)
Peak memory usage: 26.83 MiB.

The server has 128 GB RAM and 3,4TB SSD SAMSUNG RAID 1

We don't have a problem with the space but with the delay.

Especially if we ask for a report of the last 7 days. we can wait 3 minutes.

We switch off most of the features from the Timeseries Database

From the search, we find that using 'batch inserts' instead of 'inserting rows one by one' will improve speed.

Ntopng can do batch inserts ? Can we switch to 'batch inserts'?

Or what optimization we can do in our situation.

Thank you,
Gabriel

[lucaderi]

ntop does batch inserts already. I believe the problem is clickhouse and not ntopng.
I assume that you have implemented this https://github.com/ntop/ntopng/blob/dev/doc/README.clickhouse.md

Just do this

• stop ntopng
• run a query with clickhouse-client and check the speed

and report

[lucaderi]

Closing for inactivity. It does not seem to be an ntop-related problem