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 :

Comment optimiser au mieux une base de données ?


Sujet :

Optimisations SGBD

  1. #1
    Membre habitué
    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    161
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Août 2005
    Messages : 161
    Points : 193
    Points
    193
    Par défaut Comment optimiser au mieux une base de données ?
    Bonjour,

    Je souhaiterais savoir quels sont les meilleurs moyens d'optimisation d'une base de données en général (J'ai une base DB2 accédé par différents programmes Java)

    Voilà déjà ce que j'ai fait:

    1. Le modèle conceptuel respecte la 3eme forme normale et la forme normale de Boyce-Codd.
    2. Les attributs de mes tables utilisent les types de données les mieux adaptés (par exemple: utilisation de SMALLINT à la place de INT quand on peut, des varchar au lieu de char)
    3. Sur les tables qui stockeront énormément de données (plusieurs milliers de lignes jusqu'à plusieurs millions), des indexes ont été créés, et les requêtes re-écrites pour utiliser au mieux ces indexes.


    Suite à cela, j'aurais plusieurs questions:
    1. Est-il nécessaire de respecter la 4ème et la 5ème forme normale ?
    2. L'ordre des attributs dans la table peut-il jouer un rôle d'optimisation? Par exemple: la(les) clé(s) primaire(s), puis la(les) clé(s) étrangères, puis les attributs souvent accéder en lecture/mise a jour, et finalement les attributs les moins souvent accédés...
    3. A part les indexes, que peut-on faire d'autres ? partitionnement de tables ?


    Merci pour vos idées ou vos expériences .

  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
    Même les petites tables peuvent être indexées.
    Je ne connais pas les méthodes de partitionnement de DB2 : je ne peux donc me prononcer sur les gains apportés (ou non).
    Ne pas oublier non plus le rafraichissement régulier des statistiques de population des tables et des index.
    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 habitué
    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    161
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Août 2005
    Messages : 161
    Points : 193
    Points
    193
    Par défaut
    Citation Envoyé par al1_24
    [...]
    Ne pas oublier non plus le rafraichissement régulier des statistiques de population des tables et des index.
    Qu'est-ce que tu entends par-là ?
    Je ne suis pas expert en base de données, je fais juste un stage de fin d'étude (niveau DESS) sur le thème (Optimisation d'une base de données)...et j'ai donc sûrement des lacunes dans mon vocabulaire et dans certaines pratiques liés aux bases de données.

  4. #4
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonsoir MadCat34,


    Le modèle conceptuel respecte la 3eme forme normale et la forme normale de Boyce-Codd.
    Ceci est capital, aussi bien au niveau fonctionnel, qu’au niveau de la performance des applications en production, quoi qu’en disent ceux qui n’ont rien compris, je veux dire les acharnés de la dénormalisation, dont les arguments sont purement émotionnels.

    Je reprends à ce sujet une anecdote que j’ai déjà évoquée sur le forum Merise. Un traitement batch de mise à jour durait 9 heures (DB2 dans sa version 2 ou 3 ou 4, je ne sais plus...), temps de traitement jugé à juste titre trop important, puisque la fenêtre batch accordée n’était que de 1 heure 30 : pour le DBA, la coupable s’appelait Troisième forme normale et il fallait donc dénormaliser la table concernée (2 millions de lignes, autant dire pas grand-chose), car comme par hasard il y avait de la jointure et (presque) tout le monde sait bien que la jointure ça coûte la feau des pesses. Avant de passer à l’acte, le DBA me demanda quand même mon avis et je lui suggérai de n’en rien faire mais plutôt de commencer par droper les 4 index non cluster pesant de tout leur poids sur cette malheureuse table et de les recréer (avec réorganisation en prime) une fois le batch de mise à jour terminé. Temps du traitement : 5 minutes... (Je ne me souviens plus du temps de recréation des index (dont un du reste était inutile), mais on était plus qu’au large dans la fenêtre !) Innocentée, la 3e forme normale fut préservée.


    Est-il nécessaire de respecter la 4ème et la 5ème forme normale ?
    Il est hautement préférable de les respecter, mais le hic, c’est que les infractions sont très difficiles à débusquer. En gros, c’est peine perdue, même si vous êtes infiniment patient et jonglez avec les axiomes d’Armstrong étendus aux dépendances multivaluées. Au bout d’un long temps de traque aux infractions, vous finirez par craquer...


    L'ordre des attributs dans la table peut-il jouer un rôle d'optimisation?
    Vous utilisez DB2 (je vais supposer qu’il s’agit de DB2 for z/OS). Dans les Red books traitant de la performance, il était écrit par exemple, que les attributs de longueur variable devaient être placés derrière les attributs de longueur fixe. En effet, la longueur d’un attribut de longueur fixe étant connue au niveau du catalogue (table SYSCOLUMNS), DB2 n’a pas besoin d’accéder aux pages de données pour calculer la position des attributs de longueur fixe quand ceux-ci sont regroupés devant tous les autres (auquel cas les clés primaires peuvent figurer derrière les clés étrangères, etc.) Cela dit, si le SGBD calcule la position d’un attribut c’est quand même pour accéder aux données correspondantes d’où, malgré tout, entrée/sortie à suivre.

    A noter que l’utilisation de la longueur variable en remplacement de la longueur fixe peut être intéressante en ce qui concerne l’encombrement des buffers. Au niveau du disque ce fut le cas jusqu’au jour où DB2 s’est mis à compresser les données : avec le temps et les évolutions technologiques, les recommandations d’optimisation évoluent (jusqu'au point parfois de se contredire...)

    En tout état de cause, ça n’est pas en grattant du côté de l’ordre de rangement des attributs que l’on optimisera les performances de manière significative.


    A part les indexes, que peut-on faire d'autres ? partitionnement de tables
    Dans le cas de DB2, le choix de l’index cluster est capital pour la performance des applications (ne pas confondre avec les index cluster d'autres SGBD !) Si 2 tables (ou plus) ont par exemple même attribut comme 1er attribut de la clé de leur index cluster, les jointures auront un rendement maximal au niveau des entrées/sorties. Quant au partitionnement, il fonctionne avec l’index cluster ; cluster et partitionnement sont à définir au plus tôt : je dirai en même temps que l’on construit le MCD, ce qui peut paraître fou, puisque l’on traite alors en même temps de concepts de niveaux différents. En effet, dans les livres il est écrit que l’on commence par concevoir le MCD, puis, celui-ci ayant (enfin) reçu le coup de tampon de la DSI, on en vient à l’étape de dérivation en MLD. Ceci fait, le DBA prend le relais pour brancher les index et tout ça, pendant que le concepteur va concevoir sous d’autres cieux. Mais, l’expérience contredit ce scénario traditionnel, la quincaillerie n’est (hélas !) pas sans impact sur les choix au niveau conceptuel (je fais surtout référence ici au système d’identification des entités-types). Heureusement il est des DBA très compétents concernant le niveau conceptuel et capables de discuter avec les concepteurs, alors qu’inversement ces derniers n’ont pas connaissance de ce que l’on trouve sous le capot, ce dont on ne saurait du reste leur tenir grief...

    En tout cas, comme je le répète souvent sur ce forum, les recettes ne suffisent pas et rien n’est définitivement acquis. Concernant l’anecdote précédente, le fait de commencer par droper les index parfois ne suffit pas, je l’ai vécu, mais j’ai toujours réussi à préserver la 3NF. Savoir c’est prévoir : Il y a un moyen sûr de connaître objectivement la performance d’une base de données et ainsi pouvoir s’engager contractuellement avec une DSI, c’est d’en passer par la réalisation d’un prototype pertinent qui sera mis à l’épreuve par des brouillons des transactions les plus sensibles ainsi que par des traitements de masse simplifiés mais identifiés comme critiques, sans oublier les tâches de service (sauvegardes, réorganisations, etc.) En procédant ainsi, avant même que ne débute le développement des applications, on aura stabilisé la structure de la base données et les équipes de développement sauront ce qu’ils peuvent en attendre et cela sans avoir à tout casser suite à des modifications mal venues.

    A propos de l’objectivité, voyez par exemple comment fut ressentie la mise en œuvre par IBM de l’intégrité référentielle dans DB2, en 1988 :

    http://www.developpez.net/forums/sho...d.php?t=252568 (message fsmrel du 17/12/2006)


    Citation Envoyé par al1_24
    [...]
    Ne pas oublier non plus le rafraichissement régulier des statistiques de population des tables et des index.
    Qu'est-ce que tu entends par-là ?
    Je ne suis pas expert en base de données, je fais juste un stage de fin d'étude (niveau DESS) sur le thème (Optimisation d'une base de données)...et j'ai donc sûrement des lacunes dans mon vocabulaire et dans certaines pratiques liés aux bases de données.
    al1_24 attire fort justement votre attention sur un point très important. Le catalogue relationnel contient des informations statistiques relatives notamment à la volumétrie des tables, leur taux de désorganisation, etc. Pour une requête SQL donnée, selon l’ordre de grandeur de la volumétrie des tables impliquées, votre SGBD choisira d’utiliser ou non les index associés. Cette volumétrie n’est évidemment pas mise à jour en temps réel, mais seulement lors de l’exécution d’un utilitaire dédié à la mise à jour du catalogue, à savoir RUNSTATS. Tant qu’à faire, il est d’usage de réorganiser les tables concernées avant d’exécuter cet utilitaire. Ensuite, il est aussi d’usage de rafraîchir les packages (parties SQL des programmes) pour que le SGBD les mettent à jour, sinon ils ne bénéficieront pas des résultats du RUNSTATS.

    Je vous engage vivement à consulter votre DBA favori pour qu’il vous donne suffisamment d’informations sur ce que j’appelle le principe du 3R : Reorg/Runstats/Rebind.

    Et surtout, demandez-lui de vous parler de l’instruction EXPLAIN après que vous ayez lu attentivement le chapitre qui lui est consacrée dans la référence "DB2 Version 9.1 for z/OS, Application Programming And SQL Guide"

    http://www-306.ibm.com/software/data...s/v9books.html

    Et regardez-y entre autres choses ce que l’on entend par Accesstype, Matchcols, Matching, Method (index scan, Nested loop, Merge scan...)

    Il y a du pain sur la planche et nous n’avons pas épuisé le sujet...

    Bon courage à vous,

    Fsmrel
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  5. #5
    Membre habitué
    Homme Profil pro
    Inscrit en
    Août 2005
    Messages
    161
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Août 2005
    Messages : 161
    Points : 193
    Points
    193
    Par défaut
    Un grand merci pour cette dernière réponse! Je vais pouvoir me pencher fortement sur la question, à partir de tous ces éléments.


    Une dernière question, qui peut concerner l'optimisation. Tous les ouvrages sur la conception de Bdd indiquent qu'il faut éviter de mettre des attributs calculables à partir d'autres attributs. Dans la pratique, cela peut-il apporter un gain d'en mettre? (pour recuperer la valeur avec une simple instruction SELECT 'nom_attribut' ... plutot que SELECT count() ou SELECT SUM()... par exemple)

    Ex:
    1 fichier est téléchargeable par un/plusieurs utilisateurs.
    1 utilisateur peut téléchargé 1/plusieurs fichiers

    Serait-il intéressant d'avoir une colonne 'nb_dl_total' dans la table fichier pour indiquer le nombre de telechargement total (cette colonne serait incrementer a chaque download)?
    ==> actuellement, le resultat est donné par un count(*)

  6. #6
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonjour MadCat34,

    Serait-il intéressant d'avoir une colonne 'nb_dl_total' dans la table fichier pour indiquer le nombre de telechargement total (cette colonne serait incrementer a chaque download)?
    ==> actuellement, le resultat est donné par un count(*)
    Quelle bonne question, en relation avec l’injection d’une redondance qui ne met pas en cause les formes normales...
    Il n’y a pas de réponse tranchée, elle ne peut qu’être conditionnelle. Ce qui est sûr, c’est que si on introduit cette redondance, dès qu’il y aura une faille, l’erreur s’installera...
    Pour commencer et vérifier que nous sommes synchrones, je suppose que le MCD ressemblerait à ceci :





    Donnant lieu aux instructions SQL suivantes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
     
    Create Table Fichier (
       FichierId            Int                  Not null,
       FichierNom           Varchar(128)         Not null,
       NbDlTotal            Int                  Not null,
       Constraint PK_Fichier Primary Key  (FichierId)
    ) ;
    Create Table Utilisateur (
       UtilisateurId        Int                  Not null,
       UtilisateurNom       Varchar(48)          Not null,
       Constraint PK_Utilisateur Primary Key  (UtilisateurId)
    ) ;
    Create Table DownLoad (
       FichierId            Int                  Not null,
       UtilisateurId        Int                  Not null,
       Constraint PK_Download Primary Key  (FichierId, UtilisateurId),
       Constraint FK_Download_Fichier Foreign Key (FichierId)
          References Fichier (FichierId)
             On Update cascade On Delete Cascade,
       Constraint FK_Download_Utilisateur Foreign Key (UtilisateurId)
          References Utilisateur (UtilisateurId)
             On Update cascade On Delete Cascade
    ) ;
    Questions à se poser :

    Si l’on met en œuvre l’attribut NbDlTotal, quelles sont les conséquences au cas où sa valeur serait erronée, c'est-à-dire différente de ce que produirait la requête R1 :

    Select Count(*) As NbDlTotal
    From Fichier As A, Download As B
    Where FichierNom = 'Fichier 1'
    And A.FichierId = B.FichierId
    Group By A.FichierId ;

    Grave ? Même pas grave ? Cela conditionne le bon retour d’une navette spatiale vers la terre ? S’agit-il seulement d’une information à caractère statistique ?

    La durée de l’exécution de la requête est-elle acceptable (voire celle de la requête R2 suivante, sans jointure) :

    Select Count(*) As NbDlTotal
    From Download
    Where FichierId = 1
    Group By FichierId ;

    Quelle est la fréquence d’exécution de la requête ? 10 fois ? 10000 fois/jour ?

    Est-ce en transactionnel ? En batch (de nuit) ? Les deux ?

    ...

    =>

    Prototyper la performance de la requête R1 (ou R2), pour vérifier les performances actuelles. Si le temps de réponse est acceptable vu de l’utilisateur (terminaliste ou programme batch...) autant ne pas introduire de redondance. En l’occurrence, le facteur de filtrage est bon et je pense donc qu’avec les bons index, ce temps de réponse devrait être excellent (< 1 seconde), surtout avec DB2.

    Si la fréquence d'exécution de la requête est élevée et si le temps de réponse est médiocre, dans le cas du batch, on peut éventuellement valoriser l’attribut NbDlTotal et s’en servir tant qu’on ne met pas à jour la table Download (qu’on verrouillera par précaution). Mais attention si les chaînes en maintenance ne sont pas surveillées de très près, l’épreuve du temps est redoutable.

    Si vous mettez en place la colonne NbDlTotal, envisagez d’utiliser un trigger pour une surveillance par le SGBD des mises à jour de la table Download et pour une mise à jour automatique de cette colonne en fonction des Insert, Update, Delete : en effet, sans ce dispositif, on se saurait tout prévoir, par exemple que la suppression d’un utilisateur entraîne (par effet cascade) une suppression de 0 à N lignes dans la table Download...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

Discussions similaires

  1. [MySQL] Comment accéder avec php à une base de données ne se trouvant pas sur le même domaine
    Par Alexandrebox dans le forum PHP & Base de données
    Réponses: 12
    Dernier message: 25/02/2008, 19h16
  2. comment se connecter a une base de données mysql
    Par tedok dans le forum Windows
    Réponses: 2
    Dernier message: 04/09/2007, 19h13
  3. Réponses: 3
    Dernier message: 19/09/2006, 16h36
  4. [VB.Net] Comment se déplacer dans une Base de données ?
    Par balabonov dans le forum Windows Forms
    Réponses: 7
    Dernier message: 08/08/2006, 22h44
  5. Comment arbitrer le choix Une base de donnée ou deux ?
    Par medstat2 dans le forum Décisions SGBD
    Réponses: 1
    Dernier message: 28/03/2006, 17h42

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