ClickHouse Cloud & Amazon S3 Express One Zone: Making a blazing fast analytical database even faster

ClickHouse is a columnar database management system (DBMS) designed for blazing-fast real-time analytics. It was built to address the needs of interactive analytical applications requiring up-to-the-second analytics. To do that, it must support real-time data ingestion at the rate of hundreds of millions of events per second and run complex analytical queries, such as filtering, aggregations, and real-time lookups and joins, on analytical datasets that often grow into terabytes and petabytes of data.

When building ClickHouse Cloud, a software-as-a-service (SaaS) solution for running ClickHouse at scale, we decided to adopt a separated storage and compute architecture. This architecture has many benefits, including cost and operational efficiency that come from the ability to scale compute separately from storage, and ease-of-use and time-to-value that come from relying on virtually limitless shared storage. You can read more about our journey and decisions we made along the way in this AWS case study.

One of our key goals was to combine low query latency required by real-time analytical applications, with this new architecture built on top of Amazon Simple Storage Service (Amazon S3). Over the course of the year, we invested in optimizing the latency and better leveraging the superior throughput provided by Amazon S3 for this use case. We also added sophisticated caching on local disks to help make sure hot data got the fastest responses. This cache went a long way in closing the performance gap, but it was costly and difficult to operate. We found ourselves wishing for a better solution from the shared storage subsystem.

In this post, we describe why the new Amazon S3 Express One Zone storage class, the fastest cloud object storage class that delivers single-digit millisecond request latency, is a game-changer for ClickHouse Cloud. We start with a primer on our cloud architecture, specifically when it comes to how we use Amazon S3 for primary data storage and how our caching layer works. We then show how S3 Express One Zone replaces local disks in our caching architecture to the tune of 65% lower overall TCO, and how we observed performance improvements averaging out at 36% and topping out at 283% improvement for our primary storage when using S3 Express One Zone.

ClickHouse Cloud architecture

When designing ClickHouse Cloud – our cloud-native SaaS ClickHouse offering – the goal was to build a cloud database that any developer or engineer could start using without deep knowledge of analytical databases or how to appropriately size and manage database infrastructure.

We settled on a “shared everything” architecture with “separated storage and compute.” Essentially, this means that storage and compute are decoupled and can be scaled separately. We use Amazon S3 as the primary store for analytical data. This allows us to provide virtually unlimited storage that grows with customers’ data and does not require resharding procedures when scaling up or down. Local disks are only used for caching and temporary storage.

The following diagram represents the logical “shared everything” architecture of ClickHouse Cloud.

Figure 1: ClickHouse Cloud architecture

We picked a separated storage and compute architecture because it greatly simplifies data management, so there is no need to:

• Size your cluster/storage upfront
• Physically shard data
• Rebalance data across nodes as your deployment scales up or down
• Have idle compute resources due to fixed compute/storage ratios

Using Amazon S3 as the storage layer provides:

• Virtually unlimited storage for your data
• Easy vertical and horizontal scaling to adapt to changing workloads
• Lower storage costs as data does not need to be replicated
• Higher storage durability

The additional engineering effort that resulted from taking this path included:

• Object storage latency is higher than local disks, so we had to invest in smart caching (which we describe in the following section), parallel access, and prefetching on top of the object store to make sure queries remain fast.
• Object storage access, especially writes, can be costly at times, so we had to look closely at how many requests we make to write a file and how to optimize that cost. Turns out, these efforts have also helped us improve the overall reliability and performance.

In the rest of this post, we describe two use cases for S3 Express One Zone: using it as the primary data store for all analytical data, and using it for storing cached data.

Use case 1: Storing primary data in S3 Express One Zone

First, we explored the possibilities of using S3 Express One Zone, which can improve data access speeds by 10x and reduce request costs by 50% compared to S3 Standard, as a primary data store.

Why object storage performance is crucial for ClickHouse Cloud

Although we do have a caching layer that provides the best latency and throughput when a query uses hot data, all cold queries hit object storage. However, some users require low latency and high throughput for some percentage of these queries that access un-cached data. Imagine an interactive BI dashboard that is used frequently by many users. Most users typically query data within a recent interval or timeframe, and this recent data is most likely cached due to the frequency of access. However, a small portion of users may build their insights based on less frequently accessed data, and their queries may hit object storage.

This means that having more performant object storage, with lower latency and higher throughput, would enable faster reading from the main storage. As compared to increasing the cache size, which would lower the overall price and TCO, assuming the object storage costs less than the cache storage.

S3 Express One Zone as a primary data store for ClickHouse Cloud

The first thing that we did to test our hypothesis was create a service in ClickHouse Cloud that stores all data in S3 Express One Zone. We then ran a ClickBench performance test, a series of standardized queries that tests key aspects important to an analytical database, from insert throughput to query latency across a range of analytical queries, against ClickHouse Cloud running on top of S3 Express One Zone. On average, S3 Express One Zone gave us a 36% performance boost for querying cold data, a significant improvement. For some queries with aggregations (GROUP BY clause), we got a whopping 283% improvement in performance. These increases in query performance broaden the set of low-latency use cases that ClickHouse Cloud can address for our customers.

One of the ways we plan on leveraging this performance advantage is to create an additional, separately priced ClickHouse Cloud offering, with all analytical data fully hosted on S3 Express One Zone, to cater to the most latency-sensitive workloads. This includes providing access to interactive dashboards to thousands of end users with sub-second response time. As S3 Express One Zone give you the option to co-locate storage and compute resources in a single Availability Zone, when we choose to pursue this the next step would be to identify suitable workloads and datasets that don’t need replication across multiple Availability Zones, as is the typical S3 standard.

Figure 2: ClickHouse Cloud with S3 Express One Zone as primary storage

Use case 2: Storing cached data in S3 Express One Zone

After seeing the performance improvements observed in use case 1, we were excited to try out the S3 Express One Zone storage class in an area we were immediately ready to implement: our caching layer.

How the ClickHouse Cloud caching layer works 

We attach instance store volumes directly to Amazon Elastic Compute Cloud (Amazon EC2) instances for caching data. As a typical ClickHouse Cloud database instance consists of multiple compute nodes in different AZs, we need to keep the cache consistent across these multiple AZs. This means that for every byte of cached data, we need to pay for:

• Instance store * number of nodes in the service
• Object storage PUT and GET operations used to synchronize cache disks in different AZs through object storage

Although our cache system has great performance, there are opportunities to optimize the cost of running the service. For example, we do not need high availability for the cached data. In the event that it is lost, we simply invalidate the cache and pull the data from the object storage. If there was a distributed storage system that could be attached to multiple pods in different AZs, providing comparable latency and throughput as SSDs, then this could give us a big advantage.

S3 Express One Zone as a caching layer for ClickHouse Cloud

The second exciting option for us is to use S3 Express One Zone for caching data, reducing our costs. Instead of using three times (3x) SSD space for cached data, we can use the new storage class to store a single copy of data that is available to all Availability Zone database nodes with the required latency. Additionally, when resizing the database instance and moving database pods to a different machine, we do not lose the cache.

To experiment with this use case, we prototyped a new caching layer on top of S3 Express One Zone:

• We refactored the database code to use the IDisk abstraction that allows using both local disk and Amazon S3 as storage.
• We changed the way we create the segments that are absent in the cache. The new approach is described in the following diagram:

Figure 3: New cache segment creation process, enabled by S3 Express One Zone

As a result of storing only one copy of cached data in the new storage class, instead of storing three copies on SSDs, and not having to incur PUT/GET costs for synchronizing these three copies, we expect up to 65% improvement in TCO for storing the cached data. 

Figure 4: ClickHouse Cloud with S3 Express One Zone as the caching layer

Conclusion

Our tests demonstrated that the S3 Express One Zone storage class, which uses purpose-built hardware and software optimized for low latency, is a game-changer for cloud databases, both in terms of storing data and serving as a cache. We expect it to become an important component of ClickHouse Cloud, enabling us to serve latency-sensitive real-time analytics workloads in a more performant and cost-effective way and giving our cloud database an even deeper competitive advantage. Our customers can query large volumes of data with up to 283% lower query latencies, while continuing to enjoy the benefits of separated storage and compute architecture. Moreover, our business can benefit from up to 65% optimized cached data storage TCO that results from introducing S3 Express One Zone into our architecture.

Get started with ClickHouse Cloud today.

The content and opinions in this post are those of the third-party author and AWS is not responsible for the content or accuracy of this post.