Gestion (très) grosse table !

[shadowbob]

Bonjour,

Nous avons une une très grosse bdd, ou s'enregistrent quelques centaines de données à la seconde.
Pour éviter la surcharge nous faisons des agregats par jours et par mois et stockons le résultat dans 2 tables et supprimons tous les jours le contenu de la 1ere table.
Mais malgré cela, nous sommes surchargés de données, au bout d'un an d'existence de cette base, il y a plus de 50millions de données agregatés par jours et plus de 20millions dans celui par mois...
Les récupération des stats dépassait les 300 secondes pour une simple requête, et bien entendue toutes les colonnes sont indexés.
J'ai donc fait une nouvelle table qui stock le résultat des requêtes dés qu'une est utilisée, et quand ca a de nouveau besoin de celle-ci, c'est bien plus rapide.
Mais même avec ca, le serveur est plombé...
Donc la mes compétences commencent à atteindre leur limites, et je me remets à vous pour voir si vous aviez une idée comment supporter tout ca pour les 2 ans à venir.
J'offre un cadeau à la personne qui trouve une solution

Merci de votre aide !

[al1_24]

Et cette base de données, elle est traitée par quel SGBD ? Et sur quel type de matériel ?

[gregory.broissard]

et pour quel volume de données? le nombre d'enregistrements n'est pas toujours significatif

[shadowbob]

st un site Internet, et on a utilisé MySQL, peut être qu'il aurai mieux fallu prendre un autre, mais ils ne voulait pas Oracle.
il y a une vingtaines de frontaux et 2-3 serveurs SQL.

@gregory.broissard que veut tu dire par volume ?

[gregory.broissard]

taille en Mo? Go ? To ? de la table

[shadowbob]

Envoyé par gregory.broissard

taille en Mo? Go ? To ? de la table

Espace utilisé Type Espace
Données 5 160,0 Mio
Index 19 921,0 Mio
Total 25 081,0 Mio

[shadowbob]

J'ai aussi une question, le format date est "YYYY-MM-DD HH:MM:SS", sachant que quand c'est agregaté par jours le HH:MM:SS sera toujours à 00:00:00, donc est-ce pertinent de le supprimer ?
Il faut que je soit sur que ca aurai une incidence de perfs pour le modifier, car modifier la structure d'une table de 50M d'enregistrements ca prendra 1j

[gregory.broissard]

5Go de données pour 19Go d'index c'est enorme !

Donc deja la mise à jour de tes index quand tu fais des insert ca doit vachement plomber la base.

Faudrait nettoyer un peu et regarder les index vraiment utiles.

Apres : partitionnement des tables

[shadowbob]

Mouai...
Pour les insert c super rapide...
Les indexes sont tous utiles... il y a qu'une seul colonne non indexé et c'est ce chiffre qui est important, tous le reste sers à trouver ce chiffre, donc indispensable.
Au niveau de l'optimisation je pense que c'est au mieux que l'on puisse faire, même si l'on trouves une solution pour l'alléger un peu, c'est pas gagné pour durer encore 1an comme ca.
Ce que je cherchais ici c'est plutôt une solution structurelle de la base, le découper en plusieurs etc... mais la découpe rendrai hard core les requêtes de récupération...

[Jester]

InnoDB ou MyISAM?

Combien de RAM et combien de buffer_cache si le moteur est InnoDB?

Mysql a un système de cache de requête intégrée, peut-être pas efficace si c'est mis à jour en permanence.

300 seconde, s'il n'y a pas de jointures ça me semble plus lent qu'une lecture séquentielle. Je pense que la requête n'est pas archi simple non?

[shadowbob]

InnoDB, pour le reste je ne sais pas je ne suis m'occupe pas des serveurs moi
Ben même pas de jointures, c'est un select tout bête, mais dés que ca dépasse un certain millions de lignes, il n'aime pas trop, et il rame, quand on va chercher les données d'une journée ca va il y a à peine 400k lignes donc ca prends 1 à 2 seconde, mais au bout de 10 jours paf le monsieur aime pas...

[Jester]

La taille du cache a son importance car si vous en avez 8Go tout tient en mémoire. On va dire que non (car de toute façon avec les index ...).

Est-ce que la date est le premier élément de la clé primaire?

Est-ce que la requête renvoie une grande quantité de données? Si oui ou s'il y a un sort sur une grande volumétrie, mysql va créer une table temporaire sur le disque dur donc c'est une catastrophe. Si tu pouvais donner la requête même modifiée mais avec la même structure et le plan d'exécution ce serait plus simple.

Le moteur archive a un taux de compression intéressant mais pas d'index, mais vous pourriez créer un clone de votre table d'origine en les synchronisant avec un trigger ou un job ETL. Ca réduirait votre table à environ 500Mo par ans, ce qui se lis plus vite. Votre partie stats peut être déportée?

[shadowbob]

Je vais demander combien de cache on a.
La date est le 7eme élément sur 8, et le dernier de la la clé primaire, et possède également un Index propre (c'est le cas pour les 6 autres).
La requête subit un GROUP BY, qui la réduit en moins de 1000 lignes (c'est ces lignes qui sont stockés ensuite).
La compression... je vais en parler à mon collègue pour voir ce que l'on peut faire.
Mais ca me donne une autre idée, faire une autre table qui supprime certains éléments comme ca l'agregate insérera moins de données, utile pour 90% des requêtes, mes requêtes évalueront ensuite quel table est le plus adapté à ses besoins... Je vais étudier ca également.
Merci en tout cas pour votre aide, surtout que c'est difficile sans avoir les éléments sous les yeux

[shadowbob]

Au faite vous savez pas pour le champ date (7eme réponse) ?

[Jester]

Envoyé par shadowbob

La date est le 7eme élément sur 8, et le dernier de la la clé primaire, et possède également un Index propre (c'est le cas pour les 6 autres).

Ok, la raison de ma question est que innodb stocke les données avec la clé primaire. Du coup, au lieu de faire une lecture quasi séquentielle du sous-ensemble qui vous intéresse, vous aller utiliser l'autre index qui va référencer des lignes non adjacentes et donc vous allez lire une grande partie de la table en faisant des accès aléatoires, ce qui est beaucoup plus lents.

Enfin c'est une possibilité.

[Jester]

Envoyé par shadowbob

Au faite vous savez pas pour le champ date (7eme réponse) ?

Ca ne change rien il me semble, en interne je ne crois pas que ce soit stocké si vous prenez un champs date. Mais là je ne suis pas sur à 100%.

[gregory.broissard]

Envoyé par shadowbob

Ce que je cherchais ici c'est plutôt une solution structurelle de la base, le découper en plusieurs etc... mais la découpe rendrai hard core les requêtes de récupération...

voir partitionnement de tables

[shadowbob]

Envoyé par gregory.broissard

voir partitionnement de tables

Ahhhh voila une méga bonne piste !
Ben j'étudie ca de très près et je revient ici pour te dire si ca change quelque chose.
Merci pour ton aide :-)

[CinePhil]

D'après la doc MySQL, le type DATETIME occupe 8 octets, le type TIMESTAMP 4 octets et le type DATE 3 octets.
Si vous n'utilisez que la partie date, changer pour ce type fera gagner de la place.

Pour ce qui est de la clé primaire qui semble composée de plusieurs colonnes, voyez une optimisation possible du côté de l'index couvrant. En clair, l'ordre des colonnes dans la clé primaire peut avoir une importance. Il faut mettre la colonne la plus discriminante (celle qui a le plus de valeurs différentes) en premier.

Vous pouvez aussi lire cet autre article de SQLPro sur l'optimisation des des index dans les grosses bases de données.