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

91–100 of 169 posts

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

#91

Post by masterj »

DenisM wrote: post #6
matthewaveryusa wrote: post #3

The only caveat being this assumes all your data can fit on a single machine, and all your processing can fit on one machine. You can get a a u-24tb1.112xlarge with 448 vcores, 24TB RAM for 255/hour and attach 64TB of EBS -- that's a lot of runway.

Scale-up solves a lot of problems for stable workloads. But elasticity is poor, so you either live with overprovisinoed capacity (multiples, not percentages) or fail under spiky load which often time is the most valuable moment (viral traffic, Black Friday, etc). No one has solved this problem. Scale out is typically more elastic, at least for reads.

I suspect that for a large number of orgs accepting over-provisioning would be significantly cheaper than the headcount required for a more sophisticated approach while allowing faster movement due to lower overall complexity

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

#92

Post by gunnarmorling »

Tractor8626 wrote: post #65

Sqlite is very cool. But what was the point of slowing postgress down? > But, wait our transactions are not serialisable, which they need to be if we want consistent transaction processing You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here. > Let's say you have 5ms latency between your app server and your database. 5ms latency is unrealistic. U…

> No they are not common at all. You probably invented them just to make pg look bad.

Interactive transactions are a well-defined concept in database technology, exactly with the semantics described by the author: transactions with multiple queries, with application logic in between, for instance taking a result from one query, processing it, and running a second query with the outcome of that processing as input.

That said, the example in the blog post feels a bit contrived to me, the question being whether that transaction could be structured in a why so that both updates run right after each other towards the end of the transaction, thus significantly reducing the duration of the row lock being held (which is not to say that the general problem described in the article doesn't exist, it does for sure).

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

#93

Post by andersmurphy »

gunnarmorling wrote: post #92
Tractor8626 wrote: post #65

Sqlite is very cool. But what was the point of slowing postgress down? > But, wait our transactions are not serialisable, which they need to be if we want consistent transaction processing You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here. > Let's say you have 5ms latency between your app server and your database. 5ms latency is unrealistic. U…

> No they are not common at all. You probably invented them just to make pg look bad. Interactive transactions are a well-defined concept in database technology, exactly with the semantics described by the author: transactions with multiple queries, with application logic in between, for instance taking a result from one query, processing it, and running a second query with the outcome of that processing as input. Th…

I wanted the example to be simple and relatable. So I went with something close to the transaction example on the Postgres site (which to be honest also feels contrived).

In practice I'd never implement a ledger like this these days, I'd much prefer an append only model with some triggers. But, like you said interactive transactions are very much a thing.

Out of curiosity do you have a better/less contrived example in mind?

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

#94

Post by Tractor8626 »

andersmurphy wrote: post #71
Tractor8626 wrote: post #65

Sqlite is very cool. But what was the point of slowing postgress down? > But, wait our transactions are not serialisable, which they need to be if we want consistent transaction processing You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here. > Let's say you have 5ms latency between your app server and your database. 5ms latency is unrealistic. U…

> You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here. If you're processing financial transactions you want your isolation level to be serialisable. As the order in which the transactions are processed matters. > 5ms latency is unrealistic. Unless you use wifi or you database is in another datacenter. Even with 1ms latency. Amdahl's law will sti…

> If you're processing financial transactions you want your isolation level to be serialisable. As the order in which the transactions are processed matters.

So you don't know what serializable level is.

> Even with 1ms latency. Amdahl's law will still make you cap out at a theoretical 1000 TPS if you have 100% row lock contention.

So why use 5ms and 10ms for examples?

> I'm confused. I invented transactions?

"Interactive" transactions.

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

#95

Post by ch2026 »

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…

Yeah I don’t buy this article. Something is off. I run vastly more TPS on a very barebones MySQL install over the network.

Edit: Keep downvoting. I hate this fucking site.

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

#96

Post by cwillu »

tomconnors wrote: post #37

Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a new EC2 instance or whatever and once it's online kill the old one. That gets 0 or close to 0 downtime. If the DB is on the same instance, replacing it requires loading up the DB onto the new instance, which seems more error prone than just restarting the app o…

Sqlite supports multi-process access, so as long as you've configured to permit that, you can do the same “start new process before retiring the old one” dance.

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

#97

Post by wesammikhail »

earthnail wrote: post #48
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…

Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.

I was asked for a passport photo when I tried to open an account. They literally asked for a passport photo immediately after the signup form. Like WHAT? I couldn't believe my eyes. The most insane shit I've ever seen.

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

#98

Post by benjiro »

formerly_proven wrote: post #43
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…

Well that's just dqlite/rqlite.

> Well that's just dqlite.

Far from it, as now your not just dealing with network but also with raft consensus... So each write is not just a network trip, its also 2x acknowledging. And your reads go over the leader, what can mean if somebody accessed node 1 app but node 2 is the leader, well, ...

Its slower on reads and writes, then just replications that PostgreSQL does. And i do not mean async but even sync PostgreSQL will be faster.

The reason dqlite exists is because canonical needed something to synchronize their virtualization cluster (lxd), and they needed a db with raft consensus, that is a lib (as not a full blown server install like postgres). Performance was not the focus and its usage is totally different then most people needs here.

Post reply on HN