Comment optimiser au mieux une base de données ?

[MadCat34]

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 .

[al1_24]

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.

[MadCat34]

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.

[fsmrel]

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)

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

[MadCat34]

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(*)

[fsmrel]

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 :

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