altinity-tpc-ds

ClickHouse TPC-DS (Decision Support Benchmark).

Benchmark environment

• Data scale = 100 (100GB of data)
• Node size 16 CPU, 64GB RAM
• ClickHouse server version 24.1

Report

74 queries passing (74.75%)

1. Performance issues

1.1 '600s timeout'

2. Fixable failed queries

Fixes described below will require modification of related template-files or generated queries.

2.1.1 'There is no supertype for types Float32, Decimal(7, 2) because some of them have no lossless conversion to Decimal., not supported'

github issue 9881

To fix: need to explicitly cast Float64 to Decimal(7, 2).

Affected queries
query_5.sql

2.4 Intervals like this '+ 30 days' not supported

github issue 9887

To fix: need to use INTERVAL data type.

# fail
SELECT (now() + 30 days);

# success
SELECT (now() + INTERVAL 30 day);

Affected queries
query_12.sql	query_40.sql
query_16.sql	query_82.sql
query_21.sql	query_92.sql
query_32.sql	query_94.sql
query_37.sql

3. FAILED queries

3.1 Memory limit exceeded (Could be a part of Performance optimization)

Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 32.34 GiB (attempt to allocate chunk of 67108864 bytes), maximum: 32 GiB.

Affected queries
query_4.sql	query_11.sql	query_14.sql
query_18.sql
query_65.sql	query_74.sql	query_78.sql

Remark: when run test under Docker, make sure that the memory limit much more max_memory_usage (see Docker -> Settings -> Advance).

3.7.3 'Correlated subqueries (missing columns: "x" while processing query)'

github issue 6697

# success
SELECT dummy, name
FROM system.one, system.columns
WHERE (SELECT count() FROM system.columns WHERE name != '') > 0 AND dummy = 0
LIMIT 1;

# fail
SELECT dummy, name
FROM system.one, system.columns
WHERE (SELECT count() FROM system.columns WHERE name != '' AND dummy = 0) > 0
LIMIT 1;

# fail
SELECT o.dummy, name
FROM system.one o, system.columns
WHERE (SELECT count() FROM system.columns WHERE name != '' AND o.dummy = 0) > 0
LIMIT 1;

Affected queries
query_6.sql	query_10.sql	query_16.sql
query_30.sql	query_32.sql	query_35.sql
query_41.sql	query_69.sql	query_81.sql
query_92.sql	query_94.sql	query_1.sql

3.7.8 'CROSS JOIN to INNER JOIN rewrite depends on tables order in query.'

github issue 9194

Affected queries
query_18.sql
query_65.sql

3.7.9 'There is no supertype when CROSS to INNER JOIN rewrite WHERE a.key=b.key-1'

github issue 21794

Affected queries
query_47.sql
query_57.sql

3.7.10 'Resolve identifier supported for constants and CTE. Actual node type COLUMN'

github issue 58994

Affected queries
query_95.sql

3.7.11 'If(Int64 IS NULL, ...) wrong data type assumed'

github issue 58997

Affected queries
query_72.sql

3.7.12 'wrong pushdown of id.null to table engine non nullable column (null type is generated in JOIN),'

github issue 58998

Affected queries
query_78.sql

References

TPC-DS documents

TPC-DS Tools or take already downloaded

Others DB benchmarks

Vertica White paper - Benchmarks Prove the Value of an Analytical Database for Big Data

Vertica TPC-DS benchmark performance analysis

tidb-bench