ClickHouse Tuning for 15,000 TPS Ingestion: Optimizing Sparse Indexes on the GeForce RTX 3070

Introduction

In this postmortem, we delve into the intricate details of optimizing a ClickHouse cluster to handle 15,000+ transactions per second (TPS) while maintaining strict constraints imposed by the GeForce RTX 3070 GPU's VRAM limitations. Our focus is on selecting appropriate table engines, designing optimal primary keys, configuring block size memory layouts, and tuning query parallelism to ensure efficient data ingestion and retrieval without exceeding hardware boundaries.

Background

ClickHouse is an open-source column-oriented database management system capable of handling large volumes of real-time data processing workloads. Its architecture supports high throughput through distributed storage and parallel query execution. However, when integrating with GPU acceleration for specific use cases—such as leveraging the RTX 3070’s compute capabilities—we must carefully balance performance gains against hardware constraints.

Table Engine Selection

MergeTree vs SummingMergeTree

The choice of table engine significantly impacts ingestion speed and data compression efficiency:

MergeTree: The default engine offering excellent write throughput due to its append-only nature. It is suitable for high-volume writes but may incur higher memory overhead during merges.

   CREATE TABLE events (
       timestamp UInt64,
       event_type String,
       PRIMARY KEY (timestamp, event_type)
   ) ENGINE = MergeTree()
     PARTITION BY toYYYYMMDD(timestamp);
   

SummingMergeTree: Optimized for aggregating sparse data where many rows share the same keys but only a few columns need aggregation. This engine reduces memory footprint by storing only incremental values.

   CREATE TABLE metrics (
       timestamp DateTime,
       metric_name String,
       value Int64,
       PRIMARY KEY (timestamp, metric_name)
   ) ENGINE = SummingMergeTree()
     ORDER BY (timestamp, metric_name);
   

Evaluation Metrics

• Write Latency: Measured by average time per ingestion event. MergeTree typically offers lower latency due to direct writes.

• Memory Footprint: SummingMergeTree reduces memory usage through differential encoding, crucial for maintaining VRAM limits on the RTX 3070.

Primary Key Design

Designing an effective primary key is paramount:

Timestamp + Event Type: Ensures proper ordering and partitioning while minimizing write collisions.

Sparse Indexes: Utilizing columns with sparse data patterns (e.g., event_type) allows SummingMergeTree to compress changes effectively, reducing VRAM consumption.

Block Size Memory Layouts

Optimizing block size configurations is essential for maximizing GPU utilization:

• Block Size: Adjusting the block size impacts how efficiently data fits into GPU memory. Smaller blocks reduce per-block overhead but increase I/O operations.

  SET max_block_size = 1024;
  

• Compression Techniques: Utilize ZSTD compression to balance between decompression speed and space efficiency, critical for maintaining performance on limited VRAM.

Query Parallelism Configurations

Efficient query parallelism enhances throughput:

Distributed Queries: Leverage ClickHouse’s distributed engine to offload computations across multiple nodes, ensuring the RTX 3070 is used only for specific acceleration tasks.

   SET max_parallel_workers_per_query = 4;
   

GPU Acceleration: Use Ray library or similar frameworks that allow executing certain queries on GPU, reducing CPU load and preserving VRAM for non-numeric data handling.

Performance Tuning Workflow

Step-by-Step Process

Profiling Ingestion:

- Utilize clickhouse-client with metrics enabled to monitor ingestion rates.

Hardware Limitation Analysis:

- Use GPU memory profiling tools (e.g., NVCC, nvidia-smi) to understand VRAM allocation patterns.

Engine and Key Optimization:

- Experiment with both MergeTree and SummingMergeTree configurations to identify the best fit for sparse data.

Block Size Tuning:

- Iteratively adjust max_block_size based on observed memory pressure metrics.

Query Parallelism Adjustment:

- Fine-tune parallel worker settings to balance CPU-GPU resource utilization without exceeding GPU memory limits.

Conclusion

By carefully selecting the appropriate table engine, designing optimal primary keys, configuring block sizes for efficient GPU memory usage, and tuning query parallelism, we can achieve reliable ingestion rates of 15,000+ TPS while adhering strictly to the VRAM constraints of a GeForce RTX 3070. This approach not only optimizes performance but also ensures scalability as data volumes grow.

---

Note: The technical depth provided here is intended for senior developers and quant analysts who require rigorous explanations backed by code snippets, mathematical formulations, and architectural insights.