Mam ustawioną bazę MariaDB. Niestety za późno ograniczyłem zbiór encji, które mają się zapisywać. Baza w krótkim czasie się rozrosła. Czy można bezpiecznie usunąć historię dla niepotrzebnych encji i jak? Są tam klucze obce, które blokują usuwanie rekordów z tabeli states.
Skorzystaj z tego:
Wpis w configuration.yaml
recorder:
db_url: !secret mariadb_url
purge_keep_days: 5
exclude:
domains:
- weblink
- updater
- input_boolean
- input_number
- input_select
- input_text
- light
- media_player
- sun
- timer
- weather
Lista domen:
alarm_control_panel
alert
alexa
automation
binary_sensor
camera
climate
device_tracker
fan
group
image_processing
input_boolean
input_select
light
media_player
person
remote
script
sensor
sun
switch
timer
weather
zigbee2mqtt_networkmap
zone
Narzędzia deweloperskie:
{%- for d in states | groupby('domain') %}
{{ d[0] }}
{%- endfor %}
Lista encji w poszczególnych domenach:
{%- for d in states | groupby('domain') %}
{% if loop.first %}{{loop.length}} Domains:
{% endif %}- {{ d[0] }}: {{d[0]|count}}
{%- endfor %}
Lub karta w HA
Kod karty:
type: markdown
content: >
Domain | Count
:---|---:
{% for domain in states | groupby('domain') %} {% set name =
domain[0].replace('_', ' ') | title %} **{{ name }}** | {{ states[domain[0]] |
count }} {# leave blank line below otherwise table won't render #}
{% endfor %} **Total** | {{ states | count }}
card_mod:
style:
ha-markdown:
$:
ha-markdown-element: |
table {
border-spacing: 0;
width: 100%;
padding: 8px;
border-radius: var(--ha-card-border-radius);
}
th {
background-color: var(--state-icon-color);
color: white;
padding: 4px;
}
th:first-child {
border-top-left-radius: var(--ha-card-border-radius);
}
th:last-child {
border-top-right-radius: var(--ha-card-border-radius);
}
td {
padding: 4px;
}
tr:nth-child(even) {
background-color: var(--table-row-background-color);
}
Dzięki, ale mnie nie zrozumiałeś chyba. Od teraz to już ustawiłem sobie listę encji do zapisu historii. Nie chcę jednak usuwać całej dotychczasowej historii, ale usunąć niepotrzebne encje z bazy pozostawiająć historię dla wybranych encji.
To jest dla mnie za trudne :(. Nie wiem o czym oni tam piszą. Zaryzykuję operacje na bazie danych. Zdejmę klucze obce, usunę niepotrzebne dane i założe klucze ponownie. Liczyłem na to, że ktoś już to robił i ma gotowe skrypty sql na to.
Sorry, za odgrzebanie tamtau, ale też sporo szukłaem i trzeba było zrobić samemu.
Najwięcej zżera tabela states. Poniżej kod do usunięcia WSZYSTKICH stanów konkretnej encji. (Łączmy się oczywiście jakimś managerem SQL polecam “MySQL Workbench 8.0 CE” działa pomimo alertów o niezgodności.
DELETE FROM hassio.states where entity_id=“sensor.ds18b20” ORDER BY state_id DESC
jak usuwamy w kolejności “odwrotnie hronologicznej do zapisu” to nie ma problemu z kluczami. Tylko zapytanie sporo czasu zajmuje na naprawde dużej tabeli.
Poważnie, nie wystarczy ograniczyć zapisu do bazy i wykluczyć encje których nie potrzebujemy? Gotowe narzędzia masz w HA
Poważnie? Potrafisz czytać ze zrozumieniem? Pytanie było:
“Czy można bezpiecznie usunąć historię dla niepotrzebnych encji i jak?”
Rozwiązanie jest dla osób które mają ochotę zrobić to ręcznie, albo muszą. U mnie nie potrafiło wywalić danych archiwalnych encji która już nie istniały (HA postawiony od nowa z przypiętą starą bazą).
Pozwolę sobie pominąć na ile “efektywnie działą” metoda usuwania starych danych, i jak bardzo zdławi Ci system przy wywalaniu np 5GB zapisanych danych na malinie przy zapisie kilkanaśie MB/s?
A skoro tyle cudownych narzędzi jest w HA, to podaj proszę jak zostawić encje jednego sensora temperatury tydzień, a drugiego miesiąc? Da się?
Jakoś mi się nie udało… może barkło cierpliwości a może to że na codzień pracuje z bazami danych i zajęło mi chwile ogarnięcie skryptem.
Każdy robi to co chce i umie, a więc szanujmy się a nie twierdźmy że tylko my znamy to jedyne rozwiązanie. Ja osobiście baze uważam za największą porażkę HA.
Kiedyś może tak było, dziś jest dużo lepiej i nie trzeba skryptów do optymalizacji BD
Dokładnie zrozumiałem czego ty nie rozumiesz.
Człowieku nie mówie o skryptach do optymalizacji tylko o zjebanej architekturze. Od lat pracuje w bazach danych i powiem krótko zje**** jest i tyle. Nie da się tego poprawić skrypatmi bo ktoś zrobił sobie baze do wszystkiego, a jak coś jest do wszystkiego to jest do niczego.
Było gorzej a jest lepiej od kiedy zrobili tabele pośredniczące z danymi statystycznymi. Ale to taki przeszczep który zwiększa wydajność kosztem zużywania miejsca.
Pracuje w bazach danych kilkanaście lat zawodowo. Jak już zrozumiesz to co wydaje Ci się że rozumiesz to będziemy rozmawiać dalej. W obecnej sytuacji nie będę prowadził offtopa i Cię edukował bo mi po prostu szkoda na to czasu.
Edukować możesz sobie w domu rodzinę.
Pytanie z 9 czerwca musisz ogarnąć różnice czasowe i zmiany jakie zaszły od tego czasu w strukturze BD HA, nazbierałeś śmieci w BD i narzekasz ze HA jest Beeeeeee .
Ta jest na tyle inteligentna że tego nie potrzebuje
Nie przekrencaj moich słów. Nie mówie że HA jest bleeee tylko że DB jest źle zaprojektowana a to różnica.
HA od kilku lat nie zminił nic w bazie, może coś w strukturze baza bez zmian.
Z powodu braku elemnatnej wiedzy z twojej strony dotyczącej baz danych dyskuje z mojej strony z Tobą zamykam.
W końcu ( --------------------- )
Podłączę się pod ten temat i może odpowiedź mi coś rozjaśni i wskaże drogę,. HA posiadam od ponad roku, czytam, instaluję, poprawiam, bawię się, starając się korzystać z rad z tego forum. Nie korzystałem z funkcji recordera w koniguracji.yaml (include, exclude), nie korzystam z DBmaria, pytam czy i kiedy należy ograniczać zapisy encji, czy i kiedy należy przenieść bazę na DBmaria, jaką bazę uważa się za dużą i coś z tym należałoby zrobić ?
OK powiem jak to było u mnie. Zacząłem od czystego HA z wbudowaną bazą (o ile dobrze kojarze jest to SQLite), ale tam są tylko ustawienia i jakieś ostatnie dane nie nadaje się to do długoczasowych wykresów.
Później był influx jako plugin do HA. Ilość urządzeń rozrosła się do około 100 encji. Dość dużo było encjami zapisującymi dane statystyczne niektóre nawet co 5-10 sekund. W związku z tym baza się mocno rozrosła. Po 3 miesiącach osiągneła 8GB. Jakiekolwiek zapytanie, wczytanie wykresu itp trwało około 15-30 sekund.
Influx w pewnym sesie jest świetną bazą do zapisywanie danych “seryjnych” np odczyt jakiegoś sensora co 10 sekund. Problem tylko jest taki żeby influx był wydajny to dane muszą być spójne (czyli tego samego typu), wtedy indexowanie i wyszukiwanie np temperatury miesiąc temu działa szybko. Niestety, albo i stety HA tak ma, że dane nie są aż tak regularne, ponadto większość sensorów np. zigbee wysyla dane tylko przy zmianie stanu. Powoduje to że zalety influx są “tracone”.
W związku z kilkunastoma latami doświadczenia z MySQL, MariaDB (darmowy odpowiednik w 99% zgodny), microsoftSQL, PostgreSQL. Wybór padl na MariaDB.
Żeby mieć większą kontrolę nad samą bazą baze mam zainstalowaną w osobnej wirtualce (nie jako plugin do HA). Zabawy troche więcej niż dodanie pluginu, poprzez 5 kliknięć, ale mamy pare zalet. Jak mi się wywalił HA i byłem zmuszony do powrotu do kopii z przed 3 dni, to brakowało tylko danych z czasu odzyskiwania kopii, czyli kilkudziesięciu minut. Baza działała dalej aktualna.
Ale wracając do przesiadki… przerzuciłem wszystkie dane z Influxa do mariiDB, zabawy było co niemiara. Nie wiem czy drugi raz bym się z tym bawił bo naprawde trzeba przy tym posiedzieć. Jeżeli ktoś nie ma doświadczenia z bazami to proponuje odpuścić, tym bardziej że influx i maria niektóre dane trzyma w innym typie zmiennej i trzeba je przy importowaniu w locie przerabiać i zapisywać.
Obecnie mam baze która zapisuje okolo 350 encji. Większość to encje “statystyczne” a więc dość często coś zapisują. Po 10 miesiącach ma jakieś 25 GB, wiem zaraz mnie wyklną że to dużo itp, i wystarczy trzymać dane przez tydzień, albo jeszcze lepiej 5 dni 
Coż mam powiedzieć o gustach i nie mam zwyczaju dyskutować. Ja chce tak i mam tak i mam to gdzieś że ktoś chce inaczej 
Jeżeli chodzi o “wydajność praktyczną” wykres jeden encji z ostatnich 24h (zapis co 10 sek) trwa jakieś 1-2 sekundy. Uważam to za znaczny zysk w stosunku do influx.
Jeżeli chodzi o Twoje pytanie kiedy baze uznać za zbyt dużą itp.
Odpowiem tak baza jest za duża jak brakuje Ci na nią miejsca na HDD. A co z taką bazą zrobić? Odpowiem żartobliwie Przenieść na większy HDD.
A tak na poważnie największa z jaką pracowałem PostgreSQL, zapisywał jakies 3TB/m-c wymagany był dostęp do danych z ostatniego miesiąca, starsze replikowane na zapasową maszyne. Bieżące po miesiącu usuwane. Raport w którym trzeba było przemielić całą bazę generował się w jakieś 30 sekund. Fakt w porównaniu do tego maria to żółw, ale tam baza była zaprojektowana pod dokładnie takie zastosowanie i działało to na troche lepszym sprzęcie.
Jeżeli chodzi o HA, to niestety problemem poważnym jest struktura bazy, i tym nic nie zrobimy. Można sobie jednak troche pomóc. Przy wydajności baz są 2 bardzo ważne rzeczy związane ze sprzętem:
- ile mamy pamięci RAM (często wykonywane zapytania są trzymane w cache i odpowiedź mamy po ułamku sekundy)
- jaki jest nośnik bazy danych SD/HDD/SSD/FlashSSD (dane przecież trzeba z kąś odczytać).
Podstawą, którą dużo ludzi stosuje to malina + karta SD. Tutaj za dużo nie zdziałasz, bo ogranicza Cię znacznie prędkość odczytu karty (realnie około 20-30MB/s). Do tego ilość pamięci RAM starcza na HA spokojnie ale o większym cache zapomnij.
Uważam, że minimu to jakiś terminal (na początek polecam dell wyse Z90D7, albo Fujitsu s900n). Da się do tego włożyć jakiś w miare pożądny dysk SSD (240/480GB) i mamy spokojnie odczyt około 200MB/s, przy lepszym idzie dobic do 400MB/s. Do tego 8GB RAM i chodzi to całkiem całkiem.
Ale sie rozpisałem… a miało być na szybko…
Dzięki za odpowiedź, jeśli dobrze zrozumiałem to narazie mogę się nie martwić i nie interesować “podmianą bazy”.
state: "798"
automation: "39"
binary_sensor: "40"
button: "2"
device_tracker: "2"
group: "2"
input_boolean: "7"
input_button: "0"
input_datetime: "0"
input_select: "7"
input_text: "0"
light: "8"
media_player: "6"
person: "1"
remote: "1"
script: "1"
select: "7"
sensor: "606"
sun: "1"
switch: "33"
update: "13"
zone: "1"
“Sprzęt” to Dell Wyse ZX0 Z90D7 AMD G-T56N 1.65GHz 8GB 240GB SSD
Home Assistant Supervisor
System operacyjny hosta Home Assistant OS 9.3
Kanał aktualizacji stable
Wersja Supervisora supervisor-2022.10.2
Wersja agenta 1.4.1
Wersja Dockera 20.10.18
Pojemność dysku 234.0 GB
Pojemność użyta 23.3 GB
Zdrowy true
Wspierany true
Układ generic-x86-64
API Supervisora ok
Wersja API ok
Zainstalowane dodatki Mosquitto broker (6.1.3), Zigbee2MQTT (1.28.2-1), File editor (5.4.2), Terminal & SSH (9.6.1), Home Assistant Google Drive Backup (0.109.1), Samba share (10.0.0), Node-RED (13.5.2), InfluxDB (4.5.0), Cloudflared (4.0.3), ESPHome (2022.10.2)
Dashboards
Dashboardy 1
Zasoby 26
Widoki 24
Tryb storage
Recorder
Początek najstarszej sesji rejestratora 5 listopada 2022 14:14
Początek aktualnej sesji rejestratora 11 listopada 2022 16:36
Szacowany rozmiar bazy danych (MiB) 3193.41 MiB
Silnik bazy danych sqlite
Wersja bazy danych 3.38.5
Sprzęt masz ok i do ewentualniej przesiadki w przyszłości będzie ok.
Jeżeli szybkość generowanie wykresów Ci odpowiada to nie musisz się martwić. Ale troche mnie zastanawia bo wygląda że dane masz od 5.11.2022 do 11.11.2020, czyli 7 dni i to daje 5GB. Przy roku będzie 52x7 150GB a uwież mi to już naprawdę dużo, jak na ten sprzęt.
Poza tym polecam obserwować wielkość bazy poniewaź: dane są zapisywane w 2 podstawowych tabelach
tabela 1 z danymi zapisywanymi w czasie rzeczywistym, czyli wysyłamy co 1 sek to mamy 3600 zapisów na godzine (te dane są “czyszczone”)
tabela 2 z danymi statystycznymi - zapisywana co 5 minut i tutaj jest minimum, maximum, średnia z 5 minut (te dane zostają na dłużej, ale o ile się nic nie zmieniło nie usuwają się domyślnie)
Jeszcze mam pytanie, HA masz zwirtualizowane czy na czystym linuxie zainstalowane?
Miałem Debiana i HA w Dokerze, ale od 3 tygodni to Generic x86-64 bare-metal
Wiadomo, że będzie rosła , kiedy “lampka” ma się zapalić ?
Te kilkanaście ApexCharts jakoś przeżyję, z Influxa wrzucam do Grafany a tam (Rpi4) mam jeszcze Influxa z Domoticzem.
Jak to 5GB ? a nie 3.34 GB ?
czyli tą tabelę HA czyści domyślnie co 7 dni (chyba, że użytkownik ustawi np. purge_keep 5 dni), a z tabeli 2 nic nie jest usuwane ? i to rośnie do przepełnienia ?
Rozwiązanie fajne i szybkie, ale ma jedną wadę… domyślnie kopie robi na tym samym dysku co HA, a SSD jak już padają to na amen, wiem z doświadczenia. W życiu kilka SSD zajechałem.
Jak już pisałem zależy to przede wszystkim kiedy braknie Ci cierpliwości na czekanie żeby się wykres wygenerował.
A czerwona lampka ma Ci się zapalić i do tego może jeszcze jakaś syrena włączyć jak osiągniesz 80% zajętości dysku. SSD nie lubi być zapełniany do końca, bo wydajność spada. A jak osiągniesz 100% to będzie problem z działaniem systemu oczywiście
Oczywiśnie miejsce zżera nie tylko baza, ale jeszcze pare rzeczy, kopie zapasowe, logi itp.
Sorry 3,34, ale policzyłem dla roku dobrze ;|
Nie jestem pewien na 100% jak to jest w przypadku wbudowanej bazy, ale przy influx mi rosła na pewno przez 3-4 miesiace kiedy influxa mialem. ale nie przejmuj się nią tak bardzo ta baza wbrew pozorom nie rośnie szybko. Jak już pisałem 1 zapis na 5 minut dla każdego sensora, Pisze tak jako ostżeżenia na przyszłość, nie wiem czy HA to dla Ciebie taka zabawka na rok, pół roku, czy do emerytury
Obejściem problemu jest wypchnięcie Backupów na zewnątrz - można i do chmury np. tak:
Nie rośnie szybko i w końcu zoptymalizowano jakoś jej obsługę, ale przez 2-3 lata dopracowałem się 15% użycia na ssd 256GB (bo kiedyś standardsowa ilość zapisów do bazy naprawdę była na poziomie zarzynającym ssd).