3 ms·

It looks like the queries are all single table queries with group-bys and aggregates over a reasonably small data set (10s of GB)? I'm sure some real workloads

It looks like the queries are all single table queries with group-bys and aggregates over a reasonably small data set (10s of GB)? I'm sure some real workloads look like this, but I don't think it's a very good test case to show the strengths/weaknesses of an analytical databases query processor or query optimizer (no joins, unions, window functions, complex query shapes ?). For example, if there were any queries with some complex joins Clickhouse would likely not do very well right now given its immature query optimizer (Clickhouse blogs always recommend denormalizing data into tables with many columns to avoid joins).

zX41ZdbW 4y ago
There are many limitations of this benchmark, indeed: https://github.com/ClickHouse/ClickBench/#limitations https://github.com/ClickHouse/ClickBench/#limitations
qoega 4y ago
There are several existing benchmarks that test query optimisers with a lot of joins. It does not show performance of query engine, but more likely how good is your optimiser was tailored for this queries.
- AdamProut 4y ago
  I think your missing my point. The page is entitled "a Benchmark For Analytical DBMS" not "A Benchmark for Single Table Query Execution". Most analytical workloads are more complex then single table queries. I didn't say it wasn't useful to test single table columnstore performance on workload that runs best on single host databases, just that this isn't the be-all end-all of Analytical Database performance testing.
  - zX41ZdbW 4y ago
    You are absolutely right. That's why this benchmark is named "a Benchmark For Analytical DBMS", not "the definitive benchmark for analytical DBMS".
- ruw1090 4y ago
  There's a lot more involved in an execution engine running complex queries that are not single table group by than just QO (though this is important). It includes things like join implementations and associated optimizations, shuffle performance (which is important even for single table queries as you scale), etc.
- riku_iki 4y ago
  > It does not show performance of query engine, but more likely how good is your optimiser was tailored for this queries. you can join just two large tables without leaving much space for query optimizer.
doliveira 4y ago
But isn't that the main goal of analytical databases? They're not for data-warehousing
- AdamProut 4y ago
  I don't know where you draw the line between SQL analytics and SQL data warehousing. I think your typical analytical workload definitely involves more data then this benchmark though. Something like DuckDB is more ideal for this small of a data set . 10s of GB of data can be analysed on a laptop - you don't need a full fledged database server.
  - zX41ZdbW 4y ago
    DuckDB is included in this benchmark. But there were many OOMs in this benchmark and it was not easy to make it working: https://github.com/duckdb/duckdb/issues/3969 https://github.com/duckdb/duckdb/issues/3969 The data size is 75 GiB in uncompressed CSV and 13.7 GiB in Parquet.
- FridgeSeal 4y ago
  I’m somewhat convinced that the “difference” between OLAP and “data warehouses” is shady advertising. Structurally they’re really similar, I suspect some vendors couldn’t match the outright performance of existing OLAP db’s, so added extra features to differentiate it enough to justify a new product category, and then talk endlessly about how OLAP databases aren’t capable of handling this brave new future; even though for the majority of workloads, people would be better off just going with a “boring” OLAP database. Large parts of this comment are directed pointedly at Snowflake.
  - abrazensunset 4y ago
    I think it's more a matter of comparing minivans (cloud "DWH" engines) to sports cars (Clickhouse et al) here. Snowflake's performance characteristics & ops paradigm have always been more consistent with managed Spark than anything else. Thus the competition with Databricks. They have only recently started pretending to be anything than a low-maintenance batch processor with a nice managed storage abstraction, and their pricing model reinforces this. That being said, for now it's pretty hard currently to find something that gives you: - Bottomless storage - Always "OK" performance - Complete consistency without surprises (synchronous updates, cross table transactions, snapshot isolation) - The ability to happily chew through any size join and always return results - Complete workload isolation ...all in one place, so people will probably be buying Snowflake credits for a few years yet. I'm excited about the coming generation--c.f. StarRocks and the Clickhouse roadmap--but the workloads and query patterns for OLAP and DWH only overlap due to marketing and the "I have a hammer" effect. I don't think the slight misuse of either type of engine is bad at small-to-medium scale, either. It's healthy to make "get it done" stacks with fewer query engines, fewer integration points, and already-known system limitations.

zX41ZdbW 4y ago

There are many limitations of this benchmark, indeed: https://github.com/ClickHouse/ClickBench/#limitations https://github.com/ClickHouse/ClickBench/#limitations

qoega 4y ago

There are several existing benchmarks that test query optimisers with a lot of joins. It does not show performance of query engine, but more likely how good is your optimiser was tailored for this queries.

AdamProut 4y ago
I think your missing my point. The page is entitled "a Benchmark For Analytical DBMS" not "A Benchmark for Single Table Query Execution". Most analytical workloads are more complex then single table queries. I didn't say it wasn't useful to test single table columnstore performance on workload that runs best on single host databases, just that this isn't the be-all end-all of Analytical Database performance testing.
- zX41ZdbW 4y ago
  You are absolutely right. That's why this benchmark is named "a Benchmark For Analytical DBMS", not "the definitive benchmark for analytical DBMS".
ruw1090 4y ago
There's a lot more involved in an execution engine running complex queries that are not single table group by than just QO (though this is important). It includes things like join implementations and associated optimizations, shuffle performance (which is important even for single table queries as you scale), etc.
riku_iki 4y ago
> It does not show performance of query engine, but more likely how good is your optimiser was tailored for this queries. you can join just two large tables without leaving much space for query optimizer.

doliveira 4y ago

But isn't that the main goal of analytical databases? They're not for data-warehousing

AdamProut 4y ago
I don't know where you draw the line between SQL analytics and SQL data warehousing. I think your typical analytical workload definitely involves more data then this benchmark though. Something like DuckDB is more ideal for this small of a data set . 10s of GB of data can be analysed on a laptop - you don't need a full fledged database server.
- zX41ZdbW 4y ago
  DuckDB is included in this benchmark. But there were many OOMs in this benchmark and it was not easy to make it working: https://github.com/duckdb/duckdb/issues/3969 https://github.com/duckdb/duckdb/issues/3969 The data size is 75 GiB in uncompressed CSV and 13.7 GiB in Parquet.
FridgeSeal 4y ago
I’m somewhat convinced that the “difference” between OLAP and “data warehouses” is shady advertising. Structurally they’re really similar, I suspect some vendors couldn’t match the outright performance of existing OLAP db’s, so added extra features to differentiate it enough to justify a new product category, and then talk endlessly about how OLAP databases aren’t capable of handling this brave new future; even though for the majority of workloads, people would be better off just going with a “boring” OLAP database. Large parts of this comment are directed pointedly at Snowflake.
- abrazensunset 4y ago
  I think it's more a matter of comparing minivans (cloud "DWH" engines) to sports cars (Clickhouse et al) here. Snowflake's performance characteristics & ops paradigm have always been more consistent with managed Spark than anything else. Thus the competition with Databricks. They have only recently started pretending to be anything than a low-maintenance batch processor with a nice managed storage abstraction, and their pricing model reinforces this. That being said, for now it's pretty hard currently to find something that gives you: - Bottomless storage - Always "OK" performance - Complete consistency without surprises (synchronous updates, cross table transactions, snapshot isolation) - The ability to happily chew through any size join and always return results - Complete workload isolation ...all in one place, so people will probably be buying Snowflake credits for a few years yet. I'm excited about the coming generation--c.f. StarRocks and the Clickhouse roadmap--but the workloads and query patterns for OLAP and DWH only overlap due to marketing and the "I have a hammer" effect. I don't think the slight misuse of either type of engine is bad at small-to-medium scale, either. It's healthy to make "get it done" stacks with fewer query engines, fewer integration points, and already-known system limitations.