Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 19 sur 19
  1. #1
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut Gestion (très) grosse table !

    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 !

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro Alain
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    5 618
    Détails du profil
    Informations personnelles :
    Nom : Homme Alain
    Âge : 53
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 5 618
    Points : 12 711
    Points
    12 711

    Par défaut

    Et cette base de données, elle est traitée par quel SGBD ? Et sur quel type de matériel ?
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Membre expérimenté

    Inscrit en
    mai 2005
    Messages
    414
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 414
    Points : 592
    Points
    592

    Par défaut

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

  4. #4
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    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 ?

  5. #5
    Membre expérimenté

    Inscrit en
    mai 2005
    Messages
    414
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 414
    Points : 592
    Points
    592

    Par défaut

    taille en Mo? Go ? To ? de la table

  6. #6
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    Citation Envoyé par gregory.broissard Voir le message
    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

  7. #7
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    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

  8. #8
    Membre expérimenté

    Inscrit en
    mai 2005
    Messages
    414
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 414
    Points : 592
    Points
    592

    Par défaut

    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

  9. #9
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    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...

  10. #10
    Membre émérite Avatar de Jester
    Inscrit en
    septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : septembre 2003
    Messages : 813
    Points : 863
    Points
    863

    Par défaut

    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?

  11. #11
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    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...

  12. #12
    Membre émérite Avatar de Jester
    Inscrit en
    septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : septembre 2003
    Messages : 813
    Points : 863
    Points
    863

    Par défaut

    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?

  13. #13
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    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

  14. #14
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

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

  15. #15
    Membre émérite Avatar de Jester
    Inscrit en
    septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : septembre 2003
    Messages : 813
    Points : 863
    Points
    863

    Par défaut

    Citation Envoyé par shadowbob Voir le message
    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é.

  16. #16
    Membre émérite Avatar de Jester
    Inscrit en
    septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : septembre 2003
    Messages : 813
    Points : 863
    Points
    863

    Par défaut

    Citation Envoyé par shadowbob Voir le message
    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%.

  17. #17
    Membre expérimenté

    Inscrit en
    mai 2005
    Messages
    414
    Détails du profil
    Informations forums :
    Inscription : mai 2005
    Messages : 414
    Points : 592
    Points
    592

    Par défaut

    Citation Envoyé par shadowbob Voir le message
    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

  18. #18
    Membre du Club
    Inscrit en
    juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : juin 2004
    Messages : 116
    Points : 47
    Points
    47

    Par défaut

    Citation Envoyé par gregory.broissard Voir le message
    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 :-)

  19. #19
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 817
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 817
    Points : 23 074
    Points
    23 074

    Par défaut

    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •