Hacker News Reader

Comment by tiffanyh | Hacker News Reader

In 2018, folks were getting 4M queries per second using SQLite (BedrockDB) [0].

This was also accomplished from just 1 server, not needing 40 shards like blog post.

[0] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

The Expensify test was targeted at demonstrating the upper limits of SQLite itself. The hardware used: > 1TB of DDR4 RAM > 3TB of NVME SSD storage > 192 physical 2.7GHz cores (384 with hyperthreading)

And a very simple read-only query w/o overlapping rows, with the full dataset already loaded in memory: > The query itself just sums a range of 10 rows randomly located inside 10B row (447GB) two column database of random integers, one of which is indexed and the other not. This means that the test is fully cached in RAM, and the database is precached prior to the test. No writes are done during this test, only reads.

All that is good and fine for that purpose -- to demonstrate the limits of what SQLite can do in scaling UP.

The purpose of the PlanetScale post was demonstrating linear scalability by scaling OUT using a realistic workload (TPC-C is a warehouse / ecommerce site simulation) on small commodity VMs (ec2 isntances).

As noted, you're comparing two very different tests that had very different goals.

Genuinely appreciative of this comment. I feel like I learned/refreshed a lot of (should be) “common” knowledges about the subject of DBs.

Apples to oranges comparison. The benchmark in the PlanetScale blog post is based on TPC-C.

i strongly dislike planetscale and even i think this is not a useful or fair comparison .. sqlite works with local files, much simpler, no network latency, no HA

> i strongly dislike planetscale and even i think this is not a useful or fair comparison .. sqlite works with local files, much simpler, no network latency, no HA

To be fair, if you accomplish the same effect without needing distributed computing, then the solution gets much simpler, you cut out worrying about network latency and HA*

* the probability of node failure grows very rapidly with the number of nodes. Same reason you probably don't need RAID for 1 disk, but you definitely do for a dozen

At that scale any blip on the node cut out millions of users? no one sane would do that with a single box.

You can still do primary/stand-by replication.

If you actually crunch the numbers, you'll see that the likelihood of multiple node simultaneous failure of a larger cluster is actually often as high, or higher than the likelihood of a single node failure on a non-distributed application.

Despite running redundant distributed set-ups, services today really don't have particularly spectacular availability. Google, Facebook, Twitter, Github; all go down or brown out frequently.

Keep a replica.

I respect your intellectual honesty here.

why do you dislike t?

No one is running their OLTP and batch processing workloads on sqlite (bedrock or not) at scale. There may be unique cases for specific situations but show me an org with large and broad concurrency demands running sqlite at scale and I'm happy to be proven wrong.

That sounds like a leadership problem rather than an indictment of sqlite

No, it's pretty much an sqlite problem. As good as it is for limited concurrency situations (and synthetic benchmarks like this expensify blog), it does not compare to mysql or postgres for mediating hundreds or thousands of transactions per second.

One of the current Hacker News shibboleths:

If SQLite is not the solution, change the problem.

Haha too true !

If I may add:

World: "We solved Cold Fusion !"

HN: "Meh"

World: "We solved Cold Fusion in RUST !"

HN: "Shuttup you demi-god of tech and take my upvote !"

Maybe, maybe not, but that wasn't the claim here. It was just a shitty argument against sqlite, one that could equally apply to mysql, lisp, questdb, apl, and a host of other tech if it were valid. Organizations choose tech stacks for a ton of reasons, most of which are only loosely related to performance and maintenance characteristics. The (proposed) fact that most organizations don't scale out sqlite doesn't say much at all about whether doing so I'd a good idea.

My personal DB solution actually averages 45 GiB / second on my laptop on a single thread and scales 1-1 with threads. It's way faster than SQLite. This is painfully mediocre performance.

You have to make engineering trade-offs for this, though. In my case, you can only recover 1 KiB records. It doesn't support compression. And you can only write to /dev/null.

To read data from this DB (which I built with command-line tools any Unix has), run:

   dd if=/dev/zero of=/dev/null bs=1K count=${count}

You can vary the $count parameter to get more data.

Scales 1:1 with threads:-) Nice Trolling ;)

"Granted, it’s not SQLite by itself. We’ve wrapped it in a custom distributed transaction layer ..."

How is this any different than the blog post that uses Vitess, which is a custom wrapper around MySQL?

BedrockDB is a custom wrapper around SQLite.

This is not SQLite though, also the test is trivial compared to TPC-C: https://github.com/Expensify/Bedrock/blob/dbarrett_perftest/...

Only read all in memory lol.

And with awk you can query 10M lines per second.

So what you're saying is that the same type of queries were ran in expensify's case and in planetscale's case, therefore we should not be excited for running 1m queries per second using MySQL?

Reply on news.ycombinator.com