ClickHouse Case Studies DENIC Enhances Query Times by 10x Leveraging ClickHouse

Edit This Case Study Record

	DENIC Enhances Query Times by 10x Leveraging ClickHouse ClickHouse

DENIC Enhances Query Times by 10x Leveraging ClickHouse

ClickHouse

Technology Category	Application Infrastructure & Middleware - Database Management & Storage Infrastructure as a Service (IaaS) - Cloud Databases
Applicable Industries	Buildings Telecommunications
Applicable Functions	Procurement Product Research & Development
Use Cases	Behavior & Emotion Tracking Time Sensitive Networking
Services	Data Science Services Testing & Certification
Challenge	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. Read More
About Customer	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. Read More
Solution	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. Read More Log in to view content
Contents

Technology Category

Application Infrastructure & Middleware - Database Management & Storage

Infrastructure as a Service (IaaS) - Cloud Databases

Applicable Industries

Buildings

Telecommunications

Applicable Functions

Procurement

Product Research & Development

Use Cases

Behavior & Emotion Tracking

Time Sensitive Networking

Services

Data Science Services

Testing & Certification

Challenge

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.

About Customer

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.

Solution

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.

Impact #1	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.

Impact #1

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.

Benefit #1	Query runtime was optimized from 5 minutes to about 30 seconds, a 10x improvement.
Benefit #2	The increase in query runtimes with continuous filling became much flatter than before.
Benefit #3	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.

Benefit #1

Query runtime was optimized from 5 minutes to about 30 seconds, a 10x improvement.

Benefit #2

The increase in query runtimes with continuous filling became much flatter than before.

Benefit #3

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.

Download PDF Version

Overview

DENIC Enhances Query Times by 10x Leveraging ClickHouse

Operational Impact

Quantitative Benefit