Bardzo duża liczba rekordów w tabeli states w MariaDB – czy to normalne? Jak zdiagnozować problem?

[tommycam]

Cześć wszystkim,
od niedawna migruję swój Home Assistant ze SQLite na MariaDB (na HP T630 + Proxmox + HAOS VM) i zauważyłem coś, co mnie trochę zaniepokoiło.

Po przeniesieniu bazy do MariaDB sprawdziłem tabele i zobaczyłem, że tabela “states” ma ponad 1 300 000 rekordów, a część encji ma dziesiątki lub setki tysięcy wpisów.

Nie jestem specjalistą od baz danych, więc chciałbym zapytać bardziej doświadczonych użytkowników — czy to wygląda normalnie, czy może coś jest nie tak z moją konfiguracją lub jakąś encją?

Załączam screeny:

1. Widok główny bazy – tabela “states” jest największa, około 360 MB
   
   
2. Zawartość tabeli states – pierwsze rekordy mają entity_id = NULL, a pole state np. "off"
   
   
3. Wynik zapytania SQL pokazujący encje z największą liczbą rekordów:

SELECT states_meta.entity_id, count(*) cnt 
FROM states 
LEFT JOIN states_meta 
    ON (states.metadata_id = states_meta.metadata_id) 
GROUP BY states_meta.entity_id 
ORDER BY cnt DESC;

SELECT states_meta.entity_id, count(*) cnt
FROM states
LEFT JOIN states_meta
ON (states.metadata_id = states_meta.metadata_id)
GROUP BY states_meta.entity_id
ORDER BY cnt DESC;

Wygląda to tak, jakby niektóre czujniki (głównie energii i mocy z Sonoffów/PZEM) generowały po kilkadziesiąt tysięcy stanów.

Przykładowe encje z największą liczbą wpisów:

• sensor.niepomiarowane_zuzycie_moc – 245 tys.
• sensor.pzem_dzienna_energia – 80 tys.
• sensor.lodowka_energy_current – 47 tys.
• sensor.sonoff_basic_r2_humidity – 36 tys.
• wiele sensorów PZEM-004T i Sonoff R2 odłączanych czasowo

Zastanawia mnie też to, że w tabeli “states” bardzo duża część rekordów ma entity_id = NULL, co znalazłem w tym wątku na anglojęzycznym forum:
https://community.home-assistant.io/t/all-entity-id-null/555488

Wygląda podobnie jak u mnie, ale nie wiem, czy to normalne, czy błąd, nie znam za dobrze angielskiego

Moje pytania:

1. Czy ponad 1.3 mln rekordów w tabeli states i taki przyrost danych to normalne w MariaDB?
2. Skąd mogą się brać rekordy z entity_id = NULL?
3. Czy to jakiś błąd HA lub migracji z SQLite → MariaDB?
4. Jakie zapytania SQL mogę wykonać, żeby zidentyfikować problematyczne encje lub błędne wpisy?
5. Czy powinienem zmienić ustawienia recorder (np. wykluczenia lub commit_interval)?
6. Czy encje energii z Sonoffów i PZEM-004T powinny być wyłączone z przechowywania?
7. Czy mogłem coś źle skonfigurować w MariaDB?

Jeśli ktoś spotkał się z podobnym problemem albo wie, jak podejść do diagnostyki — będę wdzięczny za wskazówki.

Pozdrawiam, Tomek.

[maciej1]

1. To zależy ile masz encji i jaki czas przechowywania w bazie. Nie da się powiedzieć, że coś jest ok lub nieok na podstawie tylko samych liczb. Jeśli nie masz negatywnych symptomów (błędy w logach czy spowolnienie systemu), to znaczy że jest ok
2. Biorą się z aktualizacji struktury bazy (dawno temu), które przeniosła te dane do tabeli states_meta. Jest to normalne i prawidłowe.
3. Nie.
4. Ja sobie uruchamiam od czasu do czasu to; pokazuje encje zajmujące najwięcej miejsca (w sensie: ostatnia kolumna ‘total’ pokazuje przybliżony wynik w bajtach)

select sel.*, (cnt * len) total
from
(
SELECT 
    states_meta.entity_id, states.metadata_id,
    COUNT(*) AS cnt, max(CHAR_LENGTH(sa.shared_attrs)) len 
FROM states
LEFT JOIN states_meta ON states.metadata_id=states_meta.metadata_id
left join state_attributes sa on sa.attributes_id = states.attributes_id
GROUP BY
    states.metadata_id
) sel
order by total desc

5. Dobrze jest nie przechowywać wszystkiego jak leci, tylko te encje, które faktycznie cię interesują pod kątem historii.

Mogę ci jeszcze dać trochę danych do porównania: mam purge_keep_days: 45, największe ilości rekordów (stanów) to ok. 40 tysięcy, najwięcej miejsca zajmowanego przez 1 encję, to 5 MB (miałem wcześniej kilka encji zajmujących ponad 20 MB - czujniki temperatury - ale ograniczyłem ilość danych bo dziesiątki odczytów na minutę są zupełnie niepotrzebne w tym przypadku).

Pozdrawiam, Maciej

[artpc (artur)]

Możesz utworzyć odpowiednie czujniki dla Maria DB.

Urządzenia oraz usługi → Dodaj Integrację.

Maria DB size

SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;

Events

SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" and table_name="events" GROUP BY table_schema;

States

SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" and table_name="states" GROUP BY table_schema;