On a Raspberry Pi with a big database, Aerodrome no longer stops collecting for minutes at every start and every few hours.
After every start, and then every 6 hours, Aerodrome refreshed the statistics its database uses to plan searches. On most computers that takes a moment. On a Raspberry Pi with an SD card and a few gigabytes of sightings, it had to read about half the database first, and collection waited until it finished: 4–5 minutes on a 5 GB database, measured on a Pi 4. The Status page showed "Paused for maintenance" meanwhile. Now the statistics are refreshed only for a table that has doubled or halved in size since they were last taken, which in normal running doesn't happen, and a restart no longer refreshes them at all. If you change Retention by a lot, expect one refresh once the old sightings have gone.
Small computers had a second, shorter stall. Every 10 minutes the database-size estimate behind the Status page counted every sighting. With less memory than the database needs to stay fast (a Pi with 1 or 2 GB and a large database), that stopped collection for a minute or more each time. It now gets the same number in a fraction of a second.
And when an update removes an old index from a large database, the log now says so first. On an SD card that can take a few minutes, before the web page comes back.
Behind the scenes
- SQLite's ANALYZE counts every entry of each index exactly before the sampling that
analysis_limitbounds, so a sampled ANALYZE still reads every index page: 2.5–4 minutes from a cold cache for 25M sightings on a Pi 4's SD card, with the write lock held. A warm cache hides it (4 s). refresh_query_planner_statscompares each table's rowid span (two index seeks) with its span at its last ANALYZE, whichanalyze_dbnow keeps in_aerodrome_meta(stats_rows). It analyzes only the tables that changed by 2× either way and have at least 1,000 rows. A table analyzed by an earlier release falls back to the row count in itssqlite_stat1numbers. Virtual tables, FTS5's shadow tables, WITHOUT ROWID tables and_aerodrome_metaare left out. Thenoskipscanmarks are kept, and the log names a table of a million rows or more before it is analyzed.capacity.pycounts rows from rowids: the total is(SELECT MAX(rowid)) - (SELECT MIN(rowid)) + 1, and the last 7 days areMAX(rowid)minus the rowid of the first row at the cutoff. Rows leave all_sightings oldest-first, so these equalCOUNT(*): 0.3 s instead of 87 s from a cold cache on the Pi. The 10-minute count cache is gone.- init_db's index drops log first when the table has more than 100,000 rows. Dropping idx_all_seen on 25M rows took 129 s from a cold cache on SD.
- Measured on a Pi 4B with an invented 5.3 GB database (25M sightings, 180 aircraft a poll, SD card):
- longest gap between polls at a cold start: 4 min 14 s before, 18 s after;
- at 2 GB of RAM, before: 5 min at start, then 64 s every ~12 minutes; after: no gap over 18 s in 22 minutes.
- Tests:
test_stats_refresh.pycovers a restart, a table doubling or halving, small tables, an install analyzed by an earlier release, a table rewritten in place, the excluded tables, thenoskipscanmarks, seeks not scans, and the index-drop log.test_collector_upkeep.pychecks the capacity counts againstCOUNT(*)and their query plans.