{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimisation des requ\u00eates de base de donn\u00e9es sur l'exemple d'un service B2B pour les constructeurs","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Comment multiplier par 10 le nombre de requ\u00eates \u00e0 une base de donn\u00e9es sans changer pour un serveur plus performant tout en maintenant le bon fonctionnement du syst\u00e8me ? Je vais vous raconter comment nous avons lutt\u00e9 contre la baisse de performance de notre base de donn\u00e9es, comment nous avons optimis\u00e9 les requ\u00eates SQL pour servir le plus grand nombre d'utilisateurs possible sans augmenter les co\u00fbts des ressources de calcul.
\n<\/a><\/noindex>
\nJe d\u00e9veloppe un service de gestion des processus d'affaires pour les entreprises de construction. Environ 3 000 entreprises travaillent avec nous. Plus de 10 000 personnes utilisent notre syst\u00e8me chaque jour pendant 4 \u00e0 10 heures. Il r\u00e9sout divers probl\u00e8mes de planification, d'alerte, de notification, de validation\u2026 Nous utilisons PostgreSQL 9.6. Nous avons environ 300 tables dans notre base de donn\u00e9es et jusqu'\u00e0 200 millions de requ\u00eates y parviennent chaque jour (10 000 diff\u00e9rentes). En moyenne, nous traitons 3 000 \u00e0 4 000 requ\u00eates par seconde, et dans les moments les plus actifs, plus de 10 000 requ\u00eates par seconde. La plupart des requ\u00eates sont des OLAP. Les ajouts, modifications et suppressions sont beaucoup moins fr\u00e9quents, ce qui signifie que la charge OLTP est relativement faible. J'ai pr\u00e9sent\u00e9 tous ces chiffres pour que vous puissiez \u00e9valuer l'ampleur de notre projet et comprendre \u00e0 quel point notre exp\u00e9rience peut vous \u00eatre utile.<\/p>\n

Premi\u00e8re image. Lyrique<\/h3>\n

\nLorsque nous avons commenc\u00e9 le d\u00e9veloppement, nous ne pensions pas vraiment \u00e0 la charge qui p\u00e8serait sur la base de donn\u00e9es et ce que nous ferions si le serveur cessait de fonctionner. Lors de la conception de la base de donn\u00e9es, nous avons suivi des recommandations g\u00e9n\u00e9rales et avons essay\u00e9 de ne pas nous tirer une balle dans le pied, mais au-del\u00e0 des conseils g\u00e9n\u00e9raux tels que \u00ab ne pas utiliser le mod\u00e8le Entity Attribute Values<\/a><\/noindex> nous n'avons pas approfondi. Nous avons con\u00e7u en nous basant sur les principes de normalisation, \u00e9vitant la redondance des donn\u00e9es, sans nous soucier d'acc\u00e9l\u00e9rer certaines requ\u00eates. D\u00e8s que les premiers utilisateurs sont arriv\u00e9s, nous avons \u00e9t\u00e9 confront\u00e9s \u00e0 des probl\u00e8mes de performance. Comme d'habitude, nous \u00e9tions totalement mal pr\u00e9par\u00e9s. Les premiers probl\u00e8mes \u00e9taient simples. En r\u00e8gle g\u00e9n\u00e9rale, il suffisait d'ajouter un nouvel index. Mais il est arriv\u00e9 un moment o\u00f9 ces solutions simples ont cess\u00e9 de fonctionner. R\u00e9alisant que nous manquions d'exp\u00e9rience et que nous avions de plus en plus de mal \u00e0 comprendre la cause des probl\u00e8mes, nous avons engag\u00e9 des sp\u00e9cialistes qui nous ont aid\u00e9s \u00e0 configurer correctement le serveur, \u00e0 connecter le monitoring et \u00e0 nous montrer o\u00f9 regarder pour obtenir statistiques<\/a><\/noindex>.<\/p>\n

Deuxi\u00e8me image. Statistique<\/h3>\n

\nNous avons environ 10 000 requ\u00eates diff\u00e9rentes qui s'ex\u00e9cutent sur notre base de donn\u00e9es chaque jour. Parmi ces 10 000, il y a des monstres qui s'ex\u00e9cutent 2 \u00e0 3 millions de fois avec un temps d'ex\u00e9cution moyen de 0,1 \u00e0 0,3 ms, ainsi que des requ\u00eates avec un temps d'ex\u00e9cution moyen de 30 secondes, invoqu\u00e9es 100 fois par jour.<\/p>\n

Il n'\u00e9tait pas possible d'optimiser les 10 000 requ\u00eates, alors nous avons d\u00e9cid\u00e9 de d\u00e9terminer o\u00f9 concentrer nos efforts pour am\u00e9liorer correctement les performances de la base de donn\u00e9es. Apr\u00e8s plusieurs it\u00e9rations, nous avons commenc\u00e9 \u00e0 classer les requ\u00eates par types.<\/p>\n

REQU\u00caTES TOP<\/h4>\n

\nCe sont les requ\u00eates les plus lourdes qui prennent le plus de temps (temps total). Ce sont des requ\u00eates qui sont soit tr\u00e8s souvent appel\u00e9es, soit des requ\u00eates qui prennent beaucoup de temps \u00e0 s'ex\u00e9cuter (les requ\u00eates longues et fr\u00e9quentes avaient d\u00e9j\u00e0 \u00e9t\u00e9 optimis\u00e9es lors des premi\u00e8res it\u00e9rations pour gagner en vitesse). Au final, le serveur passe le plus de temps \u00e0 les ex\u00e9cuter. Il est \u00e9galement important de s\u00e9parer les requ\u00eates top par le temps d'ex\u00e9cution total et par le temps IO. Les m\u00e9thodes d'optimisation de ces requ\u00eates sont l\u00e9g\u00e8rement diff\u00e9rentes.<\/p>\n

La pratique courante de toutes les entreprises est de travailler sur les REQU\u00caTES TOP. Elles sont peu nombreuses, et l'optimisation d'une seule requ\u00eate peut lib\u00e9rer 5 \u00e0 10 % des ressources. Cependant, \u00e0 mesure que le projet \u00ab grandit \u00bb, l'optimisation des REQU\u00caTES TOP devient une t\u00e2che de plus en plus complexe. Tous les moyens simples ont d\u00e9j\u00e0 \u00e9t\u00e9 \u00e9prouv\u00e9s, et la requ\u00eate la plus \u00ab lourde \u00bb n'accapare \u00ab que \u00bb 3 \u00e0 5 % des ressources. Si les REQU\u00caTES TOP occupent moins de 30 \u00e0 40 % du temps total, vous avez probablement d\u00e9j\u00e0 travaill\u00e9 pour qu'elles fonctionnent rapidement et il est temps de passer \u00e0 l'optimisation des requ\u00eates du groupe suivant.
\nIl reste \u00e0 r\u00e9pondre \u00e0 la question de combien de requ\u00eates sup\u00e9rieures inclure dans ce groupe. Je prends g\u00e9n\u00e9ralement pas moins de 10, mais pas plus de 20. J'essaie de m'assurer que le temps d'ex\u00e9cution de la premi\u00e8re et de la derni\u00e8re requ\u00eate dans le groupe TOP ne diff\u00e8re pas de plus de 10 fois. C'est-\u00e0-dire que si le temps d'ex\u00e9cution des requ\u00eates chute brusquement de la premi\u00e8re \u00e0 la dixi\u00e8me place, je prends le TOP-10, si la chute est plus progressive, j'augmente la taille du groupe \u00e0 15 ou 20.
\n\"Optimisation
\n<\/p>\n

Requ\u00eates interm\u00e9diaires (medium)<\/h4>\n

\nCe sont toutes les requ\u00eates qui viennent juste apr\u00e8s les REQU\u00caTES TOP, \u00e0 l'exception des 5 \u00e0 10 % derni\u00e8res. L'optimisation de ces requ\u00eates rec\u00e8le souvent la possibilit\u00e9 d'augmenter consid\u00e9rablement les performances du serveur. Ces requ\u00eates peuvent repr\u00e9senter jusqu'\u00e0 80 %. Mais m\u00eame si leur part d\u00e9passe 50 %, il est temps de les examiner de plus pr\u00e8s.<\/p>\n

Queue (tail)<\/h4>\n

\nComme mentionn\u00e9, ces requ\u00eates arrivent \u00e0 la fin et prennent 5 \u00e0 10 % de notre temps. On peut les oublier, sauf si vous utilisez des outils d'analyse de requ\u00eates automatiques, alors leur optimisation peut aussi co\u00fbter peu.<\/p>\n

Comment \u00e9valuer chaque groupe ?<\/p>\n

J'utilise une requ\u00eate SQL qui permet de faire cette \u00e9valuation pour PostgreSQL (je suis s\u00fbr qu'il est possible d'\u00e9crire une requ\u00eate similaire pour de nombreux autres SGBD).<\/p>\n

Requ\u00eate SQL pour \u00e9valuer la taille des groupes TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
Le r\u00e9sultat de la requ\u00eate comporte trois colonnes, chacune contenant le pourcentage de temps consacr\u00e9 au traitement des requ\u00eates de ce groupe. \u00c0 l'int\u00e9rieur de la requ\u00eate, il y a deux nombres (dans mon cas, 20 et 800) qui s\u00e9parent les requ\u00eates d'un groupe \u00e0 l'autre.<\/p>\n
Voici comment se rapportaient les parts des requ\u00eates au d\u00e9but de l'optimisation et maintenant.<\/p>\n
\"Optimisation
\n
\nLe diagramme montre que la part des requ\u00eates TOP a fortement diminu\u00e9, tandis que celle des \"moyennes\" a augment\u00e9.
\nAu d\u00e9part, les erreurs \u00e9videntes figuraient dans les requ\u00eates TOP. Avec le temps, les maladies de jeunesse ont disparu, la part des requ\u00eates TOP a diminu\u00e9 et il a fallu fournir de plus en plus d'efforts pour acc\u00e9l\u00e9rer les requ\u00eates lourdes. <\/p>\n
Pour obtenir le texte des requ\u00eates, nous utilisons cette requ\u00eate.<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Voici une liste des techniques les plus souvent utilis\u00e9es qui nous ont aid\u00e9s \u00e0 acc\u00e9l\u00e9rer les requ\u00eates TOP :<\/p>\n