«

mar
14

Ma base de données est lente, une enquête sur les serial-killers de performances …

Avec plusieurs secondes pour afficher ses données, votre application est la risée de vos collègues. Après étude, les logs sont clairs : 80% du temps est passé en base de données. Vous pensez tenir la coupable. Simple grief mal placé de développeur ou réalité ?

Sans cibler une base particulière, voyons les pistes à étudier.

Avant de commencer, obtenez une valeur chiffrée du temps de réponse effectif et un ordre de grandeur du résultat espéré. Ne vous contentez pas du sentiment « c’est lent ». Ceci vous permettra de prouver les progrès accomplis et d’éviter l’écueil du « c’est toujours lent ».

Présomption d’innocence

Commençons par définir le contexte dans lequel les problèmes surviennent. Si l’objectif n’est pas de disculper la base de données à coup sûr, soyons certains d’attaquer le problème à la racine.

Même si le serveur qui pose problème est dédié, il utilise selon toute vraisemblance une baie de disque mutualisée et le même réseau que tout le monde. En premier, regardons du côté de nos voisins de serveur afin de nous assurer que nos ressources sont bien disponibles :

  • Côté mutualisation, le ratio de surcapacité (overprovisionning) est-il raisonnable ? En production, il devrait l’être, en développement ou recette c’est moins sûr.
    Une base mutualisée peut être plus performante qu’une base dédiée, car possédant plus de ressources au total. Obtenir un serveur dédié n’est pas une garantie de meilleures performances.
  • Les performances sont-elles dégradées sur une plage horaire régulière ? Si oui, il est probable qu’une tâche planifiée accapare vos ressources (sauvegarde, alimentation datamart…).
  • Si la plage de dégradation est variable, pensez à un traitement lourd occasionnel (migration, reprise, ré-indexation, maintenance, déploiement de mises à jour…).

En second, voyons s’il est normal que cet environnement pose un problème de performances. S’il s’agit d’un environnement de test de charge, et non de la production, deux aspects sont à prendre en compte pour juger a priori de sa pertinence :

  • Le dimensionnement de la plateforme qui couvre le processeur et la mémoire, bien sûr, mais aussi les performances I/O (disque et réseau) et le paramétrage (allouez les ressources disponibles). Ne vous attendez pas à ce que la relation soit linéaire entre le dimensionnement et les performances obtenues.
  • Le volume et la distribution des données modifient le comportement des requêtes. En test, veillez à respecter les volumes et la répartition attendus entre schémas et tables de production (idéalement avec les vraies données, les données projetées à 6 mois, à 1 an).

En production le volume ou l’équilibre des données peuvent avoir subitement changés suite à un import, à l’activation d’une nouvelle fonctionnalité, à l’ouverture à une nouvelle population d’utilisateurs, ou avoir atteint un seuil critique pour lequel le dimensionnement initial ne suffit plus.

Une fois ces éléments soit contrôlés soit à l’esprit, vérifions la stratégie de l’application pour échanger les données avec la base.

  • Un traitement ensembliste est souvent plus rapide que plusieurs traitements unitaires. Pensez aux boucles et aux appels en cascade dans des grappes d’objets (lazy loading, jQuery, Ajax). Halte à la paresse, écrivez de nouveaux services (avec du SQL ad hoc) lorsque cela est pertinent.
  • Les utilisateurs peuvent avoir des habitudes ou des consignes les amenant à saisir en masse à certains moments précis : avant une échéance comptable, avant le weekend…

Un traitement trop long (durée) ou trop volumineux joue avec la capacité de la base à retenir l’état de début de transaction. Je vous conseille de réaliser des COMMIT intermédiaires, et de prévoir l’annulation ou la reprise du traitement à partir de ces étapes. Pensez aussi que le coût du rollback sera du même ordre que celui de votre transaction. Pendant ce temps la base risque d’être bloquée.

C’est à ce stade qu’une exploration attentive des journaux applicatifs et de la base est pertinente. Les bases de données peuvent  vous aider dans la recherche des requêtes coûteuses :

  • Oracle conserve un historique des requêtes dans V$SQLAREA ou système de trace.
  • SQL server propose un « server plan cache » ou l’utilisation du profiler (trace).
  • MySQL et PostgreSQL permettent de loguer les requêtes dans leur journal.

Dans tous les cas, vous devez déterminer les critères qui définissent le « top » des requêtes à étudier. Ces requêtes se classent grossièrement en trois catégories : requêtes trop fréquentes, trop longues (requête complexe), trop lentes (requête supposée simple).

La correction pour la première catégorie est de modifier l’application (cache) afin de diminuer la fréquence d’appel. Par exemple, mettez les droits de l’utilisateur en cache plutôt que de les vérifier à chaque appel. Ses droits ne seront mis à jour qu’après reconnexion, certes, mais il reste possible de forcer le rafraichissement ou le test des droits sur quelques services critiques.

Il nous reste donc deux cas pour lesquels les premières actions seront les mêmes : les traitements longs et les requêtes lentes. Au moins un critère de coût (cpu, mémoire, I/O, temps moyen) doit les révéler.

L’instruction du dossier

A ce stade, il nous reste une poignée de requêtes récalcitrantes. Deux outils vont nous aider pour leur analyse : le plan et la trace.

Le plan d’exécution

Le plan est le meilleur chemin trouvé par l’optimiseur (CBO : Cost Based Optimizer) avant exécution de la requête. Plus la requête est complexe, plus il y a de chemins à envisager. Afin de ne pas ralentir les traitements, le calcul du plan a certaines particularités :

  • Sa durée est limitée (time frame constraint). S’il y a trop de chemins à envisager, tous ne seront pas testés. Le plan choisi sera le meilleur parmi ceux qui auront été évalués.
    Fragmentez le traitement, avec des stockages intermédiaires (tables, dénormalisation, vues matérialisées), indexables si le volume le nécessite. Allégez le code : supprimez les jointures et colonnes inutiles.
  • Il se base sur des informations statistiques (taille des objets, nombre d’enregistrements, distribution des clés, cpu et ram dispo …) et selon un algorithme propre à chaque éditeur.
    Tenez vos statistiques à jour, ce qui est automatique la plupart du temps. Forcez leur calcul après d’importantes opérations de migration, d’import ou de purge.
  • Il n’envisage qu’un seul index à la fois par instance de table. Si vos colonnes sont indexées séparément et que votre requête filtre sur plusieurs, un seul index sera malgré tout utilisé.
    Créer des index multi-colonnes (l’ordre compte), doublez plusieurs index unitaires par un index multiple (mais gare aux performances d’insertion/mise à jour et au stockage).
  • Il ne tient pas compte de la fragmentation. Des clés proches peuvent en fait être savamment dispersées sur le disque à l’intérieur d’un fichier, non fragmenté d’un point de vue OS.
    Pour les tables qui subissent de nombreuses suppressions et insertions reconstruisez leurs index de temps à autre. Il est plus compliqué de manipuler les données des tables pour les défragmenter.
  • Il n’évalue pas la complexité de vos fonctions qui peuvent masquer des sous-requêtes, des appels à d’autres fonctions. Il se concentre sur ce qui est visible dans la requête.
    « Inliner » les fonctions : ajouter les éléments nécessaires pour reproduire le code de la fonction dans la requête. Ceci économise aussi un empilement de contexte par ligne traitée. Dans la clause SELECT les fonctions sont appelées pour chaque ligne retournée. Dans la clause WHERE les fonctions sont appelées pour chaque ligne évaluée. Ajouter des colonnes dénormalisées afin de calculer en amont les valeurs nécessaires.
  • Un choix de stratégie peut intervenir : souhaite-t-on récupérer les premières lignes le plus vite, ou la totalité des lignes le plus vite ? (FAST / FIRST_ROWS vs. ALL_ROWS)
    Préférer les instructions de pagination des résultats. Ce HINT n’a pas de sens si la requête ramène peu de lignes ou si une agrégation ou un tri est effectué (en effet cela impose d’évaluer toutes les lignes au moins sur les critères de tri/agrégation). De nombreuses lignes de résultat ni triées ni agrégées semblent indiquer que ces données ne sont pas affichées à l’utilisateur. Peut-on rendre le traitement asynchrone ?
  • Les plans calculés sont mis en cache.
    Forcez la purge du cache et un calcul des statistiques (parfois automatique) après un import massif de données, ou tout ce qui modifie l’équilibre des données en base.
  • Afin que le cache joue son rôle, il faut que les requêtes similaires soient reconnues.
    Utiliser le binding de variable au lieu de valeurs « en dur ». Attention aux requêtes générées qui peuvent produire une grande variété de cas.

Quelques remarques :

  • Je vous encourage à chercher détails et arguments modérant les conseils synthétiques ci-dessus. Ce ne sont pas des dogmes et une base récente se sort de la plupart des pièges toute seule.
  • Jouer avec les paramètres de statistiques et de l’optimiseur est long et ne donne aucune garantie de résultat stable à long terme. Travaillez donc plutôt à rester dans de cas général.
  • Il existe certainement des outils pour votre base qui offrent une assistance à l’optimisation des paramètres de la base et des requêtes (ex : sql tuning advisor pour Oracle).
  • Pensez enfin qu’il existe d’autres types de base (NoSQL, Mémoire) qui sont peut-être mieux adaptées à telle ou telle partie spécifique de vos traitements. Une même base peut aussi proposer des versions, ou moteurs (MySQL), différents pour des besoins différents.

La trace de l’exécution

La trace est l’enregistrement des opérations concrètement effectuées lors d’une exécution de la requête selon le plan calculé à ce moment. La trace varie donc à chaque exécution et dépend de l’état de la base au moment de son enregistrement, notamment des données présentes en cache. Elle nous informe de la réalité des traitements : coût réel, opérations additionnelles.

Un écart important entre le plan et la trace nous informe que l’optimiseur disposait soit d’informations erronées, soit n’a pas pu estimer tous les chemins à temps. Un écart faible nous informe que l’optimiseur disposait d’informations correctes, même si tous les chemins n’ont peut-être pas été évalués. L’estimation est correcte pour ce chemin.

Dans tous les cas voici nos moyens d’améliorations :

  • Le dimensionnement : facile à mettre en place sur un environnement virtualisé, coûteux sur le long terme. Le gain est limité voire inexistant au bout d’un moment.
  • Le paramétrage de la base : pas toujours aisé, aucun impact sur le coût à long terme, mais avec un risque de dégrader les performances pour d’autres requêtes.
  • Le code SQL : ce qui semble le plus facile, ou en tout cas le plus rapide à demander à un développeur. Limité dans ses moyens d’action.
  • Le code de l’application : attention aux écarts avec les spécifications, nécessite peut-être de négocier avec les utilisateurs et les ergonomes pour découper des écrans, des traitements.

Le jugement

Délibérations

Plus moyen d’y couper, le gang des requêtes à problème est chargé dans votre client SQL. Quelques conseils pour les faire avouer où elles consomment les ressources ?

  1. Formater le code proprement, un peu de lisibilité :
    1. Utiliser la notation ANSI autant que possible
    2. Faire attention à ne pas fermer une jointure par une clause WHERE malencontreuse
  2. Tester la performance de chaque morceau indépendamment :
    1. Les vues, les vues inline, la clause WITH
    2. Les sous-requêtes de filtrage
    3. La clause SELECT à remplacer par un SELECT 1 FROM…
  3. Ecarter le code inutile, ajouter les éléments implicites
    1. Retirer les tables « relais » dans les jointures dont aucune colonne n’est utilisée
    2. Ajouter les jointures triangulaires manquantes (offrir plus de chemins)
    3. Fermer les jointures ouvertes sur des colonnes NOT NULL
  4. Mutualiser le code, utilisez du SQL « avancé »
    1. Utilisation de WITH, MERGE, BULK / FORALL
    2. Descendre les sous-requêtes de la clause SELECT à la clause FROM
    3. Grouper les tables en double (colonnes différentes d’une même ligne)

Serial-killers

Vérifier la liste des serial-killers de performance :

  • Appel de fonction(s) comprenant une ou plusieurs sous-requêtes, surtout en clause WHERE, encore pire si elle(s) empêche(nt) l’usage d’un index.
  • Utilisation en cascade de vues (pouvant comprendre des colonnes, des calculs, des fonctions, des sous-requêtes inutiles à votre requête finale).
  • NOT IN avec une sous-requête liée (faute de logique si elle peut renvoyer NULL), à remplacer par un NOT EXISTS.
  • Cardinalité mal maitrisée :
    • CROSS-JOIN (produit cartésien) involontaire (jointure oubliée) ou pivot ?
    • Cherchez d’où viennent les doublons plutôt que de les masquer par un « group by » ou un « distinct », dé-doublonnez au plus tôt sur des vues inline…
  • INDEX UNIQUE SCAN, très coûteux lorsque beaucoup de lignes sont évaluées avec un accès à la table en plus de l’index.

Ces éléments  ont en commun d’augmenter considérablement la quantité de données manipulée soit explicitement, soit implicitement par réutilisation d’objets tiers. Or, malgré les baies de disques et le cache qui peut être mis en place, le temps de réponse d’une requête reste lié à ce volume…

Quelques solutions

Si après plusieurs réécritures la requête reste lente, ou si la complexité du traitement impose qu’il soit long, vous pouvez toujours :

  • ajouter un index dédié au cas particulier de cette requête (et idéalement à d’autres), pensez aux index de fonctions, aux index reverse, et que l’index contient les données
  • supprimer les index inutilisés, utiliser au maximum les colonnes déjà indexées
  • rendre le traitement asynchrone, ne pas bloquer l’utilisateur
  • pré-calculer ce qui peut l’être (agrégations, dénormalisations, cache)
  • présenter les données de la veille (pré-calculer tout)
  • modifier le modèle de données (supprimer des N-N, contraindre des NOT NULL, bannir les modèles en étoile, l’héritage), supprimer tout ce qui ajoute des OR, des jointures, des ouvertures de jointure
  • ajouter des critères obligatoires dans l’IHM (limiter les données manipulées)
  • désactiver les contraintes le temps du traitement (ex : modification/suppression massive)
  • paralléliser les accès (partionnement de la table selon un critère orthogonal aux traitements)

Le cluster est une solution pour de la haute disponibilité. Il faut dépasser un certain seuil de serveurs dans la grappe afin d’absorber le coût de synchronisation et d’obtenir un gain en tenue de charge, mais probablement pas en temps de réponse.

La vérité est ailleurs

Simplement énoncée, la conclusion est qu’il faut chercher à minimiser les données manipulées, que ce soit en mémoire ou pire par des accès physiques. Pas de technique miracle, il faudra comprendre ce que fait fonctionnellement telle ou telle requête et se poser la question de la légitimité du volume de données brassé.

Lien Permanent pour cet article : http://blog.kleegroup.com/teknics/?p=619

2 commentaires

  1. akram a dit :

    Un très bon article. La responsabilité incombe toujours à l’infrastructure ou au développeur.

  2. CoursSQL a dit :

    Très bon article.

Laisser un commentaire

Votre adresse ne sera pas publiée.

Vous pouvez utiliser les balises HTML suivantes : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>