Tom, thank you — those numbers settle it, and they’re worse than my extrapolation.
The second run is the telling one. On the RPi3 datapoint earlier in the thread, the repeat query dropped from 21 s to 2.6 s once the table was in page cache. Yours barely moved: 193 s cold, 147 s on the repeat, with user+sys only about 12 s of that. So roughly 135 seconds of every scan is I/O wait, even on a repeat — with 1 GB shared across five containers, your 1.4M-row table doesn’t stay cached at all. The 9-second COUNT(*) says the same thing independently.
Your MIN(dateTime) at 0.003 s is the perfect control: same table, satisfied straight from the primary key, instant. The pm2_5 scan is slow for exactly one reason — the column is empty, so SQLite must examine all 1.4M rows to prove there’s no value anywhere.
The arithmetic on beta3, on your hardware: the search list ran once per generated file, 14 files per cycle. Your schema has no pm2_5_aqi column, so that query fails instantly and only the pm2_5 scan runs — one scan per file, 14 per cycle, at ~150 s each. That’s on the order of 35 minutes per report cycle against a 300-second archive interval — seven times over budget. Not “seldom completes”; structurally can’t. And every scan held a read lock the archive writer eventually collided with.
beta4 runs that scan once per cycle instead of 14 times — ~150 s, which fits inside your 300 s interval with room for the rest of the cycle. That matches what you reported.
Either way, the fix I’ve been advocating stands, and your numbers are the best case for it: bound the lookback to a day or two and that 150-second scan becomes milliseconds, on any hardware, at any database size.
sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1;
Run Time: real 193.630 user 3.783819 sys 8.312705
sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1;
Run Time: real 147.827 user 3.478416 sys 8.748866
sqlite> SELECT COUNT(*) FROM archive;
1391014
Run Time: real 9.171 user 0.119921 sys 0.614045
sqlite> SELECT MIN(dateTime) from archive;
1367620800
Run Time: real 0.003 user 0.000164 sys 0.000395 -- Tom Mitchell [email protected]"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell On Tue, Aug 4, 2026 at 6:23 PM 'John Kline' via weewx-user < [email protected]> wrote: I found what actually made beta4 faster — it isn’t the aqi_source gate.
beta4 caches the search list across generated files. get_extension_list() now builds everything once on the first file of a report run, saves the result, and hands every subsequent file a copy, refreshing only the per-template pieces (the timespan binder and the NOAA file index). The comment in init says it plainly: one search-list extension instance per report run.
In beta3 the full build ran for every one of the 14 generated files — 28 archive scans per cycle. In beta4 it runs once — 2 scans per cycle. That’s the speedup, and it’s the right change on its own merits: rebuilding forecasts and config for every file was waste regardless.
But the scan itself is unchanged: still unbounded, still a full pass over the table when the pm2_5 column is empty. On slow hardware that’s one ~20-second query per cycle holding a read lock against the archive writer — a smaller window than beta3’s, but the same mechanism. It should still be bounded: a WHERE dateTime >= ? window of a day or two returns the same answer for any station with current PM data and costs almost nothing no matter how large the database is.
I think this lays this thread to rest. If the OP gets back to me with the sql timing (OP, please run it twice), we can’t look at those numbers; but I don’t think there are any other open items. (And I want to get back to weewx-skyfield.)
> On Aug 4, 2026, at 6:00 PM, John Kline <[email protected]> wrote:
>
> The warm figure is the interesting one: 2.64 s wall, with user+sys accounting for essentially all of it. Once the 242 MB is cached, the cost is pure CPU — SQLite examining every row in the table. Storage speed stops mattering; that 2.6 s is the floor on that hardware no matter what the database sits on.
>
> That matters for the fix. Bounding the lookback isn’t an I/O optimization — it caps the worst case. Unbounded, an empty column means a full pass over the table: 700K rows on your database, which is where your 2.6 s goes; 1.4M on the OP’s 13 years, and growing every five minutes. Bounded to a couple of days, the query examines at most a few hundred rows no matter how big the database gets — and for a station with current PM data it still stops at the first row, same as your outTemp result.
>
> For scale: the search list extension runs once per generated file, not once per report cycle, and beta3 ships 14 template sections. At your warm number that’s around 37 s of scanning per cycle, and more when weewxd is competing for the same page cache in 1 GB.
>
> Your outTemp comparison is the cleanest demonstration in this thread, incidentally. Same query shape, same table, 30,000× difference — and the only variable is whether the first row examined has a value in it.
>
>> On Aug 4, 2026, at 5:37 PM, Greg Troxel <[email protected]> wrote:
>> I rebooted the machine and then ran the query 3 times.
>>
>> $ sqlite3 weewx.sdb
>> SQLite version 3.53.2 2026-06-03 19:12:13
>> Enter ".help" for usage hints.
>> sqlite> .timer on
>> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1;
>> Run Time: real 20.939687 user 1.811444 sys 1.555754
>> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1;
>> Run Time: real 2.640085 user 1.682205 sys 0.956678
>> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1;
>> Run Time: real 2.675249 user 1.669083 sys 1.004916
>> sqlite>
>>
>> $ ls -lh weewx.sdb
>> -rw-r--r-- 1 gdt users 242M Aug 4 20:30 weewx.sdb
--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/9AIS1DRHl00/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [email protected].
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/B54515BC-B58E-4A04-961F-EB8E915183CC%40johnkline.com.
--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAKVzASCkqR8J1Hc9cDQcmreuQRvFPN2f45MLxEGNd7-0vktK2w%40mail.gmail.com.
--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To view this discussion visit https://groups.google.com/d/msgid/weewx-user/EE175CD9-B5EB-47DD-96A8-237F747F6BF2%40johnkline.com.
|
