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

111–120 of 178 posts

Re: Why I Built Litestream

#111

Post by qbasic_forever »

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 SQLite or any other database.

The main point here is that instead of reaching for a costly to maintain, to configure, to secure, etc. DBMS first, many folks would likely be better served by the vastly simplified management of a single SQLite process.

Re: Why I Built Litestream

#112

Post by lrossi »

benbjohnson wrote: post #32
bob1029 wrote: post #24

> “But nobody writes production applications with SQLite, right?" We've been doing it for 5 years now. Basic tricks we employ are: Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer a…

Those are awesome tips. I didn't even think of using "user_version" for storing a migration version. I'm definitely stealing that trick.

This blog post gives a nice example of how that could be implemented:

https://levlaz.org/sqlite-db-migrations-with-pragma-user_ver...

Re: Why I Built Litestream

#113

Post by lrossi »

bob1029 wrote: post #103
the_duke wrote: post #99

Earlier quoted context omitted.

Out of curiosity, what kind of read and write concurrency is your application dealing with? In my experience, sqlite performance becomes problematic quite quickly, even with settings you mentioned (WAL etc).

We are able to get reads on the order of 10k/s+, and writes on the order of 5k/s+ using NVMe drives and practical serialized business object sizes (0.1~5 megabytes). I can easily saturate an NVMe drive using SQLite. In fact, it is substantially easier to max out storage devices with SQLite and carefully-tuned code than it is with something like SQL Server. I should amend my original post, because I know a lot of deve…

> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId

You mean for generating unique primary keys? Why would last insert row id be slow?

> and in cases where transactional scopes are required

Could you elaborate on what you mean by this?

Re: Why I Built Litestream

#114

Post by benbjohnson »

lrossi wrote: post #108
benbjohnson wrote: post #60

Earlier quoted context omitted.

That's a fair point. I've updated the post to read, "That translates to thousands of requests per second per core" instead of saying it's linear scaling. Thanks for the feedback!

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

Re: Why I Built Litestream

#115

Post by polyrand »

lrossi wrote: post #107
polyrand wrote: post #98

Regarding SQLite's performance, some things I've found very useful: Use WAL mode (writers don't block readers): PRAGMA journal_mode = 'WAL' Use memory as temporary storage: PRAGMA temp_store = 2 Faster synchronization that still keeps the data safe: PRAGMA synchronous = 1 Increase cache size (in this case to 64MB), the default is 2MB PRAGMA cache_size = -64000 Lastly, use a modern version of SQLite. Many default inst…

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.

Re: Why I Built Litestream

#116

Post by bob1029 »

lrossi wrote: post #113
bob1029 wrote: post #103

Earlier quoted context omitted.

We are able to get reads on the order of 10k/s+, and writes on the order of 5k/s+ using NVMe drives and practical serialized business object sizes (0.1~5 megabytes). I can easily saturate an NVMe drive using SQLite. In fact, it is substantially easier to max out storage devices with SQLite and carefully-tuned code than it is with something like SQL Server. I should amend my original post, because I know a lot of deve…

> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId You mean for generating unique primary keys? Why would last insert row id be slow? > and in cases where transactional scopes are required Could you elaborate on what you mean by this?

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.

Re: Why I Built Litestream

#117

Post by flak48 »

tnorthcutt wrote: post #95
chrisweekly wrote: post #30

Earlier quoted context omitted.

$200k/mo is remarkable for a "side" business! Can you share more of your story?

Sounds like it's https://cravecookie.com/ – so my guess is it's $200k/mo in revenue, not profit... but that's still seriously impressive, assuming at least decent margins!

They mention margins of 35-40% percent here: https://www.indiehackers.com/podcast/166-sam-eaton-of-crave-...

So, $70k - $80k a month in profits

Re: Why I Built Litestream

#118

Post by oconnor663 »

thaumasiotes wrote: post #88
pbowyer wrote: post #82

Earlier quoted context omitted.

I'm a native speaker and it confused me to. If I make $50k, I mean I am taking home $50k If I've revenue of $50k, I am taking home less than $50k

> If I make $50k, I mean I am taking home $50k If I've revenue of $50k, I am taking home less than $50k That is not normal native usage. If you make $50k, you're salaried at $50k, but you take home considerably less than that.

Another native speaker here: I think the fact that we can debate this is evidence enough that it's confusing :) Yes with salary I usually say I "make" my pre-tax income. But I don't know if that's really what "make" means per se, or if that's just a side effect of how most jobs advertise salaries in pre-tax terms. (Also I assume this usage is actually older than the income tax.) In my mind, if someone tells me they "make $X / month" from a business, it sounds to me like they're trying to draw a comparison with "making a salary of $X / year", which is a lot closer to profit than revenue.

I guess in the end, it's just uncommon to say something like "Microsoft made $X billion last year" by itself, because it's just not clear what it means. Business news articles will almost always phrase something like that as "made $X billion in profits" etc.

Re: Why I Built Litestream

#119

Post by Aeolun »

bob1029 wrote: post #116
lrossi wrote: post #113

Earlier quoted context omitted.

> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId You mean for generating unique primary keys? Why would last insert row id be slow? > and in cases where transactional scopes are required Could you elaborate on what you mean by this?

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.

Re: Why I Built Litestream

#120

Post by Aeolun »

tnorthcutt wrote: post #95
chrisweekly wrote: post #30

Earlier quoted context omitted.

$200k/mo is remarkable for a "side" business! Can you share more of your story?

Sounds like it's https://cravecookie.com/ – so my guess is it's $200k/mo in revenue, not profit... but that's still seriously impressive, assuming at least decent margins!

That’s about 60k cookies per month, or about 1 cookie every 50 seconds.
Post reply on HN