Exasol vs ClickHouse Performance Comparison on TPC-H SF100

Overview

This comprehensive benchmark report provides complete reproduction details for performance testing of 4 database systems using the TPC-H benchmark at scale factor 100. All installation steps, configuration parameters, and a self-contained benchmark package are included for independent verification.

Executive Summary

We compared 4 database systems:

Clickhouse

Median Runtime 2504.5ms

Average Runtime 5181.1ms

Fastest Query 161.8ms

Slowest Query 47354.7ms

Clickhouse_tuned

Median Runtime 2785.2ms

Average Runtime 5728.4ms

Fastest Query 166.7ms

Slowest Query 36986.6ms

Exasol

Median Runtime 242.5ms

Average Runtime 307.6ms

Fastest Query 26.5ms

Slowest Query 968.3ms

Key Findings

exasol was the fastest overall with 242.5ms median runtime
clickhouse_tuned was 11.5x slower
Tested 462 total query executions

Performance Visualizations

Systems Under Test

Exasol 2025.1.0

Software Configuration

Database	exasol 2025.1.0
Setup Method	installer

Hardware Specifications

Cloud Provider	AWS
Region	eu-west-1
Instance Type	r6id.8xlarge
CPU	Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
CPU Cores	32 vCPUs
Memory	247.7GB RAM

Clickhouse 25.9.4.58

Software Configuration

Database	clickhouse 25.9.4.58
Setup Method	native
Data Directory	`/data/clickhouse`

Hardware Specifications

Cloud Provider	AWS
Region	eu-west-1
Instance Type	r6id.8xlarge
CPU	Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
CPU Cores	32 vCPUs
Memory	247.7GB RAM

Clickhouse_tuned 25.9.4.58

Software Configuration

Database	clickhouse 25.9.4.58
Setup Method	native
Data Directory	`/data/clickhouse`

Hardware Specifications

Cloud Provider	AWS
Region	eu-west-1
Instance Type	r6id.8xlarge
CPU	Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
CPU Cores	32 vCPUs
Memory	247.7GB RAM

Clickhouse_stat 25.9.4.58

Software Configuration

Database	clickhouse 25.9.4.58
Setup Method	native
Data Directory	`/data/clickhouse`

Hardware Specifications

Cloud Provider	AWS
Region	eu-west-1
Instance Type	r6id.8xlarge
CPU	Intel(R) Xeon(R) Platinum 8375C CPU @ 2.90GHz
CPU Cores	32 vCPUs
Memory	247.7GB RAM

Workload: TPCH (Scale Factor 100)

TPC-H is a decision support benchmark that consists of a suite of business-oriented ad-hoc queries and concurrent data modifications. The queries and data are chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions. The benchmark models a wholesale supplier with a distribution network across multiple regions.

Key Characteristics

22 complex analytical queries
8 tables with varying cardinality
Queries involve multi-table joins, aggregations, and subqueries
Simulates real-world business intelligence workloads

Database Schema (8 Tables)

Nation

Nations/countries (25 rows)

Rows: ~2500

Columns (4)

n_nationkey
n_name
n_regionkey
n_comment

Foreign key to region

Region

Geographic regions (5 rows)

Rows: ~500

Columns (3)

r_regionkey
r_name
r_comment

Part

Parts/products catalog

Rows: ~20000000

Columns (9)

p_partkey
p_name
p_mfgr
p_brand
p_type
p_size
p_container
p_retailprice
p_comment

Supplier

Suppliers/vendors

Rows: ~1000000

Columns (7)

s_suppkey
s_name
s_address
s_nationkey
s_phone
s_acctbal
s_comment

Foreign key to nation

Partsupp

Parts supplied by suppliers (association table)

Rows: ~80000000

Columns (5)

ps_partkey
ps_suppkey
ps_availqty
ps_supplycost
ps_comment

Foreign keys to part and supplier

Customer

Customers/buyers

Rows: ~15000000

Columns (8)

c_custkey
c_name
c_address
c_nationkey
c_phone
c_acctbal
c_mktsegment
c_comment

Foreign key to nation

Orders

Customer orders

Rows: ~150000000

Columns (9)

o_orderkey
o_custkey
o_orderstatus
o_totalprice
o_orderdate
o_orderpriority
o_clerk
o_shippriority
o_comment

Foreign key to customer

Lineitem

Order line items (largest table)

Rows: ~600121500

Columns (16)

l_orderkey
l_partkey
l_suppkey
l_linenumber
l_quantity
l_extendedprice
l_discount
l_tax
l_returnflag
l_linestatus
l_shipdate
l_commitdate
l_receiptdate
l_shipinstruct
l_shipmode
l_comment

Foreign keys to orders, part, supplier, partsupp

Table Definitions & Optimizations

Each database system uses optimized DDL tailored to its architecture and query optimizer.

Exasol - DDL & Optimization

📋 Table Creation

Create 8 TPC-H tables with system-optimized data types

-- TPC-H Table Creation Script
-- Creates all 8 TPC-H tables with appropriate data types and storage options for each database system

-- Exasol table creation

-- Nation table
CREATE OR REPLACE TABLE benchmark.nation (
    n_nationkey DEC(11),
    n_name CHAR(25) CHARACTER SET ASCII,
    n_regionkey DEC(11),
    n_comment VARCHAR(152) CHARACTER SET ASCII
);

-- Region table
CREATE OR REPLACE TABLE benchmark.region (
    r_regionkey DEC(11),
    r_name CHAR(25) CHARACTER SET ASCII,
    r_comment VARCHAR(152) CHARACTER SET ASCII
);

-- Part table
CREATE OR REPLACE TABLE benchmark.part (
    p_partkey DEC(11),
    p_name VARCHAR(55) CHARACTER SET ASCII,
    p_mfgr CHAR(25) CHARACTER SET ASCII,
    p_brand CHAR(10) CHARACTER SET ASCII,
    p_type VARCHAR(25) CHARACTER SET ASCII,
    p_size DEC(10),
    p_container CHAR(10) CHARACTER SET ASCII,
    p_retailprice DECIMAL(12,2),
    p_comment VARCHAR(23) CHARACTER SET ASCII,
    DISTRIBUTE BY p_partkey
);

-- Supplier table
CREATE OR REPLACE TABLE benchmark.supplier (
    s_suppkey DEC(11),
    s_name CHAR(25) CHARACTER SET ASCII,
    s_address VARCHAR(40) CHARACTER SET ASCII,
    s_nationkey DEC(11),
    s_phone CHAR(15) CHARACTER SET ASCII,
    s_acctbal DECIMAL(12,2),
    s_comment VARCHAR(101) CHARACTER SET ASCII,
    DISTRIBUTE BY s_suppkey
);

-- Partsupp table
CREATE OR REPLACE TABLE benchmark.partsupp (
    ps_partkey DEC(11),
    ps_suppkey DEC(11),
    ps_availqty DEC(10),
    ps_supplycost DECIMAL(12,2),
    ps_comment VARCHAR(199) CHARACTER SET ASCII,
    DISTRIBUTE BY ps_partkey
);

-- Customer table
CREATE OR REPLACE TABLE benchmark.customer (
    c_custkey DEC(11),
    c_name VARCHAR(25) CHARACTER SET ASCII,
    c_address VARCHAR(40) CHARACTER SET ASCII,
    c_nationkey DEC(11),
    c_phone CHAR(15) CHARACTER SET ASCII,
    c_acctbal DECIMAL(12,2),
    c_mktsegment CHAR(10) CHARACTER SET ASCII,
    c_comment VARCHAR(117) CHARACTER SET ASCII,
    DISTRIBUTE BY c_custkey
);

-- Orders table
CREATE OR REPLACE TABLE benchmark.orders (
    o_orderkey DEC(12),
    o_custkey DEC(11),
    o_orderstatus CHAR(1) CHARACTER SET ASCII,
    o_totalprice DECIMAL(12,2),
    o_orderdate DATE,
    o_orderpriority CHAR(15) CHARACTER SET ASCII,
    o_clerk CHAR(15) CHARACTER SET ASCII,
    o_shippriority DEC(10),
    o_comment VARCHAR(79) CHARACTER SET ASCII,
    DISTRIBUTE BY o_custkey
);

-- Lineitem table
CREATE OR REPLACE TABLE benchmark.lineitem (
    l_orderkey DEC(12),
    l_partkey DEC(11),
    l_suppkey DEC(11),
    l_linenumber DEC(10),
    l_quantity DECIMAL(12,2),
    l_extendedprice DECIMAL(12,2),
    l_discount DECIMAL(12,2),
    l_tax DECIMAL(12,2),
    l_returnflag CHAR(1) CHARACTER SET ASCII,
    l_linestatus CHAR(1) CHARACTER SET ASCII,
    l_shipdate DATE,
    l_commitdate DATE,
    l_receiptdate DATE,
    l_shipinstruct CHAR(25) CHARACTER SET ASCII,
    l_shipmode CHAR(10) CHARACTER SET ASCII,
    l_comment VARCHAR(44) CHARACTER SET ASCII,
    DISTRIBUTE BY l_orderkey
);

🔍 Index Creation

Create indexes on foreign keys and join columns

-- TPC-H Index Creation Script
-- Creates indexes to optimize query performance for both database systems

-- Create local indexes for Exasol
ENFORCE LOCAL INDEX ON benchmark.lineitem (l_suppkey);
ENFORCE LOCAL INDEX ON benchmark.lineitem (l_partkey, l_suppkey);
ENFORCE LOCAL INDEX ON benchmark.lineitem (l_partkey);
ENFORCE LOCAL INDEX ON benchmark.lineitem (l_orderkey);
ENFORCE LOCAL INDEX ON benchmark.nation (n_nationkey);
ENFORCE LOCAL INDEX ON benchmark.region (r_regionkey);
ENFORCE LOCAL INDEX ON benchmark.supplier (s_suppkey);
ENFORCE LOCAL INDEX ON benchmark.supplier (s_nationkey);
ENFORCE LOCAL INDEX ON benchmark.customer (c_custkey);
ENFORCE LOCAL INDEX ON benchmark.customer (c_nationkey);
ENFORCE LOCAL INDEX ON benchmark.part (p_partkey);
ENFORCE LOCAL INDEX ON benchmark.partsupp (ps_partkey, ps_suppkey);
ENFORCE LOCAL INDEX ON benchmark.partsupp (ps_partkey);
ENFORCE LOCAL INDEX ON benchmark.partsupp (ps_suppkey);
ENFORCE LOCAL INDEX ON benchmark.orders (o_orderkey);
ENFORCE LOCAL INDEX ON benchmark.orders (o_custkey);

COMMIT;

📊 Table Analysis & Optimization

Update database statistics for query optimizer

-- TPC-H Table Analysis Script
-- Analyzes tables and updates statistics for optimal query performance

-- Analyze database to estimate statistics for query optimization
ANALYZE DATABASE ESTIMATE STATISTICS;
COMMIT;

Clickhouse - DDL & Optimization

📋 Table Creation

Create 8 TPC-H tables with system-optimized data types

-- TPC-H Table Creation Script
-- Creates all 8 TPC-H tables with appropriate data types and storage options for each database system

-- ClickHouse table creation

-- Nation table
CREATE OR REPLACE TABLE benchmark.nation (
    n_nationkey INTEGER NOT NULL,
    n_name CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY n_nationkey;

-- Region table
CREATE OR REPLACE TABLE benchmark.region (
    r_regionkey INTEGER NOT NULL,
    r_name CHAR(25) NOT NULL,
    r_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY r_regionkey;

-- Part table
CREATE OR REPLACE TABLE benchmark.part (
    p_partkey INTEGER NOT NULL,
    p_name VARCHAR(55) NOT NULL,
    p_mfgr CHAR(25) NOT NULL,
    p_brand CHAR(10) NOT NULL,
    p_type VARCHAR(25) NOT NULL,
    p_size INTEGER NOT NULL,
    p_container CHAR(10) NOT NULL,
    p_retailprice DECIMAL(15,2) NOT NULL,
    p_comment VARCHAR(23) NOT NULL
) ENGINE MergeTree() ORDER BY p_partkey;

-- Supplier table
CREATE OR REPLACE TABLE benchmark.supplier (
    s_suppkey INTEGER NOT NULL,
    s_name CHAR(25) NOT NULL,
    s_address VARCHAR(40) NOT NULL,
    s_nationkey INTEGER NOT NULL,
    s_phone CHAR(15) NOT NULL,
    s_acctbal DECIMAL(15,2) NOT NULL,
    s_comment VARCHAR(101) NOT NULL
) ENGINE MergeTree() ORDER BY s_suppkey;

-- Partsupp table
CREATE OR REPLACE TABLE benchmark.partsupp (
    ps_partkey INTEGER NOT NULL,
    ps_suppkey INTEGER NOT NULL,
    ps_availqty INTEGER NOT NULL,
    ps_supplycost DECIMAL(15,2) NOT NULL,
    ps_comment VARCHAR(199) NOT NULL
) ENGINE MergeTree() ORDER BY (ps_partkey, ps_suppkey);

-- Customer table
CREATE OR REPLACE TABLE benchmark.customer (
    c_custkey INTEGER NOT NULL,
    c_name VARCHAR(25) NOT NULL,
    c_address VARCHAR(40) NOT NULL,
    c_nationkey INTEGER NOT NULL,
    c_phone CHAR(15) NOT NULL,
    c_acctbal DECIMAL(15,2) NOT NULL,
    c_mktsegment CHAR(10) NOT NULL,
    c_comment VARCHAR(117) NOT NULL
) ENGINE MergeTree() ORDER BY (c_mktsegment, c_custkey);

-- Orders table
CREATE OR REPLACE TABLE benchmark.orders (
    o_orderkey BIGINT NOT NULL,
    o_custkey INTEGER NOT NULL,
    o_orderstatus CHAR(1) NOT NULL,
    o_totalprice DECIMAL(15,2) NOT NULL,
    o_orderdate DATE NOT NULL,
    o_orderpriority CHAR(15) NOT NULL,
    o_clerk CHAR(15) NOT NULL,
    o_shippriority INTEGER NOT NULL,
    o_comment VARCHAR(79) NOT NULL
) ENGINE MergeTree() ORDER BY (o_orderdate, o_orderkey);

-- Lineitem table
CREATE OR REPLACE TABLE benchmark.lineitem (
    l_orderkey BIGINT NOT NULL,
    l_partkey INTEGER NOT NULL,
    l_suppkey INTEGER NOT NULL,
    l_linenumber INTEGER NOT NULL,
    l_quantity DECIMAL(15,2) NOT NULL,
    l_extendedprice DECIMAL(15,2) NOT NULL,
    l_discount DECIMAL(15,2) NOT NULL,
    l_tax DECIMAL(15,2) NOT NULL,
    l_returnflag CHAR(1) NOT NULL,
    l_linestatus CHAR(1) NOT NULL,
    l_shipdate DATE NOT NULL,
    l_commitdate DATE NOT NULL,
    l_receiptdate DATE NOT NULL,
    l_shipinstruct CHAR(25) NOT NULL,
    l_shipmode CHAR(10) NOT NULL,
    l_comment VARCHAR(44) NOT NULL
) ENGINE MergeTree() ORDER BY (l_shipdate, l_orderkey, l_partkey);

🔍 Index Creation

Create indexes on foreign keys and join columns

-- TPC-H Index Creation Script
-- Creates indexes to optimize query performance for both database systems

-- ClickHouse Skip Indexes: DISABLED for TPC-H Analytical Workload
--
-- Analysis showed that skip indexes cause net performance REGRESSION for TPC-H:
-- - 15/22 queries became slower (1-7% regression)
-- - Only 2/22 queries improved (Q20: -9.5%, Q12: -1.3%)
-- - Overall net negative impact on workload
--
-- Root causes:
-- 1. Redundancy: Many skip indexes duplicate MergeTree primary key (ORDER BY)
--    Example: l_orderkey bloom_filter is redundant with ORDER BY (l_shipdate, l_orderkey, l_partkey)
-- 2. Low selectivity: Indexes on low-cardinality columns (o_orderstatus: 3 values) add overhead
-- 3. Workload mismatch: TPC-H = analytical scans; skip indexes = OLTP point queries
-- 4. Granularity overhead: GRANULARITY 4 adds index maintenance cost without benefit
--
-- Decision: Rely on MergeTree primary keys and OPTIMIZE TABLE compression for best performance
-- For analytical workloads, well-designed ORDER BY clauses are more effective than skip indexes

SELECT 'ClickHouse indexing: Using MergeTree primary keys only (skip indexes disabled for analytical workload)';

📊 Table Analysis & Optimization

Update database statistics for query optimizer

-- TPC-H Table Analysis Script
-- Analyzes tables and updates statistics for optimal query performance

-- ClickHouse Table Optimization
-- Statistics are disabled - using basic optimization only

-- Optimize tables to merge data parts and improve compression
-- OPTIMIZE TABLE FINAL merges all data parts into a single optimized part
-- This improves query performance by reducing the number of parts to scan
OPTIMIZE TABLE benchmark.nation FINAL;
OPTIMIZE TABLE benchmark.region FINAL;
OPTIMIZE TABLE benchmark.part FINAL;
OPTIMIZE TABLE benchmark.supplier FINAL;
OPTIMIZE TABLE benchmark.partsupp FINAL;
OPTIMIZE TABLE benchmark.customer FINAL;
OPTIMIZE TABLE benchmark.orders FINAL;
OPTIMIZE TABLE benchmark.lineitem FINAL;

SELECT 'ClickHouse tables optimized (statistics disabled)';

Clickhouse_tuned - DDL & Optimization

📋 Table Creation

Create 8 TPC-H tables with system-optimized data types

-- TPC-H Table Creation Script
-- Creates all 8 TPC-H tables with appropriate data types and storage options for each database system

-- ClickHouse table creation

-- Nation table
CREATE OR REPLACE TABLE benchmark.nation (
    n_nationkey INTEGER NOT NULL,
    n_name CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY n_nationkey;

-- Region table
CREATE OR REPLACE TABLE benchmark.region (
    r_regionkey INTEGER NOT NULL,
    r_name CHAR(25) NOT NULL,
    r_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY r_regionkey;

-- Part table
CREATE OR REPLACE TABLE benchmark.part (
    p_partkey INTEGER NOT NULL,
    p_name VARCHAR(55) NOT NULL,
    p_mfgr CHAR(25) NOT NULL,
    p_brand CHAR(10) NOT NULL,
    p_type VARCHAR(25) NOT NULL,
    p_size INTEGER NOT NULL,
    p_container CHAR(10) NOT NULL,
    p_retailprice DECIMAL(15,2) NOT NULL,
    p_comment VARCHAR(23) NOT NULL
) ENGINE MergeTree() ORDER BY p_partkey;

-- Supplier table
CREATE OR REPLACE TABLE benchmark.supplier (
    s_suppkey INTEGER NOT NULL,
    s_name CHAR(25) NOT NULL,
    s_address VARCHAR(40) NOT NULL,
    s_nationkey INTEGER NOT NULL,
    s_phone CHAR(15) NOT NULL,
    s_acctbal DECIMAL(15,2) NOT NULL,
    s_comment VARCHAR(101) NOT NULL
) ENGINE MergeTree() ORDER BY s_suppkey;

-- Partsupp table
CREATE OR REPLACE TABLE benchmark.partsupp (
    ps_partkey INTEGER NOT NULL,
    ps_suppkey INTEGER NOT NULL,
    ps_availqty INTEGER NOT NULL,
    ps_supplycost DECIMAL(15,2) NOT NULL,
    ps_comment VARCHAR(199) NOT NULL
) ENGINE MergeTree() ORDER BY (ps_partkey, ps_suppkey);

-- Customer table
CREATE OR REPLACE TABLE benchmark.customer (
    c_custkey INTEGER NOT NULL,
    c_name VARCHAR(25) NOT NULL,
    c_address VARCHAR(40) NOT NULL,
    c_nationkey INTEGER NOT NULL,
    c_phone CHAR(15) NOT NULL,
    c_acctbal DECIMAL(15,2) NOT NULL,
    c_mktsegment CHAR(10) NOT NULL,
    c_comment VARCHAR(117) NOT NULL
) ENGINE MergeTree() ORDER BY (c_mktsegment, c_custkey);

-- Orders table
CREATE OR REPLACE TABLE benchmark.orders (
    o_orderkey BIGINT NOT NULL,
    o_custkey INTEGER NOT NULL,
    o_orderstatus CHAR(1) NOT NULL,
    o_totalprice DECIMAL(15,2) NOT NULL,
    o_orderdate DATE NOT NULL,
    o_orderpriority CHAR(15) NOT NULL,
    o_clerk CHAR(15) NOT NULL,
    o_shippriority INTEGER NOT NULL,
    o_comment VARCHAR(79) NOT NULL
) ENGINE MergeTree() ORDER BY (o_orderdate, o_orderkey);

-- Lineitem table
CREATE OR REPLACE TABLE benchmark.lineitem (
    l_orderkey BIGINT NOT NULL,
    l_partkey INTEGER NOT NULL,
    l_suppkey INTEGER NOT NULL,
    l_linenumber INTEGER NOT NULL,
    l_quantity DECIMAL(15,2) NOT NULL,
    l_extendedprice DECIMAL(15,2) NOT NULL,
    l_discount DECIMAL(15,2) NOT NULL,
    l_tax DECIMAL(15,2) NOT NULL,
    l_returnflag CHAR(1) NOT NULL,
    l_linestatus CHAR(1) NOT NULL,
    l_shipdate DATE NOT NULL,
    l_commitdate DATE NOT NULL,
    l_receiptdate DATE NOT NULL,
    l_shipinstruct CHAR(25) NOT NULL,
    l_shipmode CHAR(10) NOT NULL,
    l_comment VARCHAR(44) NOT NULL
) ENGINE MergeTree() ORDER BY (l_shipdate, l_orderkey, l_partkey);

🔍 Index Creation

Create indexes on foreign keys and join columns

-- TPC-H Index Creation Script
-- Creates indexes to optimize query performance for both database systems

-- ClickHouse Skip Indexes: DISABLED for TPC-H Analytical Workload
--
-- Analysis showed that skip indexes cause net performance REGRESSION for TPC-H:
-- - 15/22 queries became slower (1-7% regression)
-- - Only 2/22 queries improved (Q20: -9.5%, Q12: -1.3%)
-- - Overall net negative impact on workload
--
-- Root causes:
-- 1. Redundancy: Many skip indexes duplicate MergeTree primary key (ORDER BY)
--    Example: l_orderkey bloom_filter is redundant with ORDER BY (l_shipdate, l_orderkey, l_partkey)
-- 2. Low selectivity: Indexes on low-cardinality columns (o_orderstatus: 3 values) add overhead
-- 3. Workload mismatch: TPC-H = analytical scans; skip indexes = OLTP point queries
-- 4. Granularity overhead: GRANULARITY 4 adds index maintenance cost without benefit
--
-- Decision: Rely on MergeTree primary keys and OPTIMIZE TABLE compression for best performance
-- For analytical workloads, well-designed ORDER BY clauses are more effective than skip indexes

SELECT 'ClickHouse indexing: Using MergeTree primary keys only (skip indexes disabled for analytical workload)';

📊 Table Analysis & Optimization

Update database statistics for query optimizer

-- TPC-H Table Analysis Script
-- Analyzes tables and updates statistics for optimal query performance

-- ClickHouse Table Optimization
-- Statistics are disabled - using basic optimization only

-- Optimize tables to merge data parts and improve compression
-- OPTIMIZE TABLE FINAL merges all data parts into a single optimized part
-- This improves query performance by reducing the number of parts to scan
OPTIMIZE TABLE benchmark.nation FINAL;
OPTIMIZE TABLE benchmark.region FINAL;
OPTIMIZE TABLE benchmark.part FINAL;
OPTIMIZE TABLE benchmark.supplier FINAL;
OPTIMIZE TABLE benchmark.partsupp FINAL;
OPTIMIZE TABLE benchmark.customer FINAL;
OPTIMIZE TABLE benchmark.orders FINAL;
OPTIMIZE TABLE benchmark.lineitem FINAL;

SELECT 'ClickHouse tables optimized (statistics disabled)';

Clickhouse_stat - DDL & Optimization

📋 Table Creation

Create 8 TPC-H tables with system-optimized data types

-- TPC-H Table Creation Script
-- Creates all 8 TPC-H tables with appropriate data types and storage options for each database system

-- ClickHouse table creation

-- Nation table
CREATE OR REPLACE TABLE benchmark.nation (
    n_nationkey INTEGER NOT NULL,
    n_name CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY n_nationkey;

-- Region table
CREATE OR REPLACE TABLE benchmark.region (
    r_regionkey INTEGER NOT NULL,
    r_name CHAR(25) NOT NULL,
    r_comment VARCHAR(152)
) ENGINE MergeTree() ORDER BY r_regionkey;

-- Part table
CREATE OR REPLACE TABLE benchmark.part (
    p_partkey INTEGER NOT NULL,
    p_name VARCHAR(55) NOT NULL,
    p_mfgr CHAR(25) NOT NULL,
    p_brand CHAR(10) NOT NULL,
    p_type VARCHAR(25) NOT NULL,
    p_size INTEGER NOT NULL,
    p_container CHAR(10) NOT NULL,
    p_retailprice DECIMAL(15,2) NOT NULL,
    p_comment VARCHAR(23) NOT NULL
) ENGINE MergeTree() ORDER BY p_partkey;

-- Supplier table
CREATE OR REPLACE TABLE benchmark.supplier (
    s_suppkey INTEGER NOT NULL,
    s_name CHAR(25) NOT NULL,
    s_address VARCHAR(40) NOT NULL,
    s_nationkey INTEGER NOT NULL,
    s_phone CHAR(15) NOT NULL,
    s_acctbal DECIMAL(15,2) NOT NULL,
    s_comment VARCHAR(101) NOT NULL
) ENGINE MergeTree() ORDER BY s_suppkey;

-- Partsupp table
CREATE OR REPLACE TABLE benchmark.partsupp (
    ps_partkey INTEGER NOT NULL,
    ps_suppkey INTEGER NOT NULL,
    ps_availqty INTEGER NOT NULL,
    ps_supplycost DECIMAL(15,2) NOT NULL,
    ps_comment VARCHAR(199) NOT NULL
) ENGINE MergeTree() ORDER BY (ps_partkey, ps_suppkey);

-- Customer table
CREATE OR REPLACE TABLE benchmark.customer (
    c_custkey INTEGER NOT NULL,
    c_name VARCHAR(25) NOT NULL,
    c_address VARCHAR(40) NOT NULL,
    c_nationkey INTEGER NOT NULL,
    c_phone CHAR(15) NOT NULL,
    c_acctbal DECIMAL(15,2) NOT NULL,
    c_mktsegment CHAR(10) NOT NULL,
    c_comment VARCHAR(117) NOT NULL
) ENGINE MergeTree() ORDER BY (c_mktsegment, c_custkey);

-- Orders table
CREATE OR REPLACE TABLE benchmark.orders (
    o_orderkey BIGINT NOT NULL,
    o_custkey INTEGER NOT NULL,
    o_orderstatus CHAR(1) NOT NULL,
    o_totalprice DECIMAL(15,2) NOT NULL,
    o_orderdate DATE NOT NULL,
    o_orderpriority CHAR(15) NOT NULL,
    o_clerk CHAR(15) NOT NULL,
    o_shippriority INTEGER NOT NULL,
    o_comment VARCHAR(79) NOT NULL
) ENGINE MergeTree() ORDER BY (o_orderdate, o_orderkey);

-- Lineitem table
CREATE OR REPLACE TABLE benchmark.lineitem (
    l_orderkey BIGINT NOT NULL,
    l_partkey INTEGER NOT NULL,
    l_suppkey INTEGER NOT NULL,
    l_linenumber INTEGER NOT NULL,
    l_quantity DECIMAL(15,2) NOT NULL,
    l_extendedprice DECIMAL(15,2) NOT NULL,
    l_discount DECIMAL(15,2) NOT NULL,
    l_tax DECIMAL(15,2) NOT NULL,
    l_returnflag CHAR(1) NOT NULL,
    l_linestatus CHAR(1) NOT NULL,
    l_shipdate DATE NOT NULL,
    l_commitdate DATE NOT NULL,
    l_receiptdate DATE NOT NULL,
    l_shipinstruct CHAR(25) NOT NULL,
    l_shipmode CHAR(10) NOT NULL,
    l_comment VARCHAR(44) NOT NULL
) ENGINE MergeTree() ORDER BY (l_shipdate, l_orderkey, l_partkey);

🔍 Index Creation

Create indexes on foreign keys and join columns

-- TPC-H Index Creation Script
-- Creates indexes to optimize query performance for both database systems

-- ClickHouse Skip Indexes: DISABLED for TPC-H Analytical Workload
--
-- Analysis showed that skip indexes cause net performance REGRESSION for TPC-H:
-- - 15/22 queries became slower (1-7% regression)
-- - Only 2/22 queries improved (Q20: -9.5%, Q12: -1.3%)
-- - Overall net negative impact on workload
--
-- Root causes:
-- 1. Redundancy: Many skip indexes duplicate MergeTree primary key (ORDER BY)
--    Example: l_orderkey bloom_filter is redundant with ORDER BY (l_shipdate, l_orderkey, l_partkey)
-- 2. Low selectivity: Indexes on low-cardinality columns (o_orderstatus: 3 values) add overhead
-- 3. Workload mismatch: TPC-H = analytical scans; skip indexes = OLTP point queries
-- 4. Granularity overhead: GRANULARITY 4 adds index maintenance cost without benefit
--
-- Decision: Rely on MergeTree primary keys and OPTIMIZE TABLE compression for best performance
-- For analytical workloads, well-designed ORDER BY clauses are more effective than skip indexes

SELECT 'ClickHouse indexing: Using MergeTree primary keys only (skip indexes disabled for analytical workload)';

📊 Table Analysis & Optimization

Update database statistics for query optimizer

-- TPC-H Table Analysis Script
-- Analyzes tables and updates statistics for optimal query performance

-- ClickHouse Table Optimization
-- Statistics are enabled - collect statistics for query optimizer (ClickHouse 24.6+)
-- Statistics help the query optimizer make better join order decisions

-- Add statistics for join optimization (most critical columns)

-- Lineitem: Most critical table for complex queries
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_orderkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_partkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_suppkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_quantity TYPE countmin, tdigest;
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_receiptdate TYPE countmin;
ALTER TABLE benchmark.lineitem ADD STATISTICS IF NOT EXISTS l_commitdate TYPE countmin;

-- Orders: Critical for joins
ALTER TABLE benchmark.orders ADD STATISTICS IF NOT EXISTS o_orderkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.orders ADD STATISTICS IF NOT EXISTS o_custkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.orders ADD STATISTICS IF NOT EXISTS o_orderdate TYPE countmin, tdigest;
ALTER TABLE benchmark.orders ADD STATISTICS IF NOT EXISTS o_orderstatus TYPE countmin, uniq;

-- Part: Used in filtered joins
ALTER TABLE benchmark.part ADD STATISTICS IF NOT EXISTS p_partkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.part ADD STATISTICS IF NOT EXISTS p_type TYPE countmin, uniq;
ALTER TABLE benchmark.part ADD STATISTICS IF NOT EXISTS p_brand TYPE countmin, uniq;
ALTER TABLE benchmark.part ADD STATISTICS IF NOT EXISTS p_container TYPE countmin, uniq;

-- Supplier: Join and filter columns
ALTER TABLE benchmark.supplier ADD STATISTICS IF NOT EXISTS s_suppkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.supplier ADD STATISTICS IF NOT EXISTS s_nationkey TYPE countmin, tdigest, uniq;

-- Customer: Join columns
ALTER TABLE benchmark.customer ADD STATISTICS IF NOT EXISTS c_custkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.customer ADD STATISTICS IF NOT EXISTS c_nationkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.customer ADD STATISTICS IF NOT EXISTS c_mktsegment TYPE countmin, uniq;

-- Partsupp: Join columns
ALTER TABLE benchmark.partsupp ADD STATISTICS IF NOT EXISTS ps_partkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.partsupp ADD STATISTICS IF NOT EXISTS ps_suppkey TYPE countmin, tdigest, uniq;

-- Nation: Small table but frequently joined
ALTER TABLE benchmark.nation ADD STATISTICS IF NOT EXISTS n_nationkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.nation ADD STATISTICS IF NOT EXISTS n_name TYPE countmin, uniq;
ALTER TABLE benchmark.nation ADD STATISTICS IF NOT EXISTS n_regionkey TYPE countmin, tdigest, uniq;

-- Region: Small table
ALTER TABLE benchmark.region ADD STATISTICS IF NOT EXISTS r_regionkey TYPE countmin, tdigest, uniq;
ALTER TABLE benchmark.region ADD STATISTICS IF NOT EXISTS r_name TYPE countmin, uniq;

-- Materialize statistics
ALTER TABLE benchmark.lineitem MATERIALIZE STATISTICS l_orderkey, l_partkey, l_suppkey, l_quantity, l_receiptdate, l_commitdate;
ALTER TABLE benchmark.orders MATERIALIZE STATISTICS o_orderkey, o_custkey, o_orderdate, o_orderstatus;
ALTER TABLE benchmark.part MATERIALIZE STATISTICS p_partkey, p_type, p_brand, p_container;
ALTER TABLE benchmark.supplier MATERIALIZE STATISTICS s_suppkey, s_nationkey;
ALTER TABLE benchmark.customer MATERIALIZE STATISTICS c_custkey, c_nationkey, c_mktsegment;
ALTER TABLE benchmark.partsupp MATERIALIZE STATISTICS ps_partkey, ps_suppkey;
ALTER TABLE benchmark.nation MATERIALIZE STATISTICS n_nationkey, n_name, n_regionkey;
ALTER TABLE benchmark.region MATERIALIZE STATISTICS r_regionkey, r_name;

-- Optimize tables to merge data parts and improve compression
-- OPTIMIZE TABLE FINAL merges all data parts into a single optimized part
-- This improves query performance by reducing the number of parts to scan
OPTIMIZE TABLE benchmark.nation FINAL;
OPTIMIZE TABLE benchmark.region FINAL;
OPTIMIZE TABLE benchmark.part FINAL;
OPTIMIZE TABLE benchmark.supplier FINAL;
OPTIMIZE TABLE benchmark.partsupp FINAL;
OPTIMIZE TABLE benchmark.customer FINAL;
OPTIMIZE TABLE benchmark.orders FINAL;
OPTIMIZE TABLE benchmark.lineitem FINAL;

SELECT 'ClickHouse statistics collected and tables optimized';

Performance Results

Detailed Performance Summary

query	system	warmup	runs	median_ms	mean_ms	std_ms	min_ms	max_ms
Q01	clickhouse	2,482.60	7	2,462.00	2,466.20	11.90	2,456.10	2,491.70
Q01	clickhouse_tuned	2,540.80	7	2,640.60	2,654.90	74.80	2,551.80	2,739.10
Q01	exasol	802.30	7	801.10	800.10	4.60	794.70	806.60
Q02	clickhouse	1,241.40	7	1,052.70	1,066.90	36.10	1,039.00	1,144.40
Q02	clickhouse_tuned	1,353.00	7	1,120.50	1,121.60	26.50	1,088.50	1,172.00
Q02	exasol	93.70	7	78.80	78.80	2.40	75.20	82.10
Q03	clickhouse	4,117.10	7	3,986.50	3,981.10	20.80	3,953.80	4,017.30
Q03	clickhouse_tuned	4,507.20	7	4,446.60	4,424.50	104.70	4,310.30	4,613.50
Q03	exasol	341.80	7	346.50	344.90	5.00	339.40	353.00
Q04	clickhouse	3,293.60	7	2,547.90	2,583.80	95.20	2,517.30	2,792.10
Q04	clickhouse_tuned	16,957.00	7	14,560.70	14,683.20	272.30	14,433.40	15,122.60
Q04	exasol	64.70	7	63.20	63.40	0.40	62.90	64.00
Q05	clickhouse	9,505.40	7	8,776.70	8,751.00	115.00	8,590.80	8,923.60
Q05	clickhouse_tuned	9,433.20	7	9,282.50	9,191.10	403.50	8,309.80	9,537.20
Q05	exasol	273.40	7	208.90	209.30	1.30	207.70	211.80
Q06	clickhouse	940.20	7	163.10	164.30	2.60	161.80	167.90
Q06	clickhouse_tuned	1,050.50	7	170.00	171.20	4.20	166.70	179.30
Q06	exasol	43.80	7	43.70	43.60	0.30	43.20	43.90
Q07	clickhouse	6,372.70	7	4,886.40	4,882.50	23.00	4,849.50	4,915.30
Q07	clickhouse_tuned	6,436.70	7	5,042.20	5,054.30	38.40	5,011.70	5,116.40
Q07	exasol	276.00	7	288.60	286.10	11.30	273.20	303.40
Q08	clickhouse	7,063.30	7	7,765.60	7,734.80	249.10	7,213.60	7,979.80
Q08	clickhouse_tuned	7,309.40	7	8,082.30	8,094.10	84.00	8,006.00	8,226.20
Q08	exasol	80.20	7	76.40	79.50	8.90	75.30	99.60
Q09	clickhouse	13,474.30	7	11,756.10	11,904.00	293.80	11,645.60	12,456.10
Q09	clickhouse_tuned	14,229.40	7	11,956.30	12,014.10	214.10	11,816.00	12,414.90
Q09	exasol	965.20	7	960.60	962.40	3.50	959.40	968.30
Q10	clickhouse	4,175.60	7	2,846.60	2,937.90	163.20	2,801.90	3,179.00
Q10	clickhouse_tuned	4,312.20	7	2,895.40	3,084.20	349.10	2,831.30	3,642.10
Q10	exasol	570.50	7	573.00	574.60	6.20	565.20	585.00
Q11	clickhouse	884.70	7	617.10	615.90	12.10	599.00	629.70
Q11	clickhouse_tuned	1,176.20	7	744.80	743.50	24.20	707.60	774.00
Q11	exasol	152.70	7	150.00	151.20	7.00	144.30	165.00
Q12	clickhouse	2,277.10	7	749.40	759.70	25.20	736.10	811.40
Q12	clickhouse_tuned	2,734.60	7	881.80	895.30	46.10	856.00	987.10
Q12	exasol	88.10	7	85.30	85.20	0.50	84.60	85.70
Q13	clickhouse	5,018.20	7	4,735.00	4,750.90	83.50	4,668.90	4,912.80
Q13	clickhouse_tuned	5,910.10	7	5,423.10	5,465.00	108.90	5,350.90	5,662.70
Q13	exasol	682.50	7	675.40	675.60	7.90	664.30	690.50
Q14	clickhouse	201.10	7	213.10	211.60	5.30	201.40	216.70
Q14	clickhouse_tuned	238.30	7	230.40	231.70	3.60	227.80	237.20
Q14	exasol	82.70	7	82.70	82.80	0.30	82.40	83.40
Q15	clickhouse	335.20	7	280.50	289.50	16.50	277.40	322.70
Q15	clickhouse_tuned	414.80	7	367.10	371.60	23.60	352.30	419.70
Q15	exasol	389.40	7	380.90	380.70	4.40	373.70	386.10
Q16	clickhouse	458.10	7	450.50	449.80	3.90	445.10	454.80
Q16	clickhouse_tuned	760.60	7	691.50	690.30	19.30	669.20	728.60
Q16	exasol	470.70	7	486.10	485.50	4.40	479.60	492.80
Q17	clickhouse	6,053.90	7	5,394.80	5,396.30	21.10	5,366.90	5,425.60
Q17	clickhouse_tuned	1,461.10	7	1,317.40	1,320.00	11.90	1,307.50	1,343.00
Q17	exasol	31.00	7	30.90	31.00	0.20	30.80	31.40
Q18	clickhouse	5,538.00	7	5,354.30	5,369.80	55.80	5,330.40	5,490.90
Q18	clickhouse_tuned	15,523.80	7	13,314.80	13,608.10	594.90	12,974.50	14,455.80
Q18	exasol	649.50	7	639.40	638.50	8.30	626.60	648.10
Q19	clickhouse	2,260.10	7	2,190.90	2,190.90	11.40	2,176.90	2,205.10
Q19	clickhouse_tuned	7,027.40	7	5,623.40	5,641.50	68.80	5,538.50	5,744.10
Q19	exasol	27.10	7	27.00	27.10	0.40	26.50	27.60
Q20	clickhouse	378.50	7	345.50	345.20	4.70	340.00	354.20
Q20	clickhouse_tuned	3,288.40	7	2,660.60	2,637.50	62.80	2,532.60	2,701.80
Q20	exasol	286.90	7	281.20	281.40	1.90	278.40	283.70
Q21	clickhouse	46,792.60	7	46,498.20	46,517.80	463.00	45,952.20	47,354.70
Q21	clickhouse_tuned	33,517.20	7	33,190.90	33,276.70	2,162.90	30,555.80	36,986.60
Q21	exasol	390.10	7	384.70	389.80	10.90	383.10	413.40
Q22	clickhouse	616.30	7	609.00	613.50	23.10	586.50	647.70
Q22	clickhouse_tuned	2,335.70	7	632.50	651.40	95.30	548.50	766.40
Q22	exasol	96.40	7	95.60	95.60	0.30	95.00	96.00

Infrastructure Setup Timings

The following table shows the time taken to provision cloud instances and install database software:

System	Instance Provisioning	Software Installation	Total Setup Time
Clickhouse	126.77s	28.59s	155.36s
Clickhouse_tuned	126.77s	29.10s	155.87s
Clickhouse_stat	126.77s	29.80s	156.57s
Exasol	126.77s	538.85s	665.62s

Infrastructure Provisioning: 126.77s

Cloud instances were provisioned (VMs created, networking configured)

Software Installation Comparison:

Clickhouse had the fastest software installation at 28.59s
Exasol took 538.85s to install (18.8x slower)

Configuration & Setup

Exasol 2025.1.0 Setup

Storage Setup

# Create GPT partition table
sudo parted /dev/nvme1n1 mklabel gpt
# Execute sudo command on remote system
sudo parted -s /dev/nvme1n1 mklabel gpt
# Create 132GB partition for data generation
sudo parted /dev/nvme1n1 mkpart primary ext4 1MiB 132GiB
# Execute sudo command on remote system
sudo parted -s /dev/nvme1n1 mkpart primary ext4 1MiB 132GiB
# Create raw partition for Exasol (1637GB)
sudo parted /dev/nvme1n1 mkpart primary 132GiB 100%
# Execute sudo command on remote system
sudo parted -s /dev/nvme1n1 mkpart primary 132GiB 100%
# Format /dev/nvme1n1p1 with ext4 filesystem
sudo mkfs.ext4 -F /dev/nvme1n1p1
# Create mount point /data/tpch_gen
sudo mkdir -p /data/tpch_gen
# Mount /dev/nvme1n1p1 to /data/tpch_gen
sudo mount /dev/nvme1n1p1 /data/tpch_gen
# Set ownership of /data/tpch_gen to $(whoami):$(whoami)
sudo chown -R $(whoami):$(whoami) /data/tpch_gen

User Setup

# Create Exasol system user
sudo useradd -m exasol
# Add exasol user to sudo group
sudo usermod -aG sudo exasol
# Set password for exasol user (interactive)
sudo passwd exasol

Tool Setup

# Download c4 cluster management tool v4.28.2
wget https://x-up.s3.amazonaws.com/releases/c4/linux/x86_64/4.28.2/c4 -O c4 && chmod +x c4

Ssh Setup

# Generate SSH key pair for cluster communication
ssh-keygen -t rsa -b 2048 -f ~/.ssh/id_rsa -N ""

Configuration

# Create c4 configuration file on remote system
cat > /tmp/exasol_c4.conf << 'EOF'
CCC_HOST_ADDRS="<PRIVATE_IP>"
CCC_HOST_EXTERNAL_ADDRS="<PUBLIC_IP>"
CCC_HOST_DATADISK=/dev/nvme1n1p2
CCC_HOST_IMAGE_USER=exasol
CCC_HOST_IMAGE_PASSWORD=<EXASOL_IMAGE_PASSWORD>
CCC_HOST_KEY_PAIR_FILE=id_rsa
CCC_PLAY_RESERVE_NODES=0
CCC_PLAY_WORKING_COPY=@exasol-2025.1.0
CCC_PLAY_DB_PASSWORD=<EXASOL_DB_PASSWORD>
CCC_PLAY_ADMIN_PASSWORD=<EXASOL_ADMIN_PASSWORD>
CCC_ADMINUI_START_SERVER=true
EOF

Cluster Deployment

# Deploy Exasol cluster using c4
./c4 host play -i /tmp/exasol_c4.conf

Cluster Management

# Get cluster play ID for confd_client operations
c4 ps

License Setup

# Install Exasol license file
confd_client license_upload license: <LICENSE_CONTENT>

Database Tuning

# Stop Exasol database for parameter configuration
confd_client db_stop db_name: Exasol
# Configure Exasol database parameters for analytical workload optimization
confd_client db_configure db_name: Exasol params_add: "['-writeTouchInit=1','-cacheMonitorLimit=0','-maxOverallSlbUsageRatio=0.95','-useQueryCache=0','-query_log_timeout=0','-joinOrderMethod=0','-etlCheckCertsDefault=0']"
# Starting database with new parameters
confd_client db_start db_name: Exasol

Tuning Parameters

dbram: 220g
optimizer_mode: analytical
Database parameters:
- -writeTouchInit=1
- -cacheMonitorLimit=0
- -maxOverallSlbUsageRatio=0.95
- -useQueryCache=0
- -query_log_timeout=0
- -joinOrderMethod=0
- -etlCheckCertsDefault=0

Data Directory: None

Clickhouse_tuned 25.9.4.58 Setup

Storage Setup

# Format /dev/nvme1n1 with ext4 filesystem
sudo mkfs.ext4 -F /dev/nvme1n1
# Create mount point /data
sudo mkdir -p /data
# Mount /dev/nvme1n1 to /data
sudo mount /dev/nvme1n1 /data
# Set ownership of /data to ubuntu:ubuntu
sudo chown -R ubuntu:ubuntu /data
# Create ClickHouse data directory under /data
sudo mkdir -p /data/clickhouse
# Set ownership of /data/clickhouse to clickhouse:clickhouse
sudo chown -R clickhouse:clickhouse /data/clickhouse

Prerequisites

# Update package lists
sudo apt-get update
# Install prerequisite packages for secure repository access
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

Repository Setup

# Add ClickHouse GPG key to system keyring
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
# Add ClickHouse official repository to APT sources
ARCH=$(dpkg --print-architecture) && echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
# Update package lists with ClickHouse repository
sudo apt-get update

Installation

# Install ClickHouse server and client version <PUBLIC_IP>
sudo apt-get install -y clickhouse-server=25.9.4.58 clickhouse-client=25.9.4.58

Configuration

# Create custom ClickHouse configuration file
sudo tee /etc/clickhouse-server/config.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <listen_host>::</listen_host>
    <path>/data/clickhouse</path>
    <tmp_path>/data/clickhouse/tmp</tmp_path>
    <max_server_memory_usage>212792564121</max_server_memory_usage>
    <max_concurrent_queries>16</max_concurrent_queries>
    <background_pool_size>32</background_pool_size>
    <background_schedule_pool_size>32</background_schedule_pool_size>
    <max_table_size_to_drop>50000000000</max_table_size_to_drop>
</clickhouse>
EOF

User Configuration

# Configure ClickHouse user profile with password and query settings
sudo tee /etc/clickhouse-server/users.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <users>
        <default replace="true">
            <password_sha256_hex>2cca9d8714615f4132390a3db9296d39ec051b3faff87be7ea5f7fe0e2de14c9</password_sha256_hex>
            <networks>
                <ip>::/0</ip>
            </networks>
        </default>
    </users>
    <profiles>
        <default>
            <max_threads>32</max_threads>
            <max_memory_usage>200000000000</max_memory_usage>
            <max_bytes_before_external_sort>100000000000</max_bytes_before_external_sort>
            <max_bytes_before_external_group_by>100000000000</max_bytes_before_external_group_by>
            <join_use_nulls>1</join_use_nulls>
            <allow_experimental_correlated_subqueries>1</allow_experimental_correlated_subqueries>
            <optimize_read_in_order>1</optimize_read_in_order>
            <max_insert_threads>8</max_insert_threads>
        </default>
    </profiles>
</clickhouse>
EOF

Service Management

# Start ClickHouse server service
sudo systemctl start clickhouse-server
# Enable ClickHouse server to start on boot
sudo systemctl enable clickhouse-server

Tuning Parameters

memory_limit: 230g
max_threads: 32
max_memory_usage: 200.0GB

Data Directory: /data/clickhouse

Clickhouse 25.9.4.58 Setup

Storage Setup

# Format /dev/nvme1n1 with ext4 filesystem
sudo mkfs.ext4 -F /dev/nvme1n1
# Create mount point /data
sudo mkdir -p /data
# Mount /dev/nvme1n1 to /data
sudo mount /dev/nvme1n1 /data
# Set ownership of /data to ubuntu:ubuntu
sudo chown -R ubuntu:ubuntu /data
# Create ClickHouse data directory under /data
sudo mkdir -p /data/clickhouse
# Set ownership of /data/clickhouse to clickhouse:clickhouse
sudo chown -R clickhouse:clickhouse /data/clickhouse

Prerequisites

# Update package lists
sudo apt-get update
# Install prerequisite packages for secure repository access
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

Repository Setup

# Add ClickHouse GPG key to system keyring
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
# Add ClickHouse official repository to APT sources
ARCH=$(dpkg --print-architecture) && echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
# Update package lists with ClickHouse repository
sudo apt-get update

Installation

# Install ClickHouse server and client version <PUBLIC_IP>
sudo apt-get install -y clickhouse-server=25.9.4.58 clickhouse-client=25.9.4.58

Configuration

# Create custom ClickHouse configuration file
sudo tee /etc/clickhouse-server/config.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <listen_host>::</listen_host>
    <path>/data/clickhouse</path>
    <tmp_path>/data/clickhouse/tmp</tmp_path>
    <max_server_memory_usage>212792564121</max_server_memory_usage>
    <max_concurrent_queries>16</max_concurrent_queries>
    <background_pool_size>32</background_pool_size>
    <background_schedule_pool_size>32</background_schedule_pool_size>
    <max_table_size_to_drop>50000000000</max_table_size_to_drop>
</clickhouse>
EOF

User Configuration

# Configure ClickHouse user profile with password and query settings
sudo tee /etc/clickhouse-server/users.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <users>
        <default replace="true">
            <password_sha256_hex>2cca9d8714615f4132390a3db9296d39ec051b3faff87be7ea5f7fe0e2de14c9</password_sha256_hex>
            <networks>
                <ip>::/0</ip>
            </networks>
        </default>
    </users>
    <profiles>
        <default>
            <max_threads>32</max_threads>
            <max_memory_usage>200000000000</max_memory_usage>
            <max_bytes_before_external_sort>100000000000</max_bytes_before_external_sort>
            <max_bytes_before_external_group_by>100000000000</max_bytes_before_external_group_by>
            <join_use_nulls>1</join_use_nulls>
            <allow_experimental_correlated_subqueries>1</allow_experimental_correlated_subqueries>
            <optimize_read_in_order>1</optimize_read_in_order>
            <max_insert_threads>8</max_insert_threads>
        </default>
    </profiles>
</clickhouse>
EOF

Service Management

# Start ClickHouse server service
sudo systemctl start clickhouse-server
# Enable ClickHouse server to start on boot
sudo systemctl enable clickhouse-server

Tuning Parameters

memory_limit: 230g
max_threads: 32
max_memory_usage: 200.0GB

Data Directory: /data/clickhouse

Clickhouse_stat 25.9.4.58 Setup

Storage Setup

# Format /dev/nvme1n1 with ext4 filesystem
sudo mkfs.ext4 -F /dev/nvme1n1
# Create mount point /data
sudo mkdir -p /data
# Mount /dev/nvme1n1 to /data
sudo mount /dev/nvme1n1 /data
# Set ownership of /data to ubuntu:ubuntu
sudo chown -R ubuntu:ubuntu /data
# Create ClickHouse data directory under /data
sudo mkdir -p /data/clickhouse
# Set ownership of /data/clickhouse to clickhouse:clickhouse
sudo chown -R clickhouse:clickhouse /data/clickhouse

Prerequisites

# Update package lists
sudo apt-get update
# Install prerequisite packages for secure repository access
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

Repository Setup

# Add ClickHouse GPG key to system keyring
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
# Add ClickHouse official repository to APT sources
ARCH=$(dpkg --print-architecture) && echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
# Update package lists with ClickHouse repository
sudo apt-get update

Installation

# Install ClickHouse server and client version <PUBLIC_IP>
sudo apt-get install -y clickhouse-server=25.9.4.58 clickhouse-client=25.9.4.58

Configuration

# Create custom ClickHouse configuration file
sudo tee /etc/clickhouse-server/config.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <listen_host>::</listen_host>
    <path>/data/clickhouse</path>
    <tmp_path>/data/clickhouse/tmp</tmp_path>
    <max_server_memory_usage>212792570675</max_server_memory_usage>
    <max_concurrent_queries>16</max_concurrent_queries>
    <background_pool_size>32</background_pool_size>
    <background_schedule_pool_size>32</background_schedule_pool_size>
    <max_table_size_to_drop>50000000000</max_table_size_to_drop>
</clickhouse>
EOF

User Configuration

# Configure ClickHouse user profile with password and query settings
sudo tee /etc/clickhouse-server/users.d/benchmark.xml > /dev/null << 'EOF'
<clickhouse>
    <users>
        <default replace="true">
            <password_sha256_hex>2cca9d8714615f4132390a3db9296d39ec051b3faff87be7ea5f7fe0e2de14c9</password_sha256_hex>
            <networks>
                <ip>::/0</ip>
            </networks>
        </default>
    </users>
    <profiles>
        <default>
            <max_threads>32</max_threads>
            <max_memory_usage>200000000000</max_memory_usage>
            <max_bytes_before_external_sort>100000000000</max_bytes_before_external_sort>
            <max_bytes_before_external_group_by>100000000000</max_bytes_before_external_group_by>
            <join_use_nulls>1</join_use_nulls>
            <allow_experimental_correlated_subqueries>1</allow_experimental_correlated_subqueries>
            <optimize_read_in_order>1</optimize_read_in_order>
            <max_insert_threads>8</max_insert_threads>
            <allow_experimental_statistics>1</allow_experimental_statistics>
            <allow_statistics_optimize>1</allow_statistics_optimize>
        </default>
    </profiles>
</clickhouse>
EOF

Service Management

# Start ClickHouse server service
sudo systemctl start clickhouse-server
# Enable ClickHouse server to start on boot
sudo systemctl enable clickhouse-server

Tuning Parameters

memory_limit: 230g
max_threads: 32
max_memory_usage: 200.0GB

Data Directory: /data/clickhouse

Download Data

📊

Query Results

Raw benchmark results (CSV format)

Download

📈

Summary Statistics

Aggregated performance metrics (JSON)

Download

💻

Clickhouse_stat System Information

Hardware specifications captured during the benchmark (JSON)

Download

💻

Clickhouse_tuned System Information

Hardware specifications captured during the benchmark (JSON)

Download

💻

Clickhouse System Information

Hardware specifications captured during the benchmark (JSON)

Download

💻

Exasol System Information

Hardware specifications captured during the benchmark (JSON)

Download

📦

Benchmark Package

Self-contained reproduction package

Download