Fork me on GitHub
#xtdb
<
2020-09-25
>
markaddleman14:09:44

I'm looking for some crux performance guidance. Obviously, there are a lot of different crux configurations and I'd like to narrow the search space for benchmarking my particular use case. In general, it would be helpful to understand: • What are the performance-sensitive components involved before an ingested document is available for querying? • What are the performance-sensitive components involved in querying recent documents vs older documents? • What are the cache considerations? Datomic Cloud as a cool concept of query groups that provide cache separation by use case. Does crux have a similar concept? My particular use case is ingesting a stream of real time documents (100 - 200 docs / sec). A small number of the attributes of these documents are needed for real-time querying. Separately, I'll need ad hoc querying of large portions of these documents for aggregate analyses.

refset13:09:50

Hi @U2845S9KL thanks for the questions! Hopefully I can shed some light :) > What are the performance-sensitive components involved before an ingested document is available for querying? Just to make sure we're on the same page - there are three steps between writing data and being able to read it back: 1. Write documents to the document store 2. Write a single transaction to the transaction log (assuming all the writes from step 1 succeed) and receive a transaction receipt 3. Wait for the given index store (backed by a KV store of some kind) to register the latest-completed-tx as equal-to or greater-than the transaction receipt returned 4. Run your query This means that all three components (document store + transaction log + index store) are on the critical path for write->read latency, and therefore if your application is sensitive to this latency then you probably want to avoid using an S3 document store or a transaction log backed by something that isn't really optimal for being a log. For minimum latency in the general case the Kafka + RocksDB combination is likely hard to beat, although if you are willing to sacrifice strong durability guarantees then there are certainly additional options and configurations that would be worth considering for significant performance gains. > What are the performance-sensitive components involved in querying recent documents vs older documents? For queries that only access indexed attributes and values (note: only top-level attributes are indexed and small values are reversibly encoded into the index), all query performance considerations lie solely with the index store, which is backed by a local KV store. However, if a specific query needs to access the original values from the document then the engine uses a read-through document cache before hitting the document store, so a sufficiently large and warm document cache may be required. All recent and older documents are maintained equivalent in the indexes, and in terms of historical queries, traversing relationships across older documents pays no additional access-time costs compared to querying the latest versions (and this applies to both transaction time and valid time). > What are the cache considerations? Datomic Cloud has a cool concept of query groups that provide cache separation by use case. Does crux have a similar concept? Each Crux node operates fully independently. It is theoretically possible to have more than one node share a RocksDB KV store (which could live on EFS etc.) using the read-only openAsSecondary facility, but we haven't kicked the tyres on that functionality yet - at that point they are not really separate nodes and you would need to figure out a way to elect only a single instance to do the work of writing the data into Rocks. I have been experimenting recently with using Redis in a similar manner, which can be an appropriate choice for certain managed environments where a large Rocks node can't work (i.e. Heroku). In general though we expect you would see the best query and price-per-query performance when operating a few large RocksDB nodes (vs many smaller nodes), and certainly each can be fully dedicated to a particular use-case as you wish. > My particular use case is ingesting a stream of real time documents (100 - 200 docs / sec). A small number of the attributes of these documents are needed for real-time querying. Separately, I'll need ad hoc querying of large portions of these documents for aggregate analyses. Interesting! Crux could well be a sensible fit, I think. We have recently added aggregates and subqueries (in our efforts to benchmark against TPC-H) that should hopefully help you with the ad hoc queries.

💡 3
markaddleman14:09:28

This is very good information. Thanks!

🙏 3
markaddleman14:09:17

> We have recently added aggregates and subqueries (in our efforts to benchmark against TPC-H) that should hopefully help you with the ad hoc queries. Regarding aggregates: Our ad hoc queries have the potential for dealing with very large interim result sets - potentially exceeding physical RAM. Does Crux aggregate support handle these situations? I gather Crux is using Calcite as its underlying query engine. I'm more familiar with Teiid which has explicit support for paging large result sets.

refset15:09:24

Actually Calcite is only used within the crux-sql module to handle SQL compilation and provide a JDBC driver, we wrote the Datalog query engine that lives in crux-core from scratch 🙂 The planner relies on a worst-case optimal join algorithm that executes lazily, tuple by tuple, not clause by clause (as with Datomic/DataScript), so nowhere near as much memory is required to handle intermediate result sets for querying in general. This laziness enables Crux aggregates to be processed incrementally, as the results stream out of the initial query stage. Also note that user-provided clause ordering is ignored as the planner determines its own join order.

markaddleman16:09:22

Ah ha. This is very good news for my use case. Thanks for the insights!

🙂 3
markaddleman16:09:12

One more question: Are user-defined aggregate functions exposed through SQL?

refset16:09:19

Yep, I think the new user-defined Datalog aggregates (via defmethod) should be possible to include in crux-sql schema definitions, though I don't suspect anyone's tried it before...

👍 3
Bryan22:09:25

Hey everyone, hopefully you can help me get off the struggle bus (relatively new to clojure and very new to crux). I'm trying to submit a document to crux from clojure w/ a var for the start time:

(let [node (crux/new-api-client "")
      id 1234
      value "my test string"
      start-time "2020-09-18T12:00:00Z"]
  (crux/submit-tx
   node
   [[:crux.tx/put
     {:crux.db/id id
      :value value}
     #inst start-time]]))
What I get back is a syntax error that I can't unpack: class clojure.lang.Symbol cannot be cast to class java.lang.CharSequence (clojure.lang.Symbol is in unnamed module of loader 'app'; java.lang.CharSequence is in module java.base of loader 'bootstrap') What am I doing wrong?

alexdavis22:09:20

On mobile so not sure this is whats causing that error message, but reader macros can't be used with symbols, i.e #inst start-time won't work but #inst "2020-08-18" will work. You can however look into how the #inst reader macro works, and you'll see it calls some function (don't remember of the top of my head, but something like read-instant ) so you can use that if the date string must be a symbol

6
alexdavis22:09:16

Or of course use java interop or a time library you may have already in your project (we recommend tick)

Bryan22:09:46

Already using tick but digging into the reader macro is an awesome pointer, thank you!