161–170 of 330 posts
It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom b…
Earlier quoted context omitted.
> So, why not use a safer choice to begin with? I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick. People underestimate "simple" tech performance, in 99% of projects by the ti…
Which part of running MySQL instead SQLite is over engineering?
- Setting up an out-of-repo config file on the server with your MySQL credentials
- Setting up a backup script for your server data
It's only about an hour of work total, but it's an hour of work that I hate doing.
One of my previous employers was using SQLite as a large distributed database - they had their own custom sharding strategy, but essentially the idea was to shard A accounts * B tables * C num_of_days with a .db file for every shard. When I first came and saw it, it...did not sound right. But I didn't want to be the guy who comes in and says "you are doing it wrong" month 1. So I went along with it. Of course, eventu…
Do you have any more information about the situation?
Could it have been in the hand spun partitioning logic instead of SQLite?
What was the ingestion throughout roughly?
Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…
Litestream is indeed a missing piece of the puzzle. But it also defeats some of the purpose of using an embedded database library in the first place. Now you're back to juggling separate processes once again.
For sake of argument, let's say I have a fixed schema/format that will never change and I never need to aggregate queries across multiple customer accounts. Also, let's say writes to a single database are never going to be more than a hundred concurrent users. Why shouldn't I store each tenant's data in its own SQLite database? It makes it very easy for local client apps to download their data all at once. Backups are incredibly easy. Peer-to-peer synchronization behaves like a git repository merge. Why shouldn't I do this?
I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…
Edit: a several hundred million txns over a few hours math. How many per second? ~500
According to here (question 19), for old HDDs you could expect 3 orders of magnitude improvement by using bigger txns. Not sure SSD wise but worth a shot.
It's probably elsewhere but I don't realize it.
I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…
[1] https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
It blew up big time. I would have saved myself lots of trouble if I had just gone with postgres from the getgo. The workload was simple (single node work tracking) and I didn't expect it to become a bottleneck. Unfortunately, there were some default settings in the storage backend (tiny page size or WAL or something) that caused severe thrashing and a dearth of tooling to track down the issue. After making a custom b…
If you have that thread would be great to see it as well.
Earlier quoted context omitted.
Litestream is indeed a missing piece of the puzzle. But it also defeats some of the purpose of using an embedded database library in the first place. Now you're back to juggling separate processes once again.
https://github.com/backtrace-labs/verneuil is in-process (precisely to minimise deployment pain).
"This effort is incomparable with litestream: Verneuil is meant for asynchronous read replication, with streaming backups as a nice side effect. The replication approach is thus completely different. In particular, while litestream only works with SQLite databases in WAL mode, Verneuil only supports rollback journaling"