DENIC Enhances Query Times by 10x Leveraging ClickHouse

• Application Infrastructure & Middleware - Database Management & Storage
• Infrastructure as a Service (IaaS) - Cloud Databases

• Buildings
• Telecommunications

• Procurement
• Product Research & Development

• Behavior & Emotion Tracking
• Time Sensitive Networking

• Data Science Services
• Testing & Certification

DENIC eG, the administrator and operator of the German namespace on the Internet, was facing challenges in improving the user experience of the internet community due to limitations in data analytics. The data relevant for their analytics was distributed among relational databases, server log data, and various other sources. These sources were already used for monitoring and system improvements, but their analytical features were limited and cross-evaluations across a wide range of sources were costly or not feasible. The initial steps of developing the data science platform involved using a database based on a relational DBMS. The data from different sources was consolidated by Python agents in containers on Kubernetes and the results were written to target tables in the database. This approach resulted in a considerable number of target tables and containers, which were difficult to administer and became somewhat overcomplicated. Furthermore, relational databases were only suitable for larger amounts of data to a limited extent, as the processing time of a query could take several minutes to hours.

DENIC eG (Deutsches Network Information Center) is the administrator and operator of .de, the German namespace on the Internet. With a portfolio of over 17.2 million domains, it is one of the world’s largest registries of top-level domains. DENIC operates on a non-profit basis and provides services that support fast, secure, and reliable access to websites and web services under the .de top-level domain. DENIC operates, among other things, a globally distributed name server network and is responsible for registry management with a domain database, registration system, and information services for .de domains. In order to continuously improve the user experience of the internet community, DENIC is increasingly focused on data analytics.

DENIC decided to test column-oriented databases, which are designed for fast queries over large amounts of data. After several case studies and presentations at conferences, the data science team became aware of ClickHouse. Initial tests and a PoC showed that ClickHouse met DENIC’s requirements very well in cluster operation and only requires a small server footprint, making it cost-effective. One of the use cases involved the design of a ClickHouse table, that is fed with several entities of DENIC’s registry database. The data is provided by a REST interface of the registry database as time-series events and fetched, processed, and written to the ClickHouse cluster by a Python agent on a daily basis. After saving several million data records, first noticeable problems occurred. The processing of the data delivered by the REST interface became noticeably slower. The query for selecting the domain states associated with holder updates took about 5 minutes. After investigating that behavior, it became clear that this would take longer and longer as the amount of data increased, making it unsustainable for the future. After several attempts and optimizations, the query runtime was optimized from 5 minutes to about 30 seconds.

• The implementation of ClickHouse and its performance, even in small clusters, provided DENIC with extensive support in the development of a data science platform. This platform is expected to be sustainable in the future due to its expandability. The solution was cost-effective and required a small server footprint. The administrative effort was significantly reduced, and the processing time of queries was drastically improved. This allowed DENIC to enhance the user experience of the internet community by providing faster, more efficient data analytics. The successful implementation of ClickHouse also demonstrated the potential of column-oriented databases in handling large amounts of data and fast queries.

• Query runtime was optimized from 5 minutes to about 30 seconds, a 10x improvement.
• The increase in query runtimes with continuous filling became much flatter than before.
• Even with several hundred million data records imported and many thousands of holder updates per day, the optimized level of query runtime could be maintained.