Something went wrong. Try again.
A public mirror for the whole atmosphere hubble.microcosm.blue
Something went wrong. Try again.
3.0 kB · 78 lines
SQL
at main
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879-- Per-host summary for stats-backfill. Cross-tool sibling of-- `mini host-summary --pds <host>`. Same shape buckets where they-- apply, so the two outputs can be eyeballed side-by-side.---- Schema reference: stats-backfill/src/db.rs:38-104.---- Run with sqlite3, parameter set ahead of `.read`:---- sqlite3 path/to/stats-backfill.db -bail \-- -cmd ".parameter set :host 'berlin-user.eurosky.social'" \-- ".read stats-backfill/sql/host_summary.sql"---- Conceptual caveat: `repos.host` is the upsert-chosen host for a DID-- (one row per DID, max-rev tie-break). mini's R/ is keyed (pds, did),-- so a multi-homed DID appears under every host there. For migrated-- accounts (started on bsky, now on indie), this view may put the DID-- under the legacy host while mini has it under the indie host. Same-- skew as compare-dump's resolver.
.mode column.headers on
-- 1. Disposition by fetch_state (captured = 'done', failed_terminal,-- still pending). Sum across rows = total DIDs sb chose this host for.SELECT '--- fetch_state ---' AS section;SELECT fetch_state, COUNT(*) AS didsFROM reposWHERE host = :hostGROUP BY fetch_stateORDER BY dids DESC;
-- 2. listRepos disposition. Mirrors mini's inactive bucket sub--- breakdown by status.SELECT '--- listrepos disposition ---' AS section;SELECT listrepos_active, listrepos_status, COUNT(*) AS didsFROM reposWHERE host = :hostGROUP BY listrepos_active, listrepos_statusORDER BY dids DESC;
-- 3. Top failure reasons among failed_terminal. Mirrors mini's-- `failed_by_error` histogram.SELECT '--- last_error (failed_terminal, top 25) ---' AS section;SELECT last_error, COUNT(*) AS didsFROM reposWHERE host = :host AND fetch_state = 'failed_terminal'GROUP BY last_errorORDER BY dids DESCLIMIT 25;
-- 4. Measured totals over captured DIDs. Mirrors mini's totals line.-- `star_zstd_bytes` is stats-backfill-only (mini doesn't compute it).SELECT '--- measured totals (captured set) ---' AS section;SELECT COUNT(*) AS measured_dids, SUM(m.car_bytes) AS total_car_bytes, SUM(m.car_wire_bytes) AS total_car_wire_bytes, SUM(m.star_zstd_bytes) AS total_star_zstd_bytes, SUM(m.record_count) AS total_records, SUM(m.blob_ref_count) AS total_blob_refs, SUM(m.blob_total_size) AS total_blob_bytesFROM repos rJOIN repo_measurements m ON m.did = r.didWHERE r.host = :host;
-- 5. Recent fetch_attempt_log activity. Surfaces clustering of errors-- in time. mini has no equivalent (it doesn't keep an attempt log).SELECT '--- recent attempts (last 20) ---' AS section;SELECT datetime(attempted_at, 'unixepoch') AS attempted_at_iso, substr(did, 1, 40) AS did, http_status, classification, substr(error_summary, 1, 80) AS error_summaryFROM fetch_attempt_logWHERE host = :hostORDER BY attempted_at DESCLIMIT 20;