NAS Synology - Base de données

[BenjyNet]

Est-ce qu'il y aurait des personnes ici qui utilisent leur NAS pour stocker des infos du HC2 dessus et faire un joli affichage des courbes de température par exemple ou des cycles de chauffage ?

[guchtpi]

Salut,

 

Tu devrais jeter un petit coup d'oeil ici :

http://forum.fibaro.com/viewtopic.php?t=2380&highlight=synology

[Lazer]

Salut,

 

J'utilise depuis mi-décembre les scripts donnés dans ce lien.

Pour le moment c'est hébergé sur un serveur Web mutualisé (chez OVH), et comme je rafraichis toutes les 60 secondes, ça bouffe un peu de mon upload (ADSL 3500 mètres du DSLAM, donc pas terrible).

 

Je viens d'investir dans un HP Proliant N54L, en cours d'install et de test (ESXi, Xpenology, et autres VM diverses).

Lorsque ce sera bien prêt (je fais des tests de failover/crash/reprise avant de mettre en prod), je basculerai la base de données et les scripts depuis OVH vers mon NAS.

 

Le soucis des scripts donnés sur le forum de Fibaro, c'est que ce n'est pas optimisé du tout. Surtout au niveau de la base de données qui grossi très vite, et n'a aucun index.

J'ai déjà  positionné des index pour améliorer un peu les requêtes.

Et comme ça ne sert à  rien de garder les courbes de températures à  la minute près pendant des semaines/mois/années, je suis en train de mettre en place une nouvelle table et des requêtes afin de consolider les données : chaque jour, on ne conserve que les valeurs min/moyen/max de chaque capteur. Sur le long terme ça permettra de faire des courbes de tendance, et des comparaisons années après années (oui je prévoie large).

 

Si ça intéresse du monde, j'essaierais de développer mon travail quand ça sera plus au point.

[BenjyNet]

Ah oui moi ça m'intéresse beaucoup.

[Moicphil]

Pareil, si tu veux bien partager.

[Lazer]

OK, bien sûr je vais partager.

Làj'ai pas trop le temps, mais je reviens donner des nouvelles dès que possible.

[Lazer]

Alors voici quelques nouvelles.

Comme je le disais, je n'ai pas terminé, mais ça fonctionne déjà  mieux comme ça.

 

J'ai modifié la table principale qui contient les données. La table prend moins de place dans la base, et les requêtes sont plus rapides :

CREATE TABLE IF NOT EXISTS `domotique_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `device` mediumint(9) DEFAULT NULL,
  `value` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device` (`device`,`time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Maintenant la table qui contient les devices. On note l'ajout des champs active et room_id :

CREATE TABLE IF NOT EXISTS `domotique_device` (
  `id` mediumint(9) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `room_id` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`,`active`,`room_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

En effet, toutes mes sondes de températures se nomment "Thermomètre" dans la Home Center 2. Donc dans les graphs, la légende était illisible.

Ce champs room_id pointe vers une nouvelle table domotique_room que j'ai créé :

CREATE TABLE IF NOT EXISTS `domotique_room` (
  `room_id` mediumint(9) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `section_id` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`room_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Pour remplir cette table, j'ai utilisé l'outil HC2 Toolkit App de Krikroff afin d'extraire les informations sur les pièces définies dans mon HC2.

Ensuite, pour afficher les informations correctes sur les graphes, j'ai modifié la requête du fichier device_get.php comme suit :

$reponse = $bdd->prepare('SELECT domotique_device.id, domotique_device.name AS device_name, domotique_room.name AS room_name FROM domotique_device, domotique_room WHERE domotique_device.room_id=domotique_room.room_id AND domotique_device.type = :type AND domotique_device.active=1');

On note la condition active=1 en lien avec le champs correspondant de la table domotique_device qui permet de masquer certains devices indésirables sur les graphes.

Dans le même fichier, il faut aussi modifier la ligne qui crée le label qui sera affiché dans la légende du graph :

	$day_array = array($donnees['id'], $donnees['device_name'].' '.$donnees['room_name']);

Ca donne des courbes du style "Thermomètre Salon", "Thermomètre Chambre", etc...

 

Pour finir, une nouvelle table domotique_data_day qui permet de synthétiser les données par jour, pour une conservation longue durée :

CREATE TABLE IF NOT EXISTS `domotique_data_day` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `device_id` mediumint(9) NOT NULL,
  `min_value` decimal(10,2) DEFAULT NULL,
  `avg_value` decimal(10,2) DEFAULT NULL,
  `max_value` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Pour remplir la table domotique_data_day à  partir de la table domotique_data, on utilise la requête suivante :

INSERT INTO domotique_data_day (date, device_id, min_value, avg_value, max_value)
SELECT
	DATE(time) AS date,
	device as device_id,
	MIN(value) AS min_value,
	AVG(value) AS avg_value,
	MAX(value) AS max_value
FROM
	domotique_data
WHERE
	DATE(time) > ( SELECT COALESCE(MAX(`date`), '0001-01-01') FROM domotique_data_day )
	AND DATE(time) < CURDATE()
GROUP BY
	date,
	device

Cette requête est à  exécuter tous les jours, par exemple à  00:05, juste après le commencement d'une nouvelle journée. Si on a plusieurs jours de retard, ce n'est pas grave, la requête recalculera tous les jours manquants, donc rien n'est perdu. Pour le moment je stocke mes données chez OVH donc je n'ai pas de scheduleur à  disposition, mais quand je migrerai vers mon NAS, je pourrai mettre ça dans la crontab.

 

Puis on purge les anciennes données afin que la table domotique_data ne grossisse pas trop, par exemple après 21 jours :

DELETE FROM domotique_data WHERE DATE(time) < SUBDATE(CURDATE(), 21)
OPTIMIZE TABLE domotique_data

Notez que sur des hébergeurs mutualisés, comme OVH avec un compte Perso, la requête Optimize Table ne passe pas bien, car elle consomme beaucoup de mémoire. La première fois ça m'a crashé par table ! Donc depuis je ne l'exécute plus. Mais sur un NAS dédié, il ne devrait pas y avoir de problème.

 

Reste à  faire :

• les graphes permettant d'exploiter les données à  long terme de la table domotique_data_day.
• ajouter un champs color à  la table domotique_device pour forcer des couleurs personnalisées à  chaque courbe.
• d'autres optimisations du code existant ?!?

[lolomail]

Beau boulot, étant newbie dans les bases de données de ce type, si j'ai bien compris ces modifs sont faites sur les fichiers a installer sur le nas correct?

Vivement que tu ai fini cela devrait être top et sympa

[Moicphil]

Oui, beau travail !

[Lazer]

En fait j'ai déployé tel quel les scripts fournis par Byackee sur le forum Fibaro.

 

Au début ça marchait bien, mais comme la table de données grossissait trop vite, j'ai décidé d'optimiser tout ça. Donc j'ai effectué toutes les modifications sur les tables qui contenaient les données (sans rien perdre). J'ai utilisé pour cela PhpMyAdmin qui permet de modifier la structure des tables très facilement (un package existe tout prêt à  déployer pour ceux qui ont un Synology).

 

Les extraits de code que j'ai présenté plus haut sont simplement les requêtes qui permettent de créer de nouvelles tables. Mais si comme moi vous avez déjà  vos scripts qui tournent, alors il faut modifier manuellement la structure des tables en s'aidant des requête que j'ai donné.

 

Pour les newbie, je vais essayer de préparer un package, qui sera en fait le package provenant du forum Fibaro, auquel je vais apporter toutes les modifications nécessaires pour que ça soit prêt à  l'emploi.

Je pense que durant la semaine prochaine j'aurai suffisamment de temps.

[BenjyNet]

Effectivement c'est ce que j'allais te dire : créer un package prêt àl'emploi plutôt que de bidouiller les tables (c'est pas donné àmr/mme tout le monde)

[Lazer]

Hello,

 

J'avance doucement, mais j'avance quand même.

Pour justifier mon retard : en voulant mettre en place le monitoring du niveau des batteries, je me suis rendu compte que les scripts existants ne géraient pas qu'un device puisse avoir 2 types d'entrées différentes dans la base de données : température_sensor et battery par exemple.

Donc je suis en train de modifier tout ça, et d'y ajouter également le monitoring des températures de consigne des thermostats.

Je fournirai un package complet avec les tables SQL, les pages Web, et le Virtual Device. J'espère que je pourrai fournir une première version dans la semaine.

 

Question aux admins : est-ce que ce sujet à  bien sa place dans le bistrot, ou ne vaut-il pas mieux que je crée un nouveau sujet dans une autre section plus adaptée ?

[BenjyNet]

Moi je suis d'avis de créer un nouveau thread pour parler de ce que tu as mis en place.

Une petite remarque : pour le monitoring des batteries fait attention àne pas les interroger trop souvent sous peine de les décharger.

[Lazer]

En fait, c'est la HC2 qui interroge les devices sur batterie.

Mon script se contente de récupérer l'info une fois de temps en temps et de l'envoyer dans la base MySQL.

En revanche, afin de ne pas surcharger inutilement la base, j'ai prévu d'avoir un intervalle différent selon ce qu'on veut monitorer (par exemple toutes les minutes pour les températures, et seulement une fois par jour pour les batteries, car les batteries ça varie heureusement moins vite que les températures).

[guchtpi]

Euh... oui mais faut faire attention si tu interroges un periphérique qui mesure la température et qui est sur batterie (comme le capteur FGFS101 Flood Sensor) !

[Lazer]

Vous me faites douter tous les deux...

 

Dans le script LUA, c'est la fonction fibaro:getValue(i, "value") qui permet de récupérer la température des devices, et qui est donc exécutée toutes les minutes.

Je pensais que cette fonction se contente d'interroger la HC2 sur la valeur de l'information qu'elle a en mémoire.

Pour la lecture de la vraie valeur de température, il me semble que ça se règle dans les paramètres avancés de chaque module. Et lorsque qu'un device envoie sa nouvelle valeur de température ou humidité, on voit un message apparaitre dans le panneau d'événement.

Donc entre 2 mesures réelles (avec réveil du device et transfert d'informations sur le réseau Zwave), il me semble que la fonction fibaro:getValue(i, "value") récupère seulement le dernier état connu.

 

De plus, quand je regarde mes courbes (mises à  jour toutes les secondes donc), je vois une nette différence entre les sondes alimentées par secteur (Fibaro Universel + sondes Dallas 1wire) et celles alimentées sur piles (Everspring ST814) :

- Les premières envoient le moindre changement de température au 10ième de degré près à  la HC2, ce qui se traduit par des courbes très fines.

- Les secondes envoient les changement de températures une fois pas heure au mieux, ou alors sur changement de température supérieur à  1°C, ce qui se traduit par des courbes en escalier.

 

Donc je ne pense pas que mes scripts usent les piles prématurément, mais peut-être que je me trompe ?!?

[guchtpi]

Je suis perplexe ...

 

J'avais implémenté les scripts d'origine il y a quelques semaines.

J'ai ensuite ajouté un capteur FGFS101 au réseau et au bout d'environ 1 semaine la batterie de celui-çi était vide, à  noter que le module a été configuré avec les valeurs par défaut.

Dans la doc: "Fibaro Smoke Sensor’s battery life is approximately 3 years when on optimum settings." ... donc problème !

 

Depuis ce w-e, j'ai désactivé les scripts de capture et remis une nouvelle batterie dans le FGFS101 ... wait and see ... si dans 1 semaine la batterie est de nouveau à  plat, le problème sera à  chercher dans les paramètres...

 

Dans le doute, ce qui pourrait être utile, ce serait d'avoir une liste d'exclusion (par exemple dans une variable globale) pour certains ID.

[Lazer]

Je pense que certains modules peuvent avoir des problèmes avec les batteries. J'ai un module ST814 dont les 3 piles AA se vident en 1 mois... clairement pas normal, alors que pour les 2 autres modules identiques je n'ai aucun problème. Je vais surement devoir le renvoyer au SAV, en espérant que ce genre de problème passe en garantie.

Tu as peut-être le même genre de problème avec le FGFS101.

 

Je note pour la variable globale ! A suivre...

[guchtpi]

Bon bein, ça devient plus clair ... je viens de tester mon module ... batterie à  2,27V au lieu des 3V ... et ça au bout de 4j seulement, il y a donc clairement un problème avec ce module !!!

Pas génial Fibaro 

[Lazer]

Tu as trouvé le problème avec ton module ?

 

Sinon, j'ai enfin publié mes scripts dans un nouveau sujet :

http://www.domotique-fibaro.fr/index.php/topic/466-graphiques-de-temp%C3%A9rature-humidit%C3%A9-consommation-batterie/

 

La suite là  bas...