tpc-ds
tpc-ds copied to clipboard
Altinity
TPC-DS benchmarks
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'
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
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)'
# 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.'
| 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'
| Affected queries |
|---|
| query_47.sql |
| query_57.sql |
3.7.10 'Resolve identifier supported for constants and CTE. Actual node type COLUMN'
| Affected queries |
|---|
| query_95.sql |
3.7.11 'If(Int64 IS NULL, ...) wrong data type assumed'
| 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),'
| 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
Altinity