Learning a few things about running SQLite
67 points by tumdum
67 points by tumdum
As that other post said, SQLite really needs to steal the idea of “editions” from Rust.
Just a new entry point that takes a version number and sets version-appropriate defaults would do me.
I find the poor defaults offputting enough that I don’t use SQLite very often just because I have to relearn so much just to make it behave reasonably.
We just built that into our sqlite library; always sane defaults and everyone has to use it.
feeling very nerd-sniped by the question of why deleting a bunch of rows should take > 5s! I agree with the OP that that doesn't sound right at all.
Me too. My best guess is there’s an expensive trigger firing? I dunno what kinds of schemas Django creates.
5s seems a bit short for a busy timeout. If it’s causing trouble, why not increase it to, say, 10s?
A thing that can make deletes slow, thought I somewhat doubt it's what happened here: with foreign keys, deleting a thing can cascade to related tables. If a user can have comments and votes, then if the user is deleted, either the comments and votes get deleted (on delete cascade) or changed to no owner (on delete set null) or the database just notices related things still exist and disallows the delete (on delete restrict). But for any of these the database looks in all the tables for things that might refer to the to-be-deleted object, to make sure it doesn't leave references to users that don't exist.
Some databases force you to make all the indexes to make those checks fast--if you're going to declare users 'own' votes through a foreign key constraint, you must have an index to look up votes by user efficiently. SQLite, however, doesn't require you to do that, and if none is created a delete of a 'parent' object (like a user) might scan the entire table of potential 'children' (like the users' votes)!
One reason I think this might not have been the problem here is that Django should create the indices you need for deletes to be efficient if you declare a ForeignKey in your schema. But I have to admit I only thought of that once I was a ways through writing the rest of the comment 😅
Not directly SQLite related but I found this interesting regarding Restic (used for the backups):
Sometimes the backup gets OOM killed and so it stays locked, do an unlock
I never noticed Restic using a lot of RAM. Is this with certain backends? Or huge filesystems or repositories? My restic repo is about 125GB, the backed up filesystems contain about 350K files and the peak memory use I see during backups and other restic operations is 250-300MB.
I recall chatting about this in the restic Libera channel awhile ago. Restic loads the repo's entire index into memory, so I don't think it depends on the backend. I ran into this because I also was trying to back up a tiny system into a repo that was shared with many other larger backups. It loads the entire index, not just portions of it related to the backup you're taking.
The index mainly consists of hashes of blobs which means as your repo increases in size it gets bigger. One of restic's maintainers talks about it at the link below. For reference, one file becomes one or more blobs, though they are de-duped. Biggest takeaway is:
The rule of thumb is that 1GB RAM is necessary for every 7 millions blobs.
https://forum.restic.net/t/memory-usage-oom-etc-on-a-big-repository/8758/2
Regarding slow deletion in “cleaning up the database is tricky”:
What’s the order of magnitude of the number of affected rows? If you really have many thousands of rows to delete, small batches are definitely the way to go (with one single transaction per batch), but 5 seconds is an eternity for an SQLite web app.