11 ms·

Presto: Interacting with petabytes of data at Facebook

dude_abides 13y ago
Will be very interesting if someone does a benchmark comparison of Presto with Cloudera Impala, Amazon RedShift and Apache Drill. Also, very curious to know (from any Googlers browsing HN) if Dremel is still the state-of-the-art within Google, or if there is already a newer replacement.
- snorkel 13y ago
  I can least say that I've used both a large Hadoop+Hive farm and a moderately sized RedShift cluster on the same large data set, and holy smoke, RedShift is orders of magnitude faster. Results vary by how big the nodes are you allocate to Redshift, and it's not cheap at all, but very impressive.
  - dman 13y ago
    I am working on a related product. Is there some way I could contact you to pick your brain about some things?
  - spikels 13y ago
    This is exactly what you should expect in almost all cases because you are kinda comparing apples and oranges. Hadoop/Hive was not focused on speed but proving it is even possible to do queries reliably on such large datasets. Once this was achieved it immediately became clear the long waits for Hadoop/Hive batch jobs to finish made it impractical for many uses. Presto, Impala, Drill, RedShift, etc were all designed Primarily to address this problem and be much, much faster than Hadoop/Hive so that the data could be queried interactively. All these new projects/products are in a very active competition to find the best way or ways to do this. You should compare RedShift to these other projects rather than Hadoop if speed is an issue for you. Each has it's pluses and minuses depending on the situation.
    - aquadrop 13y ago
      Wait, so RedShift (Drill, Impala, Presto) do the same job as Hadoop\Hive, only faster? You only mentioned they addressed the speed, but what's downside? At what cost they achieved their velocity?
      - necubi 13y ago
        Basically, Hive is incredibly inefficient. Hive works by taking apart the HiveQL query and turning it into a series of Hadoop MR steps. A complex hive query may have many such steps, and for each one data must be read from HDFS, processed, and written back to HDFS. For most queries, the vast majority of your time will be spent just doing IO. Hadoop is also very, very slow to start up tasks (> 1 minute), so when you have a lot of them that can come to dominate your total run time. Impala, Drill, etc. avoid all those unnecessary reads and writes by implementing the querying logic directly, rather than by compiling to Map Reduce. Shark [0] is an interesting counterpoint. It takes essentially the same approach as Hive but on Spark instead of Hadoop, and achieves similar or better performance than the more "direct" implementations. [0] http://shark.cs.berkeley.edu http://shark.cs.berkeley.edu
      - spikels 13y ago
        The tradeoffs are both complex and evolving and are different for each project/product. In general Hadoop is more scalable and more flexible but much slower. For example as of today RedShift can hold a maximum of 256 terrabytes of compressed data while Facebook's Hadoop cluster was over 200 Petabytes in late 2012. RedShift only supports limited query and data types and a single index while Hadoop can theoretically handle arbitrary data processing. But if these constraints are acceptable then RedShift will likely be orders of magnitude faster in most cases. Other projects/products will have different tradeoffs but they are almost always faster as this was almost always the primary goal.
        cjg_ 13y ago
        Where do you get the limit of 256TB compressed from? Amazon Redshift enables you to start with as little as a single 2TB XL node and scale up all the way to a hundred 16TB 8XL nodes for 1.6PB of compressed user data. from http://aws.amazon.com/redshift/features-and-benefits/ http://aws.amazon.com/redshift/features-and-benefits/
- necubi 13y ago
  The state of the art at Google appears to be F1 [0], although that takes a pretty different approach than Presto/Dremel and may be complementary. This area (interactive SQL on big data) has become very active lately. In addition to those you list, there's Shark [1] and BlinkDB [2] from Berkeley's AMP Lab. [0] http://research.google.com/pubs/pub41344.html http://research.google.com/pubs/pub41344.html [1] http://shark.cs.berkeley.edu http://shark.cs.berkeley.edu [2] http://blinkdb.org/ http://blinkdb.org/
  - sprizzle 13y ago
    I believe F1 is the SQL DB that powers ads (AdWords, AdSense, etc.) and is used in production for consumer-facing apps. Dremel is more of a data/log analysis tool, but doesn't typically interact directly with consumer-facing apps. Dremel (externally: Google BigQuery) is still widely used at Google across all product areas; F1 is used in a few products but not many.
  - snewman 13y ago
    As sprizzle says, F1 and Dremel are not competitors; Dremel is a read-only system supporting large analysis queries, while F1 is a read-write system designed for large numbers of smaller queries. Last year Google published a paper on PowerDrill (http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf), which sounds somewhat like Dremel but is designed to use large amounts of RAM, which in turn enables some very powerful optimizations.
- matsur 13y ago
  Would also be curious to see how the execution model and language relates to/differs from Microsoft Dryad[0]/SCOPE[1]. [0] http://research.microsoft.com/en-us/projects/dryad/ http://research.microsoft.com/en-us/projects/dryad/ [1] http://research.microsoft.com/en-us/um/people/jrzhou/pub/Scope.pdf http://research.microsoft.com/en-us/um/people/jrzhou/pub/Sco...
- curiousfiddler 13y ago
  You might want to add Apache Tez to the list as well. Series of posts on Tez: http://hortonworks.com/hadoop/tez/ http://hortonworks.com/hadoop/tez/
- tonfa 13y ago
  For interactive workflows, there is powerdrill: http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf
- monstrado 13y ago
  Although a little out of date, there is a website dedicated to this: https://amplab.cs.berkeley.edu/benchmark/ https://amplab.cs.berkeley.edu/benchmark/
  - dude_abides 13y ago
    This is awesome, thanks for sharing! Redshift looks to be order-of-magnitude faster than Impala or Shark in all the test. Does this mean that once RedShift supports user-defined functions, there is no competing solution that is any match? (Unless you want to avoid using the cloud)
    - flyovercountry 13y ago
      The file format for hadoop tools is a sequence file. For most queries this is the second slowest format, after text. Rcfile or parquet would be a more interesting benchmark.
    - monstrado 13y ago
      The benchmark above is testing Impala with SequenceFiles compressed with GZIP, against RedShift, which is not a fair comparison. In the "What's next?" section, they say they want to re-do the Impala tests using Parquet, which is a columnar format based on the Dremel whitepaper (http://parquet.io/ http://parquet.io/).
      - dude_abides 13y ago
        Ah that makes sense! Looking at their results and how RedShift was so much faster in every scenario, it looked like something was amiss. Is Parquet Cloudera-only like Impala or is it available with vanilla Hadoop?
        monstrado 13y ago
        Impala isn't technically Cloudera only, it's open source (https://github.com/cloudera/impala https://github.com/cloudera/impala), and other people have gotten it to run on their Hadoop distribution, but since it's developed by Cloudera, it was developed to run on the CDH platform (Hadoop). Parquet was a joint effort between Cloudera and Twitter, and now it's being developed by many other companies. You can use it with Hive, Pig, MapReduce, Cascading, Crunch and I think Apache Drill's first milestone has adopted it as a columnar format as well. Parquet also allows you to use your Avro or Thrift schema (soon Protobuffs) to write Parquet data, too. It's a separate project in the ecosystem and has its own roadmap (https://github.com/Parquet/parquet-mr https://github.com/Parquet/parquet-mr).
- spikels 13y ago
  There is also Shard-Query which is build on top of a cluster on MySQL servers. According to its developer, who works at Percona, running Shard-Query with MySQL using a column store (Infobrite community edition) give performance similar to RedShift. http://shardquery.com/ http://shardquery.com/
- justinerickson 13y ago
  For those of you interested in more information on Impala and it's performance characteristics see: * rideimpala.com * https://speakerdeck.com/grahn/practical-performance-analysis-and-tuning-for-cloudera-impala https://speakerdeck.com/grahn/practical-performance-analysis...
gesman 13y ago
Really nice to see posts like that from Facebook.
hatred 13y ago
It will be extremely kind if someone can explain the major differences or features that Presto offers that are different then Impala or other similar products.
- mtraverso 13y ago
  I can't speak to what other products like Impala, Apache Drill, etc. offer, but Presto supports the following: - Standard ANSI SQL syntax, including all the basic features you'd expect from a SQL engine (aggregations, joins, etc) and other more advanced features like analytic window functions, common table expressions (WITH), approximate distinct counts and percentiles. - It's extensible. The open source code base includes a connector for Hive, but we also have some custom connectors for internal data stores at Facebook. We're working on a connector for HBase, too. - In comparison to Hive, it's very fast and efficient. For our workloads it's at least 10x more CPU-efficient. Airbnb is using it and has had a similar experience. - Most importantly, Presto has been battle-tested. It's been in production at Facebook since January and it's used by 1,000 employees every day running 30,000 queries daily. We've hit every edge case you can imagine.
  - rbolkey 13y ago
    Any best practices or gotchas about query authoring in comparison to a typical relational database? What part of the traditional relational db mentality needs to be changed or thrown out as I write queries. For example, should I avoid using this aggregate function or that join in a way I'm familiar with in the context of something like Postgres.
    - electrum 13y ago
      When running against Hive data, Presto is similar to many analytic databases in that queries typically perform a full table or partition scan, so "point lookups" that search for one or a few records will be much less efficient than they would be in an OLTP system like PostgreSQL that has a precomputed index for that query. (This is actually a property of the data source and not the Presto query engine. For example, we are writing an HBase connector that can take advantage of HBase's indexes for very efficient queries.) In general, you should be able to write your query in the simplest and most readable way, and Presto should execute it efficiently. We already have the start of an advanced optimizer that supports equality inference, full predicate move-around, etc. This means that you don't need to write redundant predicates everywhere as is required with some query engines. Also, if you are familiar with PostgreSQL, you should feel right at home using Presto. When making decisions for things not covered by ANSI SQL, the first thing we look at is "what does PostgreSQL do".
electrum 13y ago
Hi, I work on Presto at Facebook and would be happy to answer any questions about it.
- agotterer 13y ago
  Congrats on open sourcing this. It sounds really great and I'm excited to get a chance to play with it. Do you think this would be a decent candidate for click stream analysis?
  - electrum 13y ago
    Thanks! Presto supports arbitrary SQL including some basic window functions (full support is on our roadmap). I've used window functions at previous companies with commercial data warehouses to do some basic click stream analysis: http://prestodb.io/docs/current/functions/window.html http://prestodb.io/docs/current/functions/window.html We have over a thousand employees in Facebook using it daily for anything you can imagine, so I recommend trying it and letting us know what you find useful and how it can be improved.
- polskibus 13y ago
  1. Can you force it to do exact (not approximate counts) and aggregations? If so, what are the preconditions (size of tables?) 2. In data warehouse terms, would you fit it as a MOLAP, ROLAP or HOLAP kind of engine? I'm not sure whether all data is held in memory and whether aggregates are cached. Can you preprocess the dataset in batch mode (let's say overnight), repopulate aggregate caches for faster retrieval later on? (I mean that as something similar to SQL Server Analysis Services in MOLAP mode). 3. Can you compare it to Apache Drill ?
  - electrum 13y ago
    1) Presto always returns exact answers, with the exception of the approximate aggregate functions approx_distinct() and approx_percentile() (see http://prestodb.io/docs/current/functions/aggregate.html http://prestodb.io/docs/current/functions/aggregate.html). We do not currently support COUNT(DISTINCT), though if you really need that you can use a subquery with SELECT DISTINCT. We have a pull request for partitioned hash aggregations, but currently all the groups must fit in memory limit specified by the "task.max-memory" configuration parameter (see http://prestodb.io/docs/current/installation/deployment.html http://prestodb.io/docs/current/installation/deployment.html for details). Regarding approximate queries, we are working with the author of BlinkDB (http://blinkdb.org/ http://blinkdb.org/) to add it to Presto. BlinkDB allows very fast approximate queries with bounded errors (an important requirement for statisticians / data scientists). 2) Presto is a traditional SQL engine, so it would be ROLAP. We don't yet have any support for building cubes or other specialized structures (though full materialized view support with rewrite is on the roadmap). The Presto query engine is actually agnostic to the data source. Data is queried via pluggable connectors. We don't currently have any caching ready for production. There is a very alpha quality native store that we plan to use soon for query acceleration. The idea is you create a materialized view against a Hive table which loads the data into the native store. The view then gets used transparently when you query the Hive table, so the user doesn't have to rewrite their queries or even know about it. All they see is that their query is 100x faster. (All this code is there today in the open source release but needs work to productionize it.) We have a dashboard system at Facebook that uses Presto. For large tables, users typically setup pipelines in Hive that run daily to compute summary tables, then write the dashboard queries against these summary tables. In the future, we would like to be able to handle all of this within Presto as materialized views. 3) We're excited about the Drill project. They have some interesting ideas about integrating unstructured data processing (like arbitrary JSON documents) with standard SQL. However, last I looked they were still in early development, whereas Presto is in production at Facebook and is usable today. Please also see this comment: https://news.ycombinator.com/item?id=6684785 https://news.ycombinator.com/item?id=6684785
pvnick 13y ago
>As mentioned above, development on Presto started in Fall 2012. We had our first production system up and running in early 2013. It was fully rolled out to the entire company by Spring 2013. Wow. As somebody who skimmed through the original Google Dremel paper and thought for a while about how one would go about implementing such an interactive system, that strikes me as an amazingly impressive timeline.
pswenson 13y ago
what is the data format FB stores? (xml/json/?)
- electrum 13y ago
  Most of the data in our data warehouse is stored as relational records using Hive's RCFile format, though we are actively working on converting it to the new ORCFile format. The Hive data model supports complex types including arrays, maps and structs (nested tables), and those are used liberally. We also have a fair amount of data stored as JSON inside of string columns in Hive tables, though much of this is actually structured and could be better modeled using Hive's complex types. Presto currently has limited support for Hive complex types. All complex types are converted to JSON at query time and can be accessed using the JSON functions: http://prestodb.io/docs/current/functions/json.html http://prestodb.io/docs/current/functions/json.html
wehadfun 13y ago
I'm curious what these 1000s of queries by facebook employees are for. What data / information does facebook mine for daily?
- nbm 13y ago
  I'm a recent user of Presto and Hive at Facebook after being here coming up to three years. I've been using them primarily to understand ways in which we might be sending people's traffic to non-optimal locations, or non-optimal paths between our CDN and the source content, or to help with understanding current and extrapolating future capacity needs on our CDN (total requests, changes in cache hit object/byte rates, &c.)
- stedaniels 13y ago
  I imagine that they could use it to track how many cats walk across peoples keyboards daily, and what peak "cat walking on keyboard" hours are. I'd be more than mildly interested in the results.
RyanZAG 13y ago
Interesting use of optimizing sql query plans down to JVM byte code. I don't think anybody has done that before - but it sounds like it would work really well. Would be really interesting to hear more on that as it might be applicable to a lot of other areas as well.
- yonks 13y ago
  Maybe with SQL but compiling a DML to byte code isn’t a new idea; http://xml.coverpages.org/saxon42Ann.html http://xml.coverpages.org/saxon42Ann.html
cakeface 13y ago
> (In a later post, we will share some tips and tricks for writing high-performance Java system code and the lessons learned while building Presto.) I'm waiting in anticipation for that article!
monokrome 13y ago
Rounding down is not a good solution to the rounding problem. As a software engineer, I also used rounding in my contracts when I was working as a contractor - and here's why: If I have 5 clients at a time, and each one has a concept/idea every day that takes 20 seconds to explain to me (and isn't on the current iteration) then they are distracting me by not managing their time properly and calling me a dozen times a day to tell me about their thoughts. If it is a change to the current iteration, then it should have been discussed when we agreed on the current iteration's feature set. Either way, the call is not a result of well thought out time management. As much as I like hearing new concepts and ideas, I also have to take attention away from a project that I'm working on in order to provide my full attention to the client calling me. After the call is done, I also have to come back to the project at hand and hopefully I'm not working on something that requires that I retain a super complicated thought chain which may or may not have been lost in discussion with another client - especially in consideration that I'm not going to bill on other client project for the time that I've spent having been sidetracked and/or getting back to where I was before the call was made. So, charging in $15 increments causes the client to actually manage their time with the same effectiveness that they would hope that I am managing mine. Rounding down makes this more of a problem for me, not less of one. Now a 90 second call is at no charge, and I can get more than one of those in one hour - still at no charge - based on the suggested agreement.
- haberman 13y ago
  Posted on the wrong story?
  - asselinpaul 13y ago
    should be here: https://news.ycombinator.com/item?id=6684156 https://news.ycombinator.com/item?id=6684156
teddyh 13y ago
“Beautiful. Unethical. Dangerous.” — Lucius Fox, The Dark Knight
tszming 13y ago
I really hope one day Google can follow Facebook to opensource some of their core infrastructure technologies (even the legacy one, e.g. 1st generation of GFS etc) to the opensource community :)
knodi 13y ago
Java -_-
leadgen 13y ago
How can I install and use this on Win 2012 server with SQL server ?

Will be very interesting if someone does a benchmark comparison of Presto with Cloudera Impala, Amazon RedShift and Apache Drill. Also, very curious to know (from any Googlers browsing HN) if Dremel is still the state-of-the-art within Google, or if there is already a newer replacement.

snorkel 13y ago
I can least say that I've used both a large Hadoop+Hive farm and a moderately sized RedShift cluster on the same large data set, and holy smoke, RedShift is orders of magnitude faster. Results vary by how big the nodes are you allocate to Redshift, and it's not cheap at all, but very impressive.
- dman 13y ago
  I am working on a related product. Is there some way I could contact you to pick your brain about some things?
- spikels 13y ago
  This is exactly what you should expect in almost all cases because you are kinda comparing apples and oranges. Hadoop/Hive was not focused on speed but proving it is even possible to do queries reliably on such large datasets. Once this was achieved it immediately became clear the long waits for Hadoop/Hive batch jobs to finish made it impractical for many uses. Presto, Impala, Drill, RedShift, etc were all designed Primarily to address this problem and be much, much faster than Hadoop/Hive so that the data could be queried interactively. All these new projects/products are in a very active competition to find the best way or ways to do this. You should compare RedShift to these other projects rather than Hadoop if speed is an issue for you. Each has it's pluses and minuses depending on the situation.
  - aquadrop 13y ago
    Wait, so RedShift (Drill, Impala, Presto) do the same job as Hadoop\Hive, only faster? You only mentioned they addressed the speed, but what's downside? At what cost they achieved their velocity?
    - necubi 13y ago
      Basically, Hive is incredibly inefficient. Hive works by taking apart the HiveQL query and turning it into a series of Hadoop MR steps. A complex hive query may have many such steps, and for each one data must be read from HDFS, processed, and written back to HDFS. For most queries, the vast majority of your time will be spent just doing IO. Hadoop is also very, very slow to start up tasks (> 1 minute), so when you have a lot of them that can come to dominate your total run time. Impala, Drill, etc. avoid all those unnecessary reads and writes by implementing the querying logic directly, rather than by compiling to Map Reduce. Shark [0] is an interesting counterpoint. It takes essentially the same approach as Hive but on Spark instead of Hadoop, and achieves similar or better performance than the more "direct" implementations. [0] http://shark.cs.berkeley.edu http://shark.cs.berkeley.edu
    - spikels 13y ago
      The tradeoffs are both complex and evolving and are different for each project/product. In general Hadoop is more scalable and more flexible but much slower. For example as of today RedShift can hold a maximum of 256 terrabytes of compressed data while Facebook's Hadoop cluster was over 200 Petabytes in late 2012. RedShift only supports limited query and data types and a single index while Hadoop can theoretically handle arbitrary data processing. But if these constraints are acceptable then RedShift will likely be orders of magnitude faster in most cases. Other projects/products will have different tradeoffs but they are almost always faster as this was almost always the primary goal.
      - cjg_ 13y ago
        Where do you get the limit of 256TB compressed from? Amazon Redshift enables you to start with as little as a single 2TB XL node and scale up all the way to a hundred 16TB 8XL nodes for 1.6PB of compressed user data. from http://aws.amazon.com/redshift/features-and-benefits/ http://aws.amazon.com/redshift/features-and-benefits/
necubi 13y ago
The state of the art at Google appears to be F1 [0], although that takes a pretty different approach than Presto/Dremel and may be complementary. This area (interactive SQL on big data) has become very active lately. In addition to those you list, there's Shark [1] and BlinkDB [2] from Berkeley's AMP Lab. [0] http://research.google.com/pubs/pub41344.html http://research.google.com/pubs/pub41344.html [1] http://shark.cs.berkeley.edu http://shark.cs.berkeley.edu [2] http://blinkdb.org/ http://blinkdb.org/
- sprizzle 13y ago
  I believe F1 is the SQL DB that powers ads (AdWords, AdSense, etc.) and is used in production for consumer-facing apps. Dremel is more of a data/log analysis tool, but doesn't typically interact directly with consumer-facing apps. Dremel (externally: Google BigQuery) is still widely used at Google across all product areas; F1 is used in a few products but not many.
- snewman 13y ago
  As sprizzle says, F1 and Dremel are not competitors; Dremel is a read-only system supporting large analysis queries, while F1 is a read-write system designed for large numbers of smaller queries. Last year Google published a paper on PowerDrill (http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf), which sounds somewhat like Dremel but is designed to use large amounts of RAM, which in turn enables some very powerful optimizations.
matsur 13y ago
Would also be curious to see how the execution model and language relates to/differs from Microsoft Dryad[0]/SCOPE[1]. [0] http://research.microsoft.com/en-us/projects/dryad/ http://research.microsoft.com/en-us/projects/dryad/ [1] http://research.microsoft.com/en-us/um/people/jrzhou/pub/Scope.pdf http://research.microsoft.com/en-us/um/people/jrzhou/pub/Sco...
curiousfiddler 13y ago
You might want to add Apache Tez to the list as well. Series of posts on Tez: http://hortonworks.com/hadoop/tez/ http://hortonworks.com/hadoop/tez/
tonfa 13y ago
For interactive workflows, there is powerdrill: http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf http://vldb.org/pvldb/vol5/p1436_alexanderhall_vldb2012.pdf
monstrado 13y ago
Although a little out of date, there is a website dedicated to this: https://amplab.cs.berkeley.edu/benchmark/ https://amplab.cs.berkeley.edu/benchmark/
- dude_abides 13y ago
  This is awesome, thanks for sharing! Redshift looks to be order-of-magnitude faster than Impala or Shark in all the test. Does this mean that once RedShift supports user-defined functions, there is no competing solution that is any match? (Unless you want to avoid using the cloud)
  - flyovercountry 13y ago
    The file format for hadoop tools is a sequence file. For most queries this is the second slowest format, after text. Rcfile or parquet would be a more interesting benchmark.
  - monstrado 13y ago
    The benchmark above is testing Impala with SequenceFiles compressed with GZIP, against RedShift, which is not a fair comparison. In the "What's next?" section, they say they want to re-do the Impala tests using Parquet, which is a columnar format based on the Dremel whitepaper (http://parquet.io/ http://parquet.io/).
    - dude_abides 13y ago
      Ah that makes sense! Looking at their results and how RedShift was so much faster in every scenario, it looked like something was amiss. Is Parquet Cloudera-only like Impala or is it available with vanilla Hadoop?
      - monstrado 13y ago
        Impala isn't technically Cloudera only, it's open source (https://github.com/cloudera/impala https://github.com/cloudera/impala), and other people have gotten it to run on their Hadoop distribution, but since it's developed by Cloudera, it was developed to run on the CDH platform (Hadoop). Parquet was a joint effort between Cloudera and Twitter, and now it's being developed by many other companies. You can use it with Hive, Pig, MapReduce, Cascading, Crunch and I think Apache Drill's first milestone has adopted it as a columnar format as well. Parquet also allows you to use your Avro or Thrift schema (soon Protobuffs) to write Parquet data, too. It's a separate project in the ecosystem and has its own roadmap (https://github.com/Parquet/parquet-mr https://github.com/Parquet/parquet-mr).
spikels 13y ago
There is also Shard-Query which is build on top of a cluster on MySQL servers. According to its developer, who works at Percona, running Shard-Query with MySQL using a column store (Infobrite community edition) give performance similar to RedShift. http://shardquery.com/ http://shardquery.com/
justinerickson 13y ago
For those of you interested in more information on Impala and it's performance characteristics see: * rideimpala.com * https://speakerdeck.com/grahn/practical-performance-analysis-and-tuning-for-cloudera-impala https://speakerdeck.com/grahn/practical-performance-analysis...

gesman 13y ago

Really nice to see posts like that from Facebook.

hatred 13y ago

It will be extremely kind if someone can explain the major differences or features that Presto offers that are different then Impala or other similar products.

mtraverso 13y ago
I can't speak to what other products like Impala, Apache Drill, etc. offer, but Presto supports the following: - Standard ANSI SQL syntax, including all the basic features you'd expect from a SQL engine (aggregations, joins, etc) and other more advanced features like analytic window functions, common table expressions (WITH), approximate distinct counts and percentiles. - It's extensible. The open source code base includes a connector for Hive, but we also have some custom connectors for internal data stores at Facebook. We're working on a connector for HBase, too. - In comparison to Hive, it's very fast and efficient. For our workloads it's at least 10x more CPU-efficient. Airbnb is using it and has had a similar experience. - Most importantly, Presto has been battle-tested. It's been in production at Facebook since January and it's used by 1,000 employees every day running 30,000 queries daily. We've hit every edge case you can imagine.
- rbolkey 13y ago
  Any best practices or gotchas about query authoring in comparison to a typical relational database? What part of the traditional relational db mentality needs to be changed or thrown out as I write queries. For example, should I avoid using this aggregate function or that join in a way I'm familiar with in the context of something like Postgres.
  - electrum 13y ago
    When running against Hive data, Presto is similar to many analytic databases in that queries typically perform a full table or partition scan, so "point lookups" that search for one or a few records will be much less efficient than they would be in an OLTP system like PostgreSQL that has a precomputed index for that query. (This is actually a property of the data source and not the Presto query engine. For example, we are writing an HBase connector that can take advantage of HBase's indexes for very efficient queries.) In general, you should be able to write your query in the simplest and most readable way, and Presto should execute it efficiently. We already have the start of an advanced optimizer that supports equality inference, full predicate move-around, etc. This means that you don't need to write redundant predicates everywhere as is required with some query engines. Also, if you are familiar with PostgreSQL, you should feel right at home using Presto. When making decisions for things not covered by ANSI SQL, the first thing we look at is "what does PostgreSQL do".

electrum 13y ago

Hi, I work on Presto at Facebook and would be happy to answer any questions about it.

agotterer 13y ago
Congrats on open sourcing this. It sounds really great and I'm excited to get a chance to play with it. Do you think this would be a decent candidate for click stream analysis?
- electrum 13y ago
  Thanks! Presto supports arbitrary SQL including some basic window functions (full support is on our roadmap). I've used window functions at previous companies with commercial data warehouses to do some basic click stream analysis: http://prestodb.io/docs/current/functions/window.html http://prestodb.io/docs/current/functions/window.html We have over a thousand employees in Facebook using it daily for anything you can imagine, so I recommend trying it and letting us know what you find useful and how it can be improved.
polskibus 13y ago
1. Can you force it to do exact (not approximate counts) and aggregations? If so, what are the preconditions (size of tables?) 2. In data warehouse terms, would you fit it as a MOLAP, ROLAP or HOLAP kind of engine? I'm not sure whether all data is held in memory and whether aggregates are cached. Can you preprocess the dataset in batch mode (let's say overnight), repopulate aggregate caches for faster retrieval later on? (I mean that as something similar to SQL Server Analysis Services in MOLAP mode). 3. Can you compare it to Apache Drill ?
- electrum 13y ago
  1) Presto always returns exact answers, with the exception of the approximate aggregate functions approx_distinct() and approx_percentile() (see http://prestodb.io/docs/current/functions/aggregate.html http://prestodb.io/docs/current/functions/aggregate.html). We do not currently support COUNT(DISTINCT), though if you really need that you can use a subquery with SELECT DISTINCT. We have a pull request for partitioned hash aggregations, but currently all the groups must fit in memory limit specified by the "task.max-memory" configuration parameter (see http://prestodb.io/docs/current/installation/deployment.html http://prestodb.io/docs/current/installation/deployment.html for details). Regarding approximate queries, we are working with the author of BlinkDB (http://blinkdb.org/ http://blinkdb.org/) to add it to Presto. BlinkDB allows very fast approximate queries with bounded errors (an important requirement for statisticians / data scientists). 2) Presto is a traditional SQL engine, so it would be ROLAP. We don't yet have any support for building cubes or other specialized structures (though full materialized view support with rewrite is on the roadmap). The Presto query engine is actually agnostic to the data source. Data is queried via pluggable connectors. We don't currently have any caching ready for production. There is a very alpha quality native store that we plan to use soon for query acceleration. The idea is you create a materialized view against a Hive table which loads the data into the native store. The view then gets used transparently when you query the Hive table, so the user doesn't have to rewrite their queries or even know about it. All they see is that their query is 100x faster. (All this code is there today in the open source release but needs work to productionize it.) We have a dashboard system at Facebook that uses Presto. For large tables, users typically setup pipelines in Hive that run daily to compute summary tables, then write the dashboard queries against these summary tables. In the future, we would like to be able to handle all of this within Presto as materialized views. 3) We're excited about the Drill project. They have some interesting ideas about integrating unstructured data processing (like arbitrary JSON documents) with standard SQL. However, last I looked they were still in early development, whereas Presto is in production at Facebook and is usable today. Please also see this comment: https://news.ycombinator.com/item?id=6684785 https://news.ycombinator.com/item?id=6684785

pvnick 13y ago

>As mentioned above, development on Presto started in Fall 2012. We had our first production system up and running in early 2013. It was fully rolled out to the entire company by Spring 2013. Wow. As somebody who skimmed through the original Google Dremel paper and thought for a while about how one would go about implementing such an interactive system, that strikes me as an amazingly impressive timeline.

pswenson 13y ago

what is the data format FB stores? (xml/json/?)

electrum 13y ago
Most of the data in our data warehouse is stored as relational records using Hive's RCFile format, though we are actively working on converting it to the new ORCFile format. The Hive data model supports complex types including arrays, maps and structs (nested tables), and those are used liberally. We also have a fair amount of data stored as JSON inside of string columns in Hive tables, though much of this is actually structured and could be better modeled using Hive's complex types. Presto currently has limited support for Hive complex types. All complex types are converted to JSON at query time and can be accessed using the JSON functions: http://prestodb.io/docs/current/functions/json.html http://prestodb.io/docs/current/functions/json.html

wehadfun 13y ago

I'm curious what these 1000s of queries by facebook employees are for. What data / information does facebook mine for daily?

nbm 13y ago
I'm a recent user of Presto and Hive at Facebook after being here coming up to three years. I've been using them primarily to understand ways in which we might be sending people's traffic to non-optimal locations, or non-optimal paths between our CDN and the source content, or to help with understanding current and extrapolating future capacity needs on our CDN (total requests, changes in cache hit object/byte rates, &c.)
stedaniels 13y ago
I imagine that they could use it to track how many cats walk across peoples keyboards daily, and what peak "cat walking on keyboard" hours are. I'd be more than mildly interested in the results.

RyanZAG 13y ago

Interesting use of optimizing sql query plans down to JVM byte code. I don't think anybody has done that before - but it sounds like it would work really well. Would be really interesting to hear more on that as it might be applicable to a lot of other areas as well.

yonks 13y ago
Maybe with SQL but compiling a DML to byte code isn’t a new idea; http://xml.coverpages.org/saxon42Ann.html http://xml.coverpages.org/saxon42Ann.html

cakeface 13y ago

> (In a later post, we will share some tips and tricks for writing high-performance Java system code and the lessons learned while building Presto.) I'm waiting in anticipation for that article!

monokrome 13y ago

Rounding down is not a good solution to the rounding problem. As a software engineer, I also used rounding in my contracts when I was working as a contractor - and here's why: If I have 5 clients at a time, and each one has a concept/idea every day that takes 20 seconds to explain to me (and isn't on the current iteration) then they are distracting me by not managing their time properly and calling me a dozen times a day to tell me about their thoughts. If it is a change to the current iteration, then it should have been discussed when we agreed on the current iteration's feature set. Either way, the call is not a result of well thought out time management. As much as I like hearing new concepts and ideas, I also have to take attention away from a project that I'm working on in order to provide my full attention to the client calling me. After the call is done, I also have to come back to the project at hand and hopefully I'm not working on something that requires that I retain a super complicated thought chain which may or may not have been lost in discussion with another client - especially in consideration that I'm not going to bill on other client project for the time that I've spent having been sidetracked and/or getting back to where I was before the call was made. So, charging in $15 increments causes the client to actually manage their time with the same effectiveness that they would hope that I am managing mine. Rounding down makes this more of a problem for me, not less of one. Now a 90 second call is at no charge, and I can get more than one of those in one hour - still at no charge - based on the suggested agreement.

haberman 13y ago
Posted on the wrong story?
- asselinpaul 13y ago
  should be here: https://news.ycombinator.com/item?id=6684156 https://news.ycombinator.com/item?id=6684156

teddyh 13y ago

“Beautiful. Unethical. Dangerous.” — Lucius Fox, The Dark Knight

tszming 13y ago

I really hope one day Google can follow Facebook to opensource some of their core infrastructure technologies (even the legacy one, e.g. 1st generation of GFS etc) to the opensource community :)

knodi 13y ago

Java -_-

leadgen 13y ago

How can I install and use this on Win 2012 server with SQL server ?