15 ms·

How to Analyze Billions of Records per Second on a Single Desktop PC

317 points by cwinter 8y ago

minimaxir 8y ago
Note: there are multiple pages to the post, which have the benchmarks.
- Aissen 8y ago
  Thanks, I missed this at first. I've always found pagination to be disrespectful of the reader, I wonder what was the motivation here.
ducreux 8y ago
The author had no idea what they were doing with kdb. They even admit that they couldn't be bothered to modify their ingestion scripts to not partition their data.
- gricardo99 8y ago
  Yup. >The pickup_ntaname column is stored as varchars by ClickHouse and kdb+, and as dictionary encoded single byte values by LocustDB. But it would be trivial to convert to enum/sym type in kdb+. It's silly to query and group by strings.
  - inteleng 8y ago
    This is one of the frustrating parts of database software. Where can information about the ways to optimize these parameters be found (outside of random posts scattered around StackExchange)?
    - gricardo99 8y ago
      so true. Either you have lots of experience working with specific databases to setup/optimize queries, and you know what works best through personal blood, sweat and tears, and/or you have intimate knowledge of the inner workings of the database architecture/implementation and know the theoretical best approach to structure your schema/queries. But even then, hardware/networking performance and tuning can throw a wrench in the most seasoned/knowledgeable approaches. Users can further bring otherwise solid setups to a grinding halt with unanticipated use-cases. The only hope when you hit these inevitable road-blocks is that you're working for someone that appreciates the difficulty of the problem.
- frankmcsherry 8y ago
  This seems like a very unfair reading of what the author actually wrote: > One note about the results for kdb+: The ingestion scripts I used for kdb+ partition/index the data on the year and passenger_count columns. This may give it a somewhat unfair advantage over ClickHouse and LocustDB on all queries that group or filter on these columns (queries 2, 3, 4, 5 and 7). I was going to figure out how to remove that partitioning and report those results as well, but didn’t manage before my self-imposed deadline.
- anonu 8y ago
  Completely moot point though as he demonstrates that even when kdb+ is advantaged by having data be indexed, LocustDB is still faster in 4 of the 5 queries he runs... So yeah, maybe the guy has no idea what to do with kdb - but ultimately having a fast, free and open-source database & query language beats a fast and expensive piece of commercial software.
- geocar 8y ago
  The first red flag is that Mark's benchmarks look very different for kdb, even though his ClickHouse times are similar to Clemens. Looking over the queries, he made some... interesting changes that have him benchmarking oranges to apples.
paulsutter 8y ago
Correct title is "How to Analyze Billions of Records in 20 Minutes and One Second" All of these "fast" databases have a fatal flaw - it takes forever to load the data in the first place. In this case loading takes >1000x longer than the query so loading is all that matters.
- placebo 8y ago
  Have you experienced this issue with the other databases mentioned in the article?
  - chiph 8y ago
    I think he was implying that if you access the disk at all, your performance is hidden behind that cost.
- t0mbstone 8y ago
  Loading data from cold storage (HDD/SSD, etc) into RAM only has to be done once on startup, though. After that single slow startup, all of the additional queries happen super fast. Your complaint only makes sense if you only intend to perform one single query against a dataset.
  - paulsutter 8y ago
    My complaint makes sense because I might have 100B records per day to process, and the delay to load them up is significant
    - wmf 8y ago
      Could you stream the data into memory as it is created so that loading data isn't in the critical path?
      - AboutTheWhisles 8y ago
        Does this software do that?
nkurz 8y ago
I have encountered multiple claims that in-memory analytics databases are often constrained by memory bandwidth, and I myself held that misconception for longer than I want to admit. So if not memory-bandwidth, what is the constraining factor? Or specifically, what's the limiting factor that causes the observed benchmark speeds for LocustDB? My guess would be that a well designed in-memory database should be limited by memory-bandwidth, but that real-world memory-bandwidth shouldn't be thought of as a single number. Instead, it depends on the size and pattern of requests, the latency of different cache hierarchies, and (crucially but often forgotten) on the number of requests in flight. Is there a better terminology that distinguishes these two uses of memory-bandwidth?
- blattimwind 8y ago
  > there a better terminology that distinguishes these two uses of memory-bandwidth? theoretical peak bandwidth effective/observed bandwidth
  - nkurz 8y ago
    That's not the distinction I'm looking for. I'm looking for a better term to describe the throughput the application would achieve if all non-memory constraints were removed. In the context of the article, the author is comparing the ratio of the "effective/observed bandwidth" and the "theoretical peak bandwidth", noting that the ratio is large, and concluding that he is not constrained by memory bandwidth. I don't know that this is a reasonable conclusion. I'm looking for a different denominator, which is also theoretical. Maybe call it the "theoretically achievable bandwidth", which takes into account all the details of the requests. If the "observed bandwidth" equals the "theoretically achievable bandwidth", your only path to improvement is to change your data layout or increase the parallelism of your requests. If the "observed bandwidth" is less than this theoretical, there should still be room for implementation optimizations. Falling short of the "theoretical peak bandwidth" (even by a lot) doesn't tell you which of these is the case.
    - squeed 8y ago
      Hmm. I'm not exactly sure if it's what you mean, but in the networking world, we use the term "Goodput" as a shorthand for "actual user-observed bandwidth".
golanggeek 8y ago
cwinter does Locust also support scalability across machines.. what is the timeline for it to be production ready..
emmelaich 8y ago
It'd be interesting to compare this against traildb. http://traildb.io/ http://traildb.io/
- ryanworl 8y ago
  This is a very interesting project! Thank you for posting it. Having read both this post and the documentation for TrailDB, I don’t think they are comparable past very simple use cases. This post seems to be covering a complete database system with a query parser, planner, and (previously?) a storage engine. TrailDB is more like a storage engine you can push certain kinds of filters down into, but performs neither query parsing nor planning for you.
  - emmelaich 8y ago
    True. But the latest blog post for TrailDB mentions two new query interfaces, trck and reel. I also wouldn't be surprised if someone has done a sqlite backend for it.
- mkarlsch 8y ago
  Interesting project, however it looks like the last commit is from September 2017 so either it is just very stable or not maintained anymore?
bcaa7f3a8bbc 8y ago
Past: How to Analyze Billions of Records per Second on a Supercomputer Present: How to Analyze Billions of Records per Second on a Single Desktop PC Future: How to Analyze 5 Records per Second on Thousands of Machines Distributed Across the Globe Over the Blockchain.
- TrevorJ 8y ago
  That's axiomatic of the direction consumer software has gone over the last 20 years as well. How a word processor can feel sluggish on a modern PC is beyond me.
  - zeusk 8y ago
    Because that word processor can do a lot more things and is not the sole thing running on a modern machine.
    - _emacsomancer_ 8y ago
      and, one would imagine, a lot of accumulated cruft.
      - zeusk 8y ago
        what you're calling cruft is some other person's usecase.
        frankling_ 8y ago
        I'd still prioritize the basic use case of comfortably typing words, which to my admittedly sensitive mind is not supported that well anymore by some common pieces of software. Between the apparent round-trip per keypress in Outlook Web, the noticeable general display delay of Windows 10, and the frequent wait for characters to appear in a reasonably small Powerpoint presentation, I feel like I might be holding it wrong.
        TeMPOraL 8y ago

317 points by cwinter 8y ago

minimaxir 8y ago

Note: there are multiple pages to the post, which have the benchmarks.

Aissen 8y ago
Thanks, I missed this at first. I've always found pagination to be disrespectful of the reader, I wonder what was the motivation here.

ducreux 8y ago

The author had no idea what they were doing with kdb. They even admit that they couldn't be bothered to modify their ingestion scripts to not partition their data.

gricardo99 8y ago
Yup. >The pickup_ntaname column is stored as varchars by ClickHouse and kdb+, and as dictionary encoded single byte values by LocustDB. But it would be trivial to convert to enum/sym type in kdb+. It's silly to query and group by strings.
- inteleng 8y ago
  This is one of the frustrating parts of database software. Where can information about the ways to optimize these parameters be found (outside of random posts scattered around StackExchange)?
  - gricardo99 8y ago
    so true. Either you have lots of experience working with specific databases to setup/optimize queries, and you know what works best through personal blood, sweat and tears, and/or you have intimate knowledge of the inner workings of the database architecture/implementation and know the theoretical best approach to structure your schema/queries. But even then, hardware/networking performance and tuning can throw a wrench in the most seasoned/knowledgeable approaches. Users can further bring otherwise solid setups to a grinding halt with unanticipated use-cases. The only hope when you hit these inevitable road-blocks is that you're working for someone that appreciates the difficulty of the problem.
frankmcsherry 8y ago
This seems like a very unfair reading of what the author actually wrote: > One note about the results for kdb+: The ingestion scripts I used for kdb+ partition/index the data on the year and passenger_count columns. This may give it a somewhat unfair advantage over ClickHouse and LocustDB on all queries that group or filter on these columns (queries 2, 3, 4, 5 and 7). I was going to figure out how to remove that partitioning and report those results as well, but didn’t manage before my self-imposed deadline.
anonu 8y ago
Completely moot point though as he demonstrates that even when kdb+ is advantaged by having data be indexed, LocustDB is still faster in 4 of the 5 queries he runs... So yeah, maybe the guy has no idea what to do with kdb - but ultimately having a fast, free and open-source database & query language beats a fast and expensive piece of commercial software.
geocar 8y ago
The first red flag is that Mark's benchmarks look very different for kdb, even though his ClickHouse times are similar to Clemens. Looking over the queries, he made some... interesting changes that have him benchmarking oranges to apples.

paulsutter 8y ago

Correct title is "How to Analyze Billions of Records in 20 Minutes and One Second" All of these "fast" databases have a fatal flaw - it takes forever to load the data in the first place. In this case loading takes >1000x longer than the query so loading is all that matters.

placebo 8y ago
Have you experienced this issue with the other databases mentioned in the article?
- chiph 8y ago
  I think he was implying that if you access the disk at all, your performance is hidden behind that cost.
t0mbstone 8y ago
Loading data from cold storage (HDD/SSD, etc) into RAM only has to be done once on startup, though. After that single slow startup, all of the additional queries happen super fast. Your complaint only makes sense if you only intend to perform one single query against a dataset.
- paulsutter 8y ago
  My complaint makes sense because I might have 100B records per day to process, and the delay to load them up is significant
  - wmf 8y ago
    Could you stream the data into memory as it is created so that loading data isn't in the critical path?
    - AboutTheWhisles 8y ago
      Does this software do that?

nkurz 8y ago

I have encountered multiple claims that in-memory analytics databases are often constrained by memory bandwidth, and I myself held that misconception for longer than I want to admit. So if not memory-bandwidth, what is the constraining factor? Or specifically, what's the limiting factor that causes the observed benchmark speeds for LocustDB? My guess would be that a well designed in-memory database should be limited by memory-bandwidth, but that real-world memory-bandwidth shouldn't be thought of as a single number. Instead, it depends on the size and pattern of requests, the latency of different cache hierarchies, and (crucially but often forgotten) on the number of requests in flight. Is there a better terminology that distinguishes these two uses of memory-bandwidth?

blattimwind 8y ago
> there a better terminology that distinguishes these two uses of memory-bandwidth? theoretical peak bandwidth effective/observed bandwidth
- nkurz 8y ago
  That's not the distinction I'm looking for. I'm looking for a better term to describe the throughput the application would achieve if all non-memory constraints were removed. In the context of the article, the author is comparing the ratio of the "effective/observed bandwidth" and the "theoretical peak bandwidth", noting that the ratio is large, and concluding that he is not constrained by memory bandwidth. I don't know that this is a reasonable conclusion. I'm looking for a different denominator, which is also theoretical. Maybe call it the "theoretically achievable bandwidth", which takes into account all the details of the requests. If the "observed bandwidth" equals the "theoretically achievable bandwidth", your only path to improvement is to change your data layout or increase the parallelism of your requests. If the "observed bandwidth" is less than this theoretical, there should still be room for implementation optimizations. Falling short of the "theoretical peak bandwidth" (even by a lot) doesn't tell you which of these is the case.
  - squeed 8y ago
    Hmm. I'm not exactly sure if it's what you mean, but in the networking world, we use the term "Goodput" as a shorthand for "actual user-observed bandwidth".

golanggeek 8y ago

cwinter does Locust also support scalability across machines.. what is the timeline for it to be production ready..

emmelaich 8y ago

It'd be interesting to compare this against traildb. http://traildb.io/ http://traildb.io/

ryanworl 8y ago
This is a very interesting project! Thank you for posting it. Having read both this post and the documentation for TrailDB, I don’t think they are comparable past very simple use cases. This post seems to be covering a complete database system with a query parser, planner, and (previously?) a storage engine. TrailDB is more like a storage engine you can push certain kinds of filters down into, but performs neither query parsing nor planning for you.
- emmelaich 8y ago
  True. But the latest blog post for TrailDB mentions two new query interfaces, trck and reel. I also wouldn't be surprised if someone has done a sqlite backend for it.
mkarlsch 8y ago
Interesting project, however it looks like the last commit is from September 2017 so either it is just very stable or not maintained anymore?

bcaa7f3a8bbc 8y ago

Past: How to Analyze Billions of Records per Second on a Supercomputer Present: How to Analyze Billions of Records per Second on a Single Desktop PC Future: How to Analyze 5 Records per Second on Thousands of Machines Distributed Across the Globe Over the Blockchain.

TrevorJ 8y ago
That's axiomatic of the direction consumer software has gone over the last 20 years as well. How a word processor can feel sluggish on a modern PC is beyond me.
- zeusk 8y ago
  Because that word processor can do a lot more things and is not the sole thing running on a modern machine.
  - _emacsomancer_ 8y ago
    and, one would imagine, a lot of accumulated cruft.
    - zeusk 8y ago
      what you're calling cruft is some other person's usecase.
      - frankling_ 8y ago
        I'd still prioritize the basic use case of comfortably typing words, which to my admittedly sensitive mind is not supported that well anymore by some common pieces of software. Between the apparent round-trip per keypress in Outlook Web, the noticeable general display delay of Windows 10, and the frequent wait for characters to appear in a reasonably small Powerpoint presentation, I feel like I might be holding it wrong.
      - TeMPOraL 8y ago