Le lecteur retrouvera ci-dessous les 10 meilleurs conseils proposés par le Journal du Net pour optimiser le fonctionnement de sa base de données relationnelle.

Nous ne resistons pas au plaisir de reproduire ces conseils, non sans avoir au préalable précisé que ceux qui, comme ALCADE, utilisent la base de données objet InterSystems Caché ne connaissent pas les affres du DBA relationnel.

La base de données objet InterSystems Caché se pilote seule, sans intervention du DBA, et offre des temps de réponse jusqu'à 100 fois plus performants que ceux des bases de données relationnelles. Cela est si vrai qu'il est possible (et courant) de faire du datamining temps réel sur une base de données objet InterSystems Caché en transaction.

Donc, si comme nous vous utilisez la base de données objet InterSystems Caché, vous pouvez quitter cette page. Pour tous les autres : bonne lecture.

L'optimisation d'une base transactionnelle est nécessaire à sa pérennité. Elle garantit la satisfaction des utilisateurs à long terme et réduit les coûts d'exploitation et de maintenance. Deux experts livrent leurs bonnes pratiques.

Sollicitées en permanence pour des informations clés, les bases de données transactionnelles représentent le cœur du système d'information des entreprises. Elles doivent être accessibles en permanence et offrir des délais de réponse raisonnables, alors même que les requêtes peuvent être très nombreuses et parfois très coûteuses.

Outre l'achat d'une base de données plus robuste, d'un réseau de taille ou de nouveau matériel, certaines petites optimisations peuvent permettre de gagner déjà énormément en temps d'exécution des requêtes SQL. Un travail de fourmi qu'effectuent au quotidien les administrateurs de base de données (ou DBA) et les experts. Zoom sur 10 conseils pratiques fréquemment utilisés dans le milieu.

1) Se fixer un objectif d'utilisation de la base et en déduire un modèle physique de données

Le modèle physique de données définit l'organisation de la base de données et la structure des tables. C'est sur ce modèle que repose une bonne partie des performances obtenues par la suite, il faut donc y être particulièrement attentif. Dans le cas d'une base de données MySQL, il faudra par exemple éviter de multiplier les jointures et les requêtes imbriquées.

"MySQL est une base de données adaptée pour les requêtes simples et offre des temps de réponse très rapide. Mais ces performances s'écroulent lorsque les requêtes deviennent complexes, même si un gros travail en ce sens est fait en ce moment par les équipes de développement de l'éditeur", précise Bruno Pennec, responsable de la cellule ingénierie à Paris chez SQLI.

2) Veiller à l'adéquation entre les champs définis et les besoins

Le modèle de données doit être cohérent et dimensionné en fonction de son utilisation car cela à un impact à la fois sur le volume de stockage et les performances des requêtes.

"J'ai l'exemple d'un client qui utilisait sur un de ses champs un Char de 10 au lieu d'un Integer de 4. On a refait le modèle et gagné 25% de performance sur sa table. De plus, cette correction a réglée quelques bugs applicatifs", affirme Jean-Marc Blaise, expert DB2 chez IDB Consulting.

3) Poser des index en fonction de la consommation de ressources mesurées

Par défaut, les clés primaires des tables disposent d'un index et il est pertinent de le conserver mais les clés secondaires posent un problème plus complexe. Parfois, selon le volume, la sollicitation de la base ou l'évolution des applications métiers, les index des clés secondaires perdent de leur attrait. Il est donc préférable de poser ses index en mesurant régulièrement l'efficacité de ses requêtes.

"Sous DB2, l'utilisateur dispose d'un outil de monitoring automatique baptisé Design Adviser, qui consiste à indiquer le chemin du SQL passé sur la base. DB2 détermine ensuite les index qui pourraient améliorer les plans d'accès", ajoute Jean-Marc Blaise. La mesure constitue donc l'outil de travail essentiel de l'administrateur de base de données.

4) Elargir la mémoire allouée aux traitements de la base

La majorité des SGBDR du marché le permettent. Cette fonction permet d'attribuer manuellement ou dynamiquement la mémoire virtuelle allouée aux index et aux requêtes de sa base de données de manière à éviter une surconsommation des ressources mais aussi d'accélérer certains traitements jugés plus critiques et plus lourds que la moyenne.

Cette optimisation reste toutefois moins intéressante que le travail sur les index car la taille des volumes traités peut varier fortement dans le temps.

5) Organiser son partitionnement

C'est surtout vrai sur les bases Oracle pour lesquelles les données sont disséminées, par exemple dans le cas d'un entrepôt de données. Dans ce cas précis, l'administrateur va placer les informations les plus demandées (les index par exemple) en tête de secteur sur les disques du serveur.

Ainsi, lorsqu'une requête s'exécute, les données seront lues et transmises plus rapidement. Cette technique peut également porter ses fruits dans le cas de tables de grande volumétrie et donc disséminer sur plusieurs disques.

6) Eviter la fragmentation des tables

"Quand les utilisateurs ajoutent des données dans la base et les retirent, cela désorganise peu à peu le système. Il faut pouvoir déclencher une alerte lorsqu'il y a un trop fort décalage et que des index pointent n'importe où", souligne l'expert d'IDB Consulting. Cette fragmentation des tables se présente sous la forme d'un ratio que l'administrateur peut mesurer.

Dans le cas d'une table "commande" par exemple, où beaucoup de champs sont créés, mis à jour et supprimés, une réorganisation de la table peut être planifiée tous les jours.

7) Ne pas établir des plans d'optimisation sur un environnement de test

Parce que les données et les volumes varient énormément entre une base de test et une base de production, il est nécessaire de réaliser ses optimisations directement sur les environnements de production, après évidemment validation des plans d'optimisations.

Sinon, le travail fourni en amont n'aura servi à rien et risque même de pénaliser le système en production au lieu d'améliorer ses performances.

8) Déporter les calculs et les données non critiques dans des bases secondaires

Lorsque cela est possible, il est préférable de séparer des données non stratégiques dans une autre base de données afin de consacrer la base transactionnelle à sa seule activité.

"Le cluster peut être intéressant pour répliquer en temps réel sa base de données, dans le cas de traitements de consolidation par exemple. Mais tout va dépendre du besoin, car en scindant par domaine d'activité, on limite le rapprochement de données par la suite", analyse Bruno Pennec de SQLI.

9) Surveiller son code SQL

Il faut parfois faire la traque de certaines fonctions SQL consommatrices en ressources machines, notamment les requêtes imbriquées sous MySQL, les fonctions de tri sous Oracle ou DB2...

Les applications externes à la base de données génèrent parfois automatiquement des requêtes SQL sous un format spécifique qui s'avère pas ou peu optimisé pour le SGBDR de l'entreprise. Aux administrateurs d'être attentif à cet aspect.

10) Vérifier les mises à jour disponibles pour son SGBDR

Des travaux d'optimisation des équipes d'Oracle, Microsoft, IBM, MySQL, PostgreSQL améliorent régulièrement les performances des traitements SQL.

La mise à jour peut donc régler une bonne partie des problèmes, même si au contraire elle peut en générer d'autres. Il est donc utile de surveiller périodiquement les dernières versions logicielles et d'intégrer des tests de performances aux tests de non régression pratiqués avant une migration.