Bonjour tout le monde  
 
Pour commencer, je tiens à préciser que je suis développeur "touche à tout", et donc spécialisé dans rien Je pense pas être unique, c'est comme ça dans certaines boîtes...
 
A ce titre j'ai travaillé il y a 1 an sur un projet de traitement de données remontées par des machines. Ces remontées se font toutes les secondes multiplié par le nombre de machines (une trentaine à ce jour). Tout ça est stocké dans une table ayant la structure suivante :
 

 
A part les 3 premières, j'ai modifié le nom des colonnes pour plus de confidentialité.  
 
Les spécialistes vont sûrement sauter au plafond tellement ma table doit être horrible...   Elle contient aujourd'hui plus de 66 millions d'enregistrement... Et bien sûr devinez quoi : les temps de lecture sont affreusement lents voir impossibles malgré les 10 Go de mémoire sur le serveur...
 
J'imagine que MySQL (dans le cas d'une bdd bien concue) est capable de gérer des tables de plusieurs milions d'enregistrements, non ?
 
Est-ce que quelq'un aurait des conseils pour optimiser tout ça ? J'ai déjà partitionné ma table pour tenter d'aémliorer les perf, ce qui a marché quelques semaines seulement...
 
Merci pour vos retours bienveillants    

J'y connais pas grand chose en ressources nécessaires pour faire fonctionner ce genre de bases, mais imo 10go sur un serveur c'est pas grand chose, du tout.

Sinon, je connais pas du tout l'application de ta table, mais remonter des choses chaque seconde, est-ce vraiment essentiel pour ton usage ?

C'est des machines susceptibles de remonter des défauts, donc des alertes doivent être générées à chaque nouveau défaut.
 
Ensuite je suis d'accord avec toi : toutes les secondes c'est beaucoup ! J'aurai préféré toutes les 15 secondes si possible mais le client final ne veut pas faire de compromis sur ça...

déjà la table avec 120 colonnes, ça pique
 
je ferai 2 tables  
une machinedata (id, date, macid) et une machinedatavalue (id, machinedataid, txtValue, intValue)  
 
mais sinon quelques possiilités :  

• agglomère les data : si il y a peu de changement d'état tu stocke value/startedAt, endedAt et tu ne cré une ligne que si ça change. Attention, les requetes d'exploitation peuvent être plus complexes
• utilise les bons index par rapport à ton exploitation
• partitionne ta table par date / machine

J'imagine bien que ça pique    
 
Pour le partitionnement j'ai déjà partitionné selon l'expression suivante :  
 

 
Pour l'agglomération c'est le code qu'il va falloir retoucher au niveau de l'applicatif qui me fait peur, car effectivement (et j'en m'en veux de pas l'avoir vu au départ) il y a des colonnes dont la valeur ne change pas souvent...

Sinon t'as essayé de surveiller les ressources matérielles du serveur au moins ?

66 millions de lignes pour une table, c'est pas énorme pour une table MySQL
J'ai moitié moins sur un serveur avec que 2 Go de ram et ça passe tranquille. Par contre, ma table n'a pas 120 colonnes
 
Comme on n'a pas le nom des colonnes, ça va être difficile d'être plus précis que ce que flo a proposé. Un SDBG, ça se tune : as-tu modifié le fichier de conf de mysql, notamment pour augmenter la taille des buffers des tables temporaires, le nb de tables ouvertes et le buffer de lecture ? Ca, ça devrait déjà aider.
Ensuite, il faudrait probablement restructurer la table en plusieurs tables pour minimiser les données identiques et mieux indexer certains champs. Là, ça va dépendre des requêtes SELECT qui sont faites. Il faut que tu utilises le EXPLAIN pour voir le goulet d'étranglement. Peut-être qu'au lieu d'indexer plusieurs colonnes, pour certains index, tu pourrais faire un seul index qui regroupe 2 ou 3 colonnes.
 
Après, une autre solution pourrait être de passer sur une BD NoSQL. Mais franchement, sans le nom des colonnes, on voit pas ce que ça fait, donc perso, je suis incapable de te proposer une autre structure.

Une autre piste serait de savoir ce que tu SELECT ?
Est ce que tu compares des machines entre elles, des variations sur un champ pour une même machine, etc...
Dans tous les cas sortir l'id machine et la date dans un table annexe et avoir une table avec l'identifiant unique précédent etun id champ et la valeur sera sans doute plus efficace (je crois que c'est ce que te conseillait flo850)...
Tu peux même optimiser encore plus avec un data_val_int, un data_val_float et un data_val_string.
Magento le fait pour les données des produits, c'est que ça ne doit pas être si mal ! ^^

Oui, enfin Magento ne brille pas par ses perfs Même s'ils ont fait des améliorations, le modèle de BD et ses perfs bof-bof restent l'un des principaux reproches qui lui ont été faits.
Mais c'est vrai qu'une table sur le modèle attribut/valeur pourrait être pertinent.
Dans mon soft de gestion de conf Icare (cf ma signature), c'est ce que j'avais fait : une table "composant" avec juste un libellé et l'ensemble de ses valeurs était dans une table "attribut" qui avait un ID, un libellé d'attribut et une valeur type string. Entre les 2, une table composant-attribut qui liait les ID d'une composant avec les ID des attributs qui le composaient.
C'était très efficace car 70 à 80% des attributs étaient commun entre mes configurations de composants. Du coup, au lieu de dupliquer des valeurs identiques, je faisais juste une entrée dans la table composant-attribut en y mettant 2 entiers.
Pour 250 conf ayant chacune entre 100 à 150 attributs, le logiciel Advitium avait une BD d'environ 100 Mo. Avec ma technique, mon logiciel faisait 5 Mo

On va commencer par les basiques :
quand tu parles de "temps de lecture" c'est en envoyant quoi comme SELECT ?
Et est ce que les colonnes du WHERE sont indexées ? J'imagine que t'as pas besoin des 60 millions de lignes d'un coup.
Tu fais un SELECT * ou un SELECT sur des colonnes spécifiques ? J'imagine que t'as pas besoin des 150 colonnes d'un coup.
Y a pas moyen de reporter dans une table d'historique les trucs qui ne servent plus (vieilles données, machines disparues du parc...) ?
Éventuellement le SELECT lui même peut être optimisé en rajoutant des critères qui vont aider le SGBDR à cibler les lignes à sortir plus facilement.

Faire une table historique ou partitionner sa table sur la date, ça va revenir à peu près au même, je pense, non ? Or, il a dit qu'il avait déjà partitionné...

 
J'ai effectivement fait différentes optimisation en fonction de tuto trouvés sur divers sites. Mais rien n'y a fait...  
 
Je me doute bien que ma structure est foireuse... Mais avec plus de 66 millions de lignes, ça va être compliqué d'éclater ma table et de retravailler les enregistrements... Dans les colonnes il y a de tout... ça peut être un niveau de carburant, une pression de pneus, mais aussi des codes défaut, des messages, etc. Il y a beaucoup de numérique mais pas que.  
 

 
il n'y a pas de comparaison de machines entre elle, le logiciel renvoie uniquement les dernières valeurs à un moment T, et permet également au client de générer des rapports et des graphiques. C'est cette dernière partie qui est laborieuse car il suffit que le client mette 20 colonnes sur 2 mois dans son état et les temps de traitement son monstrueusement longs...
 

 
Je ne connais pas du tout ces types, je vais me renseigner, merci ^^
 

 
Je ne fais jamais de SELECT *, ça c'est sûr ^^ Par contre comme dit plus haut, il suffit que je fasse un SELECT avec 20 colonnes sur une plage de date de 2 mois et là j'ai droit à un temps d'attente dépassé... Côté indexation j'ai indexé la date/heure car c'est surtout là-dessus que le client va appliquer des filtres. L'ID de la machine également.
 
 

Ok, donc, c'est un système de log.
Pour le tuning, t'as utilisé mysqltuner pour avoir des conseils ? https://www.geeek.org/mysqltuner-ou [...] mysql-155/
 
Un changement de structure peut se faire malgré les 66 millions de lignes. T'auras juste à faire un script qui se chargera de prendre les données du schéma initial pour les transformer et les mettre dans la nouvelle BD. Rien de bien compliqué.
 
Vu que c'est un système de Log, pour la scalabilité, passer sur une BD NoSQL, ça serait pas déconnant.

Sur les divers sites que j'ai consulté, j'ai vu que ça parlait de mysqltuner effectivement. Je vais y jeter un oeil, peut-être 2 ^^
 
Mon problème pour répartir les 66 millions d'enregistrement, c'est que je vois pas comment c'est possible sachant que déjà, si je fais un select qui retourne 5000 enregistrements, j'ai une requête qui échoue...

Heu, comment tu peux avoir un échec en retournant juste 5000 records
Soit t'as vraiment très mal indexé ta table, soit t'as un pb de conf de Mysql ou un pb hardware (barrette de ram défectueuse ou HDD qui a des pbs de secteurs)

Graylog/ElasticSearch/MongoDB comme tout le monde, quoi...
A+,

Merci pour vos retours, je ne suis pas encore initié aux bdd NoSQL. Il faut que je prenne le temps de regarder car j'en entend de plus en plus parler.
 
Après réunion avec le client, il a enfin entendu raison sur la nécessité de ne pas remonter toutes les colonnes chaque seconde. Nous allons donc travailler sur un fractionnement de cette table avec une table comprenant les propriétés qui changent toutes les 15 secondes, et un autre les propriétés toutes les secondes.
 
J'en profiterai pour optimiser les lignes pour ne pas les réinsérer si elles ont les mêmes valeurs qu'à la remontée précédente.
 
Concernant ton message rufo, j'ai optimisé autant que les infos que j'ai pu trouver sur le net. Il y a 10 Go de mémoire sur la machine, selon les tutos que j'ai vu il en faudrait 15 mais là je ne pourrai pas augmenter la capacité.

Votre client est capable de financer le développement d'une solution mais a les poches vides pour quelques barrettes de RAM ?

C'est vrai que passer de 10 à 15 Go, c'est quand même pas ruineux

Si c'est sur une config AWS, ça te monte la facture mensuelle...
A+,

 
Oui enfin bon, une solution qui fonctionne mal ça coûte aussi.    
 
10go on est plus proche du laptop lambda que du serveur, même le pauvre T320 sur lequel je bricole chez moi était livré avec 32go.  
Au taf nos serveurs neufs ont 384 à 768go de ram, sans aller jusque là, quelle machine pro (si c'est pas dans le cloud ofc) est autant limitée ?

Oh purée!  
Par curiosité, tu as une idée du prix d'une telle machine?

C'est des nœuds Dell VxRail qui supportent un cluster vSphere étendu. Les montants sont complètement débiles, plusieurs dizaines de milliers d'euros par serveur, pas loin des 100000 avec les licences qui vont avec si je dis pas de bêtises.

Mais à ce niveau ça devient complètement insensé, les prix d'origine sont sur-gonflés pour te proposer de grosses remises et faire croire au client qu'ils font une affaire. Ah, et les prix plus haut sont après*** remise.

Et si t'es une administration, c'est encore plus cher
Moi, j'ai vu un PC portable vendu 350 € TTC sur CDiscount vendu 1900 € HT !

Les derniers Raspberry Pi 4 peuvent avoir jusqu'à 8Go je crois...
10Go, ça fait vraiment cheap pour une solution pro de BDD.
 
A+,

Je suis dans la territoriale.  

Je compatis Moi, ça me fait toujours bondir quand je vois des prix pareil. Même si dans le prix, t'as la maintenance et la gestion de l'obsolescence (encore que c'est pas toujours le cas) + 2-3 autres trucs, ça faut quand même une marge assez indécente Mais l'administration ne peut généralement pas acheter sur CDiscount ou Amazon...

UGAP et SIPPEREC.    
 
J'ai acheté un produit à 15000€ chez un fournisseur étranger en Europe, hors catalogues.
 
Ça a pris plus d'un an.