Hi
Ich habe mich heute hier angemeldet, hatte mir aber in der Vergangenheit schon öfters Anregungen geholt. Danke an alle, die hier helfen, insbesondere an Simon, dessen Videos vieles erleichterte. Heute will ich ein Stück zurückgeben.
Derzeit optimiere ich einige Aspekte meines Homeassistant-Setups, mit dem Ziel, eine möglichst schlanke Datenbank zu erreichen. Ich nutze MariaDB 2.7.1 und greife über das Addon phpMyAdmin 0.9.1 auf die Datenbank zu. Auf der Suche nach den größten Speicherverbrauchern haben mir diese SQL-Abfragen geholfen. Ich habe nur grundlegende SQL-Kenntnisse, aber mit der Hilfe von ChatGPT ist vieles möglich.
Lange Zeit habe ich das Thema ignoriert, bis die Datenbankgröße über 5 GB hinauswuchs. Das tägliche Vollbackup dauerte gefühlt ewig, und da ich auf eine NAS sichere, begann auch dort das rotierende Backup sich aufzublähen. Heute beträgt die Größe meiner Datenbank etwa 1,3 GB, mit 11 Tagen Historie und 1290 Entities.
Gutes Gelingen und viel Spaß beim Aufräumen 
1. Listet auf welche Sensoren am meisten Daten liefern
Braucht man die wirklich alle in der Fülle? Ich ging dann alle durch und entschied ja oder nein und bei nein, schloß ich die Statistik über configuration.xaml aus. Manche Entities benutzte ich gar nicht und der Informationswert war auch beschränkt. In diesen Fällen habe ich die Entity gleich deaktiviert. Das sind Abwägungsfragen, die jeder für sich beantworten muß.
SELECT
ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM states)), 0) AS cnt_pct,
COUNT(*) AS cnt,
states_meta.entity_id
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY cnt DESC;
2. Zeigt welche Event Typen am meisten gefeuert werden
Ich war erstaunt, daß timer.restart bei mir zu den Top3 gehörte und ging dem nach. Am Ende ließ ich über einen Template Trigger 2 Automatisierung nur dann starten bzw. den Timer erneuern, wenn der Timer bereits seit 30 min läuft. Dies reichte für den Zweck vollkommen aus.
SELECT
ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM events)), 0) AS cnt_pct,
COUNT(*) AS cnt,
event_types.event_type
FROM events
INNER JOIN event_types ON events.event_type_id = event_types.event_type_id
GROUP BY event_types.event_type
ORDER BY cnt DESC;
3. Zeigt wie häufig welche Automatisierung getriggert wurde
Den Ursachen ging ich auf den Grund und vor allem bei Bewegungsmeldern wurde ich fündig. Man kann jede Bewegung eine Automatisierung starten lassen und z.B. die gewünschten über Zeit-Conditions abfangen. Man kann aber auch einen Template Trigger nehmen und Bewegung und Zeit kombinieren und nur dann startet die Automatisierung und schont somit die Datenbank.
Das SQL Statement kannst Du auch für andere EVENT_TYPES nehmen wie
“script_started” oder “shelly.click” nehmen, je nachdem was am meisten angezeigt wird und auch Sinn ergibt. “script_started” fand ich noch hilfreich aber am Ende führt das meistens wieder auf Automatisierung zurück.
WITH triggered_events AS (
SELECT
e.event_id,
JSON_UNQUOTE(JSON_EXTRACT(ed.shared_data, '$.entity_id')) AS automation_id
FROM
events e
JOIN
event_types et ON e.event_type_id = et.event_type_id
JOIN
event_data ed ON e.data_id = ed.data_id
WHERE
et.event_type = 'automation_triggered'
)
SELECT
ROUND((COUNT(te.event_id) * 100.0 / total_triggers.total_count), 0) AS cnt_pct,
COUNT(te.event_id) AS trigger_count,
te.automation_id
FROM
triggered_events te
CROSS JOIN
(SELECT COUNT(*) AS total_count FROM triggered_events) total_triggers
GROUP BY
te.automation_id,
total_triggers.total_count
ORDER BY
trigger_count DESC;
4. Welcher Trigger triggert Automatisierung XYZ am meisten?
Ich habe eine Top-Automatisierung mit fast 10 Triggern und ich wollte wissen welchen ich optimieren sollte. Und siehe da, 2 Sensortrigger die bei beliebiger Statusänderung anschlugen. Dies grenzte ich auf einige ausgewählte Attributsänderungen ein.
WITH automation_events AS (
SELECT
e.event_id,
JSON_UNQUOTE(JSON_EXTRACT(ed.shared_data, '$.source')) AS source
FROM
events e
JOIN
event_types et ON e.event_type_id = et.event_type_id
JOIN
event_data ed ON e.data_id = ed.data_id
WHERE
et.event_type = 'automation_triggered'
AND JSON_UNQUOTE(JSON_EXTRACT(ed.shared_data, '$.entity_id')) = 'automation.XYZ'
)
SELECT
ROUND((COUNT(ae.event_id) * 100.0 / total_triggers.total_count), 0) AS cnt_pct,
COUNT(ae.event_id) AS trigger_count,
ae.source
FROM
automation_events ae
CROSS JOIN
(SELECT COUNT(*) AS total_count FROM automation_events) total_triggers
GROUP BY
ae.source,
total_triggers.total_count
ORDER BY
trigger_count DESC;
wollte nur nochmal sicher gehen ob wir von gleichen reden, vor allem bei percantage sahen ein paar Ergebnisse komisch aus. Muss ich nochmal prüfen.