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

Why I Built Litestream

litestream.io

Post reply on HN
Print view View original

121–130 of 178 posts

Re: Why I Built Litestream

#121

Post by iampims »

qbasic_forever wrote: post #111
haolez wrote: post #106

But what's the endgame of this approach regarding scaling? If I ever need horizontal scaling, am I screwed? Genuinely curious :)

This isn't really a new approach to scaling--even if you're using a server DBMS like postgres/mysql/etc. you still have to figure out scaling based on your data. If that data is easily isolated into customer/user units then it's straightforward to shard them across more instances, whether they're more postgres boxes or SQLite processes. If the data isn't easily sharded... well you're no worse off whether you're using…

I don’t think the comparison is fair with regard to scaling. Imagine this situation: you’d like to have 2 instances of your application running on two different machines, and you’d like to rollout updates with no downtime. It’s trivial with MySQL/Postgres, but it’s tricky with sqlite.

A lot of solutions don’t take into consideration “day 2” operations: mostly around no downtime upgrades.

Re: Why I Built Litestream

#122

Post by bob1029 »

Aeolun wrote: post #119
bob1029 wrote: post #116

Earlier quoted context omitted.

LastInsertRowId is not slow, but if you are inserting on the same connection from multiple threads, you will require a mutex or you will be getting other threads' row ids. Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.

So the mutex in sqlite (for multiple connections) is worse than the one you implement in your own application? I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.

Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code.

We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.

Re: Why I Built Litestream

#123

Post by justsomeuser »

On GCP I do something similar with regional disks:

- Disks are replicated to another zone on every write.

- Incremental disk snapshots can run once per hour and are stored in Cloud Storage.

This means when the OS fsync's it is actually copying those bytes to another zone.

https://cloud.google.com/compute/docs/disks#repds

Re: Why I Built Litestream

#124

Post by haolez »

qbasic_forever wrote: post #111
haolez wrote: post #106

But what's the endgame of this approach regarding scaling? If I ever need horizontal scaling, am I screwed? Genuinely curious :)

This isn't really a new approach to scaling--even if you're using a server DBMS like postgres/mysql/etc. you still have to figure out scaling based on your data. If that data is easily isolated into customer/user units then it's straightforward to shard them across more instances, whether they're more postgres boxes or SQLite processes. If the data isn't easily sharded... well you're no worse off whether you're using…

Sounds similar to CouchDB's approach, but I've never used that either.

Re: Why I Built Litestream

#125

Post by qwantim1 »

benbjohnson wrote: post #114
lrossi wrote: post #108

Earlier quoted context omitted.

That’s nice. But I didn’t really take it literally. Do you have some benchmark results by any chance? Although it’s a bit of a can of worms, and I would understand if you didn’t want to get into it at this time.

I've only done some light benchmarking so far. I had it running on a two-core DigitalOcean machine with sustained write load to test for race bugs and it was replicating 1K+ writes per second. But honestly I haven't even tried optimizing the code yet. I'm mainly focused on correctness right now. I would bet it could get a lot faster. https://twitter.com/benbjohnson/status/1351590920664313856

Sharding is not really scaling for many, and I think despite your good intentions you may be misleading others. I’m glad you like the setup but people flocking to SQLite scares me.

Re: Why I Built Litestream

#126

Post by sciurus »

With this approach, people have to start sharding their data as soon as one server can't handle their application and sqlite usage.

Why not advocate instead for people to start out running their application and postgresql on the same server, with wal-e for backups to S3?

That has almost the same benefits while everything fits on one server, but opens up alternative approaches to scaling if one day things no longer fit.

Re: Why I Built Litestream

#127

Post by [deleted] »

polyrand wrote: post #115
lrossi wrote: post #107

Earlier quoted context omitted.

The negative value in the cache size pragma seems a mistake, but it isn’t. Negative values are used to specify a cache size that is a multiple of the page size (more or less) and the sign is discarded. Not a great choice of API IMHO, but it’s a database, I’ve seen much worse. https://www.sqlite.org/pragma.html#pragma_cache_size

I agree it's a very weird API, but you get used to it. In python, when initializing the DB I always use: conn.execute(f"PRAGMA cache_size = {-1 * 64_000}") That way I never forget about the minus.

[deleted]

Re: Why I Built Litestream

#128

Post by uniqueid »

This is off-topic, but when I was a Mac user, I always wishes Apple built a barebones graphical tool into the OS to view and edit SQlite databases. They pop up now and then, including via Apple applications, and it would be convenient to view their contents in the GUI without third party tools.

Re: Why I Built Litestream

#129

Post by awill »

"Solutions such as Kubernetes tout the benefits of zero-downtime deployments but ignore that their inherent complexity causes availability issues."

This is completely accurate. I've seen several teams do kubernetes, only to both spend 50% of their dev time on ops, AND cause outages due to kubernetes complexity. They do this all while boasting about zero downtime deployments. It's comical really.

Re: Why I Built Litestream

#130

Post by liuliu »

bob1029 wrote: post #122
Aeolun wrote: post #119

Earlier quoted context omitted.

So the mutex in sqlite (for multiple connections) is worse than the one you implement in your own application? I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.

Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code. We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.

That doesn't sound right. SQLite's lock for writes is not the best, but it is still pthread mutex under the hood. Are you sure your compilation options for SQLite is right? One common pitfall is compiling without `-DHAVE_USLEEP`. In absence of that flag, SQLite will use sleep in case of conflict, and that will have time resolution of 1 second, causing 1s delay on every lock contention. That flag tells SQLite to use usleep instead, and it is substantially faster on busy timeout.

Here is my SQLite compilation flags: https://github.com/liuliu/dflat/blob/unstable/external/sqlit...

Here is where the flag used: https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d...

Post reply on HN