Discussion :

[Yellow-Sky]

Bonjour,
je dispose de nombreux automates mesurant des capteurs. Je voudrais enregistrer toutes les données de capteurs dans une base données et je suis bloqué sur la meilleure conception à réaliser.

Actuellement:
Création d'une table avec la définition des capteurs (identifiant unique, type de variable à enregistrer, nom) (plusieurs centaines)
Création de X tables (une par capteur) où je stocke les données Timestamp + valeur de chaque capteur

/!\ A chaque lecture des données sur l'automate, je vérifie que celle de la base de données n'est pas la même (l'automate ne met pas à jour la donnée tant qu'elle n'a pas changé et donc le timestamp liée à la données non plus)
=> réduction de la taille de la basse de donnée.
Exemple: une donnée d'un état peut rester de nombreuses heures/jours à la même valeur => l'idée est de ne pas enregistrer 96 fois la même valeur par jour (si les données sont vérifiées chaque 15 minutes mais parfois c'est toutes les minutes et on passerait à 1440 enregistrements alors qu'en pratique un seul par jour suffit voire moins)
Le "problème" de cette technique est qu'à chaque lecture de d'une valeur de l'automate, je dois faire une lecture de la table du capteur correspondant pour voir quelle est la dernière valeur enregistrée , la comparer et l'enregistrer si c'est pertinent

En alternative, je m'étais dit que je pourrais changer mon schéma par
Création d'une table avec la définition des capteurs (identifiant unique, type de variable à enregistrer, nom)
Création d'une table où je stocke les données Timestamp + valeur de chaque capteur + identifiant de chaque capteur (avec timestamp + identifiant du capteur en key)
=> je pourrais créer une requête join sur les deux tables qui m'indique pour chaque capteur la dernière valeur pour chaque capteur => moins appel à des tables mais une requête de base plus longue pour sortir ce tableau.

Le problème c'est que je ne sais pas si lorsque j'aurais des milliers d'enregistrements ce sera pertinent ou non de faire cette requête join

Auriez vous des suggestions/ commentaires / remarques pour optimiser cela?
Merci

[alassanediakite]

Salut
La deuxième solution est la meilleur.
En plus de la clé définie sur timestamp + identifiant du capteur, mettez un index sur identifiant de chaque capteur cela accélère la requête en évitant (si nécessaire) le parcours de la table entière.
@+

[Yellow-Sky]

Merci (sachez que je n'y connais rien en optimisation de base de données donc je pose peut être trop de questions évidentes):

Quand vous dites un index sur l'identifiant de chaque capteur, on parle bien de la première des deux tables?

J'ai dit milliers mais si je réfléchis un peu plus, j'ai (pour certains capteurs) 1440 enregistrements par jour, donc j'aurais (rétention de 2 ans) 1 000 000 d'enregistrements par capteur dans cette seconde table. Et si j'ai des centaines de capteurs, j'ai peur que cela devienne vite gros et ait peur que la seconde solution pose problème?

Par ailleurs, le fait de mettre des index sur certains champs ne va t'il pas à l'encontre d'une augmentation significative de la taille de la base de données par rapport à des tables séparées où je n'aurais qu'un index sur le timestamp?

Finalement, faut-il ajouter un identifiant unique de l'enregistrement (de la seconde table) et du capteur (de la première table) ou est-ce superflu?
Merci

[alassanediakite]

Salut
Il y a contradiction à ce niveau (ou j'ai pas bien compris)

Exemple: une donnée d'un état peut rester de nombreuses heures/jours à la même valeur => l'idée est de ne pas enregistrer 96 fois la même valeur par jour (si les données sont vérifiées chaque 15 minutes mais parfois c'est toutes les minutes et on passerait à 1440 enregistrements alors qu'en pratique un seul par jour suffit voire moins)

J'ai dit milliers mais si je réfléchis un peu plus, j'ai (pour certains capteurs) 1440 enregistrements par jour, donc j'aurais (rétention de 2 ans) 1 000 000 d'enregistrements par capteur dans cette seconde table. Et si j'ai des centaines de capteurs, j'ai peur que cela devienne vite gros et ait peur que la seconde solution pose problème?

J'ai proposé la deuxième solution avec...

l'idée est de ne pas enregistrer 96 fois la même valeur par jour

Pour l'index proposé, il s'agit de identifiant du capteur dans la deuxième table.

Par ailleurs, le fait de mettre des index sur certains champs ne va t'il pas à l'encontre d'une augmentation significative de la taille de la base de données par rapport à des tables séparées où je n'aurais qu'un index sur le timestamp?

L'index augmente globalement la taille de la base de données mais accélère significativement les recherches (dans notre cas, la vérification de valeur existante).
Par ailleurs, la création de la clé primaire (identifiant du capteur+timestamp) de la deuxième table peut éviter un deuxième index sur uniquement identifiant du capteur. Attention à l'ordre des champs pour la clé primaire: identifiant du capteur en première position!
@+

[Yellow-Sky]

Bonjour, je n'ai pas exclus que les données ne changeait pas chaque minute... je dis qu'il y a des données qui ne changent quasi jamais mais désolé de l'explication partielle.

Dans le cas de valeurs qui peuvent ou non changer fréquemment: quelle serait une solution intelligente?

Merci

[vanagreg]

Bonjour,

Il faut d'abord modéliser et ensuite penser à l'alimentation. Si j'ai bien compris, tu auras au moins 2 tables:
- une table capteurs, qui va contenir la liste de tes capteurs existants, avec comme pk id_capteur
- une table valeurs des capteurs, avec pour chaque capteur une valeur relevée. Moi j'y mettrai une pk id_valeur, et une fk sur id_capteur de la table capteurs

Il faudra un index sur id_capteur et timestamp_releve

Quelle est ton SGBD?

[alassanediakite]

Salut

Dans le cas de valeurs qui peuvent ou non changer fréquemment: quelle serait une solution intelligente?

La première solution n'est jamais la bonne!!!
A la date d'aujourd'hui des tables avec des millions de lignes n'est vraiment pas un problème pour les SGBD communément connus si les index sont correctement placés.
Une solution intermédiaire serait d'utiliser le partitionnement de table (si mariadb le supporte).
@+

[Waldar]

/!\ A chaque lecture des données sur l'automate, je vérifie que celle de la base de données n'est pas la même (l'automate ne met pas à jour la donnée tant qu'elle n'a pas changé et donc le timestamp liée à la données non plus)
=> réduction de la taille de la basse de donnée.

C'est une mauvaise idée. Vous ne saurez pas distinguer si votre capteur n'envoie plus de données à cause d'une panne (du capteur ou du réseau ou du logiciel ou autre) ou d'une valeur fixe.
Gardez une rythme régulier dans l'ingestion de vos données, tant pis si vos données se répètent, il vaut mieux une base de données plus volumineuse mais fiable que l'inverse.

Une de mes tables de "capteurs" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select count(*) (bigint) from <lagrossetable>;
299 667 864 022

[Yellow-Sky]

Merci pour vos réponses

@escartefigue: Je vois, cela dit, j'ai une tendance à séparer les tables en fonction du type de variable afin d'optimiser l'espace:
Si j'ai un état logique qui s'enregistre par exemple dans un tinyint, je sépare d'une température que j'enregistre dans un décimal (5,1) ou (4,1) mais sinon donc l'idée est donc bien d'envisager votre solution 2 qui semble donc meilleure que ma solution 1, merci

@Waldar: l'automate ne met pas à jour la valeur (et le timestamp associé) si la valeur ne change pas => ma solution permet quand même de voir si la sonde n'est plus mise à jour depuis x heures car en // j'ai une fonction qui m'informe si la communication a été opérationnelle ou non.

Pour la panne logicielle, c'est clair que je pourrais traiter l'erreur également mais en pratique je reçois également une alerte par mail si une erreur d'éxécution du code est apparue => je suis couvert pour la majorité des cas me semble t-il, non?

[escartefigue]

Si seul le type (decimal, integer, float...) de la mesure change, on peut utiliser mon modèle en y ajoutant l'héritage et la spécialisation avec des attributs d'un type adapté

[Yellow-Sky]

Je suis désolé, là mon niveau ne me permet plus de savoir que faire même si en pratique je pense comprendre le propos.
Auriez vous une référence pour en savoir/comprendre plus?
Merci

[SQLpro]

A lire : https://sqlpro.developpez.com/cours/...tion/heritage/

A +