When it comes to monitoring very large networks and the flows’ cardinality reaches into the billions, the performance of historical data storage and query systems becomes a critical bottleneck. Network operators, analysts, and engineers need to access flow records quickly and reliably, whether for traffic analysis, security investigations, or compliance reporting. When faced with massive datasets, even small inefficiencies in the data pipeline can result in slow queries, high CPU and disk usage, and poor responsiveness.
At ntop, our mission is to help users gain visibility into their networks with speed and precision. For this reason, over the past few months, we’ve undertaken a complete redesign of our ClickHouse integration in ntopng, with one clear goal in mind: accelerate queries and improve performance at scale.
Whether you’re storing millions or billions of flow records, our new ClickHouse support ensures that queries return faster (much faster), exports scale better, and system resources are used more efficiently than ever before.
Native ClickHouse API: A Clean Break from MySQL
One of the biggest architectural changes is the move away from using the MySQL API to interact with ClickHouse. While functional, the MySQL API was not designed to support the native data types and high-performance capabilities of ClickHouse. This led to unnecessary in-query data conversions. Also, it was definitely not optimize for high insertion throughput, and for this reason we were using CSV files, stored on disk, to implement batch insert.
For the above reasons, the ClickHouse support in ntopng has now completely reworked to use the native ClickHouse C++ API for all operations, which provides:
- Native batch insertion using in-memory ClickHouse blocks.
- High-speed, type-safe querying with no need for runtime data transformations.
- An optimized database schema.
- A cleaner, more robust codebase better aligned with ClickHouse’s architecture.
Schema Optimizations
ClickHouse is a columnar database designed for performance, but only if used correctly. We’ve reworked the database schema to take full advantage of its strengths:
- No more NULLABLE columns: in ClickHouse, these are implemented using a hidden indirection table, which hurts performance. We’ve replaced them with proper default values and schema adjustments.
- Improved partitioning and indexing: flows are now partitioned and indexed in a way that accelerates common queries dramatically, especially when dealing with large time ranges or filtering by interface.
Disk I/O Eliminated
Previously, flow data destined for ClickHouse was first serialized to CSV files and written to disk before being inserted. This approach introduced latency, consumed I/O bandwidth, and increased disk wear.
In the new implementation:
- Data goes straight from memory to ClickHouse via native blocks, providing a performance boost from under 100k insert/sec to over 200k insert/sec.
- FIFO queues have been introduced to handle traffic peaks and avoid any overhead in the flow processing path.
- No intermediate files are created, reducing latency and system load.
- Batching is now more efficient, with larger payloads sent in parallel.
Built to Scale
A common ntopng configuration when deployed across large networks includes the introduction of view interfaces, to aggregate multiple capture or collection interfaces into a single logical view. The new implementation has been designed with scalability and flexibility in mind:
- Parallel inserts per interface: when multiple interfaces are aggregated into a view, each one now writes data to ClickHouse in parallel, maximizing throughput. An aggregated view is still available in the view interface.
- Simultaneous exports to multiple backends: you can now export flow data to ClickHouse, Kafka, ElasticSearch, and syslog at the same time.
Export Statistics
To help you monitor and troubleshoot flow exports, we’ve added export statistics that are available:
- Per interface
- Per export method (ClickHouse, Kafka, ElasticSearch, Syslog)
- You can now view export statistics directly from the Interface Details page in the GUI, making it easier to understand how each component of your data pipeline is performing.
Cleaner Codebase
The new implementation is built on a restructured codebase that introduces a single DataBase superclass used across all storage backends, including SQLite and ClickHouse. This brings:
- Unified architecture for handling historical flows, alerts, assets, and more.
- Easier future maintenance and expansion, as all database interactions follow the same internal interface.
- Less duplication, fewer bugs, and more predictable behavior across modules.
Built for Today, Ready for Tomorrow
This work is not just a performance improvement, it’s a massive upgrade in terms of performance, scalability, and efficiency, that changes how we handle historical flow data at scale.
In real-world scenarios involving billions of flow records, we’ve observed dramatic improvements in query responsiveness. In some of the most common use cases, such as filtering flows by interface and time range, query times have dropped from over a minute to just a few seconds, and in many cases, to under one second. A huge performance boost that dramatically changes your traffic analysis experience.
We encourage all users to upgrade (if you are running ntopng 6.5 from nightly builds, otherwise be patient and wait for the next stable release) and experience the performance gains firsthand. As always, we welcome your feedback and are here to help you make the most of your ntop-powered monitoring stack.
Enjoy!