IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Optimisations SGBD Discussion :

Gestion (très) grosse table !


Sujet :

Optimisations SGBD

  1. #1
    Membre régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 763
    Points
    30 763
    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 éclairé

    Profil pro
    Inscrit en
    Mai 2005
    Messages
    414
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2005
    Messages : 414
    Points : 671
    Points
    671
    Par défaut
    et pour quel volume de données? le nombre d'enregistrements n'est pas toujours significatif

  4. #4
    Membre régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 éclairé

    Profil pro
    Inscrit en
    Mai 2005
    Messages
    414
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2005
    Messages : 414
    Points : 671
    Points
    671
    Par défaut
    taille en Mo? Go ? To ? de la table

  6. #6
    Membre régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 éclairé

    Profil pro
    Inscrit en
    Mai 2005
    Messages
    414
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2005
    Messages : 414
    Points : 671
    Points
    671
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 éprouvé Avatar de Jester
    Inscrit en
    Septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : Septembre 2003
    Messages : 813
    Points : 1 058
    Points
    1 058
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 éprouvé Avatar de Jester
    Inscrit en
    Septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : Septembre 2003
    Messages : 813
    Points : 1 058
    Points
    1 058
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    Par défaut
    Au faite vous savez pas pour le champ date (7eme réponse) ?

  15. #15
    Membre éprouvé Avatar de Jester
    Inscrit en
    Septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : Septembre 2003
    Messages : 813
    Points : 1 058
    Points
    1 058
    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 éprouvé Avatar de Jester
    Inscrit en
    Septembre 2003
    Messages
    813
    Détails du profil
    Informations forums :
    Inscription : Septembre 2003
    Messages : 813
    Points : 1 058
    Points
    1 058
    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 éclairé

    Profil pro
    Inscrit en
    Mai 2005
    Messages
    414
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2005
    Messages : 414
    Points : 671
    Points
    671
    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 régulier
    Inscrit en
    Juin 2004
    Messages
    116
    Détails du profil
    Informations forums :
    Inscription : Juin 2004
    Messages : 116
    Points : 82
    Points
    82
    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
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 792
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    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 : 16 792
    Points : 34 013
    Points
    34 013
    Billets dans le blog
    14
    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 Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « 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 !

Discussions similaires

  1. purge très grosse table (+1téra)
    Par serge0934 dans le forum MS SQL Server
    Réponses: 12
    Dernier message: 23/09/2011, 11h33
  2. Performances sur très grosse table
    Par CinePhil dans le forum Optimisations
    Réponses: 2
    Dernier message: 17/09/2008, 18h52
  3. [SSIS][2k5] jointure entre très grosse table
    Par RicardMan dans le forum SSIS
    Réponses: 1
    Dernier message: 18/04/2008, 17h54
  4. Trier de trés grosses tables
    Par funckfot dans le forum Algorithmes et structures de données
    Réponses: 12
    Dernier message: 07/06/2007, 18h30
  5. Gestion de très grosse table
    Par Arni23 dans le forum Requêtes
    Réponses: 11
    Dernier message: 04/06/2007, 21h41

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo