Dimensionner une base de données SQL selon son jeu de travail et ses requêtes coûteuses
Le dimensionnement d'une base de données ne se résume pas à comparer la taille d'un répertoire de données avec la RAM d'un serveur. Une petite base de données peut être peu performante à cause de scans répétés ou de contentions de verrous, tandis qu'une base plus volumineuse peut servir efficacement une charge de travail ciblée. Étudiez le travail que l'application demande à la base de données avant de choisir l'allocation de ressources suivante.
L'essentiel à retenir
Identifiez les requêtes coûteuses et répétées, inspectez leurs plans en toute sécurité, mesurez ensemble la mémoire et la concurrence, puis modifiez la requête ou la ressource qui limite réellement le travail utile.
Décrivez la charge de travail avant de choisir une taille
Listez le moteur de base de données, l'application, les principales tables et les parcours utilisateurs importants. Une tâche de reporting qui parcourt des données historiques se comporte différemment d'une petite recherche derrière chaque requête web. Notez le volume normal de requêtes, les tâches simultanées, la croissance des données et les opérations sensibles au temps. Incluez les tâches d'arrière-plan, les imports et les exports : ils peuvent créer les périodes les plus chargées même lorsque le trafic public est calme. Tenez les identifiants et les données personnelles hors des notes de dimensionnement que vous partagez.
Estimez le stockage au-delà des données actuelles des tables. Les index, les journaux de transactions, le travail temporaire, la croissance et la maintenance peuvent tous nécessiter de l'espace. Les sauvegardes exigent aussi une destination et une procédure de restauration réfléchies. Une valeur de stockage sur une fiche d'hébergement correspond à la capacité allouée au service, et non à l'affirmation que chaque octet doit être occupé par la base de données active. Surveillez l'évolution de l'espace libre et laissez de la marge pour les opérations que votre moteur et votre application effectuent réellement.
Le jeu de travail correspond aux données et index qu'une charge de travail sollicite de façon répétée. Il peut être bien plus petit que la base de données historique complète, et il peut évoluer lorsqu'un rapport ou une promotion devient populaire. La mémoire aide lorsque des pages utiles peuvent être réutilisées, mais une règle générique selon laquelle toutes les données doivent tenir en RAM est trop grossière. Observez les lectures, le comportement du cache et la durée des requêtes pendant un travail représentatif plutôt que d'appliquer un pourcentage de mémoire universel.
Trouvez la requête qui consomme du temps utile
Partez d'un parcours applicatif lent et reliez-le à l'activité de la base de données. Une requête exceptionnellement lente est visible, mais une requête modeste répétée des centaines de fois peut consommer davantage de travail total. Regroupez les requêtes similaires et comparez leur fréquence, leur durée, les lignes examinées ou renvoyées, ainsi que les périodes où les utilisateurs constatent le délai. Utilisez les fonctions de journalisation et de diagnostic disponibles pour votre moteur, avec un accès et une conservation adaptés aux données de requête potentiellement sensibles.
Un plan d'exécution décrit comment le moteur prévoit de récupérer et de combiner les données. Lisez ensemble le chemin d'accès, les lignes estimées, les jointures et le travail de tri. Un scan n'est pas automatiquement erroné : pour une petite table ou une requête large, il peut être pertinent. La question est de savoir si le travail correspond au résultat dont l'application a besoin. Comparez le comportement estimé et le comportement observé lorsque des outils adaptés sont disponibles, et notez la version exacte du schéma et du moteur.
| Symptôme | Cause possible | Éléments à recueillir |
|---|---|---|
| De nombreuses lignes traitées pour un petit résultat | Forme de la requête ou index utile manquant | Plan, prédicats et lignes examinées |
| Le délai augmente avec les écritures simultanées | Contention de verrous ou de transactions | Transactions en attente et durée des transactions |
| Recherches identiques répétées | Répétition des requêtes applicatives | Fréquence des requêtes groupées et trace des requêtes |
| Tris ou rapports volumineux lents | Les données de travail dépassent la mémoire disponible | Travail temporaire, pression mémoire et taille des rapports |
Soyez prudent avec les outils qui exécutent une instruction tout en la mesurant. PostgreSQL EXPLAIN ANALYZE exécute la requête, et les instructions qui modifient les données peuvent les modifier, sauf si l'investigation empêche délibérément ce résultat. Les fonctionnalités d'analyse de MySQL exigent également de comprendre l'instruction et la version choisies. Utilisez si possible un environnement de test représentatif. Ne collez jamais une commande de diagnostic inconnue en production simplement parce qu'elle contient le mot explain.
Modifier une seule cause à la fois
Un index peut réduire le nombre de lignes nécessaires à une recherche utile, mais il occupe aussi de l'espace de stockage et ajoute du travail de maintenance aux écritures. Concevez-le en fonction des prédicats et du tri réels, puis mesurez les lectures et écritures concernées. Créer des index sur chaque colonne génère des coûts sans prouver de bénéfice. Les opérations de schéma importantes nécessitent leur propre plan, car le verrouillage, l'espace temporaire et le délai d'achèvement dépendent du moteur, de l'opération et de la charge de travail.
Les modifications applicatives peuvent éliminer du travail avant que la base de données ne le reçoive. Ne récupérez que les colonnes nécessaires, évitez de récupérer plusieurs fois la même relation et adoptez une stratégie de pagination réfléchie pour les grandes listes. Gardez les transactions ciblées afin qu'elles ne retiennent pas de ressources en attendant des appels réseau sans rapport. Ces choix améliorent souvent à la fois la requête moyenne et les périodes de contention les plus difficiles. Validez le résultat renvoyé aux utilisateurs ainsi que la réduction de la durée des requêtes.
Augmentez les ressources lorsque les preuves le justifient. Davantage de RAM peut aider un ensemble de travail utile, tandis qu'un CPU supplémentaire peut aider un travail réellement parallélisable. Aucune de ces modifications ne résout automatiquement un verrou détenu longtemps ou un balayage complet inutile. Lors du choix de l'hébergement, comparez l'allocation exacte de mémoire, de stockage et de processeur, puis tenez compte du système d'exploitation et des autres services qui partagent la machine. Documentez pourquoi cette allocation correspond à la charge de travail mesurée.
Validez en situation de concurrence et intégrez la reprise au plan
- Conservez le schéma, la version du moteur et les mesures de référence.
- Utilisez des distributions de données représentatives plutôt que de simples tables d'échantillon minuscules.
- Comparez la même requête et la même charge de travail après une modification contrôlée.
- Mesurez les opérations réussies, la latence et les erreurs pendant un travail concurrent.
- Vérifiez les sauvegardes et une procédure de restauration avant toute maintenance risquée.
Un test avec une seule connexion ne décrit pas tous les pics de production. Répétez la charge de travail concernée avec des requêtes concurrentes réalistes et des tâches planifiées, et arrêtez-vous lorsque les limites d'erreur ou de ressources convenues sont atteintes. Utilisez des données de test synthétiques ou correctement protégées. N'impliquez pas un service de paiement externe ni les systèmes d'un tiers dans une charge de travail générée sans avoir organisé cette participation. Gardez le générateur de charge suffisamment séparé pour que ses propres limites soient visibles.
Enfin, transformez le diagnostic en un court compte rendu de maintenance. Conservez l'empreinte de la requête, le plan, la contrainte observée, la modification choisie et le chemin de retour arrière. Ajoutez la croissance du stockage et les exigences de reprise à la prochaine date de revue. Les futurs mainteneurs disposent ainsi d'un point de départ argumenté lorsque la charge de travail évolue, au lieu d'un réglage mémoire ou d'un index mystérieux que personne n'ose toucher.
