Wojciech Biela, Co-founder at Starburst
Introduction
As mentioned in our previous blog about the Starburst Presto release and its hottest addition – the Cost Based Optimizer for Presto we’re happy to share the results of benchmarks we did for this release (195e) comparing it to the ‘vanilla’ Presto release 195. Now we will continue on the process of getting all those CBO-related changes merged into the ‘vanilla’ Presto repository.
The benchmarks were performed using a standard set of TPC-H and TPC-DS queries. As a side-note, I would like to highlight that, thanks to our team’s contributions throughout the last couple of years, Presto supports 100% TPC benchmark queries and executes them unmodified! That is with no prohibited query modifications. You can find the queries in our repository.
The queries in our benchmarks are run using various standard schema scale factors. This is to have all execution times in the same ballpark. Otherwise some queries will run too long for the benchmark to be practical, while other queries would finish under a second, making it hard to compare the results. That said, over 80% queries are run on a 1TB scale factor or more (up to 10TB).
Benchmark environment
The benchmarks were run on a 8+1 node, bare-metal cluster, with HDP. Eight Presto Workers are sharing the nodes with HDFS Data Node processes. Presto Coordinator lives on a single machine with the HDFS NameNode and Hive Metastore (we witnessed no bottlenecks on this node).
Each node had two Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz processors and 256GB RAM available.
TPC benchmark data was stored on HDFS in ORC format (with ZLIB compression, which is the default in Presto).
Each query was run multiple times and the mean execution time was taken as the result. Before each new query, ie. before starting a new series of runs, the disk caches on all nodes were dropped.
Presto configuration for both testes releases was default in terms of feature flags and properties (and our release didn’t change default values for any existing properties).
The only properties set were fitting Presto to utilize the available hardware or otherwise recommended in Presto docs.
config.properties just configured the memory limits:
query.max-memory=400GB
query.max-memory-per-node=40GBjvm.config contained the following, as recommended in the Presto docs:
-server
-Xmx160G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryErrorResults
Various queries are affected by the CBO in different ways, depending on how bad the initial join order was, etc. The overall results are stunning nevertheless. The executive summary of the benchmark results is such that the Starburst Presto 195e release compared to the ‘vanilla’ Presto 195 release blasts the benchmark away with:
- Up to 13x speed-up for selected queries – that’s over an order of magnitude!
- Over 50% of all queries get a 2-5x boost
- About 10% of all queries get over 6x to up to 10x speed up.
We are thrilled to bring you this brand new Starburst Presto release!
You can see more details on the charts below. The first one shows the top improvements in TPC-DS, narrowed down for brevity and clarity to speed-ups of 5x and above.
The second chart (pie) visualizes the stunning total number of queries improved by our CBO in the spectrum of all TPC-DS queries.
One can clearly see that the vast majority of queries are benefitting from our new optimizer at least 2-5x (orange), a lot even 5x and more, up to 13 times (red). You can see some queries improved by the CBO insignificantly or are not affected at all (blue); this is usually due to either the original plan getting lucky and already having a relatively good join order or because the query doesn’t have joins, so there was no change to the query plan.
You’ll see even more query improvements for queries that are being auto-generated by tools or written by hand without proper care. Benchmarks are interesting as a reference point, but we always encourage our prospects and customers to PoC Presto with their business specific datasets and queries. We found that then Presto shines even more!
We are happy to be able to share this release with you! It’s ready for download, ready for your testing, and ready for your production! Enterprise grade Presto distribution. Hardened, stabilized, benchmarked and supported by Starburst. Available also via our brand new offering, the fully Managed Presto Service.
In the upcoming days/weeks we will be posting much more detailed blog posts on this new development of ours. Explaining the conceptual and technical details of the CBO for Presto in a series of focused blogs. So stay tuned, watch our blog space, Twitter, LinkedIn or Facebook for more exciting news and further details. Also we’re always happy to hear from you what were your experiences with the Starburst Presto release, so don’t hesitate to drop us a line and share your use cases.
