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

alju · Novembre 19, 2024, 11:29

@Plouf34 sur les appareils comme celui-ci Tuya PJ-1203A control via MQTT | Zigbee2MQTT tu peux modifier la fréquence d’actualisation

Perso j’ai mis la fréquence à 4sec sur ces pinces ampèremétriques.
Mais quand tu regardes les flux MQTT publiés, la fréquence n’est pas respectée. Il y a toujours plusieurs publications par seconde :

19/11/2024 12:25:00 
{"current_a":0.313,"current_b":3.449,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":595,"update_frequency":10}

19/11/2024 12:24:59(-0.94 seconds) 
{"current_a":0.313,"current_b":3.449,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":595,"update_frequency":10}

19/11/2024 12:24:59(-0.14 seconds) 
{"current_a":0.313,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:59(-0.04 seconds) 
{"current_a":0.313,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:58(-0.08 seconds) 
{"current_a":0.313,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:58(-0.04 seconds) 
{"current_a":0.313,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":54.9,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:58(-0.15 seconds) 
{"current_a":0.382,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":58.2,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:58(-0.06 seconds) 
{"current_a":0.382,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":58.2,"power_b":2682.5,"update_frequency":10}

19/11/2024 12:24:58(-0.14 seconds) 
{"current_a":0.382,"current_b":12.176,"energy_a":0.1,"energy_b":2771.58,"energy_flow_a":"producing","energy_flow_b":"consuming","energy_produced_a":361.11,"energy_produced_b":19.25,"power_a":58.2,"power_b":2682.5,"update_frequency":10}

bobflux · Novembre 19, 2024, 12:54

Pour lister les tables, avec la taille

SELECT 
	name,
	size/1048576.0 AS size_MB,
	rowcount,
	size/rowcount AS bytes_per_row
FROM (
	SELECT name, sum(pgsize) size FROM dbstat GROUP BY name
) sizes
NATURAL JOIN (
	SELECT 'event_data' AS name, (SELECT count(*) FROM event_data) rowcount UNION ALL
	SELECT 'event_types', (SELECT count(*) FROM event_types) rowcount UNION ALL
	SELECT 'events', (SELECT count(*) FROM events) rowcount UNION ALL
	SELECT 'migration_changes', (SELECT count(*) FROM migration_changes) rowcount UNION ALL
	SELECT 'recorder_runs', (SELECT count(*) FROM recorder_runs) rowcount UNION ALL
	SELECT 'schema_changes', (SELECT count(*) FROM schema_changes) rowcount UNION ALL
	SELECT 'state_attributes', (SELECT count(*) FROM state_attributes) rowcount UNION ALL
	SELECT 'states', (SELECT count(*) FROM states) rowcount UNION ALL
	SELECT 'states_meta', (SELECT count(*) FROM states_meta) rowcount UNION ALL
	SELECT 'statistics', (SELECT count(*) FROM statistics) rowcount UNION ALL
	SELECT 'statistics_meta', (SELECT count(*) FROM statistics_meta) rowcount UNION ALL
	SELECT 'statistics_runs', (SELECT count(*) FROM statistics_runs) rowcount UNION ALL
	SELECT 'statistics_short_term', (SELECT count(*) FROM statistics_short_term) rowcount
) counts
ORDER BY size DESC;

Pour savoir quelles entités bourrent la BDD

SELECT m.metadata_id, m.entity_id, cnt, datetime(ts, 'unixepoch') last_update FROM
(SELECT metadata_id, count(*) cnt, max(last_updated_ts ) ts FROM states GROUP BY metadata_id ORDER BY 2 DESC LIMIT 100) c
JOIN states_meta m USING (metadata_id)

Pour savoir quelles statistiques bourrent la BDD

SELECT m.statistic_id, m.id, cnt, datetime(ts, 'unixepoch') last_update FROM
(SELECT metadata_id, count(*) cnt, max(start_ts) ts FROM statistics_short_term GROUP BY metadata_id ORDER BY 2 DESC LIMIT 100) c
JOIN statistics_meta m ON m.id=c.metadata_id

Plouf34 · Novembre 19, 2024, 5:16

Savez vous pourquoi la somme des tables me donnent environ 600 MB, et que au final mon fichier home-assistant_v2.db en fait 1.05GB?

bastgau · Novembre 19, 2024, 6:13

Lors d’une suppression de données, l’espace disque n’est pas libéré de suite. Il sera libéré une fois par mois lors du repack de la base de données.

Automatically repack the database every second sunday after the auto purge. Without a repack, the database may not decrease in size even after purging, which takes up disk space and can make Home Assistant slow. If you disable auto_repack it is recommended that you create an automation to call the recorder.purge periodically. This flag has no effect if auto_purge is disabled.

Plouf34 · Novembre 19, 2024, 6:37

J’ai pourtant bien fait une purge + Repack dans les services

tb44 · Novembre 20, 2024, 5:53

J’ai le même soucis que toi.
Je ne sais pas quoi faire mis à part lancer une purge de la BDD à 2 jours + repack

bastgau · Novembre 20, 2024, 8:36

Il ne faut peut-être rien faire

C’est quoi cette obsession de vouloir réduire la taille de sa base de données pour gagner un peu de place ?

Sinon il faut aller chercher sans doute du côté des index pour commencer.

Un peu de lecture :

https://www.sqlite.org/fileformat.html#index_btrees

bobflux · Novembre 20, 2024, 7:58

Une grosse BDD fait ramer les backups.

Il suffirait d’exclure la BDD des backups, puisque la raison de faire un backup, c’est de sauvegarder les configurations, automatisations, etc, qui font que le truc marche. Tout ça prend très peu de place. Dans la BDD il n’y a que l’historique, ce n’est pas important, une sauvegarde par mois suffirait largement. Si tu perds l’historique de la température de ton salon, tu ressentiras au pire un léger picotement. Par contre si t’as pas sauvegardé ta config HA et que le SSD saute…

SQLite est un très bon outil dans son domaine, mais le stockage de séries temporelles ne fait pas partie de son domaine. Il vaudrait mieux influx ou clickhouse, mais ce n’est pas supporté par HA.

SELECT count(*) FROM mqtt_float;
   ┌────count()─┐
1. │ 2790548659 │ -- 2.79 billion
   └────────────┘

Pulpy-Luke · Novembre 20, 2024, 8:17

Salut

Dans ce cas, tu mets carrement rien dans la bdd … et tu n’as ni souci de perf ou de backup
Un truc avec des trous d’un mois, ça n’a absolument aucune utilité

bobflux · Novembre 20, 2024, 11:09

En effet, j’ai mis la purge à 7 jours.

bastgau · Novembre 21, 2024, 8:12

C’est ta vision des choses et pour les personnes pour qui ce n’est pas important autant passer tout son HA en full memory.

bobflux · Novembre 21, 2024, 9:14

Si tu sais comment mettre la BDD de HA en full memory avec purge de 24h, je suis preneur.

Toutes les données MQTT sont déjà historisées dans Clickhouse, donc si je peux éviter de les avoir en double dans HA…

bastgau · Novembre 21, 2024, 9:32

A l’ancienne, avec un point de montage en RAMFS …

bobflux · Novembre 21, 2024, 10:23

J’ai mis commit_interval à 3600 dans la config du recorder, ça revient quasiment au même et réduira les écritures au minimum, en cas de crash ou coupure de courant je perdrai donc la dernière heure.