6 ms·

SQL in CockroachDB: Mapping Table Data to Key-Value Storage (2015)

55 points by yinso 9y ago

coldcode 9y ago
I hadn't considered that most storage engines for SQL databases worked in a "similar" fashion.
noahdesu 9y ago
This was a good introduction, but I expected to see some detail regarding the table mapping strategy and concurrency control / isolation levels. In addition to InnoDB and SQLite cited in the article, there is also a lot of good information on the MyRocks wiki on mapping the relational model onto a RocksDB-based storage engine: https://github.com/facebook/mysql-5.6/wiki/MyRocks-record-format https://github.com/facebook/mysql-5.6/wiki/MyRocks-record-fo...
elvinyung 9y ago
I still don't understand why CockroachDB doesn't just use Spark as the compute layer. Spark seems perfectly capable as a platform for performing arbitrary distributed computations on distributed data sources. It even has a full-fledged distributed SQL engine! As it stands, it seems to me that CockroachDB is mostly just reinventing Spark from scratch, except maybe from a more OLTP-centric perspective.
- lobster_johnson 9y ago
  Spark is built around a query planner that can aggregate multiple data sources and "push down" predicates, ordering and so on into the "leaf" node itself, much in the same way that Cockroach does. But I suspect that while this design is flexible and fast, to get the kind of OLTP-friendly performance that Cockroach is aiming for, a good the distributed execution logic has to be fine-tuned in a way that Spark -- being a general-purpose, backend-agnostic system -- cannot do. Spark can query things like Hive, Elasticsearch, Cassandra and so on; Cockroach has the luxury of only needing to target its own engine. Also, last I checked Spark had very limited support for mutating data in SQL; it was designed for queries. Cockroach has to do both.
  - elvinyung 9y ago
    There's overhead in the flexibility, but it seems that you'll have to do the same kind of tuning all over again if you build your own query engine from scratch. And frankly it seems (at least at a high level) that Cockroach is heading towards building the exact same kind of dataflow engine anyways[1]. Spark has some support for SQL DML, and to me it actually mostly seems like a concern for the underlying KV layer to provide ACID transactions, and expose them to Spark through the Data Sources API. [1] https://www.cockroachlabs.com/blog/local-and-distributed-processing-in-cockroachdb/ https://www.cockroachlabs.com/blog/local-and-distributed-pro...
- andrea_s 9y ago
  To be fair, Spark is way too slow to be used as a back-end database system (there's a big bap between "lightning speed computation", as they put them, and a common workload for a database serving data to a user-facing application). Now of course Spark makes up for it with its great flexibility and scalability, but I do not really see the two technologies as competing ones. This even without getting into the other parts of the data model (insert, update, delete) that do not exist in Spark (or "kind of" exist), by design.
- lwansbrough 9y ago
  I'm not an expert with either system, but I think the answer is pretty clear: that's not at all an efficient solution. Spark's approach is: do the data however you want, then use a ridiculous amount of resources getting that data into the format you want it in. You also get a ton of guarantees that you don't get with that Spark system you've proposed. Here's their comparison page[1]. If you're familiar with the technology you're suggesting it should become fairly obvious why Cockroach's nuanced approach to SQL is superior to the Spark SQL solution. For one, Spark SQL doesn't support indices. You can build one, but I'd be surprised if you could get the same general performance out of that system vs. a dedicated system like the one that's built into CockroachDB. I know the hype is to "use NoSQL" but the reality is that there's a limited set of cases where the existing solutions truly make sense. Generally speaking, SQL is the best choice for many companies. That CockroachDB is bringing some new ideas to the SQL space is something that should be lauded. And I think they're doing it in a way that can't really be replicated easily or efficiently by current systems (which is perhaps why they're receiving so much attention.) [1] https://www.cockroachlabs.com/docs/stable/cockroachdb-in-comparison.html https://www.cockroachlabs.com/docs/stable/cockroachdb-in-com...
  - elvinyung 9y ago
    I... agree. But my argument was not "use NoSQL". I never suggested to use NoSQL. I suggested to use Spark as the compute layer. Spark has a sophisticated [1] query engine, which seems perfectly capable of pruning partitions and pushing down index scans into data sources. Yes, Spark can't do writes, so you'll still have to build a transactional KV store that sits underneath the query layer, but you won't have to implement SQL from scratch. (This seems to be similar to the approach taken by e.g. SnappyData [2] and Splice Machine [3].) [1] https://databricks.com/blog/2016/05/23/apache-spark-as-a-compiler-joining-a-billion-rows-per-second-on-a-laptop.html https://databricks.com/blog/2016/05/23/apache-spark-as-a-com... [2] http://snappydatainc.github.io/snappydata/ http://snappydatainc.github.io/snappydata/ [3] http://doc.splicemachine.com/2.0/GettingStarted/ArchitectureOvervew.html http://doc.splicemachine.com/2.0/GettingStarted/Architecture...
    - plamb 9y ago
- buremba 9y ago
  > "except maybe from a more OLTP-centric perspective" This is a big difference, separating compute and storage layer comes with a big overhead with the flexibility.
  - elvinyung 9y ago
    I would actually argue that CockroachDB isn't really a pure OLTP play, nor does it really want to be one. If you read some of their more recent literature like [1], it seems pretty clear to me that they're headed towards building a "good enough" one-size-fits-all [2] database solution, one that can do both OLTP and OLAP on the same data (i.e. HTAP). Furthermore, it seems to me that query evaluation techniques aren't really that different between OLTP and OLAP workloads, i.e. the difference is mainly in the storage format. In that sense, I think it follows that Spark is a good fit as the query layer. Notice phrasings like "arbitrary computations close to your data". That's basically exactly what Spark already is. I think there's a lot of synergy in combining it with a strongly consistent distributed storage engine. Anyways, my $0.02, evidently not a very popular opinion ¯\_(ツ)_/¯ [1] https://www.cockroachlabs.com/blog/local-and-distributed-processing-in-cockroachdb/ https://www.cockroachlabs.com/blog/local-and-distributed-pro... [2] https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf
  - johnnyhillbilly 9y ago
    Can anybody give me an example of any dbms that spans the OLTP/OLAP gap properly _on the same data_? Potentially added constraints: -ACID for OLTP -## TB+ -30-way analytical joins on complex criteria and multiple data sets with billions of entries -fast iterations on data prep for analytics, so analysts can make, find and correct errors -proper workload management (almost no "stupidly designed" queries) -HA -HPC I'm asking because I can't see this without hw and sw being integrated to allow for it (appliance). Are there any cloud offerings that live up to this? EDIT: Formatting got mangled on submit.
    - ddorian43 9y ago
      Check snappydata ?
    - 9y ago
- alexnewman 9y ago
  @elvinyung I am going to try to be as positive as I can. I understand why someone who hasn't written a database or who focused on the internals would see spark as similar to cockroach. Frankly they aren't even similar. Things like isolation levels, data distribution strategies and computational placement are very different in the two systems. They really aren't similar. That being said I think both of them are junk. They are the best junk out there, but junk nonetheless. So maybe take my opinion with a grain of salt.
  - tamasnet 9y ago
    Genuinely curious: what's not junk? (Ideally for use cases similar to what CockroachDB targets.)
  - kinleyd 9y ago
    Why do you say they are junk?
ForHackernews 9y ago
[Official containment subthread for complaining about the name "CockroachDB"] Under the terms of the Unified HN Convention, agreed 2015, every thread about CockroachDB must by law contain a series of complaints about the name of the database. Please post yours below. To help you get started, here's some prompts you might use: "My Enterprise CTO will never go for something named..." "I just think the name sounds really disgusting and off-putting..." "Marketing a product is at least as important as making a product, and this is bad marketing..."
- SeeDave 9y ago
  Yikes. Guilty as charged for unironically making comments as the first two. Going to stare very quietly in nature and reflect.
- look_lookatme 9y ago
  Have they fixed the performance issues in Atom yet? Last time I checked it couldn't load a 800MB file without locking up.
  - mlok 9y ago
    The limit seems to be around 40MB now https://discuss.atom.io/t/atom-file-size-restriction-is-it-still-set-at-2mb/43395/6 https://discuss.atom.io/t/atom-file-size-restriction-is-it-s...
- Fiahil 9y ago
  "For the Swarm!"
- Gigablah 9y ago
  "Should've been called RoachDB"
  - sametmax 9y ago
    Blizzard called and wants you to talk to their lawyer

55 points by yinso 9y ago

I hadn't considered that most storage engines for SQL databases worked in a "similar" fashion.

This was a good introduction, but I expected to see some detail regarding the table mapping strategy and concurrency control / isolation levels. In addition to InnoDB and SQLite cited in the article, there is also a lot of good information on the MyRocks wiki on mapping the relational model onto a RocksDB-based storage engine: https://github.com/facebook/mysql-5.6/wiki/MyRocks-record-format https://github.com/facebook/mysql-5.6/wiki/MyRocks-record-fo...

elvinyung 9y ago

I still don't understand why CockroachDB doesn't just use Spark as the compute layer. Spark seems perfectly capable as a platform for performing arbitrary distributed computations on distributed data sources. It even has a full-fledged distributed SQL engine! As it stands, it seems to me that CockroachDB is mostly just reinventing Spark from scratch, except maybe from a more OLTP-centric perspective.

lobster_johnson 9y ago
Spark is built around a query planner that can aggregate multiple data sources and "push down" predicates, ordering and so on into the "leaf" node itself, much in the same way that Cockroach does. But I suspect that while this design is flexible and fast, to get the kind of OLTP-friendly performance that Cockroach is aiming for, a good the distributed execution logic has to be fine-tuned in a way that Spark -- being a general-purpose, backend-agnostic system -- cannot do. Spark can query things like Hive, Elasticsearch, Cassandra and so on; Cockroach has the luxury of only needing to target its own engine. Also, last I checked Spark had very limited support for mutating data in SQL; it was designed for queries. Cockroach has to do both.
- elvinyung 9y ago
  There's overhead in the flexibility, but it seems that you'll have to do the same kind of tuning all over again if you build your own query engine from scratch. And frankly it seems (at least at a high level) that Cockroach is heading towards building the exact same kind of dataflow engine anyways[1]. Spark has some support for SQL DML, and to me it actually mostly seems like a concern for the underlying KV layer to provide ACID transactions, and expose them to Spark through the Data Sources API. [1] https://www.cockroachlabs.com/blog/local-and-distributed-processing-in-cockroachdb/ https://www.cockroachlabs.com/blog/local-and-distributed-pro...
andrea_s 9y ago
To be fair, Spark is way too slow to be used as a back-end database system (there's a big bap between "lightning speed computation", as they put them, and a common workload for a database serving data to a user-facing application). Now of course Spark makes up for it with its great flexibility and scalability, but I do not really see the two technologies as competing ones. This even without getting into the other parts of the data model (insert, update, delete) that do not exist in Spark (or "kind of" exist), by design.
lwansbrough 9y ago
I'm not an expert with either system, but I think the answer is pretty clear: that's not at all an efficient solution. Spark's approach is: do the data however you want, then use a ridiculous amount of resources getting that data into the format you want it in. You also get a ton of guarantees that you don't get with that Spark system you've proposed. Here's their comparison page[1]. If you're familiar with the technology you're suggesting it should become fairly obvious why Cockroach's nuanced approach to SQL is superior to the Spark SQL solution. For one, Spark SQL doesn't support indices. You can build one, but I'd be surprised if you could get the same general performance out of that system vs. a dedicated system like the one that's built into CockroachDB. I know the hype is to "use NoSQL" but the reality is that there's a limited set of cases where the existing solutions truly make sense. Generally speaking, SQL is the best choice for many companies. That CockroachDB is bringing some new ideas to the SQL space is something that should be lauded. And I think they're doing it in a way that can't really be replicated easily or efficiently by current systems (which is perhaps why they're receiving so much attention.) [1] https://www.cockroachlabs.com/docs/stable/cockroachdb-in-comparison.html https://www.cockroachlabs.com/docs/stable/cockroachdb-in-com...
- elvinyung 9y ago
  I... agree. But my argument was not "use NoSQL". I never suggested to use NoSQL. I suggested to use Spark as the compute layer. Spark has a sophisticated [1] query engine, which seems perfectly capable of pruning partitions and pushing down index scans into data sources. Yes, Spark can't do writes, so you'll still have to build a transactional KV store that sits underneath the query layer, but you won't have to implement SQL from scratch. (This seems to be similar to the approach taken by e.g. SnappyData [2] and Splice Machine [3].) [1] https://databricks.com/blog/2016/05/23/apache-spark-as-a-compiler-joining-a-billion-rows-per-second-on-a-laptop.html https://databricks.com/blog/2016/05/23/apache-spark-as-a-com... [2] http://snappydatainc.github.io/snappydata/ http://snappydatainc.github.io/snappydata/ [3] http://doc.splicemachine.com/2.0/GettingStarted/ArchitectureOvervew.html http://doc.splicemachine.com/2.0/GettingStarted/Architecture...
  - plamb 9y ago
buremba 9y ago
> "except maybe from a more OLTP-centric perspective" This is a big difference, separating compute and storage layer comes with a big overhead with the flexibility.
- elvinyung 9y ago
  I would actually argue that CockroachDB isn't really a pure OLTP play, nor does it really want to be one. If you read some of their more recent literature like [1], it seems pretty clear to me that they're headed towards building a "good enough" one-size-fits-all [2] database solution, one that can do both OLTP and OLAP on the same data (i.e. HTAP). Furthermore, it seems to me that query evaluation techniques aren't really that different between OLTP and OLAP workloads, i.e. the difference is mainly in the storage format. In that sense, I think it follows that Spark is a good fit as the query layer. Notice phrasings like "arbitrary computations close to your data". That's basically exactly what Spark already is. I think there's a lot of synergy in combining it with a strongly consistent distributed storage engine. Anyways, my $0.02, evidently not a very popular opinion ¯\_(ツ)_/¯ [1] https://www.cockroachlabs.com/blog/local-and-distributed-processing-in-cockroachdb/ https://www.cockroachlabs.com/blog/local-and-distributed-pro... [2] https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf
- johnnyhillbilly 9y ago
  Can anybody give me an example of any dbms that spans the OLTP/OLAP gap properly _on the same data_? Potentially added constraints: -ACID for OLTP -## TB+ -30-way analytical joins on complex criteria and multiple data sets with billions of entries -fast iterations on data prep for analytics, so analysts can make, find and correct errors -proper workload management (almost no "stupidly designed" queries) -HA -HPC I'm asking because I can't see this without hw and sw being integrated to allow for it (appliance). Are there any cloud offerings that live up to this? EDIT: Formatting got mangled on submit.
  - ddorian43 9y ago
    Check snappydata ?
  - 9y ago
alexnewman 9y ago
@elvinyung I am going to try to be as positive as I can. I understand why someone who hasn't written a database or who focused on the internals would see spark as similar to cockroach. Frankly they aren't even similar. Things like isolation levels, data distribution strategies and computational placement are very different in the two systems. They really aren't similar. That being said I think both of them are junk. They are the best junk out there, but junk nonetheless. So maybe take my opinion with a grain of salt.
- tamasnet 9y ago
  Genuinely curious: what's not junk? (Ideally for use cases similar to what CockroachDB targets.)
- kinleyd 9y ago
  Why do you say they are junk?

ForHackernews 9y ago

[Official containment subthread for complaining about the name "CockroachDB"] Under the terms of the Unified HN Convention, agreed 2015, every thread about CockroachDB must by law contain a series of complaints about the name of the database. Please post yours below. To help you get started, here's some prompts you might use: "My Enterprise CTO will never go for something named..." "I just think the name sounds really disgusting and off-putting..." "Marketing a product is at least as important as making a product, and this is bad marketing..."

SeeDave 9y ago
Yikes. Guilty as charged for unironically making comments as the first two. Going to stare very quietly in nature and reflect.
look_lookatme 9y ago
Have they fixed the performance issues in Atom yet? Last time I checked it couldn't load a 800MB file without locking up.
- mlok 9y ago
  The limit seems to be around 40MB now https://discuss.atom.io/t/atom-file-size-restriction-is-it-still-set-at-2mb/43395/6 https://discuss.atom.io/t/atom-file-size-restriction-is-it-s...
Fiahil 9y ago
"For the Swarm!"
Gigablah 9y ago
"Should've been called RoachDB"
- sametmax 9y ago
  Blizzard called and wants you to talk to their lawyer