Comment by georgewfraser | Hacker News Reader

Comment by georgewfraser | Hacker News Reader

As someone who's spent a lot of time working with TPC-DS [1] and talking to people about it [2], I see a couple areas that could be improved in this benchmark:

1. Total run time is not an appropriate way to summarize the performance across queries, because some queries take 100x longer than others. The appropriate way to summarize this kind of data is to use the geomean [3].

2. The official TPC-DS queries make heavy use of grouping sets, which are a rarely-used SQL feature. I think TPC-DS is better if you rewrite the queries to eliminate grouping sets.

3. You used the exact same queries to "warm up" the data warehouse, and to test the performance. Some data warehouses (notably Redshift) aggressively cache intermediate compilation results, so they are much faster the second time they see a query or even a fragment of a query. To model a real user submitting queries interactively, you should use warmup queries that are similar to but not the same as the ones you use to measure performance.

4. You can solve the "vendor benchmarking their own product" problem by submitting a PR to our repo [4], which currently tests Redshift, Snowflake, BigQuery, Azure SQL DW, and Presto. We'd be happy to review it and endorse the timing if it meets our standards for fairness!

[1] https://fivetran.com/blog/warehouse-benchmark

[2] https://www.youtube.com/watch?v=XpaN-PqSczM

[3] https://en.wikipedia.org/wiki/Geometric_mean

[4] https://github.com/fivetran/benchmark

There aren't a lot of 'official' published tpcds results. Until a little less than 2 months ago, there was exactly one published result; it was at the 10TB scale. I like your site and comparisons, but you only have 100 gig and 1 TB. If you had 10TB results I would have wanted to use them for comparison purposes. How come you haven't go to 10T?

We had to figure out how to get something to compare to and understand how we were doing. We looked at the recent Giga Om Microsoft 30T tpch results [1]. They used sum of query times to compare results (sum of times in terms of fraction of an hour to run them all * cost per hour). I decided to do the same sum of query time, except with TPC-DS 10T. As TPC-DS official results provide the power run time, we can get query times from there.

We wanted to push ourselves to test 10TB TPC-DS. It was much more data, much larger intermediate results.

Some databases don't support grouping sets, and that means they can't run the official queries as you said. When we added support for them in MemSQL we made a choice to implement features that customers want; customers want to run TPC-DS without changing the queries, too for testing and other reasons. There are suggested rewrites for systems that don't have grouping sets. MemSQL didn't always have that feature. It's another way to distinguish system capability, I don't know why some of those other companies choose not to add support. I don't think using grouping sets or rewriting matters too much for our perf.

We never cache results; for us the warmup time separates the time to compile and code gen. Some companies do cache final or intermediate results.

I will update our blog post with some of this info, but it might not be completed until tomorrow. Since you were interested in the first run, aka warmup only time I will provide it here first. It's about a 5% difference.

MemSQL Sum of query time (avg of runs not including the warm-up time, already in the blog post): 6,494.74

MemSQL Warmup time only (just the first run, so includes optimize and compile time): 6,860.68

I work at MemSQL.

[1] https://gigaom.com/report/data-warehouse-cloud-benchmark/ [2] https://fivetran.com/blog/warehouse-benchmark

There's a ton of gray area in benchmarking, you have to make a lot of little choices along the way, so I appreciate you sharing your reasoning.

> How come you haven't go to 10T?

We don't see a lot of 10 TB single datasets "in the wild". We think 100 GB - 1 TB is more representative of the average real-world data warehouse user.

> for us the warmup time separates the time to compile and code gen

You should include compile time---your compile time is really good! Redshift sometimes takes longer to compile the query than to run it, though I think they may have improved this in the last 6 months.

Curious to see TiDB in there

You need columnstore storage and a reasonably mature query optimizer to get good results on TPC-DS. TiDB is lacking both right now.

TiDB developer here, we're working on columnstore for TiDB, will release in the near future. On the query optimizer side, I think in TiDB 3.0, TiDB's optimizer has given the best execution plan for most of the TPC-H (TPC-H 50G) queries. But we've never tested on TPC-DS yet.

We just did a TPC-C test on a 3 nodes TiDB3.0 cluster with similar hardware like r3.4xlarge, the result is: 5000 warehouses, TPmC is ~38K.

Reply on news.ycombinator.com