Problème de taille base de données / Gestion de la BDD

[alju]

Bonjour,

Je rencontre un problème de taille de ma base de données, celle-ci ne fait que grossir à vue d’oeil, malgré plusieurs mesures déjà mises en place.

Le contexte :

• HAOS 11.1 sur VM Proxmox (espace disque alloué : 40Go)
• Clé Conbee 2
• 92 devices Zigbee divers et variés appairés via Zigbee2MQTT (mais une 15aine pas allumés)

En gros : 9 modules pour volets, ~20 prises connectées, 3 capteurs de mouvements, ~10 capteurs d’ouverture de porte, 5 ampoules, 1 bandeau LED, 11 modules de suivi de consommation sur le tableau électrique, 4 modules switch / relais ouverture de porte, 3 télécommandes, ~20 thermomètres (oui j’abuse ), 1 ZLinky

Initialement la VM avait 32Go d’espace disque.
Il y a 1 mois j’ai eu une alerte de disque plein.
J’ai donc passé la VM à 40Go et suivi des tutos pour optimiser la gestion de la BDD :

How to keep your recorder database size under control

Optimize Your Home Assistant Database

J’ai installé MariaDB, configuré tout ce qu’il faut pour la purge auto.
J’ai défini spécifiquement les sensors à enregistrer. Je n’ai gardé que les sensors avec des valeurs qui me sembles « utiles », tous les sensors « annexes » ne devraient donc pas être enregistrés :

recorder:
  # MariaDB
  db_url: !secret mariadb_url
  auto_purge: true
  auto_repack: true
  purge_keep_days: 15
  commit_interval: 30
  include:
    domains:
      - automation
      - device_tracker
      - cover
      - person
      - input_boolean
      - input_datetime
      - input_number
      - light
      - zone
    entity_globs:
      - weather.*
      - sensor.*weather*
      - sensor.mariadb_database_*
      - sun.*
      - sensor.sun*
      - sensor.*_temperature
      - sensor.*_humidity
      - sensor.*_battery
      - sensor.*_illuminance
      - sensor.*_power
      - sensor.*_power_a
      - sensor.*_power_b
      - sensor.*_energy
      - sensor.*_energy_a
      - sensor.*_energy_b
      - sensor.*_action
      - sensor.*_last_seen
      - sensor.zlinky_*
      - sensor.compteur_eau_*
      - binary_sensor.lave_linge_en_marche
      - binary_sensor.seche_linge_en_marche
      - binary_sensor.lave_vaisselle_en_marche
      - binary_sensor.*_contact
      - binary_sensor.*_tamper
      - binary_sensor.*_water_leak
      - binary_sensor.*_occupancy
      - binary_sensor.ping_*
      - update.*

Pour autant, la BDD ne fait que grossir.
J’ai créé un sensor pour suivre son évolution :

En 3 semaines elle est passée de 0 à plus de 25Go !
+10Go en 7 jours !

Ce matin, le disque était de nouveau plein.
J’ai lancé une purge à la hate via les outils de développement, rebooté. Pour autant rien n’a changé (je crois que la purge ne se fait que la nuit ?)
(et où sont les 40 - 25 = 15 Go restants de ma VM ??)

J’ai du mal à concevoir qu’une BDD puisse augmenter aussi vite.
Qu’est ce que j’ai raté dans ma config et que faire pour conserver une taille raisonnable avec un certain historique ?

Merci pour votre aide

[AlexHass]

Salut,

Bon là effectivement ce n’est pas normal
Concernant le recorder, c’est marrant j’ai plus tendance à exclure qu’à inclure. Tu n’as que des inclusions avec beaucoup de domaines.

Pour continuer l’analyse il faudrait:

• Confirmer que c’est la BD qui est vraiment la cause. Car en théorie elle est sencé se stabiliser une fois ta durée de rétention atteinte. Paramètres>système>Correction - options-information du système tu dois avoir un section recorder avec une estimation de la taille.

• Regarder dans la base la taille et le nombre d’entrées dans chaque table.

• Regarder dans la base quelles sont les entités qui polluent le plus… avec une requete sur states (je vais poster la requête plus bas)

Ca c’est ma base avec plus de 500 entités en tout genre…

Requete SQL:

select states.metadata_id, states_meta.entity_id , count(*) as cntd 
from states join states_meta on states.metadata_id = states_meta.metadata_id
group by states.metadata_id order by cntd desc

[vincen]

N’as-tu pas de debug activé sur certains components de HA ? Car cela peut aussi faire exploser très vite le stockage !

[alju]

Je ne crois pas. Tu penses à quoi par exemple ?

[AlexHass]

Je viens de voir que tu as un ZLinky.
C’est connu comme étant un gros gros bourrin dans la base de données.
J’ai déjà vu qu’il envoie une donnée toute les 4sec ou moins. Du coup sur 15 jours avec une bonne 10aine de valeurs (si ce n’est plus) il au moins 3,5millions d’enregistrements
Donc en gros rien que Zlinky lâché dans la nature c’est 350 à 400 Mo sur 15 jours.

Ce n’est pas 25Go biensûr, mais suffit d’avoir d’autres appareils de ce type et t’y est vite…

[alju]

La BDD fait bien la taille indiquée par le sensor :

J’ai passé ta requête, et ce sont les modules de suivi de consommation sur le tableau éléctrique qui ressortent en tête de liste !

Je ne les ai que depuis 1 mois :
Entre 300 000 et 900 000 lignes par entité, x3 entités par device, x11 devices … on y est !
Mais alors pourquoi autant de lignes ?
Alors que les prises connectées avec suivi de conso ne pèsent quasiment rien à côté …

Serait-ce un intervalle de rafraichissement trop élevé ?

[AlexHass]

@alju

Serait-ce un intervalle de rafraichissement trop élevé ?

Oui clairement 926.000 entrées en 15 jours!
C’est 42 enregistrement par minute! donc toutes les 1,5 sec!

Avec 11 devices et le Zlinky ton HA et ton MariaDB ne doit pas chaumer d’ailleurs!!

[AlexHass]

J’ai une prise Shelly qui fait des updates toutes les 3 sec.
J’aimais pas avoir ça dans la DB alors j’ai créé un filtre de type time_throttle.
Pour ne qu’enregistrer 1 valeur toutes les minutes.
Et j’ai exclu l’entité originale du recorder pour ne garder que la filtrée.
Du coup j’ai:

• sensor.shelly_plug_s_puissance : mis à jour toutes les 3sec pour afficher ou utiliser quand je veux de la précision.
• sensor.shelly_plug_s_puissance_filtre : pour l’enregistrement… car au final dans l’historique une précision de 1 min c’est largement assez.

Je m’en doute que faire ça sur 50 entités c’est fastidieux

Autre chose pour Zlinky, si je me souviens bien il envoie aussi les valeurs techniques comme le tpye d’abonnement ou autres valeurs qui ne changent jamais ou pour lesquelles l’intérêt de l’historique est quasi nul… c’est tjrs ça de gagné de les exclure.

  - platform: filter
    name: "Shelly Plug S Puissance Filtre"
    entity_id: sensor.shelly_plug_s_puissance
    filters:
      - filter: time_throttle
        window_size: "00:01"

[alju]

Merci, je vais essayer tes 2 pistes.

Ce qui m’étonne c’est que Z2M m’indique un update frequency à 10s pour les modules de conso …

[AlexHass]

@alju

Ce qui m’étonne c’est que Z2M m’indique un update frequency à 10s pour les modules de conso …

Attention je ne suis pas certain que c’est ce que ça veut dire.
Si m’appareil envoie des nouvelles valeurs Z2M les transmet.
C’est quoi le modèle de ces modules de conso?

[alju]

Ce sont ceux-là :

TuYa PJ-1203A control via MQTT | Zigbee2MQTT

J’ai vérifié dans Z2M, dans l’onglet état d’un des devices , les valeurs changent bien toutes les 10sec

Edit : par contre j’ai l’impression que « Last Seen » refresh un peu plus souvent (genre toutes les 3-5 sec)
Edit 2 : du coup je vais désactiver l’enregistrement des lastseen dans le recorder pour voir …

[alju]

Suite à l’exclusion du recorder des modules de suivi de conso, la taille de ma BDD baisse chaque nuit après la purge du recorder.
On est passé de 30Go à 4,3Go.

Pour autant, l’espace disque disque affiché dans les paramètres système ne baisse, et reste à 88%
Pour rappel la VM a 45Go de disque alloué

J’ai déja rebooté HA, la VM et Proxmox, mais rien ne bouge …

[alju]

J’ai poursuivi mes investigations.
J’ai vérifié mon (full) backup hebdo de cette nuit : celui-ci fait 5,1 Go
Je l’ai décompressé pour vérifier le contenu.
Le fichier core_mariadb.tar.gz fait aussi ~5Go, soit la quasi-totalité du backup.
J’ai décompressé le fichier à son tour, et là je constate que taille des fichiers de la BDD ne sont pas du tout cohérents avec ce qui est indiqué dans phpMyAdmin ou dans les propriétés système de HA :

J’ai fouillé dans phpMyAdmin, et j’ai exécuté cette commande sur les 2 tables en question :

Et l’espace disque utilisé a baissé instantanément !