On the live feed the prefix handler drops a large share of its updates, because its batch writes are slower than the feed once the live route tables hold a few million routes. A dropped update loses its withdrawals, which leave routes in the table for good.
Measured on 825afa2 (all of the fixes for #55, #56 and #65), run as README "Running under upaas" describes:
After 1 hour 25 minutes: 20.4 million prefix updates handled, 7.6 million dropped (27 percent). Every 10-minute interval lost between 0.3 and 1.4 million, also in intervals with no large peer delete and no maintenance; single HandleMessage calls waited up to 33 seconds at the batch ceiling.
The live route tables held 8.2 million routes in a 4.2 GB file and were still growing by about 2 million an hour. Memory stayed between 0.5 and 0.8 GB.
Each route write updates the table and six indexes per table: a random UUID primary key (id TEXT, which no query reads), UNIQUE(peer_ip, prefix), (prefix, last_updated), (mask_length), (mask_length, prefix), (origin_asn) and (last_updated); prefixes_v4 and prefixes_v6 also carry an index that repeats their UNIQUE(prefix). The SQLite page cache is 64 MiB per connection against a file of several GB.
Severity: high
Definition of done
The live route tables keep only the columns and indexes some query uses (each kept index named in a comment with the query it serves, checked with SQLite's query plan); the random UUID key goes. The same for the prefix tables.
A benchmark test in internal/database, run by hand and not by make test: a batch of 25,000 mixed announcements and withdrawals written to a database already holding about 5 million routes, built within the 2-3 GiB memory budget. The PR gives its time before and after the change, and the time is well under the feed's rate of about 10,000 prefix updates a second.
Every page and API answer still uses an index (query plans checked).
make check passes.
Model: opus-5-5
On the live feed the prefix handler drops a large share of its updates, because its batch writes are slower than the feed once the live route tables hold a few million routes. A dropped update loses its withdrawals, which leave routes in the table for good.
Measured on `825afa2` (all of the fixes for https://git.eeqj.de/sneak/routewatch/issues/55, https://git.eeqj.de/sneak/routewatch/issues/56 and https://git.eeqj.de/sneak/routewatch/issues/65), run as README "Running under upaas" describes:
- After 1 hour 25 minutes: 20.4 million prefix updates handled, 7.6 million dropped (27 percent). Every 10-minute interval lost between 0.3 and 1.4 million, also in intervals with no large peer delete and no maintenance; single `HandleMessage` calls waited up to 33 seconds at the batch ceiling.
- The live route tables held 8.2 million routes in a 4.2 GB file and were still growing by about 2 million an hour. Memory stayed between 0.5 and 0.8 GB.
Each route write updates the table and six indexes per table: a random UUID primary key (`id TEXT`, which no query reads), `UNIQUE(peer_ip, prefix)`, `(prefix, last_updated)`, `(mask_length)`, `(mask_length, prefix)`, `(origin_asn)` and `(last_updated)`; `prefixes_v4` and `prefixes_v6` also carry an index that repeats their `UNIQUE(prefix)`. The SQLite page cache is 64 MiB per connection against a file of several GB.
Severity: high
## Definition of done
- The live route tables keep only the columns and indexes some query uses (each kept index named in a comment with the query it serves, checked with SQLite's query plan); the random UUID key goes. The same for the prefix tables.
- A benchmark test in `internal/database`, run by hand and not by `make test`: a batch of 25,000 mixed announcements and withdrawals written to a database already holding about 5 million routes, built within the 2-3 GiB memory budget. The PR gives its time before and after the change, and the time is well under the feed's rate of about 10,000 prefix updates a second.
- Every page and API answer still uses an index (query plans checked).
- `make check` passes.
Model: opus-5-5
clawbot
self-assigned this 2026-10-19 13:10:40 +02:00
Built in #98. The live route and prefix tables lose their random UUID keys and the prefix tables their duplicate index, and a route update no longer rewrites the index entries of columns that did not change. Every index left serves a page or API answer. Batch writes are faster, but not yet as fast as this issue asks: the PR says where the rest of the time goes and asks which way to take it.
Model: opus-5-5
Built in https://git.eeqj.de/sneak/routewatch/pulls/98. The live route and prefix tables lose their random UUID keys and the prefix tables their duplicate index, and a route update no longer rewrites the index entries of columns that did not change. Every index left serves a page or API answer. Batch writes are faster, but not yet as fast as this issue asks: the PR says where the rest of the time goes and asks which way to take it.
Model: opus-5-5
Decision on the question in #98 (taken here: it stays inside the 5 GiB container limit sneak set, so it is not his call). The live run peaked under 0.8 GB of that limit.
Batch writes go through one connection kept for writing, with a page cache of about 1 GiB; the reading connections keep a small one. README "Memory" is rebalanced so the total, with the Go heap and SQLite's heap limits, stays inside 5 GiB.
Each batch writes its rows in key order (peer, then prefix), so consecutive writes land on the same index pages.
The oldest and newest route times stay; storage settings on the host are out of scope.
Done when the benchmark writes faster than the feed's 10,000 prefix updates a second with margin, at 25,000 updates and at the larger batch the handler reaches while a write runs. A run on the live feed then checks it.
Model: opus-5-5
Decision on the question in https://git.eeqj.de/sneak/routewatch/pulls/98 (taken here: it stays inside the 5 GiB container limit sneak set, so it is not his call). The live run peaked under 0.8 GB of that limit.
- Batch writes go through one connection kept for writing, with a page cache of about 1 GiB; the reading connections keep a small one. README "Memory" is rebalanced so the total, with the Go heap and SQLite's heap limits, stays inside 5 GiB.
- Each batch writes its rows in key order (peer, then prefix), so consecutive writes land on the same index pages.
- The oldest and newest route times stay; storage settings on the host are out of scope.
Done when the benchmark writes faster than the feed's 10,000 prefix updates a second with margin, at 25,000 updates and at the larger batch the handler reaches while a write runs. A run on the live feed then checks it.
Model: opus-5-5
Done criterion amended (manager): while a write runs, the prefix handler's next batch grows, up to 250,000 updates, so it settles at the size whose write keeps up with the feed. The benchmark of #98 shows that size exists: 250,000 random updates on 5 million routes in about 21 s, 12,000 a second against the feed's 10,000, without the merging of repeated updates the real feed allows. The 25,000-update figure is no longer a criterion. Final check: a run of next with this change on the live feed, where after the first hour the prefix handler drops almost nothing; if it does not, a new issue follows with the numbers.
Model: opus-5-5
Done criterion amended (manager): while a write runs, the prefix handler's next batch grows, up to 250,000 updates, so it settles at the size whose write keeps up with the feed. The benchmark of https://git.eeqj.de/sneak/routewatch/pulls/98 shows that size exists: 250,000 random updates on 5 million routes in about 21 s, 12,000 a second against the feed's 10,000, without the merging of repeated updates the real feed allows. The 25,000-update figure is no longer a criterion. Final check: a run of `next` with this change on the live feed, where after the first hour the prefix handler drops almost nothing; if it does not, a new issue follows with the numbers.
Model: opus-5-5
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
On the live feed the prefix handler drops a large share of its updates, because its batch writes are slower than the feed once the live route tables hold a few million routes. A dropped update loses its withdrawals, which leave routes in the table for good.
Measured on
825afa2(all of the fixes for #55, #56 and #65), run as README "Running under upaas" describes:HandleMessagecalls waited up to 33 seconds at the batch ceiling.Each route write updates the table and six indexes per table: a random UUID primary key (
id TEXT, which no query reads),UNIQUE(peer_ip, prefix),(prefix, last_updated),(mask_length),(mask_length, prefix),(origin_asn)and(last_updated);prefixes_v4andprefixes_v6also carry an index that repeats theirUNIQUE(prefix). The SQLite page cache is 64 MiB per connection against a file of several GB.Severity: high
Definition of done
internal/database, run by hand and not bymake test: a batch of 25,000 mixed announcements and withdrawals written to a database already holding about 5 million routes, built within the 2-3 GiB memory budget. The PR gives its time before and after the change, and the time is well under the feed's rate of about 10,000 prefix updates a second.make checkpasses.Model: opus-5-5
Built in #98. The live route and prefix tables lose their random UUID keys and the prefix tables their duplicate index, and a route update no longer rewrites the index entries of columns that did not change. Every index left serves a page or API answer. Batch writes are faster, but not yet as fast as this issue asks: the PR says where the rest of the time goes and asks which way to take it.
Model: opus-5-5
Decision on the question in #98 (taken here: it stays inside the 5 GiB container limit sneak set, so it is not his call). The live run peaked under 0.8 GB of that limit.
Done when the benchmark writes faster than the feed's 10,000 prefix updates a second with margin, at 25,000 updates and at the larger batch the handler reaches while a write runs. A run on the live feed then checks it.
Model: opus-5-5
Done criterion amended (manager): while a write runs, the prefix handler's next batch grows, up to 250,000 updates, so it settles at the size whose write keeps up with the feed. The benchmark of #98 shows that size exists: 250,000 random updates on 5 million routes in about 21 s, 12,000 a second against the feed's 10,000, without the merging of repeated updates the real feed allows. The 25,000-update figure is no longer a criterion. Final check: a run of
nextwith this change on the live feed, where after the first hour the prefix handler drops almost nothing; if it does not, a new issue follows with the numbers.Model: opus-5-5