Optimisation des performances PostgreSQL : la puissance de work_mem

Briefing

Il y a des années, j'ai été chargé de résoudre un problème de performances dans un système critique pour l'entreprise dans laquelle je travaillais. C'était un défi difficile, des nuits blanches et encore plus de perte de cheveux. Le backend utilisait PostgreSQL, et après beaucoup d'efforts et de recherches, la solution s'est avérée être aussi simple qu'une seule ligne :

ALTER USER foo SET work_mem='32MB';

Maintenant, pour être honnête, cela pourrait ou non résoudre votre problème de performances immédiatement. Cela dépend fortement de vos modèles de requêtes et de la charge de travail de votre système. Cependant, si vous êtes un développeur backend, j'espère que cet article ajoutera un autre outil à votre arsenal pour résoudre les problèmes, notamment avec PostgreSQL ?

Dans cet article, nous allons créer un scénario pour simuler la dégradation des performances et explorer quelques outils pour étudier le problème, comme EXPLAIN, k6 pour les tests de charge et même une plongée dans le code source de PostgreSQL. Je partagerai également quelques articles pour vous aider à résoudre des problèmes connexes.

• ➡️ dépôt github avec l'implémentation complète

Étude de cas

Créons un système simple pour analyser les performances des joueurs de football. Pour l'instant, la seule règle métier est de répondre à cette question :

• Qui sont les N joueurs les plus impliqués dans les buts ?

Le SQL suivant crée notre modèle de données et le remplit :

CREATE TABLE players (
    player_id SERIAL PRIMARY KEY,
    nationality TEXT,
    age INT,
    position TEXT
);

CREATE TABLE matches (
    match_id SERIAL PRIMARY KEY,
    match_date DATE,
    home_team TEXT,
    away_team TEXT
);

CREATE TABLE player_stats (
    player_stat_id SERIAL PRIMARY KEY,
    player_id INT REFERENCES players(player_id),
    match_id INT REFERENCES matches(match_id),
    goals INT,
    assists INT,
    minutes_played INT
);

-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
    ('Country' || (1 + random()*100)::int),  -- 100 different nationalities
    (18 + random()*20)::int,                 -- Ages between 18 and 38
    (ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);

Le script pour initialiser et remplir la base de données est disponible dans le dépôt github.

Oui, nous pourrions concevoir une base de données pour améliorer les performances du système, mais l'objectif principal ici est d'explorer des scénarios non optimisés. Croyez-moi, vous rencontrerez probablement des systèmes comme celui-ci, dans lesquels de mauvais choix de conception initiale ou une croissance inattendue nécessitent des efforts importants pour améliorer les performances.

Déboguer le problème

Pour simuler le problème lié à la configuration work_mem, créons une requête pour répondre à cette question : Quels sont les 2000 meilleurs joueurs qui contribuent le plus aux objectifs ?

SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
FROM player_stats ps
JOIN players p ON ps.player_id = p.player_id
GROUP BY p.player_id
ORDER BY total_score DESC
LIMIT 2000;

Très bien, mais comment pouvons-nous identifier les goulots d'étranglement dans cette requête ? Comme d'autres SGBD, PostgreSQL prend en charge la commande EXPLAIN, qui nous aide à comprendre chaque étape exécutée par le planificateur de requêtes (optimisée ou non).

Nous pouvons analyser des détails tels que :

• Quel type de scan a été utilisé ? Analyse d'index, analyse d'index uniquement, analyse séquentielle, etc.
• Quel indice a été utilisé, et dans quelles conditions ?
• S’il s’agit d’un tri, quel type d’algorithme a été utilisé ? Repose-t-il entièrement sur la mémoire ou nécessite-t-il l'utilisation du disque ?
• L'utilisation de tampons partagés.
• Estimation du temps d'exécution.

Vous pouvez en savoir plus sur le planificateur/optimiseur PostgreSQL ici :

• documentation officielle
• pganalyze - bases de la planification des requêtes postgres
• cybertec - comment interpréter postgresql expliquer

Parler n'est pas cher

Parler ne coûte pas cher, alors passons à un exemple pratique. Tout d’abord, nous allons réduire le work_mem à sa plus petite valeur possible, soit 64 Ko, telle que définie dans le code source :

ALTER USER foo SET work_mem='32MB';

Ensuite, analysons le résultat de la commande EXPLAIN :

CREATE TABLE players (
    player_id SERIAL PRIMARY KEY,
    nationality TEXT,
    age INT,
    position TEXT
);

CREATE TABLE matches (
    match_id SERIAL PRIMARY KEY,
    match_date DATE,
    home_team TEXT,
    away_team TEXT
);

CREATE TABLE player_stats (
    player_stat_id SERIAL PRIMARY KEY,
    player_id INT REFERENCES players(player_id),
    match_id INT REFERENCES matches(match_id),
    goals INT,
    assists INT,
    minutes_played INT
);

-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
    ('Country' || (1 + random()*100)::int),  -- 100 different nationalities
    (18 + random()*20)::int,                 -- Ages between 18 and 38
    (ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);

Nous pouvons voir que le temps d'exécution était de 82,718 ms, et l'Algorithme de tri utilisé était une fusion externe. Cet algorithme s'appuie sur le disque plutôt que sur la mémoire, car les données ont dépassé la limite work_mem de 64 Ko.

Pour votre information, le module tuplesort.c signale quand l'algorithme de tri utilisera le disque en définissant l'état sur SORTEDONTAPE sur cette ligne. Les interactions disque sont gérées par le module logtape.c.

Si vous êtes une personne visuelle (comme moi), il existe des outils qui peuvent vous aider à comprendre le résultat EXPLAIN, tels que https://explain.dalibo.com/. Vous trouverez ci-dessous un exemple montrant un nœud avec l'étape de tri, y compris des détails tels que Méthode de tri : fusion externe et Espace de tri utilisé : 2,2 Mo :

La section « Statistiques » est particulièrement utile pour analyser des requêtes plus complexes, car elle fournit des détails sur le temps d'exécution pour chaque nœud de requête. Dans notre exemple, il met en évidence un temps d'exécution étrangement élevé (près de 42 ms) dans l'un des nœuds de tri :

• Vous pouvez visualiser et analyser ce plan de requête ici : https://explain.dalibo.com/plan/2gd0a8c8fab6a532#stats

Comme le montre la sortie EXPLAIN, l'une des principales raisons du problème de performances est le nœud de tri utilisant le disque. Un effet secondaire de ce problème, en particulier dans les systèmes avec des charges de travail élevées, est des pics dans les métriques d'E/S d'écriture (j'espère que vous les surveillez ; sinon, bonne chance lorsque vous en aurez besoin !). Et oui, même les requêtes en lecture seule peuvent provoquer des pics d'écriture, car l'algorithme de tri écrit les données dans des fichiers temporaires.

Solution

Lorsque nous exécutons la même requête avec work_mem=4MB (valeur par défaut dans PostgreSQL), le temps d'exécution diminue de plus de 50 %.

SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
FROM player_stats ps
JOIN players p ON ps.player_id = p.player_id
GROUP BY p.player_id
ORDER BY total_score DESC
LIMIT 2000;

• Pour une analyse visuelle, consultez ce lien : https://explain.dalibo.com/plan/b094ec2f1cfg44f6#

Dans cette sortie EXPLAIN, l'un des nœuds de tri utilise désormais un algorithme en mémoire, heapsort. Pour le contexte, le planificateur opte pour le tri en tas uniquement lorsque son exécution est moins coûteuse que le tri rapide. Vous pouvez approfondir le processus de prise de décision dans le code source de PostgreSQL.

De plus, le deuxième nœud Sort, qui représentait auparavant près de 40 ms de temps d'exécution, disparaît entièrement du plan d'exécution. Ce changement se produit car le planificateur sélectionne désormais un HashJoin au lieu d'un MergeJoin, car l'opération de hachage tient dans la mémoire, consommant environ 480 Ko.

Pour plus de détails sur les algorithmes de jointure, consultez ces articles :

• Algorithme HashJoin
• Algorithme de fusion et de jointure

Impact sur l'API

Le work_mem par défaut n'est pas toujours suffisant pour gérer la charge de travail de votre système. Vous pouvez ajuster cette valeur au niveau de l'utilisateur en utilisant :

ALTER USER foo SET work_mem='32MB';

Remarque : Si vous utilisez un pool de connexions ou un pooler de connexions, il est important de recycler les anciennes sessions pour que la nouvelle configuration prenne effet.

Vous pouvez également contrôler cette configuration au niveau des transactions de la base de données. Exécutons une API simple pour comprendre et mesurer l'impact des modifications work_mem à l'aide des tests de charge avec k6 :

• k6-test.js
  

  CREATE TABLE players (
      player_id SERIAL PRIMARY KEY,
      nationality TEXT,
      age INT,
      position TEXT
  );
  
  CREATE TABLE matches (
      match_id SERIAL PRIMARY KEY,
      match_date DATE,
      home_team TEXT,
      away_team TEXT
  );
  
  CREATE TABLE player_stats (
      player_stat_id SERIAL PRIMARY KEY,
      player_id INT REFERENCES players(player_id),
      match_id INT REFERENCES matches(match_id),
      goals INT,
      assists INT,
      minutes_played INT
  );
  
  -- Populate players with a range of nationalities, ages, and positions
  INSERT INTO players (nationality, age, position)
  SELECT
      ('Country' || (1 + random()*100)::int),  -- 100 different nationalities
      (18 + random()*20)::int,                 -- Ages between 18 and 38
      (ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
  FROM generate_series(1, 10000);
  

L'API a été implémentée dans Go et expose deux points de terminaison qui exécutent la requête avec différentes configurations work_mem :

• main.go
  

  SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score
  FROM player_stats ps
  JOIN players p ON ps.player_id = p.player_id
  GROUP BY p.player_id
  ORDER BY total_score DESC
  LIMIT 2000;
  

Vous trouverez ci-dessous le fichier docker-compose contenant toutes les dépendances nécessaires pour exécuter le test de charge :

• docker-compose.yaml
  

      /*
       * workMem is forced to be at least 64KB, the current minimum valid value
       * for the work_mem GUC.  This is a defense against parallel sort callers
       * that divide out memory among many workers in a way that leaves each
       * with very little memory.
       */
      state->allowedMem = Max(workMem, 64) * (int64) 1024;
  

Nous pouvons définir la variable d'environnement ENDPOINT pour définir le scénario à tester : /low-work-mem ou /optimized-work-mem. Exécutez le test en utilisant : docker compose up --abort-on-container-exit. Pour cet exemple, j'ai utilisé Docker version 20.10.22.

• Test ENDPOINT : /low-work-mem - work_mem=64kB
  

  BEGIN; -- 1. Initialize a transaction.
  
  SET LOCAL work_mem = '64kB'; -- 2. Change work_mem at transaction level, another running transactions at the same session will have the default value(4MB).
  
  SHOW work_mem; -- 3. Check the modified work_mem value.
  
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -- 4. Run explain with options that help us to analyses and indetifies bottlenecks. 
  SELECT 
      p.player_id, 
      SUM(ps.goals + ps.assists) AS total_score 
  FROM 
      player_stats ps
  INNER JOIN 
      players p ON p.player_id = ps.player_id
  GROUP BY 
      p.player_id
  ORDER BY 
      total_score DESC
  LIMIT 2000;
  --
  
  QUERY PLAN                                                                                                                                                          |
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  Limit  (cost=18978.96..18983.96 rows=2000 width=12) (actual time=81.589..81.840 rows=2000 loops=1)                                                                  |
    Output: p.player_id, (sum((ps.goals + ps.assists)))                                                                                                               |
    Buffers: shared hit=667, temp read=860 written=977                                                                                                                |
    ->  Sort  (cost=18978.96..19003.96 rows=10000 width=12) (actual time=81.587..81.724 rows=2000 loops=1)                                                            |
          Output: p.player_id, (sum((ps.goals + ps.assists)))                                                                                                         |
          Sort Key: (sum((ps.goals + ps.assists))) DESC                                                                                                               |
          Sort Method: external merge  Disk: 280kB                                                                                                                    |
          Buffers: shared hit=667, temp read=860 written=977                                                                                                          |
          ->  GroupAggregate  (cost=15076.66..17971.58 rows=10000 width=12) (actual time=40.293..79.264 rows=9998 loops=1)                                            |
                Output: p.player_id, sum((ps.goals + ps.assists))                                                                                                     |
                Group Key: p.player_id                                                                                                                                |
                Buffers: shared hit=667, temp read=816 written=900                                                                                                    |
                ->  Merge Join  (cost=15076.66..17121.58 rows=100000 width=12) (actual time=40.281..71.313 rows=100000 loops=1)                                       |
                      Output: p.player_id, ps.goals, ps.assists                                                                                                       |
                      Merge Cond: (p.player_id = ps.player_id)                                                                                                        |
                      Buffers: shared hit=667, temp read=816 written=900                                                                                              |
                      ->  Index Only Scan using players_pkey on public.players p  (cost=0.29..270.29 rows=10000 width=4) (actual time=0.025..1.014 rows=10000 loops=1)|
                            Output: p.player_id                                                                                                                       |
                            Heap Fetches: 0                                                                                                                           |
                            Buffers: shared hit=30                                                                                                                    |
                      ->  Materialize  (cost=15076.32..15576.32 rows=100000 width=12) (actual time=40.250..57.942 rows=100000 loops=1)                                |
                            Output: ps.goals, ps.assists, ps.player_id                                                                                                |
                            Buffers: shared hit=637, temp read=816 written=900                                                                                        |
                            ->  Sort  (cost=15076.32..15326.32 rows=100000 width=12) (actual time=40.247..49.339 rows=100000 loops=1)                                 |
                                  Output: ps.goals, ps.assists, ps.player_id                                                                                          |
                                  Sort Key: ps.player_id                                                                                                              |
                                  Sort Method: external merge  Disk: 2208kB                                                                                           |
                                  Buffers: shared hit=637, temp read=816 written=900                                                                                  |
                                  ->  Seq Scan on public.player_stats ps  (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.011..8.378 rows=100000 loops=1)    |
                                        Output: ps.goals, ps.assists, ps.player_id                                                                                    |
                                        Buffers: shared hit=637                                                                                                       |
  Planning:                                                                                                                                                           |
    Buffers: shared hit=6                                                                                                                                             |
  Planning Time: 0.309 ms                                                                                                                                             |
  Execution Time: 82.718 ms                                                                                                                                    |
  
  COMMIT; -- 5. You can also execute a ROLLBACK, in case you want to analyze queries like INSERT, UPDATE and DELETE.
  

• Test ENDPOINT : /optimized-work-mem - work_mem=4MB
  

  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) 
  SELECT 
      p.player_id, 
      SUM(ps.goals + ps.assists) AS total_score 
  FROM 
      player_stats ps
  INNER JOIN 
      players p ON p.player_id = ps.player_id
  GROUP BY 
      p.player_id
  ORDER BY 
      total_score DESC
  LIMIT 2000;
  --
  QUERY PLAN                                                                                                                                          |
  ----------------------------------------------------------------------------------------------------------------------------------------------------+
  Limit  (cost=3646.90..3651.90 rows=2000 width=12) (actual time=41.672..41.871 rows=2000 loops=1)                                                    |
    Output: p.player_id, (sum((ps.goals + ps.assists)))                                                                                               |
    Buffers: shared hit=711                                                                                                                           |
    ->  Sort  (cost=3646.90..3671.90 rows=10000 width=12) (actual time=41.670..41.758 rows=2000 loops=1)                                              |
          Output: p.player_id, (sum((ps.goals + ps.assists)))                                                                                         |
          Sort Key: (sum((ps.goals + ps.assists))) DESC                                                                                               |
          Sort Method: top-N heapsort  Memory: 227kB                                                                                                  |
          Buffers: shared hit=711                                                                                                                     |
          ->  HashAggregate  (cost=2948.61..3048.61 rows=10000 width=12) (actual time=38.760..40.073 rows=9998 loops=1)                               |
                Output: p.player_id, sum((ps.goals + ps.assists))                                                                                     |
                Group Key: p.player_id                                                                                                                |
                Batches: 1  Memory Usage: 1169kB                                                                                                      |
                Buffers: shared hit=711                                                                                                               |
                ->  Hash Join  (cost=299.00..2198.61 rows=100000 width=12) (actual time=2.322..24.273 rows=100000 loops=1)                            |
                      Output: p.player_id, ps.goals, ps.assists                                                                                       |
                      Inner Unique: true                                                                                                              |
                      Hash Cond: (ps.player_id = p.player_id)                                                                                         |
                      Buffers: shared hit=711                                                                                                         |
                      ->  Seq Scan on public.player_stats ps  (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.008..4.831 rows=100000 loops=1)|
                            Output: ps.player_stat_id, ps.player_id, ps.match_id, ps.goals, ps.assists, ps.minutes_played                             |
                            Buffers: shared hit=637                                                                                                   |
                      ->  Hash  (cost=174.00..174.00 rows=10000 width=4) (actual time=2.298..2.299 rows=10000 loops=1)                                |
                            Output: p.player_id                                                                                                       |
                            Buckets: 16384  Batches: 1  Memory Usage: 480kB                                                                           |
                            Buffers: shared hit=74                                                                                                    |
                            ->  Seq Scan on public.players p  (cost=0.00..174.00 rows=10000 width=4) (actual time=0.004..0.944 rows=10000 loops=1)    |
                                  Output: p.player_id                                                                                                 |
                                  Buffers: shared hit=74                                                                                              |
  Planning:                                                                                                                                           |
    Buffers: shared hit=6                                                                                                                             |
  Planning Time: 0.236 ms                                                                                                                             |
  Execution Time: 41.998 ms                                                                                                                           |
  

Les résultats démontrent que le point de terminaison avec un work_mem plus élevé a surpassé celui avec une configuration inférieure. La latence p90 a chuté de plus de 43 ms et le débit s'est considérablement amélioré sous la charge de travail de test.

Si les métriques centiles sont nouvelles pour vous, je vous recommande de les étudier et de les comprendre. Ces mesures sont incroyablement utiles pour guider les analyses de performances. Voici quelques ressources pour vous aider à démarrer :

• temps de réponse k6
• p90 contre p99

Conclusion

Après avoir rêvé du problème, vous être réveillé plusieurs fois pour essayer de nouvelles solutions et enfin avoir découvert que work_mem pouvait aider, le prochain défi consiste à trouver la bonne valeur pour cette configuration. ?

La valeur par défaut de 4 Mo pour work_mem, comme de nombreux autres paramètres PostgreSQL, est conservatrice. Cela permet à PostgreSQL de fonctionner sur des machines plus petites avec une puissance de calcul limitée. Cependant, nous devons faire attention à ne pas planter l'instance PostgreSQL avec des erreurs de mémoire insuffisante. Une seule requête, si elle est suffisamment complexe, peut consommer plusieurs fois la mémoire spécifiée par work_mem, en fonction du nombre d'opérations comme les tris, les jointures de fusion, les jointures de hachage (influencées par hash_mem_multiplier), et plus encore. Comme indiqué dans la documentation officielle :

il est nécessaire de garder ce fait à l'esprit lors du choix de la valeur. Les opérations de tri sont utilisées pour les jointures ORDER BY, DISTINCT et de fusion. Les tables de hachage sont utilisées dans les jointures de hachage, l'agrégation basée sur le hachage, la mémorisation des nœuds et le traitement basé sur le hachage des sous-requêtes IN.

Malheureusement, il n’existe pas de formule magique pour définir work_mem. Cela dépend de la mémoire disponible de votre système, de la charge de travail et des modèles de requêtes. L'équipe TimescaleDB dispose d'un outil de réglage automatique et le sujet est largement discuté. Voici quelques excellentes ressources pour vous guider :

• Tout ce que vous savez sur work_mem est faux
• Comment dois-je régler work_mem pour un système donné

Mais en fin de compte, à mon humble avis, la réponse est : TEST. TESTEZ AUJOURD'HUI. TESTEZ DEMAIN. TESTEZ POUR TOUJOURS. Continuez à tester jusqu'à ce que vous trouviez une valeur acceptable pour votre cas d'utilisation qui améliore les performances des requêtes sans faire exploser votre base de données. ?

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!