Database monitoring

[leccelecce]

As a potential new feature request / PR, keen to see if there is any wider interest in this, or maintainer feedback.

I consider sqlite to be a bit of a black box, but Frigate doesn't currently support alternative options (i.e. postgres or mysql) that I can better monitor.

Things I would consider interesting to know, while balancing the need to keep it reasonably simple for most users:

1. DB file size
2. WAL log file size
3. Number of events in database (possibly some other key tables too - previews, recordings, timeline)
4. Date of oldest event in DB
5. Periodic SQLite integrity check result (not sure this is currently run)
6. Last vacuum date/time

Items 1 and 3 (events) preferably supported by a 30-day graph.

[hawkeye217]

Sqlite is intentionally used as an embedded database precisely because it's low-maintenance and doesn't need the kind of monitoring you'd apply to a standalone RDBMS system like Postgres or MySQL.

A few specifics:

• Integrity checking in sqlite does a full table scan, which is expensive on a large DB and blocks writes. From what we've seen from users over the years, corruption is extremely rare and almost always caused by hardware issues (bad SD cards, power loss). I'm not sure if this is something worth running periodically.
• Sqlite does support auto_vacuum and in WAL mode, manual VACUUM rewrites the entire DB file, requires equal temporary disk space, and blocks everything. We already do this periodically.
• Sqlite manages WAL checkpointing automatically. If the WAL grows abnormally, that's a bug and not really something a user dashboard would help with.
• As for database file size, that that might be mildly interesting as an informational metric, though it's not really actionable for users.

So generally I think we'd rather keep things simple for users rather than expose metrics that could cause unnecessary concern. Curious if the other maintainers have thoughts.

[leccelecce]

Thank you for the feedback. I accept I do tend towards over-thinking from a previous life maintaining systems with large, but flakey Oracle / clustering dependencies!

I do agree that the more single/few-user model of Frigate, rather than massive concurrency, removes a whole class of problems requiring full-fledged RDBMSs. However my hypothesis would be that most people do care about their NVR data / health (even if they don't realise it until something goes wrong) - defensive features that help identify risks / degradation earlier contribute towards overall higher uptime.

Integrity checking - my system executes pragma quick_check in < 2 seconds on a 500MB DB. This doesn't require a full table scan and apparently in WAL mode doesn't block writes. A nightly 03:00 scan might not pick up every possible issue - but if it picks up an early sign of disk failure, filesystem corruption, misbehaving network that is a win.

There is a bit of a mixture in my list above of different concerns - some are "is sqlite healthy at file level", "am I going to get bitten by vacuuming during next restart", some are "is my DB growing at a sustainable rate", "how is my DB size correlated by cameras, retention timespan" etc. I think a separate question is whether there is interesting / useful information in alerts/detections by camera, by label etc.

Database backups - another area - would you consider a PR to enable manual backups via VACUUM INTO? Or is the recommended approach just to cp the files?

[hawkeye217]

Right, though I think the core question is whether this complexity is worth it for Frigate's use case. Frigate typically runs on single-user home systems, not production infrastructure, where the failure modes (disk degradation, filesystem corruption) would show up in plenty of other ways before they manifest in SQLite. And if someone's SD card is dying, a nightly quick_check isn't really saving them, proper hardware and backups are.

On backups, for most users, copying the DB files while Frigate is stopped is just the simplest and most reliable approach. VACUUM INTO is technically cleaner for live backups, but I personally don't think it's not something we'd want to build into Frigate itself. To me that feels like it belongs in the user's own backup strategy rather than the application.

[leccelecce]

Re backups, I don't really see the need for downtime. Stopping applications for backups feels quite dated, particularly when it's an application intended for 24/7 use. I know a minute or two of downtime isn’t much in the scheme of things, it's just the fact it's not necessary given various solutions exist.

For a live backup I could probably publish a sidecar docker container that mounts the DB readonly and executes vacuum into or one of the other built-in options. Either as a one-shot task, or with a scheduling option and simple UI. It just feels like something that would sit fairly neatly in Frigate (as it does in HASS, Unifi, Portainer to name three other apps I run off the top of my head). Do that, then tar.gz with config.yml included for good measure (+anything else particularly relevant).

But I appreciate this adds maintenance burden

Interested philosophically where Frigate sits - is it always going to be firmly a home user product vs enterprise? Funnily enough its exactly because it doesn't run on enterprise kit for most people that I would have thought monitoring and backup strategy important.

[hawkeye217]

Your sidecar idea is great. Run with that!

I don't really know if we have a particular philosophy or target userbase in mind for Frigate. It just seems generally to have adoption in the "home lab"/self-hosted crowd than in the enterprise space, where other large companies with support infrastructure will always dominate.