Clojurians Log v2
xtdb
Is there some way to understand how much a minimal deployment will cost per month? Similar to this page from Datomic docs: https://docs.datomic.com/operation/growing-your-system.html#understanding-bill
not currently I'm afraid. for a basic single node, you can do it all in a Docker container, so pay as much or as little as you want for that. for a highly available cluster, you'll need the resources as outlined in the relevant guide (e.g. https://docs.xtdb.com/ops/aws.html) - generally speaking: • a Kafka cluster • object storage • some compute nodes • and a network load balancer
All right. Perhaps someone who's got a minimal HA cluster running can chime in how much is costing them?
I have another related question. I inserted the sample 500k user rows with num field containing incremented integer values. From logical point of view those values are "unique".
When I perform a lookup of element "by _id", that gives me pretty much constant, fast lookup times (3-4ms).
When I try to lookup by the "logically unique" num column,
1) "select * from users where num = 250000" # middle value, without limit clause -> ~90ms
2) "select * from users where num = 250000 limit 1" # middle value, with limit clause-> ~35ms
3) "select * from users where num = 499999" # last inserted value - 3-4ms - with limit as well as without limit
4) "select * from users where num = 0" # first inserted value - 3-4ms - with limit as well as without limitnum is that much slower than the "side ones"?
• what indexes are being used here for lookup by num , that would explain such a big difference in lookup time? I tried running explain of those queries, and the plan for 1) and 3) is the same - just the different num value is being looked up.
• since xtdb has no schema and therefore "no knowledge about uniqueness of the num field", I'd expect limit 1 should have similar impact in all cases. But it doesn't seem to be affecting those "first/last values" in 3) and 4)
• may the performance of this be affected by the way how data was inserted into db (number of batching transactions, use of INSERT INTO ... RECORDS etc). All the data in the described scenatio ^^^ was performed with just a single with-transaction. I then tried using batch size of 5000 and even 1000 and got quite different results. For example, I was no longer able to get the "edge values" under 5ms.. suddenly it took at least 20ms. Regarding the and performance optimization techniques. part of the quoted datomic link.. is there any docs/benchmark/discussion worth checking?
I remember that for example count wasn't too fast in xtdb1. Is this different in xtdb2?
I just quickly tried spawning a fresh docker xtdb2 instance, inserted 1 000 000 simple "user" rows (_id, firstname, lastname, num), and it seems that the count query takes ~150ms already (quite a lot I'd say for "just 1 million items") on my machine (12th Gen Intel(R) Core(TM) i7-12800H).
Would any other (more complex) deployment setup improve this?
@brdloush don't forget that you're getting quite a lot for your money there - XT isn't just counting there, it's also checking that all of your rows are valid at the given time, haven't been deleted, etc.
A more accurate comparison might be against a query like SELECT COUNT(*) FROM user WHERE NOW() >= valid_from AND NOW() < valid_to AND NOW() >= system_from AND NOW() < system_to
regarding whether a more complex deployment setup would change things - likely not, because in both cases all of this data would be hot in either local disk or in memory caches
XT isn't just counting there, it's also checking that all of your rows are valid at the given time, haven't been deleted, etc.Yup. I personally understand this. But many times, people you're trying to "sell idea and benefits of xtdb to" have this implicit fear of unknown. And they might easily end up trying a POC, where they spam xtdb database with content of some of their legacy tables, containing a gazillion of entries. And it might be counter-intuitive and a bit unexpected for them that some of the "basic" scenarios (from their point of view, counting may be basic and relatively common) is surprisingly slow. It would be great to know similar "counter intuitive / unexpected" performance characteristics in advance, so that you can prepare those people & hint them regarding what to do, what not..
> regarding whether a more complex deployment setup would change things - likely not, because in both cases all of this data would be hot in either local disk or in memory caches Does it mean that even this simple docker setup may be considered to have "production-grade-usable performance for index seeking operations"?
absolutely, yep 👌 the production grade components are more about improving durability and availability rather than raw performance - being able to spill your data out to S3 when it no longer fits on disk, sharing maintenance work, etc. the query engine's the same in both cases - just that in the production case, if the data you need isn't already in memory or on local disk, it additionally goes to S3 - but if all the data's already local it makes no difference
regarding counter intuitive performance cases - much fewer than in XT1, largely because the engine is now a more standard relational columnar query engine. the main difference is in the lowest level scan operator, where we additionally do our bitemporal resolution, but once the data's been scanned it's all the same joins, selects, projects, groups etc as you'd get in a regular DB
Understood. I wasn't sure about the whole index neccessarily being in the memory. Instinctively, I'd consider 1 milion freshly created "trivial records" (ie: with just 1 version) to be "countable" in way less than 150ms. So I was considering "maybe the docker container is running in naive configuration or something"
Hey @brdloush thanks for sharing this write up!
> • why is it that the lookup of ~middle element by num is that much slower than the "side ones"?
It's hard to say for sure without booting this up in the REPL, but it could be due to the somewhat probabilistic nature of how the batches are pruned using metadata before the scanning happens. The lookups for the 'side ones' will be terminating the scans fairly early after scanning very little data, whereas the ~middle element scanning is likely having to scan nearly all the data to complete the join. I would be surprised if it was always possible to predict the performance of these lookups based on ordering, so I wouldn't always the middle elements to be slower in other scenarios.
> what indexes are being used here for lookup by num
Currently there are no other 'indexes' asides from the primary index on _id but XT tracks https://vldb.org/pvldb/vol10/p1622-ziauddin.pdf-style metadata for pruning column scans (as alluded to above)
> I'd expect limit 1 should have similar impact in all cases
In many cases, I believe limit 1 may only be applied at the end of the processing, after numerous scans have been eagerly evaluated and more-intermediate-results-than-needed have been materialized
> I then tried using batch size of 5000 and even 1000 and got quite different results
This is not necessarily surprising either due to how any when the metadata gets calculated. At a large enough scale though (100s millions records) the transaction sizes should be irrelevant
For running XTDB v2 in docker, is there a place I am able to add configuration? Like if I wanted to change the storage/log config?
you should be able to mount and use a custom config like docker run -p 5432:5432 -p 8080:8080 -v ./config.yaml:/config/config.yaml