Optimize ClickHouse performance for large datasets with advanced tuning and memory management

Verify system resources

ClickHouse requires substantial memory and fast storage for optimal performance with large datasets.

free -h
df -h
lscpu | grep "CPU(s):"
cat /proc/meminfo | grep MemTotal

Configure system limits

Increase system limits for ClickHouse processes to handle large datasets and concurrent connections.

clickhouse soft nofile 262144
clickhouse hard nofile 262144
clickhouse soft nproc 32768
clickhouse hard nproc 32768

Optimize kernel parameters

Configure kernel parameters for high-performance database workloads and large memory operations.

vm.max_map_count = 262144
vm.overcommit_memory = 1
vm.swappiness = 1
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.netdev_max_backlog = 30000

sudo sysctl -p /etc/sysctl.d/99-clickhouse.conf

Configure memory allocation limits

Set memory limits to prevent OOM conditions while maximizing performance for large query operations.

0
0
0
0.8
0.2

Optimize buffer pool settings

Configure buffer pools for optimal memory utilization during insert and query operations.

10737418240
8589934592
1073741824
1073741824

Configure background merge settings

Optimize merge operations for large datasets to maintain query performance and storage efficiency.

    10
    300
    600
    1
    100000
    8
    161061273600

Configure insert buffer optimization

Optimize insert performance for high-throughput data ingestion scenarios.

    
        1048576
        1048576
        268435456
        16
        16
        0
        1
        random
    

Configure optimal table engine settings

Create a reference table with optimized MergeTree settings for large dataset performance.

clickhouse-client --query="
CREATE TABLE analytics_optimized (
    event_time DateTime CODEC(Delta(4), LZ4),
    user_id UInt64 CODEC(Delta(8), LZ4),
    event_type LowCardinality(String) CODEC(ZSTD(3)),
    properties String CODEC(ZSTD(9)),
    revenue Decimal64(4) CODEC(Gorilla, LZ4)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
PRIMARY KEY (event_time)
SETTINGS 
    index_granularity = 8192,
    index_granularity_bytes = 10485760,
    enable_mixed_granularity_parts = 1,
    use_minimalistic_part_header_in_zookeeper = 1,
    min_merge_bytes_to_use_direct_io = 10737418240,
    merge_with_ttl_timeout = 86400,
    storage_policy = 'tiered_storage'
"

Configure advanced compression strategies

Set up tiered storage with different compression levels based on data access patterns.

    
        
            /var/lib/clickhouse/hot/
        
        
            /var/lib/clickhouse/warm/
        
        
            /var/lib/clickhouse/cold/
        
    
    
        
            
                
                    hot_disk
                    1073741824
                
                
                    warm_disk
                    10737418240
                
                
                    cold_disk
                
            
            0.2
        
    

Optimize column compression codecs

Configure compression codecs based on data characteristics for maximum storage efficiency.

clickhouse-client --query="
ALTER TABLE analytics_optimized 
MODIFY COLUMN event_time DateTime CODEC(Delta(4), ZSTD(3)),
MODIFY COLUMN user_id UInt64 CODEC(Delta(8), ZSTD(1)),
MODIFY COLUMN properties String CODEC(ZSTD(9))
"

Create data skipping indexes

Implement specialized indexes to dramatically improve query performance on large datasets.

clickhouse-client --query="
ALTER TABLE analytics_optimized 
ADD INDEX user_bloom_idx user_id TYPE bloom_filter(0.01) GRANULARITY 1,
ADD INDEX event_type_set_idx event_type TYPE set(100) GRANULARITY 1,
ADD INDEX event_time_minmax_idx event_time TYPE minmax GRANULARITY 1
"

Configure projection indexes for common queries

Create projection indexes to pre-aggregate common analytical queries for faster execution.

clickhouse-client --query="
ALTER TABLE analytics_optimized 
ADD PROJECTION daily_metrics
(
    SELECT 
        toDate(event_time) AS date,
        event_type,
        count(),
        sum(revenue)
    GROUP BY date, event_type
)
"

Optimize query execution settings

Configure query execution parameters for optimal performance with large datasets.

    
        16
        32000000000
        1
        nearest_hostname
        7200
        30
        34359738368
        34359738368
        100000
        50000000
        1
    

Implement query result caching

Enable query result caching for frequently executed analytical queries.

    5368709120
    1024
    1048576
    30000000

Configure distributed table architecture

Set up distributed tables with optimal sharding strategy for horizontal scaling.

clickhouse-client --query="
CREATE TABLE analytics_distributed AS analytics_optimized 
ENGINE = Distributed('analytics_cluster', 'default', 'analytics_local', rand())
"

Configure cluster topology

Define cluster configuration with proper shard and replica distribution for high availability.

    
        
            1
            true
            
                clickhouse-01
                9000
            
            
                clickhouse-02
                9000
            
        
        
            1
            true
            
                clickhouse-03
                9000
            
            
                clickhouse-04
                9000
            
        
    

Configure ZooKeeper for coordination

Set up ZooKeeper ensemble for distributed coordination and metadata management.

    
        zk1.example.com
        2181
    
    
        zk2.example.com
        2181
    
    
        zk3.example.com
        2181
    
    30000
    10000
    /clickhouse/tables/{shard}
    user:password

Create replicated tables

Configure ReplicatedMergeTree tables for data replication and high availability.

clickhouse-client --query="
CREATE TABLE analytics_local ON CLUSTER analytics_cluster
(
    event_time DateTime CODEC(Delta(4), ZSTD(3)),
    user_id UInt64 CODEC(Delta(8), ZSTD(1)),
    event_type LowCardinality(String) CODEC(ZSTD(3)),
    properties String CODEC(ZSTD(9)),
    revenue Decimal64(4) CODEC(Gorilla, LZ4)
) 
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/analytics', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
PRIMARY KEY (event_time)
SETTINGS 
    index_granularity = 8192,
    replicated_deduplication_window = 1000,
    replicated_deduplication_window_seconds = 604800
"

Optimize distributed query execution

Configure distributed query settings for optimal performance across cluster nodes.

    
        global
        1
        2
        1
        1
        1
        1024
        16
    

Restart ClickHouse with new configuration

Apply all configuration changes by restarting the ClickHouse service.

sudo systemctl restart clickhouse-server
sudo systemctl status clickhouse-server

Test query performance

Execute test queries to verify optimization improvements and measure performance gains.

clickhouse-client --query="
SELECT 
    formatReadableSize(sum(bytes_on_disk)) as compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) as uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(bytes_on_disk), 2) as compression_ratio
FROM system.parts 
WHERE table = 'analytics_optimized' AND active
"

Monitor memory usage

Check memory allocation and usage patterns after optimization.

clickhouse-client --query="
SELECT 
    type,
    formatReadableSize(value) as size
FROM system.asynchronous_metrics 
WHERE metric LIKE '%Memory%' OR metric LIKE '%Cache%'
ORDER BY value DESC
"

Verify cluster health

Check distributed cluster status and replication health.

clickhouse-client --query="SELECT * FROM system.clusters WHERE cluster = 'analytics_cluster'"
clickhouse-client --query="SELECT * FROM system.replicas WHERE table = 'analytics_local'"

Set up performance monitoring

Configure monitoring queries to track performance metrics over time. For comprehensive monitoring setup, see our ClickHouse monitoring with Prometheus and Grafana tutorial.

clickhouse-client --query="
SELECT 
    table,
    formatReadableSize(sum(bytes_on_disk)) as size_on_disk,
    sum(rows) as total_rows,
    count() as parts_count,
    avg(compression_codec) as avg_compression
FROM system.parts 
WHERE active = 1 
GROUP BY table 
ORDER BY sum(bytes_on_disk) DESC
"

Configure automated optimization

Set up automated maintenance tasks for ongoing performance optimization.

# Optimize tables daily at 2 AM
0 2   * clickhouse /usr/bin/clickhouse-client --query="OPTIMIZE TABLE analytics_optimized FINAL"
Update projections weekly
0 3   0 clickhouse /usr/bin/clickhouse-client --query="ALTER TABLE analytics_optimized MATERIALIZE PROJECTION daily_metrics"