My interim report on this slow query is at Query during backup takes too long, does not finish in the hope of seeing if its other slow query shows the same symptoms under lots of general-purpose performance tools.
Summary is that SQLite is reading at a crazy rate (maybe about 200 MB/sec, but not actually to the drive). Writing doesn’t seem to be happening much, which makes me wonder if 17 days’ run made any headway.
Architecture of SQLite makes me wonder if the super heavy read sequences are from the Pager using up all its cached pages (cache is indirectly configurable, so experiment is possible), so thrashing I/O instead.
The other issue at least maybe has hope of getting a database bug report with the relevant tables intact to attempt a performance analysis (and maybe SQL revamp) because it doesn’t use File/FixedFile table.
It also had a query that was semi-slow before things fell off the cliff. Maybe that one could be tuned instead.
Ick. I’ve been sort of wondering if there are any SQLite issues in here, but it’s hard to prove. Have you seen any well-explored-and-documented writeups? If any workarounds were identified, that would be even better.