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