Optimisation des requêtes de base de données sur l'exemple de service B2B pour les constructeurs

Comment faire croître 10 fois le nombre de requêtes de base de données sans passer à un serveur plus efficace et maintenir le système en marche? Je vais vous dire comment nous avons lutté avec la dégradation 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 fais un service de gestion des processus d'affaires dans les entreprises de construction. Environ 3 000 entreprises travaillent avec nous. Plus de 10 000 personnes travaillent quotidiennement avec notre système pendant 4 à 10 heures. Il résout diverses tâches de planification, alertes, avertissements, validations ... Nous utilisons PostgreSQL 9.6. Nous avons environ 300 tables dans la base de données et chaque jour, jusqu'à 200 millions de demandes (10 000 différentes) lui sont envoyées. En moyenne, nous avons 3 à 4 000 demandes par seconde, dans les moments les plus actifs, plus de 10 000 demandes par seconde. La plupart des demandes sont OLAP. Il y a beaucoup moins d'ajouts, de modifications et de suppressions, c'est-à-dire que la charge OLTP est relativement faible. J'ai donné tous ces chiffres afin que vous puissiez évaluer la portée de notre projet et comprendre comment notre expérience peut vous être utile.

La première photo. Lyrique

Lorsque nous avons commencé le développement, nous ne pensions pas vraiment au type de charge qui tomberait 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 dans le pied, mais au-delà des conseils généraux tels que «n'utilisez pas le modèle de valeurs d'attribut d'entité , nous n'y sommes pas allés. Conçu sur la base des principes de normalisation évitant la redondance des données et ne se souciant pas 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 étaient simples. En règle générale, tout a été décidé en ajoutant un nouvel index. Mais il est arrivé un moment où de simples correctifs ne fonctionnaient plus. Ayant réalisé qu'il n'y a pas assez d'expérience et qu'il devient de plus en plus difficile de comprendre quelle est la cause des problèmes, nous avons embauché des spécialistes qui nous ont aidés à configurer correctement le serveur, à connecter la surveillance, à montrer où chercher pour obtenir des statistiques .

La deuxième photo. Statistiques

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

Il n'a pas été possible d'optimiser les 10 000 requêtes, nous avons donc décidé de déterminer où diriger les 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.

TOP requêtes

Ce sont les requêtes les plus difficiles qui prennent le plus de temps (temps total). Ce sont des requêtes qui sont soit appelées très souvent, soit des requêtes qui prennent très longtemps (les requêtes longues et fréquentes ont été optimisées dès les premières itérations de la lutte pour la vitesse). En conséquence, le serveur passe le plus de temps à leur exécution au total. De plus, il est important de séparer les requêtes principales par le temps d'exécution total et séparément par le temps d'E / S. Les moyens d'optimiser ces requêtes sont légèrement différents.

La pratique habituelle de toutes les entreprises est de traiter les demandes TOP. Il y en a peu, l'optimisation d'une seule demande peut libérer jusqu'à 5 à 10% des ressources. Cependant, à mesure que le projet vieillit, l'optimisation des requêtes TOP devient une tâche de plus en plus simple. Toutes les méthodes simples ont déjà été élaborées, et même la demande la plus «difficile» 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 qu'elles fonctionnent rapidement et il est temps de passer à l'optimisation des requêtes du groupe suivant.
Il reste à répondre à la question du nombre de requêtes principales à inclure dans ce groupe. Je prends habituellement pas moins de 10, mais pas plus de 20. J'essaie de faire en sorte que l'heure du premier et du dernier dans le groupe TOP ne diffère pas plus de 10 fois. Autrement dit, si le temps d'exécution de la requête chute fortement de 1 à 10, alors je prends TOP-10, si la chute est plus fluide, alors j'augmente la taille du groupe à 15 ou 20.

Paysans moyens (moyen)

Ce sont toutes des demandes qui vont immédiatement après TOP, à l'exception des 5-10% derniers. Habituellement, dans l'optimisation de ces demandes particulières réside la possibilité d'augmenter considérablement les performances du serveur. Ces requêtes peuvent «peser» jusqu'à 80%. Mais même si leur part a dépassé 50%, il est temps de les regarder de plus près.

Queue

Comme cela a été dit, ces demandes vont à 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 aide à faire une telle évaluation pour PostgreSQL (je suis sûr que pour de nombreux autres SGBD, vous pouvez écrire une requête similaire)


Le résultat de la requête est composé de trois colonnes, chacune contenant un pourcentage du temps consacré au traitement des demandes de ce groupe. Dans la requête, il y a deux nombres (dans mon cas, 20 et 800) qui séparent les demandes d'un groupe d'un autre.

C'est ainsi que les proportions de demandes au moment du démarrage des travaux d'optimisation sont à peu près en corrélation.



Le diagramme montre que la part des demandes TOP a fortement diminué, mais les «paysans moyens» ont augmenté.
Initialement, les erreurs TOP ont touché les requêtes TOP. Au fil du temps, les maladies infantiles ont disparu, la part des demandes TOP a été réduite et davantage d'efforts ont dû être faits pour accélérer les demandes difficiles.


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

• Refonte des systèmes, par exemple, traitement de la logique de notification sur le courtier de messages au lieu des requêtes de base de données périodiques
• Ajout ou modification d'index
• Réécrire les requêtes ORM en SQL pur
• Réécrire la logique de chargement des données paresseuses
• Mise en cache via la dénormalisation des données. Par exemple, nous avons un lien entre les tables Livraison -> Facture -> Demande -> Demande. En d'autres termes, chaque livraison est associée à l'application via d'autres tables. Afin de ne pas lier toutes les tables dans chaque demande, nous avons dupliqué le lien vers l'application dans la table de livraison.
• Mise en cache des tables statiques avec des répertoires et rarement des tables changeantes dans la mémoire du programme.

Parfois, les changements entraînaient une refonte impressionnante, mais ils donnaient 5 à 10% du déchargement du système et étaient justifiés. Au fil du temps, l'échappement est devenu de moins en moins, et la refonte devait être de plus en plus sérieuse.

Nous avons ensuite attiré l'attention sur le deuxième groupe de demandes - le groupe des paysans moyens. Il a beaucoup plus de demandes et il semblait que cela prendrait beaucoup de temps pour analyser l'ensemble du groupe. Cependant, la plupart des requêtes se sont avérées très simples pour l'optimisation, et de nombreux problèmes ont été répétés des dizaines de fois dans différentes variantes. Voici des exemples d'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 avec COUNT et une analyse complète de la table, EXISTS
  
• Nous nous sommes débarrassés 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 requête 10 à 100 fois).
  
  Par exemple, au lieu d'interroger pour sélectionner tous les pilotes sur une grande table de livraison (LIVRAISON)
  
  

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

  
  a fait une demande pour une table PERSON relativement petite
  
  

   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élative, mais cela donne une accélération de plus de 10 fois.
  
• Dans de nombreux cas, COUNT et
  remplacé par le calcul de la valeur approximative
  
• au lieu de
  
  

   UPPER(s) LIKE JOHN%' 

  
  utiliser
  
  

   s ILIKE “John%” 

  
  

Chaque demande spécifique a parfois été accélérée de 3 à 1 000 fois. Malgré les performances impressionnantes, au début, il nous semblait inutile d'optimiser la requête, qui était exécutée pendant 10 ms, incluse dans la 3e centaine des requêtes les plus lourdes et dans le temps total de chargement de la base de données, elle prenait des centièmes de pour cent. Mais en appliquant la même recette à un groupe de demandes similaires, nous avons regagné plusieurs pour cent. Afin de ne pas perdre de temps à visualiser manuellement les centaines de requêtes, nous avons écrit plusieurs scripts simples qui, à l'aide d'expressions régulières, ont trouvé des requêtes similaires. Par conséquent, la recherche automatique de groupes de requêtes nous a permis d'améliorer encore nos performances en déployant des efforts modestes.

En conséquence, nous travaillons sur le même matériel depuis trois ans maintenant. La charge journalière moyenne est d'environ 30%, aux pointes elle atteint 70%. Le nombre de demandes ainsi que le nombre d'utilisateurs a augmenté d'environ 10 fois. Et tout cela grâce au suivi constant de ces mêmes groupes de requêtes 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 passons en revue le groupe MEDIUM une fois par semaine à l'aide de scripts d'analyse de requête. Si vous rencontrez de nouvelles demandes que nous savons déjà optimiser, nous les modifions rapidement. Parfois, nous trouvons de nouvelles méthodes d'optimisation qui peuvent être appliquées à plusieurs requêtes à la fois.

Selon nos prévisions, le serveur actuel supportera une augmentation du nombre d'utilisateurs de 3 à 5 fois. Certes, nous avons un atout de plus dans la pochette; nous n'avons toujours pas traduit les requêtes SELECT dans le miroir, comme recommandé. Mais nous ne le faisons pas consciemment, car nous voulons d'abord épuiser pleinement les possibilités d'optimisation «intelligente» avant d'activer «l'artillerie lourde».
Un examen critique du travail effectué peut suggérer l'utilisation d'une mise à l'échelle verticale. Achetez un serveur plus puissant, au lieu de perdre le temps des spécialistes. Le serveur peut ne pas coûter si cher, d'autant plus que les limites de la mise à l'échelle verticale n'ont pas encore été épuisées. Cependant, seul le nombre de demandes a augmenté 10 fois. Depuis plusieurs années, la fonctionnalité du système a augmenté et il existe désormais plus de variétés de demandes. La fonctionnalité qui était due à la mise en cache est réalisée par moins de demandes, de plus, des demandes plus efficaces. Vous pouvez donc multiplier en toute sécurité par 5 autres pour obtenir le coefficient d'accélération réel. Ainsi, selon les estimations les plus prudentes, nous pouvons dire que l'accélération a été de 50 fois ou plus. Secouer verticalement le serveur 50 fois coûterait plus cher. Surtout si l'on considère qu'une fois l'optimisation est effectuée tout le temps, et une facture pour un serveur loué vient chaque mois.