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

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [MySQL] temps fetching trop important

 



 Mot :   Pseudo :  
 
 Page :   1  2
Page Précédente
Auteur Sujet :

[MySQL] temps fetching trop important

n°2330532
matheo265
Posté le 19-03-2019 à 11:43:09  profilanswer
 

Bonjour tout le monde,
 
Je sais que je vais sûrement m'attirer des remarques et remontrances mais je me lance !  :sweat:  
 
Je suis un développeur touche à tout (et malheureusement donc spécialisé nulle part...). Pour les besoins d'un de nos clients j'ai développé une solution chargée d'enregistrer dans une base MySQL les caractéristiques de machines (caractéristiques qui remontent toutes les secondes).  
 
J'ai donc créé, entre autres, une table "machinesvalues" avec les champs suivants :

  • mav_id INT AUTO_INCREMENT PRIMARY
  • mav_macid INT NOT NULL
  • mav_statedate DATETIME NOT NULL
  • mav_<nomcaracteristique1a120>

macid est la clé étrangère de la table "machines" où sont répertoriées les machines.
statedate est la date/heure à laquelle la mesure a été prise.
Les caractéristiques stockées dans cette table sont toutes celles liées à un véhicule : heures moteur, température, pression pneus, etc etc. Elles sont pour la plupart (90%) de type int/float. Il y en a 120 en tout (donc 123 colonnes dans ma table exactement).
 
Aujourd'hui la table comporte plusieurs millions d'enregistrements et on se retrouve face à un problème bloquant. Je lance une simple requête :
 

Code :
  1. select * from machinevalues where mav_macid=505 and mav_statedate between '20190301 07:00:00' and '20190318 20:00:00'


Et j'obtiens mes 75 résultats en... 134 secondes (dont 93 de fetch).
 
On m'a bien conseillé de répartir mes colonnes dans plusieurs tables. Mais ça complexifie l'insertion via mon webservice, mais aussi la composition des requêtes car les utilisateurs doivent pouvoir générer des rapports et graphiques via un applicatif que j'ai développé...
 
Côté serveur la machine dispose de 8 Go, mes tables sont en innodb et j'ai augmenté les valeurs de paramétrage suivantes :

  • innodb_buffer_pool_size = 2G
  • query_cache_type = 1
  • query_cache_size = 1MB
  • query_cache_limit = 1MB

J'aurais besoin de l'avis de personnes plus spécialisées que moi en MySQL afin de savoir comment optimiser tout ceci. Tous les conseils seront les bienvenus.
 
Merci d'avance pour votre aide  :)  
 

mood
Publicité
Posté le 19-03-2019 à 11:43:09  profilanswer
 

n°2330534
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 12:25:34  profilanswer
 

Effectivement, tu vas te prendre des remontrances :D
Personnellement, je pense que ta modélisation est foireuse : faire une table avec autant de champs me paraît pas être une bonne idée même si Mysql supporte des tables avec 1000 champs. Du coup, avant de se lancer dans une reprise du modèle de ta bD, ça vaudrait le coup de faire un EXPLAIN sur tes requêtes et voir où ça coince. Un petit coup de Mysqltuner ( https://raw.githubusercontent.com/m [...] qltuner.pl ) ne sera pas inutile non plus. L'idée est de voir si en tunant ton my.ini, y'aurait pas moyen d'améliorer les perfs. Le fichier de conf par défaut de Mysql est pas forcément top. Mysqltuner analysera les stats de ton serveur Mysql (donc à exécuter sur un serveur qui a un certain nb de jours de prod) et te fera des propositions de dimensionnement de certains variables de conf.
Le EXPLAIN te montrera comment sont exécutées tes requêtes et quels index sont utilisés. Du coup, en recodant certains requêtes et en faisant des index différents, tu vas peut-être gagner en perfs. Pour ma part, en faisant ces 2 actions, sur 1 requête importante de recherche, j'ai divisé par 5 le temps d'exécution !
 
Après, toujours sans changer de modèle de BD, tu peux utiliser le partitionnement en lignes de Mysql. Le principe est similaire à répartir tes enregistrements dans plusieurs tables sauf que là, c'est transparent pour toi. Ton modèle présente une seule table mais physiquement, il y en a plusieurs. Je te propose un partitionnement en lignes avec comme critère la date.
 
Sinon, d'un point de vue évolutivité, ça serait plus pertinent de faire une table "Caracteristiques" qui contient la liste des caractéristiques et une autre table qui va contenir les valeurs de ces caractéristiques pour tes relevés de données des machines.
Caracteristiques :
caract_id
caract_label
 
ValeursCaracteristiques :
valcaract_id
valcaract_val
mav_id
caract_id
 
Ainsi, si t'as une nouvelle caractéristique à ajouter, t'as juste à ajouter une entrée dans ta table "Caracteristiques". Alors qu'avec ton système, faut reprendre le code de l'appli. C'est pas évolutif facilement. Tu vas voir que ça va pas te faire tant de choses que ça à recoder car c'est très générique. ;) J'ai fait ce genre de système dans mon appli Icare (cf. ma signature).
A noter que tu peux aussi appliquer le partitionnement à la table contenant les valeurs des caractéristiques.


---------------
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°2330537
matheo265
Posté le 19-03-2019 à 13:58:43  profilanswer
 

Merci Rufo pour ton retour.
 
Je ne suis pas contre les remontrances si on y mets les formes, ce que tu as fait :)
 
C'est plein de pistes que je n'ai pas encore exploité et je vais voir l'idée de la configuration dans un premier temps.
 
La réécriture de mon schéma je vais me contenter d'y penser ^^ Car derrière ça va demander un travail monstrueux... Mais je note l'idée si un projet similaire se représente.
 
Merci encore :)

n°2330538
mechkurt
Posté le 19-03-2019 à 14:07:57  profilanswer
 

Pour accélérer Mysql en lecture, y'a 2 axes principaux :
 - mettre les index de recherche au bon endroits (avec EXPLAIN, et un peu d’expérience connaitre ses requêtes)
 - mettre tes bases en Ram (ou sur des SSD)  mysqltuner devrait t'aider à ce niveau, par exemple j'ai ce genre de valeur sur un serveur en production (128Go de RAM)

Code :
  1. innodb_buffer_pool_size = 80G
  2. innodb_buffer_pool_instances = 32
  3. innodb_read_io_threads = 16
  4. innodb_write_io_threads = 16
  5. query_cache_size = 2000M
  6. query_cache_limit = 5M
  7. max_allowed_packet = 256M


Mais comme la dit Rufo poses toi aussi la question de ton modèle de base, à l'heure actuelle tes données sont dupliqué à chaque timestamp, même si elle ne changes pas, ce qui fait que la taille de ta table doit avoir une taille "artificiellement grosse".
Si tu as des données qui ne change pas, sort les et met les dans une autre table car multiplié par X timestamp ça peut vite faire des Go de données !


---------------
D3
n°2330540
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 14:31:15  profilanswer
 

Effectivement, c'est un point que je n'ai pas abordé dans ma réponse : rajouter une table entre celle des valeurs des caractéristiques et celle des timestamps. Cette table LiensValeursCaracteristiques est de la forme :
lien_id
mav_id
valcaract_id
 
Du coup, la table ValeursCaracteristiques devient :
valcaract_id
valcaract_val
caract_id
 
Ainsi, une valeur dupliquée x fois ne va être stokée qu'une fois et à chaque valeur identique, tu ne fais que rajouter un lien. CEPENDANT, cette approche est intéressante dans le cas de valeurs de type chaînes de caractères dont la taille est > la taille des 3 ID présents dans la table LiensValeursCaracteristiques. Or, dans ton cas, tu gères principalement des INT ou Float. Du coup, je penses pas que ça vaille le coup.
 
Dans mon appli Icare (gestion de conf), c'est ce que j'avais fait. Par rapport à l'outil de gestion conf utilisé initialement (Advitium), pour les mêmes infos gérées (environ 270 confs, chacune comportant entre 150 et 300 valeurs), on est passé d'une BD de 100 Mo environ à 5 Mo :o
En effet, 70% des valeurs d'un même attribut étaient identiques d'une conf à l'autre. On est passé d'une appli qui ramait à une appli très rapide ;)


---------------
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°2330541
matheo265
Posté le 19-03-2019 à 14:42:28  profilanswer
 

Houlà vous me perdez un peu là ^^
 
Si je comprends un peu le principe, il s'agit de sortir les valeurs identiques pour les stocker dans une table annexe qu'on se contente de lier (sauf si valeur différente). Malheureusement une bonne partie des valeurs changent souvent : vitesse, température moteur, niveau de carburant, etc. De plus ça va m'obliger à tout refondre mon schéma ainsi qu'une bonne partie de mon code... Je ne sais pas si le jeu en vaut la chandelle :(
 
Table memory j'avais essayé une fois mais je ne me souviens plus quel blocage j'avais rencontré... Je vais me replongé dedans.
 
Niveau index en revanche j'ai bien créé sur mon idmachine et sur la date/heure. Pas sûr que je puisse faire mieux de ce côté-là...

n°2330542
mechkurt
Posté le 19-03-2019 à 15:03:27  profilanswer
 

Quel taille fait ta table actuelle ?
Quelle fréquence sur tes timestamp ?
Combien de mav_id DISTINCT ?

Spoiler :


C'est pour une flotte d'entreprise ou de la location ?


---------------
D3
n°2330543
matheo265
Posté le 19-03-2019 à 15:08:55  profilanswer
 

La table fait actuellement...

Spoiler :

4,7 Go pour 6 550 335 lignes...


C'est une flotte d'entreprise et le j'ai une ligne de données par seconde (le client ne souhaite pas transiger sur ce point)...
 
J'ai fait un EXPLAIN et RAS, j'ai bien créé les bons index. Maintenant je m'attarde du côté de Mysqltuner puis je regarderai aussi les table memory.

n°2330544
mechkurt
Posté le 19-03-2019 à 15:26:42  profilanswer
 

1 données par seconde, je doutes que tout change "tout le temps", donc j’espère que ton code à une manière ou une autre d'ignorer les temps mort (inactivité du véhicule) !
Elle est en prod depuis combien de temps ?

Message cité 1 fois
Message édité par mechkurt le 19-03-2019 à 15:27:24

---------------
D3
n°2330545
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 15:50:13  profilanswer
 

matheo265 a écrit :

Houlà vous me perdez un peu là ^^
 
Si je comprends un peu le principe, il s'agit de sortir les valeurs identiques pour les stocker dans une table annexe qu'on se contente de lier (sauf si valeur différente). [...]


Pas du tout. La table se contente de faire le lien entre le table des relevés (avec timestamp) et la table des valeurs. Ainsi, x enregistrements qui auraient la même valeur pour la même caractéristique vont pointer sur le même enregistrement dans la table des valeurs des caractéristiques.
Mais comme je l'ai expliqué, dans ton cas, je pense pas que ça soit pertinent. Par contre, tu pourrais être plus fin et ne stocker un nouvel enregistrement (pour une même machine et même caractéristique, bien évidemment) que si la valeur a changé par rapport à l'enregistrement précédent. Si elle n'a pas changé, tu te contentes de mettre à jour le timestamp avec la date/heure courante du dernier relevé. Ainsi, tu sauras que la valeur n'a pas changé pendant le temps écoulé entre 2 enregistrements successifs.
 

matheo265 a écrit :

Table memory j'avais essayé une fois mais je ne me souviens plus quel blocage j'avais rencontré... Je vais me replongé dedans.


Laisse-moi deviner : y'avait pas assez de RAM pour faire tenir la table et son index dans son entier en mémoire...
 


---------------
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
mood
Publicité
Posté le 19-03-2019 à 15:50:13  profilanswer
 

n°2330546
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 15:55:51  profilanswer
 

matheo265 a écrit :

[...]
J'ai fait un EXPLAIN et RAS, j'ai bien créé les bons index. Maintenant je m'attarde du côté de Mysqltuner puis je regarderai aussi les table memory.


J'ai quelques doutes sur le RAS. Tu pourrais nous poster le contenu du EXPLAIN, svp ? Notamment pour voir le nb d'enregistrements lus à chaque étape sans utilisation d'un index ou les Using temporary, DERIVED...
 
Edit : au niveau des paramètres de Mysql, augmenter la taille des variables suivantes :
tmp_table_size
sort_buffer_size
 
Si tu as un phpMyAdmin, vas sur l'onglet "Etat" puis "Toutes les variables d'état". Tu vas trouver des stats qui pourront t'aider.
 
 
@mechkurt : t'as vraiment 128 Go de RAM sur un serveur de prod ? :ouch: Moi, j'ai 2 Go pour chacune de mes pauvres VM :cry:
 
Edit 2 : @matheo265 : si je dis pas de bêtises, une table Mysql est "limitée" à 50 millions d'enregistrements. T'es donc à 13% du remplissage max. En fonction depuis quand la BD est en prod, tu vas avoir une idée du temps qu'il te reste avant les grosses emmerdes :o
--> Correction, que ça soit en MyIsam ou InnoDB, la limite du nb d'enregistrements est largement > 50 millions. On parle plutôt de 4 milliards en MyIsam.
https://stackoverflow.com/questions [...] base-table

Message cité 1 fois
Message édité par rufo le 19-03-2019 à 16:13:58

---------------
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°2330547
matheo265
Posté le 19-03-2019 à 16:22:01  profilanswer
 

mechkurt a écrit :

1 données par seconde, je doutes que tout change "tout le temps", donc j’espère que ton code à une manière ou une autre d'ignorer les temps mort (inactivité du véhicule) !
Elle est en prod depuis combien de temps ?


Les véhicules disposent d'un modem qui envoi les données via SFTP. Lorsque le véhicule est éteint il n'y a pas de données. La base est en prod depuis 6 mois...  
 

rufo a écrit :

Laisse-moi deviner : y'avait pas assez de RAM pour faire tenir la table et son index dans son entier en mémoire...


Le serveur dispose actuellement de 6Go dont seulement 3 sont alloués à l'instance MySQL. J'ai demandé à augmenter à 16 ce qui me permettra de passer innodb_buffer_pool_size à 10 Go.
 

rufo a écrit :


J'ai quelques doutes sur le RAS. Tu pourrais nous poster le contenu du EXPLAIN, svp ? Notamment pour voir le nb d'enregistrements lus à chaque étape sans utilisation d'un index ou les Using temporary, DERIVED...


Voilà le résultat :

Code :
  1. # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  2. '1', 'SIMPLE', 'machinevalues', NULL, 'ref', 'fk_machinesv_idx,mav_statedate,mav_macid', 'fk_machinesv_idx', '4', 'const', '3260667', '11.11', 'Using where'


Message édité par matheo265 le 19-03-2019 à 16:32:41
n°2330550
matheo265
Posté le 19-03-2019 à 16:36:28  profilanswer
 

Je sais maintenant que ce n'est pas un problème de mémoire car innodb_buffer_pool_size est à 10 et je suis toujours à 75 secondes de fetch...

n°2330554
mechkurt
Posté le 19-03-2019 à 17:08:24  profilanswer
 

6 mois de prod est déjà 4.7 Go de données, je penses que le futur ne présage rien de bon...
 
Je penses que tu auras effectivement besoin de te pencher sur la partitionnement de ta table de données brut.
 
Ce que tu peux faire aussi pour soulager la machine c'est de garder les données brut dans un coin, et de générer tes rapports de stats dans d'autres tables via un cron plusieurs fois par jour, données qui elle seront consulté par le client final.
 
@rufo 128Go c'est pour un gros Magento multi site et qui en plus sert d'outils de caisse, c'est un peu surdimensionné mais qui peut le plus peut le moins ! ^^
Et quand il s'agit de refaire les index pour 30 000 produits, la puissance n'est pas tant que ça superflu...


---------------
D3
n°2330555
matheo265
Posté le 19-03-2019 à 17:10:29  profilanswer
 

Oui je suis en train de me pencher sur le partitionnement et tu as vu juste : ça semble convenir dans ce cas de figure.
 
Je suis bien conscient que ce n'est qu'une rustine et qu'il faudra bien que je me penche dans des changements majeurs de la structure de ma bdd :(

n°2330560
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 17:24:51  profilanswer
 

Effectivement, 5 Go en 6 mois avec déjà des pbs de perfs, tu vas au devant de gros ennuis assez rapidement. :/
L'idée de faire une table pour les stats avec MAJ périodique est une bonne idée. Ca soulagera le serveur pour des requêtes lourdes qui intéressent souvent les clients.
 
Après, y'a une autre solution plus radicale. Passer sur un SGBD NoSQL. Dans ton cas, je dirais de type document. C'est très utilisé pour des systèmes d'analyses de logs, ça me paraît donc tout indiqué pour ton cas de figure.
 
Mais en tout cas, ne fais pas l'autruche : la conception de votre appli n'est pas bonne vue la taille des données à manipuler. C'est même étonnant d'avoir fait ce choix d'une seule table avec autant de colonnes. On est à la limite de la faute professionnelle.  :pfff:  
Donc, temps que vous avez encore un peu de temps, recodez les mécanismes appli<->BD. Vous pouvez faire des benchmarks pour trouver la meilleur solution. Vous faites un extract de votre BD actuelle, avec un outil de type ETL ou autre (genre un script de transfo fait sur-mesure en fonction de la destination), vous transformez les données et vous les chargez dans une BD NoSQL de type document puis dans ton actuelle table mais partitionnée horizontalement puis dans le nouveau schéma qui a en plus une table Caractéristiques et une table ValeursCaractéristiques avec et sans partitionnement mais avec la feature de n'insérer une nouvelle valeur que si différente de la précédente.
 
Ca va vous prendre quelques jours ou quelques semaines en fonction de vos compétences et dispos mais au moins, vous aurez une bonne idée de vers où il faut aller. Sans ça, dans 1 an, l'appli se cassera la gueule régulièrement et sera tellement lente que tous les clients vont gueuler. J'ai connu ça avec une appli de GMAO : j'avais fait une analyse de la BD, j'avais repéré pleins de malfaçons et j'ai fait un rapport indiquant quelques bugs ou problèmes ces malfaçons allaient provoquer. Ben petit à petit, un bon nombre se sont produites au point qu'a bout de 3-4 ans d'exploitation, on parlait déjà de remplacer  l'appli. Et pourtant, elle n'avait pas un défaut de conception aussi gros au niveau de son MCD (modèle conceptuel de données) :o


---------------
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°2330564
matheo265
Posté le 19-03-2019 à 17:33:49  profilanswer
 

Le problème c'est que le développement n'est qu'une partie de mon métier, et les dossiers de ce genre sont trop irréguliers pour que je puisse acquérir une réelle compétence sur le sujet...
Alors je suis bien conscient que pour des pros comme vous ça peut sembler lamentable une telle conception... ;)
 
Quoi qu'il en soit j'essaye déjà de comprendre et d'exploiter le partitionnement. Ensuite sans aller vers du NoSql que je ne connais pas, je vais essayer de remodéliser ma base en suivant vos différents conseils.
 
Merci encore :)

n°2330574
rufo
Pas me confondre avec Lycos!
Posté le 19-03-2019 à 20:01:47  profilanswer
 

Je ne te jette pas la pierre. Quand c'est pas son métier..ben c'est pas son métier et donc on ne peut pas faire comme des "pros", sachant que dans un certain nb de cas, même les "pros" peuvent aussi faire de la merde :/
 
C'est pas pour rien qu'un bon développeur, c'est bac+5 et quelques années d'expé ;)
Si votre appli est stratégique, ça peut valoir le coup (financier) d'externaliser le dév et la maintenance (mais garder l'exploitation) après d'entreprises spécialisées dans le domaine.


---------------
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°2330583
matheo265
Posté le 20-03-2019 à 07:55:20  profilanswer
 

Ouais mais tu imagines bien que quand les entreprises peuvent gratter des sous... ils évitent d'externaliser. Ça risque de rogner leurs marges les pauvres petits ^^
 

n°2330584
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 09:21:42  profilanswer
 

Sauf que c'est un mauvais calcul sur le moyen terme.
 
Expérience vécue : l'appli de GMOA mal codée. A la base, une maquette a été faite. Pour gratter des sous, le choix a été fait de partir de la maquette faite par un non spécialiste des BD (comme toi, c'était pas son métier). Le choix a aussi été fait de prendre l'entreprise de dév la moins chère mais qui n'était pas spécialiste des technos utilisées.
Au final, les quelques sous grattés (qq dizaines de K€) se sont transformés en un doublement du coût de dév (et du délai de livraison), une insatisfaction des utilisateurs, des frais de maintenance bien supérieurs à la moyenne et une prise de décision au bout de 3-4 d'exploitation de remplacer complètement l'appli et de prendre une entreprise plus chère mais spécialisée dans le domaine. En prenant cette décision dès le départ, des sous auraient réellement été économisés. Pourtant, les décideurs ont été maintes fois alertés et conseillés par des pros du dév et des BD :o
 
J'utilise souvent cette comparaison pour bien faire comprendre l'importance qu'une BD soit bien modélisée : pour une appli de gestion, une BD, c'est l'équivalent des fondations d'une maison. Si elles sont fragiles ou présentent des malfaçons, tôt ou tard, la maison va s'écrouler...


---------------
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°2330585
matheo265
Posté le 20-03-2019 à 09:25:31  profilanswer
 

Je suis à 200% d'accord avec ça. Mais quand tu es un petit pion dans l'immense échiquier des dirigeants, tu ne peux pas toujours leur faire entendre raison. Car à côté de ça : le commercial a vendu, il a eu sa com, et les merdes que le ST doit se coltiner ensuite c'est pas son problème.

n°2330587
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 09:41:25  profilanswer
 

Oui, c'est malheureusement souvent le cas :(  
 
Par contre, ce que tu peux faire, c'est un document qui synthétise tout ce qui s'est dit sur ce topic. Tu présentes les différents solutions à apporter pour améliorer le temps de réponse en partant de la plus simple (et donc moins coûteuse) à la plus complexe (le NoSQL je pense), avec leurs avantages/inconvénients, une estimation de la charge de travail (et donc du coût, réalisation en interne) et pour combien de temps la solution va fonctionner (i.e. donc le temps après lequel il faudra trouver une nouvelle solution ou changer d'outil). Et tu diffuses à ta hiérarchie.
Au moins, comme ça, y'a une trace que les problèmes avaient été anticipés et que c'est juste le management qui a été défaillant. Le jour où les problèmes identifiés arriveront (et ça ne manquera pas d'arriver), ça évitera que ça retombe sur les techs :o


---------------
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°2330588
matheo265
Posté le 20-03-2019 à 10:27:07  profilanswer
 

T'inquiète pas, je ne me laisse pas faire non plus ;) Mais il est sûr que j'adorerai me spécialiser...
 
Pour en revenir aux histoires de partitionnement, j'ai donc tenté, sur une copie de la base, de partitionner comme suit :

Code :
  1. alter table machinevalues partition by hash(month(mav_statedate)) partitions 12;


Résultat des courses :

  • Sans partitionnement : 329 292 résultats en 66 secondes
  • Avec partitionnement : 329 292 résultats en... 4 secondes !!!!!!

C'est extra merci !!! :)
 
Mais je reste bien conscient qu'il faut que je revois mon schéma comme vous me l'avez conseillé tous les deux. Répartir mes colonnes dans plusieurs tables, essayer de mutualiser les données identiques, etc. Dès que j'aurais un moment creux je vais m’atteler à cette lourde tâche. Et pour les prochains dossiers je serais plus vigilant ! :)

n°2330589
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 10:39:14  profilanswer
 

C'est bien que tu gardes en tête que le partitionnement est très probablement qu'une solution temporaire. Je parle en "probabilité" car sans connaître précisément le fonctionnement de l'appli et qu'il est toujours difficile de savoir comment Mysql va se comporter, c'est impossible d'avoir des certitudes sur les perfs.
 
En solution "gratuite", tu peux aussi faire des tests sur le tuning du fichier my.ini en augmentant les variables de conf qu'on t'a indiquées.
 
Et pour les prochaines fois, n'hésites pas à faire valider sur ce forum ton MCD. ;)


---------------
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°2330590
matheo265
Posté le 20-03-2019 à 10:41:12  profilanswer
 

J'ai déjà augmenté les variables que vous m'avez indiqué et ça n'avait hélas rien changé :(
 
Ensuite je me vois mal venir sur le forum pour faire valider mon MCD... Ça fait un peu celui qui cherche à ce qu'on lui mâche le travail...

n°2330591
mechkurt
Posté le 20-03-2019 à 10:43:41  profilanswer
 

Tes données ne sont conserver que sur une année et ensuite vous purger la base de donnée ?
Car là comme j'entends ton partitionnement, tu as 12 fichiers au lieu d'un seul pour ta base de donnée mais dans 7 mois, tu vas recommencer à écrire dans ton fichier le plus vieux, qui vas lui aussi se mettre à grossir...
Dans 6 ans tu auras 12 fichier de 4.7Go et les même problèmes de performance...
 
Je penses que tu devrais ajouter l'année à ton hash de partitionnement. ^^


---------------
D3
n°2330592
matheo265
Posté le 20-03-2019 à 10:47:48  profilanswer
 

Oui j'ai pensé à ça également... Mais ça me laisse du temps pour redessiner le modèle de ma base et peut-être espérer l'améliorer sans trop casser la pyramide...  
 
Par "ajouter l'année" tu veux dire quelque chose comme ça ?

Code :
  1. alter table machinevalues partition by hash(year(mav_statedate)+month(mav_statedate)) partitions ??;


Mais dans ce cas je ne sais pas quoi ajouter après "partitions"...

n°2330595
mechkurt
Posté le 20-03-2019 à 11:15:20  profilanswer
 

Aucune idée, je n'ai jamais partitionné de table...
 
Cependant si on en croit la doc, le nb de partition n'est pas forcement corellé avec les hash, cf. l'exemple donnée :

Code :
  1. CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
  2.     ENGINE=INNODB
  3.     PARTITION BY HASH( MONTH(tr_date) )
  4.     PARTITIONS 6;


https://dev.mysql.com/doc/refman/5. [...] rview.html


---------------
D3
n°2330597
matheo265
Posté le 20-03-2019 à 12:00:40  profilanswer
 

Alors de ce que j'ai pu constater, c'est que si je pars sur ça :

Code :
  1. alter table machinevalues partition by hash(year(mav_statedate)+month(mav_statedate));


Ca va me créer une seule partition. Ensuite je peux ajouter des partitions supplémentaires avec la commande suivante :

Code :
  1. alter table machinevalues add partition (partition p1);


Et ça va répartir les données entre les anciennes et la nouvelle.
Je vais donc partir sur 10 pour commencer, et j'en créerai de temps en temps en attendant de refondre mon schéma de base.

n°2330598
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 12:05:44  profilanswer
 

matheo265 a écrit :

J'ai déjà augmenté les variables que vous m'avez indiqué et ça n'avait hélas rien changé :(
 
Ensuite je me vois mal venir sur le forum pour faire valider mon MCD... Ça fait un peu celui qui cherche à ce qu'on lui mâche le travail...


Y'en a qui viennent ici avec beaucoup moins de scrupules :whistle:  
Si tu viens avec un MCD déjà bien avancé voir finalisé, que tu expliques à quels besoins il répond et que tu demandes s'il est bon où s'il y a mieux, y'a pas de problème, tu trouveras de l'aide. Au besoin, si des données sont confidentielles, tu peux transposer ton modèle sur un exemple différent. Je le fais régulièrement. J'énonce un pb de même nature que celui que j'ai réellement puis je transpose la solution proposée. ;)
 
Edit : sur le partitionnement, si je connais le principe, je ne l'ai encore jamais mis en oeuvre, donc aucune expé. Je vais pas pouvoir t'aider sur ce coup-là.


Message édité par rufo le 20-03-2019 à 12:06:37

---------------
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°2330602
mechkurt
Posté le 20-03-2019 à 13:39:17  profilanswer
 

Comme le dit Rufo, c'est rare que personne ici n'aide quelqu'un qui as un problème, surtout si il prouve d'une manière ou d'une autre qu'il à chercher...
 
Le partitionnement à l'air d'être complexe, mais sans doute à même de résoudre ton problème, dans la mesure ou tu as le contrôle sur la façon dont tes données vont être "vue".
 
Par exemple dans cet autre exemple issu de la doc :

Code :
  1. CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
  2.     PARTITION BY RANGE( YEAR(purchased) ) (
  3.         PARTITION p0 VALUES LESS THAN (1990),
  4.         PARTITION p1 VALUES LESS THAN (1995),
  5.         PARTITION p2 VALUES LESS THAN (2000),
  6.         PARTITION p3 VALUES LESS THAN (2005)
  7.     );


https://dev.mysql.com/doc/refman/5. [...] ement.html
On semble partis sur la l'archivage de donnée ancienne, sans doute que les perfs s'écroulent si une requête filtre et/ou tri des données à cheval sur 2 partitions.


---------------
D3
n°2330603
matheo265
Posté le 20-03-2019 à 13:59:08  profilanswer
 

Ouais... En général les gens aident oui. Mais pour avoir eu l'expérience sur un autre forum (que j'ai quitté) ce que je n'aime pas c'est les jugements dévalorisants des personnes d'expérience qui se croient supérieurs (ce n'est pas votre cas je vous rassure ^^). Dans mon imbécilité je partais du principe qu'une personne qui a besoin d'aide c'est qu'elle est dans la m**** et qu'elle n'a pas besoin d'y être enfoncée encore plus ;) Ensuite c'est sûr que certains ne font parfois même pas l'effort de chercher cédant à la facilité...  
 
Pour le partitionnement je commence à comprendre le principe et il y a plein de configurations possibles ! C'est très intéressant... Partitionner par ligne (ce que je fais) ou par colonne (pour répartir les champs sur plusieurs fichiers), partitionner les vieux enregistrement, ou sur un critère commun, etc.  
 
Au moins je pourrais répondre à l'urgence en attendant la suite.

n°2330604
mechkurt
Posté le 20-03-2019 à 14:20:47  profilanswer
 

Oui l'idée c'est de faire la partitionnement le plus intelligemment possible car tu as une contrainte d'écriture les moulinage des fichiers envoyé via sftp mais aussi les contraintes de lecture qu t'imposes les "recherches" par le client.
A mon avis en fonction de l'écran de visualisation / recherche du client, tu as une grande possibilité d'optimisation de ton application, as t'il accès à toutes les colonnes , sur toutes les valeurs ou seulement sur des plages, par  jours / semaine / mois, etc.
Si il s'agit de générer des rapports "statiques", tu as par exemple intérêt à les générer via un cron à des moments ou tu écrits pas (ou peu) de nouvelle données.


---------------
D3
n°2330605
matheo265
Posté le 20-03-2019 à 14:28:17  profilanswer
 

Non, il s'agit de rapport "dynamiques". En fait chaque utilisateur peut générer des rapports sur ses machines et il choisit les données qu'il souhaite y faire apparaître : le nom, la date/heure (à minima) ainsi que d'autres données comme le niveau de carburant, la température moteur, etc. L'idée du cron me semble donc compliquée...  
 
Le partitionnement a également ses limites car il n'est pas compatible avec les clés étrangères : https://dev.mysql.com/doc/refman/5. [...] tions.html.
 
Ce qui signifie que je dois faire quelque chose de sale : à savoir supprimer la contrainte de clé étrangère...  :sarcastic:

n°2330607
mechkurt
Posté le 20-03-2019 à 14:58:37  profilanswer
 

Peut être un partitionnement par "park de machine", surtout si les machines ne partage pas les mêmes "propriétés" et/ou les utilisateurs ne s’occupent que de "leur machine" ?


---------------
D3
n°2330608
matheo265
Posté le 20-03-2019 à 15:01:51  profilanswer
 

Les propriétés sont identiques d'une machine à une autre. C'est une société qui vend la même machine utilisée sur divers chantiers.  
 
Mais par mois et par année c'est un excellent point de départ. Et dès que je peux je redessine et je posterai le schéma sur le forum pour avoir des avis.

n°2330609
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 15:27:21  profilanswer
 

matheo265 a écrit :

Non, il s'agit de rapport "dynamiques". En fait chaque utilisateur peut générer des rapports sur ses machines et il choisit les données qu'il souhaite y faire apparaître : le nom, la date/heure (à minima) ainsi que d'autres données comme le niveau de carburant, la température moteur, etc. L'idée du cron me semble donc compliquée...  
 
Le partitionnement a également ses limites car il n'est pas compatible avec les clés étrangères : https://dev.mysql.com/doc/refman/5. [...] tions.html.
 
Ce qui signifie que je dois faire quelque chose de sale : à savoir supprimer la contrainte de clé étrangère...  :sarcastic:


La doc semble dire que le partitionnement n'est pas compatible avec les clés étrangères dans le cas où le moteur est InnoDB. Tu peux très bien utiliser MyIsam ou MariaDB.


---------------
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°2330610
matheo265
Posté le 20-03-2019 à 15:44:04  profilanswer
 

Il me semble que sur MyIsam on ne peut pas gérer de transactions ?
MariaDB je ne connais que sur mon NAS à la maison ^^

n°2330623
rufo
Pas me confondre avec Lycos!
Posté le 20-03-2019 à 20:22:51  profilanswer
 

Effectivement, MyIsam ne gère pas les transactions. Ton appli en a besoin ?
Si tu connais MariaDB par ton NAS, j'en déduis que tu as un Synology :D Comme moi ;)


---------------
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°2330631
matheo265
Posté le 21-03-2019 à 08:02:27  profilanswer
 

Oui, j'utilise les transactions car mon script d'insertion des données machines concerne plusieurs tables.
Pour MariaDB tu as vu juste ^^ Mais je débute et je ne sais pas encore ce que ça vaut.

mood
Publicité
Posté le   profilanswer
 

 Page :   1  2
Page Précédente

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

  [MySQL] temps fetching trop important

 

Sujets relatifs
[MySQL] Plusieurs tables ou une seule grosse dans ce cas?[Python] Tableaux qui s'actualisent en même temps
Changement de logo selon le tempsOut of memory - sql Oracle/php
Projet de stage php/mysql[MySQL] Supprimer toutes les contraintes d'une table
securiser l'acces à la base mysqlMySQL Conflit Xampp / Wamp
[MySQL] Combiner un select classique et un count()[MySQL] Ajout de multiples enregistrements avec trigger
Plus de sujets relatifs à : [MySQL] temps fetching trop important


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR