Forum |  HardWare.fr | News | Articles | PC | Prix | S'identifier | S'inscrire | Aide | Shop Recherche
704 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Choix de schéma

 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

Choix de schéma

n°2305609
barbaputas
Wtf ?!
Posté le 13-09-2017 à 15:35:43  profilanswer
 

Hello, la bdd n'est pas mon fort j'aurais besoin d'un avis svp  :o  
 
Je dois stocker toutes les minutes la valeur de 3300 sondes.
 
Aujourd'hui j'ai la structure suivante : (en gras clé primaire),
val_sondes_mois_annee (id_sonde int, le int, J001 float,J002 float....J031 float)
 
La colonne "le" correspond au numéro de minute dans la journée, donc pour un mois j'ai 3300*1440= 4 752 000 lignes.
Tous les mois, je créé une autre table.
 
Je pensais à une structure, plus flexible à mes yeux:
val_sondes_mois_annee(id_sonde int, la_date unsigned int,valeur float)
 
Le champ "la_date" serait stockée sous forme UNIX EPOCH et me permet de stocker des valeurs qui sont sous la minute (si le besoin se présente)
Mais pour un mois (et stockage toujours à la minute) cela ferait : 3300*1440*31=147 312 000 lignes. Ca commence à faire...
 
Que serait le mieux à votre avis ?
 

mood
Publicité
Posté le 13-09-2017 à 15:35:43  profilanswer
 

n°2305610
rufo
Pas me confondre avec Lycos!
Posté le 13-09-2017 à 15:52:51  profilanswer
 

Reprendre tout le schéma de la BD effectivement. Le coup des 31 champs pour stcoker une valeur et faire une table par mois, c'est pas top.
 
Une seule table valeurs_sondes(id_sonde int, date_valeur datetime, valeur float) sera plus approprié.
 
Par ailleurs, vu la volumétrie, pas sûr qu'un SGBR soit le plus adapté. Ca risque de galérer aux niveaux perfs puisque tu dois collecter et enregistrer en BD en moins d'une minute la valeur de 3300 sondes :/ Sans être un très grand nb d'enregistrements, ça commence à faire. Faudra peut-être regarder du côté de NoSQL.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°2305611
barbaputas
Wtf ?!
Posté le 13-09-2017 à 15:59:34  profilanswer
 

Merci pour ton avis. Comme je l'ai dit, optimiser la partie SQL c'est pas mon fort (bon j'ai pas le choix :o), saurais-tu m'expliquer pourquoi stocker sur 31 champs c'est pas top ? Imagine la V1 c'était 366 champs :o


Message édité par barbaputas le 13-09-2017 à 16:04:26
n°2305612
rufo
Pas me confondre avec Lycos!
Posté le 13-09-2017 à 16:32:05  profilanswer
 

Intuitivement, tu dois sentir plusieurs pbs :
1) Bon courage pour récupérer les valeurs des sondes pour des dates à cheval sur plusieurs mois voire années ou sur des jours bien définis, genre, les valeurs des sondes pour tous les lundis de l'année. Avec tes 31 champs par table pour chaque mois, ou tes 366 champs par table pour chaque année, je te dis même pas la tronche de ta requête SQL  :pt1cable: Alors qu'avec la structure proposée, ça se fait les doigts dans le nez :o
 
2) avec 31 champs, tu perds de la place avec les mois ayant moins de 31j. Je parle même pas du cas des 366 champs.
 
3) faire une table par mois (voire par an), tu vois la galère : à chaque changement de mois/année, faut recréer la structure de la table.
 
Je t'invite à regarder les formes normalisées des BD relationnelles, en particulier la forme 3NF de Codd ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°2305617
barbaputas
Wtf ?!
Posté le 13-09-2017 à 17:59:19  profilanswer
 

J'ai jetté un oeil aux formes normalisées, pas simple  :o  
 
Si je comprends bien, la structure avec les 31 champs n'est pas 3NF mais 2NF alors que la 2ème proposition semble bien 3NF mais peut-être pas 3NF Codd ?
 
Récupérer les valeurs c'est un peu chiant c'est vrai, le calvaire étant plutôt les affichages à la semaine qui se superpose sur 2 mois différents mais c'est géré.
En fait le problème aujourd'hui ce sont les écritures disques après les update, ca paralyse le système entier pendant 20-30 sec, c'est pour cela que je cherche une autre forme structure.
 
Comme je dois assurer 10ans de stockage, celà me fait grosso 18 milliards de lignes... Sans forcément passer par NoSQL, est-ce que mysql peut ingérer ca ? Aujourd'hui j'ai (imposé hein :o) sybase sqlanywhere; j'ai créé un fichier txt avec toutes les données pour un mois, et la base est encore en train de l'ingurgiter (depuis hier), alors ca m'inquiète pour les 18milliards de lignes  :sarcastic:

n°2305620
h3bus
Troll Inside
Posté le 13-09-2017 à 18:36:06  profilanswer
 

Perso j'aurai fais un fichier de données et un fichier d'index, et une librairie aux petits oignons pour accéder aux données.
 
Pas sur qu'une BDD soit vraiment adaptée pour ce genre de besoins.


---------------
sheep++
n°2305621
Farian
Posté le 13-09-2017 à 18:39:22  profilanswer
 

Tout à fait d'accord avec h3bus, surtout que les enregistrements sont tous de même taille, l'index risque d'être très simple à faire.
 
Et pour l'exploitation, on lit le bloc, on mappe tout ça sur la structure "qui va bien" et on a tout directement en mémoire.


---------------
On n'est jamais très fort pour ce calcul !
n°2305624
rufo
Pas me confondre avec Lycos!
Posté le 13-09-2017 à 19:12:04  profilanswer
 

Faut voir les traitements à faire derrière. Le coup du fichier indexé, dans le cas traitements un peu complexes, ça peut viet devenir délicat (je pense à des stats, par ex).
 
Sur Mysql, j'avais remarqué qu'il valait mieux retirer les index lors de l'import car ça ralentissait beaucoup le chargement. A la fin de l'import, on remet les index et c'est bon. Après, pour des imports "bruts" (ie pas de traitement et tout dans une seule table), il existe des fonctions de Mysql qui font ça nativement et plus optimisées.
 
18 milliards, ça commence à faire mais je crois que la limite d'une table pour Mysql, c'est 50 milliards d'enregistrements.
Après, tu peux très bien avoir une table pour le mois en cours (et éventuellement les 2-3 précédents si besoin est) et mettre les autres mois dans une table archive.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°2305625
Farian
Posté le 13-09-2017 à 19:22:24  profilanswer
 

Je suis d'accord avec ton raisonnement pour des index sur des enregistrements de taille variable, mais là, on va plus avoir un tableau avec des éléments (les valeurs d'une minute, puis les valeurs de chaque sonde) de taille fixe, et l'index sera à peu près égal au décalage en minutes x la taille des données enregistrées chaque minute.
 
Et, quand bien même, on ne ferait qu'ajouter des valeurs au fichier et des éléments dans l'index (si on ajoute des sondes à un moment, par exemple, ou même plus de valeurs pour une sonde), donc on n'a pas besoin de gérer cette activation / désactivation.
 
Et si on ne veut / peut pas gérer un seul fichier, pas de souci, on peut très bien faire un couple de fichiers (index, données) par semaine/mois/année/siècle :)


---------------
On n'est jamais très fort pour ce calcul !
n°2305638
TotalRecal​l
Posté le 13-09-2017 à 22:37:26  profilanswer
 

Le bordel avec plein de jours numérotés sur un seul record et un compteur de minute c'est effectivement moche, moi j'aime pas :o.

 

Pour la taille max, en mysql InnoDB il me semble qu'il n'y a pas de limite à part la capacité de stockage de la machine et l'OS (taille des fichiers). Si tu mets tout dans une table avec une ligne par sonde avec une clé en bigint tu devrais pouvoir tenir pendant environ 10 milliard 635 millions d'années, ça devrait suffire.
Par contre côté perfs effectivement c'est possible que ça devienne problématique d'ici quelques siècles.

 

Du coup pour l'archivage, faire des tables par période c'est pas forcément déconnant tant que t'as pas besoin d'aller sortir en une fois des données sur des périodes différentes. Je suppose que quand t'accède aux données de 2017 tu t'en fous des données de 2004, etc.
Sachant qu'une fois une période bouclée j'imagine que ses données sont immuables. Genre une table pour la période "active", et en fin de période tu balances ça dans une table nommée selon la période.

 

Si les requêtes pour accéder à tout ça sont construites dynamiquement (genre par d'ORM) c'est pas forcément une mauvaise approche, et ça aidera aussi à gérer les backups, etc (car une seule table "active" et plein de tables "figées" ).

 

Un truc qui me perturbe c'est que tu "numérotes" tes sondes, mais tu n'as pas besoin de les caractériser et les identifier individuellement ?
Le choix de ta PK actuelle me parait douteux. Je ne vois pas ce que la date vient foutre dedans.

 

edit : Et ton histoire de Unix Epoch me parait un peu inquiétante, les SGBDR ont des types natifs pour gérer les dates, il faut s'en servir.

Message cité 1 fois
Message édité par TotalRecall le 13-09-2017 à 22:41:08

---------------
Réalisation amplis classe D / T      Topic .Net - C# @ Prog
mood
Publicité
Posté le 13-09-2017 à 22:37:26  profilanswer
 

n°2305646
barbaputas
Wtf ?!
Posté le 14-09-2017 à 09:01:01  profilanswer
 

h3bus a écrit :

Perso j'aurai fais un fichier de données et un fichier d'index, et une librairie aux petits oignons pour accéder aux données.
 
Pas sur qu'une BDD soit vraiment adaptée pour ce genre de besoins.


 

rufo a écrit :

Faut voir les traitements à faire derrière. Le coup du fichier indexé, dans le cas traitements un peu complexes, ça peut viet devenir délicat (je pense à des stats, par ex).
 
Sur Mysql, j'avais remarqué qu'il valait mieux retirer les index lors de l'import car ça ralentissait beaucoup le chargement. A la fin de l'import, on remet les index et c'est bon. Après, pour des imports "bruts" (ie pas de traitement et tout dans une seule table), il existe des fonctions de Mysql qui font ça nativement et plus optimisées.
 
18 milliards, ça commence à faire mais je crois que la limite d'une table pour Mysql, c'est 50 milliards d'enregistrements.
Après, tu peux très bien avoir une table pour le mois en cours (et éventuellement les 2-3 précédents si besoin est) et mettre les autres mois dans une table archive.


 

Farian a écrit :

Je suis d'accord avec ton raisonnement pour des index sur des enregistrements de taille variable, mais là, on va plus avoir un tableau avec des éléments (les valeurs d'une minute, puis les valeurs de chaque sonde) de taille fixe, et l'index sera à peu près égal au décalage en minutes x la taille des données enregistrées chaque minute.
 
Et, quand bien même, on ne ferait qu'ajouter des valeurs au fichier et des éléments dans l'index (si on ajoute des sondes à un moment, par exemple, ou même plus de valeurs pour une sonde), donc on n'a pas besoin de gérer cette activation / désactivation.
 
Et si on ne veut / peut pas gérer un seul fichier, pas de souci, on peut très bien faire un couple de fichiers (index, données) par semaine/mois/année/siècle :)


 
J'avais jamais pensé à créer ma propre structure. Le problème des fichiers (je pense peut être à tord), c'est qu'ils seront souvent modifiés/lus etc et je me dis que ca peut réduire la durée de vie du disque dur. D'où l'idée d'un sgdb.
 
 

TotalRecall a écrit :

Le bordel avec plein de jours numérotés sur un seul record et un compteur de minute c'est effectivement moche, moi j'aime pas :o.  
 
Pour la taille max, en mysql InnoDB il me semble qu'il n'y a pas de limite à part la capacité de stockage de la machine et l'OS (taille des fichiers). Si tu mets tout dans une table avec une ligne par sonde avec une clé en bigint tu devrais pouvoir tenir pendant environ 10 milliard 635 millions d'années, ça devrait suffire.
Par contre côté perfs effectivement c'est possible que ça devienne problématique d'ici quelques siècles.
 
Du coup pour l'archivage, faire des tables par période c'est pas forcément déconnant tant que t'as pas besoin d'aller sortir en une fois des données sur des périodes différentes. Je suppose que quand t'accède aux données de 2017 tu t'en fous des données de 2004, etc.  
Sachant qu'une fois une période bouclée j'imagine que ses données sont immuables. Genre une table pour la période "active", et en fin de période tu balances ça dans une table nommée selon la période.
 
Si les requêtes pour accéder à tout ça sont construites dynamiquement (genre par d'ORM) c'est pas forcément une mauvaise approche, et ça aidera aussi à gérer les backups, etc (car une seule table "active" et plein de tables "figées" ).
 
edit : Et ton histoire de Unix Epoch me parait un peu inquiétante, les SGBDR ont des types natifs pour gérer les dates, il faut s'en servir.


 
Découper par période oui, comme je fais déjà actuellement. Aujourd'hui on remonte guère plus que 2 ans en arrière.
 

TotalRecall a écrit :


Un truc qui me perturbe c'est que tu "numérotes" tes sondes, mais tu n'as pas besoin de les caractériser et les identifier individuellement ?
Le choix de ta PK actuelle me parait douteux. Je ne vois pas ce que la date vient foutre dedans.
 


 
Pour aller chercher les valeurs d'une sonde ou plusieurs sondes, j'ai besoin d'un numéro pour les identifier non ? Tu ferais comment ? :o  
La clé "le" représente ma minute, et si je veux plusieurs fois stocker ma sonde dans la journée, il faut bien qu'elle fasse partie de ma clé primaire non ?
 

TotalRecall a écrit :


Et ton histoire de Unix Epoch me parait un peu inquiétante, les SGBDR ont des types natifs pour gérer les dates, il faut s'en servir.


 
Le problème c'est que je dois faire avec le hardware dispo, je n'ai pas un espace disque immense (la machine n'est pas un serveur dédié d'ailleurs, elle héberge tout).  
J'y vois un gain de place avec un champ de 4 octets (mon uint) et un datetime par exemple(8 octets), pour les recherches dans la table, je "suppose" que chercher un int est plus rapide qu'une date (encore une fois c'est pas mon coeur de métier..)
 
Donc, ma structure V2 vous semble plus viable ?

n°2305648
TotalRecal​l
Posté le 14-09-2017 à 09:28:27  profilanswer
 

Hello,

 

J'aurais une table Sonde pour les caractériser et une FK vers cette sonde dans ma table de Valeurs, mais évidemment ça dépend du besoin. Si le numéro d'une sonde te permet de t'y retrouver c'est toi qui voit.

 

Oui le deuxième modèle est mieux (beaucoup moins chiant à requêter, pas besoin de passer son temps à mettre à jour des records qui existent déjà) mais je pense quand même que stocker une date dans un int n'est pas une super idée. Et je ne dis pas ça juste à cause du problème de l'an 2038.
Mais c'est en bonne partie une affaire de goût et de côté pratique, et je comprend ton argument.

 

J'ai aussi un peu de mal à concilier "je veux stocker 15 ans de données mises à jour chaque seconde avec 4400 nouvelles valeurs" et "je dois bosser sur une machine avec un hardware moisi qui doit aussi encaisser plein d'autres trucs en même temps".
Pour consommer efficacement ce genre de données , il faut quand même un minimum de moyens et une bonne maîtrise du SGBDR (ça s'optimise ces bestioles là). Et je parle juste des requêtes SELECT, il y a encore plus à dire sur la fiabilité ou la charge constante pour les MAJ, l'indexation, etc.
Je ne sais pas dans quel contexte tu bosses mais ça me parait un peu amateur comme approche (rien d'offensant là dedans!).

Message cité 1 fois
Message édité par TotalRecall le 14-09-2017 à 09:30:35

---------------
Réalisation amplis classe D / T      Topic .Net - C# @ Prog
n°2305651
rufo
Pas me confondre avec Lycos!
Posté le 14-09-2017 à 10:03:17  profilanswer
 

Au passage, que tu passes par un sgbd ou par un fichier, ça revient à peut près au même puisque le sgbd stocke les données sur le HDD aussi :o Après, il optimise ses accès disques en général.
 
De même, il faut que le hardware suive un minimum pour qu'il soit à la hauteur de tes ambitions (ou celles de ton donneur d'ordre).


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°2305654
barbaputas
Wtf ?!
Posté le 14-09-2017 à 10:58:32  profilanswer
 

TotalRecall a écrit :

Hello,
 
J'aurais une table Sonde pour les caractériser et une FK vers cette sonde dans ma table de Valeurs, mais évidemment ça dépend du besoin. Si le numéro d'une sonde te permet de t'y retrouver c'est toi qui voit.
 
Oui le deuxième modèle est mieux (beaucoup moins chiant à requêter, pas besoin de passer son temps à mettre à jour des records qui existent déjà) mais je pense quand même que stocker une date dans un int n'est pas une super idée. Et je ne dis pas ça juste à cause du problème de l'an 2038.
Mais c'est en bonne partie une affaire de goût et de côté pratique, et je comprend ton argument.
 
J'ai aussi un peu de mal à concilier "je veux stocker 15 ans de données mises à jour chaque seconde avec 4400 nouvelles valeurs" et "je dois bosser sur une machine avec un hardware moisi qui doit aussi encaisser plein d'autres trucs en même temps".
Pour consommer efficacement ce genre de données , il faut quand même un minimum de moyens et une bonne maîtrise du SGBDR (ça s'optimise ces bestioles là). Et je parle juste des requêtes SELECT, il y a encore plus à dire sur la fiabilité ou la charge constante pour les MAJ, l'indexation, etc.
Je ne sais pas dans quel contexte tu bosses mais ça me parait un peu amateur comme approche (rien d'offensant là dedans!).


 
Pas de problèmes je suis pas offensé je vais juste aller me jeter sur l'autoroute :o
Je comprends bien que cela demande optimisation, je suis en plein dedans d'où mes interrogations.
Aujourd'hui c'est 3300 stockages par minutes, à conserver sur 10 ans (on fait de la régulation). Le stockage pourrait évoluer ponctuellement en dessous de la minute pour observer des phénomènes.  
Pourquoi envisagerait-tu un fk sur une sonde ?
 

rufo a écrit :

Au passage, que tu passes par un sgbd ou par un fichier, ça revient à peut près au même puisque le sgbd stocke les données sur le HDD aussi :o Après, il optimise ses accès disques en général.
De même, il faut que le hardware suive un minimum pour qu'il soit à la hauteur de tes ambitions (ou celles de ton donneur d'ordre).


 
Le sgbd optimise donc ses accès, c'est déjà ça de pris :)
 
 
 

n°2305655
rufo
Pas me confondre avec Lycos!
Posté le 14-09-2017 à 12:08:56  profilanswer
 

Franchement, si jamais les conditions de volumétrie viennent à changer (fréquence de collecte plus élevée ou plus de sondes), tu pourrais arriver aux limites de ce que peut faire un simple fichier ou un SGBDR. Du coup, partir dès à présent sur une BD NoSQL pourrait avoir du sens et anticiper sur l'avenir.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Choix de schéma

 

Sujets relatifs
[Ada][A.I.] le choix systémic des relations logique.Choix logiciel de gestion de version pour petite équipe
Choix de configurations a l'ouverture d'un fichier Excelverifier le contenu des champs masqué/affichés selon le choix de la ca
Problème de schéma de BDDSql server un champ avec 3 choix
Choix tabletteCréation page HTML choix de la langue
[Résolu] Choix d'un schéma d'une tableChoix de pattern à partir d'un attribut dans un Schema
Plus de sujets relatifs à : Choix de schéma



Copyright © 1997-2016 Hardware.fr SARL (Signaler un contenu illicite) / Groupe LDLC / Shop HFR