Optimisation des requêtes de base de données à l'aide de l'exemple d'un service B2B pour les constructeurs

Comment multiplier par 10 le nombre de requêtes sur la base de données sans passer à un serveur plus productif et maintenir les fonctionnalités du système ? Je vais vous expliquer comment nous avons géré la baisse des performances de notre base de données, comment nous avons optimisé les requêtes SQL pour servir le plus d'utilisateurs possible et ne pas augmenter le coût des ressources informatiques.

Je réalise un service de gestion des processus commerciaux dans les entreprises de construction. Environ 3 10 entreprises travaillent avec nous. Plus de 4 10 personnes travaillent chaque jour avec notre système pendant 9.6 à 300 heures. Il résout divers problèmes de planification, de notification, d'avertissement, de validation... Nous utilisons PostgreSQL 200. Nous avons environ 10 tables dans la base de données et jusqu'à 3 millions de requêtes (4 10 différentes) sont reçues chaque jour. En moyenne, nous avons XNUMX à XNUMX XNUMX requêtes par seconde, aux moments les plus actifs, plus de XNUMX XNUMX requêtes par seconde. La plupart des requêtes sont OLAP. Il y a beaucoup moins d’ajouts, de modifications et de suppressions, ce qui signifie que la charge OLTP est relativement légère. J'ai fourni tous ces chiffres afin que vous puissiez évaluer l'ampleur de notre projet et comprendre à quel point notre expérience peut vous être utile.

Imaginez-en un. Lyrique

Lorsque nous avons commencé le développement, nous n'avions pas vraiment réfléchi au type de charge qui pèserait sur la base de données et à ce que nous ferions si le serveur arrêtait de tirer. Lors de la conception de la base de données, nous avons suivi les recommandations générales et essayé de ne pas nous tirer une balle dans le pied, mais nous sommes allés au-delà des conseils généraux du type « n'utilisez pas le modèle. Valeurs des attributs d'entité nous ne sommes pas entrés. Nous avons conçu sur la base des principes de normalisation, en évitant la redondance des données et sans nous soucier d'accélérer certaines requêtes. Dès l’arrivée des premiers utilisateurs, nous avons rencontré un problème de performances. Comme d’habitude, nous n’étions absolument pas préparés à cela. Les premiers problèmes se sont avérés simples. En règle générale, tout était résolu en ajoutant un nouvel index. Mais il est arrivé un moment où de simples correctifs ont cessé de fonctionner. Réalisant que nous manquons d'expérience et qu'il nous est de plus en plus difficile de comprendre la cause des problèmes, nous avons embauché des spécialistes qui nous ont aidés à configurer correctement le serveur, à connecter la surveillance et nous ont montré où chercher pour obtenir statistiques.

Image deux. Statistique

Nous avons donc environ 10 10 requêtes différentes exécutées chaque jour sur notre base de données. Parmi ces 2 3, il y a des monstres qui sont exécutés 0.1 à 0.3 millions de fois avec un temps d'exécution moyen de 30 à 100 ms, et il y a des requêtes avec un temps d'exécution moyen de XNUMX secondes qui sont appelées XNUMX fois par jour.

Il n'a pas été possible d'optimiser les 10 XNUMX requêtes, nous avons donc décidé de déterminer où diriger nos efforts afin d'améliorer correctement les performances de la base de données. Après plusieurs itérations, nous avons commencé à diviser les demandes en types.

Demandes TOP

Ce sont les requêtes les plus lourdes qui prennent le plus de temps (durée totale). Il s’agit soit de requêtes appelées très souvent, soit de requêtes très longues à exécuter (les requêtes longues et fréquentes ont été optimisées dans les premières itérations de la lutte pour la vitesse). En conséquence, le serveur consacre le plus de temps à leur exécution. De plus, il est important de séparer les principales requêtes par temps d'exécution total et séparément par temps d'E/S. Les méthodes d'optimisation de ces requêtes sont légèrement différentes.

La pratique habituelle de toutes les entreprises est de travailler avec des demandes TOP. Il y en a peu : l'optimisation d'une seule requête peut libérer 5 à 10 % de ressources. Cependant, à mesure que le projet mûrit, l'optimisation des requêtes TOP devient une tâche de moins en moins triviale. Toutes les méthodes simples ont déjà été élaborées, et la demande la plus « lourde » ne prend « que » 3 à 5 % des ressources. Si les requêtes TOP prennent au total moins de 30 à 40 % du temps, alors vous avez probablement déjà fait des efforts pour les faire fonctionner rapidement et il est temps de passer à l'optimisation des requêtes du groupe suivant.
Il reste à répondre à la question de savoir combien de requêtes principales doivent être incluses dans ce groupe. J'en prends généralement au moins 10, mais pas plus de 20. J'essaie de faire en sorte que le temps du premier et du dernier du groupe TOP ne diffère pas de plus de 10 fois. Autrement dit, si le temps d'exécution de la requête diminue fortement de la 1ère à la 10ème place, alors je prends TOP-10, si la baisse est plus progressive, alors j'augmente la taille du groupe à 15 ou 20.

Paysans moyens

Ce sont toutes des demandes qui surviennent immédiatement après TOP, à l'exception des derniers 5 à 10 %. Habituellement, l'optimisation de ces requêtes offre la possibilité d'augmenter considérablement les performances du serveur. Ces demandes peuvent peser jusqu'à 80 %. Mais même si leur part dépasse les 50 %, il est temps de les examiner de plus près.

Queue

Comme mentionné, ces requêtes arrivent à la fin et prennent 5 à 10 % du temps. Vous ne pouvez les oublier que si vous n'utilisez pas d'outils d'analyse automatique des requêtes, leur optimisation peut également être bon marché.

Comment évaluer chaque groupe ?

J'utilise une requête SQL qui permet de faire une telle évaluation pour PostgreSQL (je suis sûr qu'une requête similaire peut être écrite pour de nombreux autres SGBD)

Requête SQL pour estimer la taille des groupes TOP-MEDIUM-TAIL

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

Le résultat de la requête est constitué de trois colonnes, chacune contenant le pourcentage de temps nécessaire au traitement des requêtes de ce groupe. À l’intérieur de la requête, il y a deux nombres (dans mon cas, c’est 20 et 800) qui séparent les requêtes d’un groupe d’un autre.

C'est ainsi que les parts de requêtes se comparent grossièrement au moment où les travaux d'optimisation ont commencé et aujourd'hui.

Le diagramme montre que la part des demandes TOP a fortement diminué, mais que les « paysans moyens » ont augmenté.
Au début, les demandes TOP comportaient des erreurs flagrantes. Au fil du temps, les maladies infantiles ont disparu, la part des demandes TOP a diminué et de plus en plus d'efforts ont dû être déployés pour accélérer les demandes difficiles.

Pour obtenir le texte des demandes, nous utilisons la requête suivante

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

Voici une liste des techniques les plus couramment utilisées qui nous ont aidés à accélérer les requêtes TOP :

• Refonte du système, par exemple en retravaillant la logique de notification en utilisant un courtier de messages au lieu de requêtes périodiques à la base de données
• Ajout ou modification d'index
• Réécriture des requêtes ORM en SQL pur
• Réécriture de la logique de chargement de données différée
• Mise en cache via la dénormalisation des données. Par exemple, nous avons une connexion de table Livraison -> Facture -> Demande -> Application. Autrement dit, chaque livraison est associée à une application via d'autres tables. Afin de ne pas lier toutes les tables de chaque requête, nous avons dupliqué le lien vers la requête dans la table Delivery.
• Mise en cache de tables statiques avec des ouvrages de référence et tables rarement modifiées dans la mémoire du programme.

Parfois, les changements équivalaient à une refonte impressionnante, mais ils représentaient 5 à 10 % de la charge du système et étaient justifiés. Au fil du temps, l'échappement est devenu de plus en plus petit et une refonte de plus en plus sérieuse a été nécessaire.

Ensuite, nous avons porté notre attention sur le deuxième groupe de demandes : le groupe des paysans moyens. Il contient beaucoup plus de requêtes et il semblait qu'il faudrait beaucoup de temps pour analyser l'ensemble du groupe. Cependant, la plupart des requêtes se sont révélées très simples à optimiser et de nombreux problèmes ont été répétés des dizaines de fois dans différentes variantes. Voici des exemples de quelques optimisations typiques que nous avons appliquées à des dizaines de requêtes similaires et chaque groupe de requêtes optimisées a déchargé la base de données de 3 à 5 %.

• Au lieu de vérifier la présence d'enregistrements à l'aide de COUNT et d'une analyse complète de la table, EXISTS a commencé à être utilisé
• Je me suis débarrassé de DISTINCT (il n'y a pas de recette générale, mais parfois vous pouvez facilement vous en débarrasser en accélérant la demande de 10 à 100 fois).

  Par exemple, au lieu d'une requête pour sélectionner tous les chauffeurs d'une grande table de livraisons (LIVRAISON)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  fait une requête sur une table relativement petite PERSONNE

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Il semblerait que nous ayons utilisé une sous-requête corrélée, mais elle donne une accélération de plus de 10 fois.

• Dans de nombreux cas, COUNT a été complètement abandonné et
  remplacé par le calcul de la valeur approximative
• au lieu de

  UPPER(s) LIKE JOHN%’ 
  

  utilisation

  s ILIKE “John%”
  

Chaque demande spécifique était parfois accélérée de 3 à 1000 10 fois. Malgré les performances impressionnantes, il nous a d'abord semblé qu'il ne servait à rien d'optimiser une requête qui prend 3 ms, est l'une des XNUMX cent requêtes les plus lourdes et occupe des centièmes de pour cent du temps de chargement global de la base de données. Mais en appliquant la même recette à un groupe de requêtes du même type, nous avons récupéré quelques pour cent. Afin de ne pas perdre de temps à examiner manuellement les centaines de requêtes, nous avons écrit plusieurs scripts simples utilisant des expressions régulières pour rechercher des requêtes du même type. En conséquence, la recherche automatique de groupes de requêtes nous a permis d'améliorer encore nos performances avec un effort modeste.

Cela fait donc maintenant trois ans que nous travaillons sur le même matériel. La charge quotidienne moyenne est d'environ 30 %, en pointe elle atteint 70 %. Le nombre de demandes, ainsi que le nombre d'utilisateurs, ont été multipliés par environ 10. Et tout cela grâce au suivi constant de ces mêmes groupes de demandes TOP-MEDIUM. Dès qu'une nouvelle demande apparaît dans le groupe TOP, nous l'analysons immédiatement et essayons de l'accélérer. Nous examinons le groupe MEDIUM une fois par semaine à l'aide de scripts d'analyse de requêtes. Si nous rencontrons de nouvelles requêtes que nous savons déjà optimiser, nous les modifions rapidement. Parfois, nous trouvons de nouvelles méthodes d’optimisation pouvant être appliquées à plusieurs requêtes à la fois.

Selon nos prévisions, le serveur actuel résistera à une augmentation du nombre d'utilisateurs de 3 à 5 fois supplémentaires. Certes, nous avons encore un atout dans notre manche : nous n'avons toujours pas transféré les requêtes SELECT vers le miroir, comme cela est recommandé. Mais nous ne le faisons pas consciemment, car nous voulons d’abord épuiser complètement les possibilités d’optimisation « intelligente » avant d’activer « l’artillerie lourde ».
Un regard critique sur le travail effectué peut suggérer d’utiliser une mise à l’échelle verticale. Achetez un serveur plus puissant au lieu de perdre le temps des spécialistes. Le serveur n'est peut-être pas si cher, d'autant plus que nous n'avons pas encore épuisé les limites de la mise à l'échelle verticale. Cependant, seul le nombre de demandes a été multiplié par 10. Au fil des années, les fonctionnalités du système ont augmenté et les types de demandes sont désormais plus nombreux. Grâce à la mise en cache, les fonctionnalités existantes sont exécutées avec moins de requêtes et des requêtes plus efficaces. Cela signifie que vous pouvez multiplier en toute sécurité par 5 supplémentaires pour obtenir le coefficient d'accélération réel. Ainsi, selon les estimations les plus prudentes, on peut dire que l'accélération était de 50 fois ou plus. Faire pivoter un serveur verticalement coûterait 50 fois plus cher. D'autant plus qu'une fois l'optimisation effectuée, cela fonctionne tout le temps et la facture du serveur loué arrive chaque mois.

Source: habr.com