Well the hits keep coming. When I started the container back up after running the queries against the db, for some reason the report cycle that had been taking 120-150 seconds took almost 10 minutes, and beta4/weewx did as I originally expected. The only changes I made are those in the beta4 update. I am curious why it does what I expect.
vt-1 | 2026-08-04 21:43:35,179 weewxd[8] INFO weewx.engine: Loading station type Vantage (weewx.drivers.vantage) vt-1 | 2026-08-04 21:43:41,211 weewxd[8] INFO user.wxskyfield: Service version: 1.14 vt-1 | 2026-08-04 21:43:41,211 weewxd[8] INFO user.wxskyfield: WxSkyfield status: enabled...continuing. vt-1 | 2026-08-04 21:43:41,213 weewxd[8] INFO user.wxskyfield: stars : True vt-1 | 2026-08-04 21:43:41,213 weewxd[8] INFO user.wxskyfield: user_root: /home/weewx/weewx-data/bin/user vt-1 | 2026-08-04 21:43:41,214 weewxd[8] INFO user.wxskyfield: Skyfield version: 1.54. vt-1 | 2026-08-04 21:43:41,299 weewxd[8] INFO user.wxskyfield: Loaded 420 named stars from the Hipparcos catalog. vt-1 | 2026-08-04 21:43:41,301 weewxd[8] INFO user.wxskyfield: Skyfield almanac registered; reports will use Skyfield for almanac computations. vt-1 | 2026-08-04 21:43:41,303 weewxd[8] INFO weewx.engine: StdConvert target unit is 0x1 vt-1 | 2026-08-04 21:43:41,303 weewxd[8] INFO weewx.wxservices: StdWXCalculate will use data binding wx_binding vt-1 | 2026-08-04 21:43:41,310 weewxd[8] INFO weewx.engine: Archive will use data binding wx_binding vt-1 | 2026-08-04 21:43:41,310 weewxd[8] INFO weewx.engine: Record generation will be attempted in 'hardware' vt-1 | 2026-08-04 21:43:41,311 weewxd[8] INFO weewx.engine: Using archive interval of 300 seconds (specified by hardware) vt-1 | 2026-08-04 21:43:41,313 weewxd[8] INFO weewx.restx: StationRegistry: Station will be registered. vt-1 | 2026-08-04 21:43:41,315 weewxd[8] INFO weewx.restx: Wunderground-RF: Data for station KVTLUNEN4 will be posted vt-1 | 2026-08-04 21:43:41,319 weewxd[8] INFO weewx.restx: PWSWeather: Data for station KVTLUNEN4 will be posted vt-1 | 2026-08-04 21:43:41,328 weewxd[8] INFO weewx.restx: CWOP: Data for station EW2749 will be posted vt-1 | 2026-08-04 21:43:41,334 weewxd[8] INFO weewx.restx: WOW: Data for station 5419f9b8-2beb-e711-bd60-0003ff5962e0 will be posted to https://wow.metoffice.gov.uk/automaticreading vt-1 | 2026-08-04 21:43:41,336 weewxd[8] INFO weewx.restx: AWEKAS: No config info. Skipped. vt-1 | 2026-08-04 21:43:41,336 weewxd[8] INFO user.mqtt: service version is 0.24 vt-1 | 2026-08-04 21:43:41,336 weewxd[8] INFO user.mqtt: binding to ['archive', 'loop'] vt-1 | 2026-08-04 21:43:41,336 weewxd[8] INFO user.mqtt: data_binding is wx_binding vt-1 | 2026-08-04 21:43:41,342 weewxd[8] INFO user.mqtt: topic is weather/vt vt-1 | 2026-08-04 21:43:41,342 weewxd[8] INFO user.mqtt: desired unit system is US vt-1 | 2026-08-04 21:43:41,343 weewxd[8] INFO user.mqtt: data will be uploaded to mqtt://tom:[email protected]:1883 vt-1 | 2026-08-04 21:43:41,344 weewxd[8] INFO user.windy: version is 0.8 vt-1 | 2026-08-04 21:43:41,356 weewxd[8] INFO user.windy: Data will be uploaded to https://stations.windy.com/api/v2/observation/update vt-1 | 2026-08-04 21:43:41,357 weewxd[8] INFO weewx.engine: 'pyephem' not detected, extended almanac data is not available vt-1 | 2026-08-04 21:43:41,360 weewxd[8] INFO weewxd: Starting up weewx version 5.4.0 vt-1 | 2026-08-04 21:43:43,363 weewxd[8] INFO weewx.engine: Clock error is -0.36 seconds (positive is fast) vt-1 | 2026-08-04 21:43:43,363 weewxd[8] INFO weewx.engine: Using binding 'wx_binding' to database 'weewx.sdb' vt-1 | 2026-08-04 21:43:43,364 weewxd[8] INFO weewx.manager: Starting backfill of daily summaries vt-1 | 2026-08-04 21:43:43,364 weewxd[8] INFO weewx.manager: Daily summaries up to date vt-1 | 2026-08-04 21:43:45,867 weewxd[8] INFO weewx.engine: Starting main packet loop. vt-1 | 2026-08-04 21:43:49,968 weewxd[8] INFO user.mqtt: client established for mqtt://tom:[email protected]:1883 vt-1 | 2026-08-04 21:45:19,339 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:45:00 EDT (1785894300) to database 'weewx.sdb' vt-1 | 2026-08-04 21:45:19,391 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:45:00 EDT (1785894300) to daily summary in 'weewx.sdb' vt-1 | 2026-08-04 21:45:20,063 weewxd[8] INFO weewx.restx: Windy: Published record 2026-08-04 21:45:00 EDT (1785894300) vt-1 | 2026-08-04 21:45:21,746 weewxd[8] INFO user.new_belchertown: version 2.1beta4 vt-1 | 2026-08-04 21:45:44,763 weewxd[8] ERROR weewx.restx: StationRegistry: Failed to publish record 2026-08-04 21:45:00 EDT (1785894300): HTTP Error 429: TOO MANY REQUESTS vt-1 | 2026-08-04 21:50:19,565 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:50:00 EDT (1785894600) to database 'weewx.sdb' vt-1 | 2026-08-04 21:50:19,704 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:50:00 EDT (1785894600) to daily summary in 'weewx.sdb' vt-1 | 2026-08-04 21:50:20,134 weewxd[8] INFO weewx.engine: Launch of report thread aborted: existing report thread still running vt-1 | 2026-08-04 21:50:20,815 weewxd[8] INFO weewx.restx: Windy: Published record 2026-08-04 21:50:00 EDT (1785894600) vt-1 | 2026-08-04 21:54:00,639 weewxd[8] INFO weewx.cheetahgenerator: Generated 16 files for report new-belchertown in 519.73 seconds vt-1 | 2026-08-04 21:54:06,452 weewxd[8] INFO weeutil.rsyncupload: rsync'd 61 files (5,013,997 bytes) in 1.33 seconds vt-1 | 2026-08-04 21:55:19,799 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:55:00 EDT (1785894900) to database 'weewx.sdb' vt-1 | 2026-08-04 21:55:19,871 weewxd[8] INFO weewx.manager: Added record 2026-08-04 21:55:00 EDT (1785894900) to daily summary in 'weewx.sdb' vt-1 | 2026-08-04 21:55:20,682 weewxd[8] INFO weewx.restx: Windy: Published record 2026-08-04 21:55:00 EDT (1785894900) vt-1 | 2026-08-04 21:56:04,272 weewxd[8] INFO user.new_belchertown: New forecast Current Conditions file downloaded to /home/weewx/weewx-data/public_html/belchertown/json/current_conditions.json vt-1 | 2026-08-04 21:57:03,975 weewxd[8] INFO weewx.cheetahgenerator: Generated 16 files for report new-belchertown in 103.70 seconds vt-1 | 2026-08-04 21:57:09,611 weewxd[8] INFO weeutil.rsyncupload: rsync'd 26 files (4,826,059 bytes) in 1.25 seconds -- 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:38 PM Tom Mitchell <[email protected]> wrote: > > 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/CAKVzASAy0Qmy1LUpYkL%3DZeLUVtkCkoFmm7o80EO339xxFAE%2BbQ%40mail.gmail.com.
