Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

100k TPS over a billion rows: the unreasonable effectiveness of SQLite

andersmurphy.com

Post reply on HN
Print view View original

121–130 of 169 posts

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#121

Post by Scaevolus »

rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

WAL2 might help with the checkpointing problem: https://sqlite.org/src/doc/wal2/doc/wal2.md

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#122

Post by rich_sasha »

asa400 wrote: post #120
rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

From your experience, would you call these behaviors bugs, or are they more known issues that result from SQLites specific implementation quirks? What kinds of workloads were you throwing at it when these types of issues happened? Asking as someone who really enjoys and respects SQLite but hasn't encountered these specific behaviors before.

I was pushing SQLite quite hard. My DB was at peak 25GB or so. Occasional queries of O(1e6) rows while simultaneously inserting etc. Many readers and a few writers too. Id expect some degradation, sure, but Id say it wasn't very graceful.

I think, however, I was well within the parameters that SQLite maximalists would describe as within th envelope of heavy but fine usage. YMMV.

I found a very small number of people online with the exact same issues. Enough to know I'm not hallucinating, but not enough to find good support for this :/ but, TLDR, forcing WAL truncation regularly fixed it all. But I had to do it from an external process on a heartbeat, etc etc

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#123

Post by rich_sasha »

Scaevolus wrote: post #121
rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

WAL2 might help with the checkpointing problem: https://sqlite.org/src/doc/wal2/doc/wal2.md

Iirc it was somewhat experimental when I last used SQLite like this. Is is not considered mainstream? Thanks for the top tip.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#124

Post by whatevermom3 »

aquariusDue wrote: post #108
ethanwillis wrote: post #40

Earlier quoted context omitted.

This might be true in terms of direct monetary costs. I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful. Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash. I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so. Otherwise, I think I'd rathe…

It's weird seeing people on HN complain about this aspect regarding Hetzner because it's the complete opposite of my experience. Two years I've rented a dedicated server for around 40 euros monthly from Hetzner as a business customer and I had no issues whatsoever. They didn't ask for a business license or personal ID or anything really, I provided a VAT ID along with a business name and address but it wasn't anythin…

Same, Hetzner has always been very flexible with me when it comes to practically anything. It's always been humans answering my queries, with of course various quality but overall quite good especially for the price. I gave them some VAT number to get reduced prices at some point and that was it :shrug:

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#125

Post by andersmurphy »

tomasol wrote: post #83
andersmurphy wrote: post #80

Earlier quoted context omitted.

PRAGMA synchronous="normal" is fine if you are in WAL mode. The database cannot be corrupted by power loss unlike in journal mode. > The synchronous=NORMAL setting provides the best balance between performance and safety for most applications running in WAL mode. You lose durability across power lose with synchronous NORMAL in WAL mode, but that is not important for most applications. Transactions are still atomic, c…

fsync is the most expensive operation during a write. NORMAL mode means you don't care whether last ~100 ms of transactions before a process crash / VM restart are going to be persisted or not. My suggestion is either to use synchronous="full" or disable `synchronous_commit` on Postgres to avoid comparing apples to oranges. Edit: Also, the example indicates financial transactions. Can you explain why you need seriali…

Very good point I've added an epilogue to the post with updated numbers

Really shows the power of dynamic batching.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#126

Post by andersmurphy »

NorwegianDude wrote: post #114

This is very misleading. The secure defaults for sqlite is changed, so commits are not actually written to the disk. Running sqlite like this will cause data loss on os crash or power loss.

I've added numbers for synchronous FULL to the article for those who are interested.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#127

Post by andersmurphy »

slashdave wrote: post #109
SJC_Hacker wrote: post #106

Earlier quoted context omitted.

SQLite can also do in memory

Yeah, very good point. It all comes down to requirements. If you require persistence, then we can start talking about redundancy and backup, and then suddenly this performance metric becomes far less relevant.

Backups are to the second with litestream.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#128

Post by andersmurphy »

runako wrote: post #118
badmonster wrote: post #20

The real insight here is recognizing when network latency is your bottleneck. For many workloads, even a mediocre local database beats a great remote one. The question isn't "which database is best" but "does my architecture need to cross network boundaries at all?"

So much this. My inner perf engineer shudders every time I see one of these "modern" architectures that involve databases sited hundreds of miles from the application servers.

This article is very much a reaction to that. The problem is the problem as Mike Acton would say.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#129

Post by andersmurphy »

gunnarmorling wrote: post #85
BugsJustFindMe wrote: post #39

Earlier quoted context omitted.

> If you have to keep the initial requirement for your software, then SQLite is completely out of equation. No it isn't? You can run a thin sqlite wrapping process on another server just fine. Ultimately all any DB service is, PostgreSQL included, is a request handler and a storage handler. SQLite is just a storage handler, but you can easily put it behind a request handler too. Putting access to sqlite behind a seri…

You could do that, but you'd run into exactly the same bottleneck the author describes with a remote Postgres instance. The workload exposes high contention on hot rows. If transactions are kept open for several milliseconds due to this being a remote network call between client and DB server, throughput will be equally limited also when using SQLite.

Yeah this is a very good point. Any sort of network drive will have similar issues with SQLite. You're very much wanting attached NVME.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#130

Post by andersmurphy »

jaketoronto wrote: post #62
jaketoronto wrote: post #60

Are you limiting your # of connections to postgres to 8? Is this unnecessarily throttling your throughput? This seems like quite the bottleneck... connection pools are good when your app is overwhelming your db.. but in this case, you really should be trying to put more load on Postgres... I'm concerned that this whole experiment is tainted by this choke point. I would love to see this tested again with a much larger…

To further explain : You mention setting the conn pool to 8 to match your # of cores. That would be fine if you didn't have any sleeps inside of your txns... But the moment you added the sleeps inside the txns, your limit of 8 kills through throughput... because no other thread can access the DB once 8 of them grab connections and start the 20ms of total sleep. Imagine instead if you had 64 connections... you would 8…

Added an epilogue that covers larger connection pools and the results.
Post reply on HN