update(2610.1).sql brings an existing database up to the current schema
without PHP: base tables (CREATE IF NOT EXISTS), the v2603.x migrations
(last_event_at column, uq_event removal) and the v2610.1 index changes
(idx_occurred, idx_src_ip_occ, drop idx_src_ip). All steps are idempotent
via information_schema checks + PREPARE/EXECUTE, so the file is safe to
re-run and portable across MySQL 8 and MariaDB.
The purge ran one DELETE over the whole retention cutoff in a single
transaction, spiking undo/binlog and holding row locks on a table the
fetch cron writes to. Now it loops in batches of 5000 rows with a short
pause between batches, keeping each commit small.
- global_stats() executed the 'last N seconds' COUNT twice per page
load (prepare()->execute() result was discarded, then re-run)
- fetch_upstream_version() cached nothing on failure, so an
unreachable git server added a cURL wait (up to 5 s) to every
dashboard load via the 30 s auto-refresh; failures are now cached
for 15 minutes and get a 2 s connect timeout
Replace one INSERT per event (up to FETCH_LIMIT auto-committed
round trips per node per poll) with multi-row INSERTs in chunks of
200, wrapped in a single transaction per node. Same data, one
commit instead of hundreds, which was making fetch runs slower as
the table grew.
Time-window queries (dashboard stats, recent connections, JSON API,
purge) had no usable index and full-scanned the connections table on
every load, so the dashboard slowed down as data accumulated.
- Add idx_occurred (occurred_at) for counts, MAX, ORDER BY ... LIMIT,
and the purge delete
- Add idx_src_ip_occ (src_ip, occurred_at) so frequent_ips() runs as a
covering index scan
- Drop the now-redundant standalone idx_src_ip
Migrations in setup.php are idempotent (information_schema checks) and
portable across MySQL 8 and MariaDB.
daprogs