Ajustement des paramètres mémoire de PostgreSQL pour des performances optimales

Je me suis récemment plongé dans les paramètres mémoire de PostgreSQL, cherchant des moyens de les ajuster et d'améliorer les performances. Après avoir exploré de nombreuses ressources et effectué quelques tests, j'ai trouvé quelques paramètres clés qui peuvent faire une grande différence.

Si vous utilisez PostgreSQL et souhaitez optimiser son utilisation de la mémoire, voici quelques paramètres importants auxquels prêter attention.

Paramètres mémoire clés à ajuster

Décomposons les paramètres mémoire les plus importants de PostgreSQL que vous devriez envisager d'ajuster.

1. shared_buffers

Considérez shared_buffers comme la réserve de mémoire personnelle de PostgreSQL où il conserve les données fréquemment accessibles. L'idée est de stocker autant de vos données en mémoire que possible pour réduire ces coûteuses lectures disque.

Paramètre recommandé :

La plupart des experts suggèrent de le régler à environ 25 % de la RAM totale de votre système.

Par exemple, si vous avez 16 Go de RAM, le régler à environ 4 Go est un bon point de départ.

2. work_mem

Chaque fois que PostgreSQL traite des requêtes impliquant le tri, la jointure ou le regroupement de données, il utilise work_mem. Ce paramètre contrôle combien de mémoire chaque opération obtient avant de commencer à écrire des fichiers temporaires sur le disque.

Exemple :

• Si work_mem = 50 Mo et que votre requête a 5 tâches de tri, elle pourrait utiliser 50 Mo x 5 = 250 Mo de mémoire.
• Maintenant, imaginez 10 utilisateurs exécutant des requêtes similaires – cela fait 250 Mo x 10 = 2,5 Go de mémoire utilisée.

Conseil :

• Si vous le réglez trop bas, les requêtes pourraient ralentir en raison de l'utilisation du disque.
• Si vous le réglez trop haut, vous pourriez manquer rapidement de mémoire, surtout avec de nombreux utilisateurs.

Parfois, des valeurs work_mem plus élevées peuvent en fait ralentir les choses car PostgreSQL doit gérer de gros blocs de mémoire.

3. effective_cache_size

Ce paramètre est un peu différent – il n'alloue pas réellement de mémoire mais indique à PostgreSQL combien de mémoire il pense pouvoir utiliser à partir du cache OS. PostgreSQL utilise cette valeur pour décider s'il doit utiliser des index ou effectuer des scans séquentiels.

Le régler incorrectement :

• Trop bas ? PostgreSQL pourrait éviter les index, pensant que la mémoire est rare.
• Trop haut ? PostgreSQL pourrait devenir trop optimiste et prendre de mauvaises décisions.

Une bonne règle de base : Réglez cela à 50-75 % de votre RAM totale, surtout si PostgreSQL est la charge principale sur la machine.

4. maintenance_work_mem

Celui-ci concerne la quantité de mémoire que PostgreSQL peut utiliser pour des tâches comme VACUUM, CREATE INDEX et d'autres tâches de maintenance. Augmenter cette valeur peut accélérer ces opérations sans ralentir l'activité normale de la base de données.

Paramètre suggéré :

Si vous avez de la place, augmentez-le à 1 Go ou plus pour que les tâches de maintenance soient rapides.

Façons de modifier les paramètres mémoire

PostgreSQL permet de modifier les paramètres mémoire de plusieurs manières, selon vos besoins et votre tolérance au risque. Voici les approches les plus efficaces :

1. Modification des paramètres mémoire globalement

Pour des changements permanents à travers toutes les sessions et requêtes, modifiez le fichier de configuration de PostgreSQL (postgresql.conf) ou utilisez ALTER SYSTEM :

ALTER SYSTEM SET work_mem = '500MB';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '20GB';
SELECT pg_reload_conf();  -- Appliquer les changements sans redémarrage

2. Changement des paramètres par session

Pour des ajustements temporaires qui n'affectent que la connexion actuelle :

SET work_mem = '100MB';
SET effective_cache_size = '16GB';

Mon exemple réel d'ajustement de la mémoire PostgreSQL

Scénario ##o

Une base de données PostgreSQL 10 fonctionnant sur un serveur de 30 Go de RAM, avec souvent 15 Go de RAM disponibles, rencontrait des performances de requête d'exportation CSV lentes lors du traitement de transactions financières. Une requête complexe joignant quatre grandes tables prenait 33 secondes pour produire des résultats :

SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Tailles des tables :

• finance_transaction (~1 million de lignes)
• transaction_log (~1 million de lignes)
• transaction_audit_history (~4 millions de lignes)
• billing_record (~1 million de lignes)

Paramètres mémoire par défaut :

Plan d'exécution de la requête (EXPLAIN ANALYZE)

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=26603.645..33465.648 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=26817.703..29038.177 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=18471.362..19081.998 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..42.297 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=18468.587..18468.589 rows=1299656 loops=5)
                    Buckets: 4096  Batches: 512  Memory Usage: 708kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=9094.933..15042.241 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.019..133.507 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=9092.767..9092.768 rows=1300948 loops=5)
                                Buckets: 16384  Batches: 128  Memory Usage: 2199kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.005..143.568 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=8267.360..8267.360 rows=4852654 loops=5)
              Buckets: 32768  Batches: 512  Memory Usage: 2059kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.020..789.242 rows=4852654 loops=5)
Planning time: 0.346 ms
Execution time: 33586.541 ms

Analysez le plan d'exécution de la requête :

Après avoir augmenté les paramètres mémoire, voici le résultat de EXPLAIN ANALYZE :

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=16228.246..23884.145 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=17226.338..20072.376 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=14289.482..15561.150 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..37.096 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=14287.210..14287.212 rows=1299656 loops=5)
                    Buckets: 524288  Batches: 8  Memory Usage: 46914kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=3728.706..6986.704 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.018..123.814 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=3704.309..3704.309 rows=1300948 loops=5)
                                Buckets: 2097152  Batches: 2  Memory Usage: 151889kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.018..139.211 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=2927.466..2927.466 rows=4852654 loops=5)
              Buckets: 2097152  Batches: 4  Memory Usage: 246002kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.025..630.852 rows=4852654 loops=5)
Planning time: 0.341 ms
Execution time: 24013.565 ms

Comparaison des performances : Avant vs Après l'augmentation des paramètres mémoire

Choisir les bons paramètres mémoire : Une approche au cas par cas

Ajuster les paramètres mémoire de PostgreSQL n'est pas une solution universelle. Cela dépend de la taille de votre base de données, des utilisateurs concurrents et des modèles de requêtes. Trouver le bon work_mem, shared_buffers et effective_cache_size.

• Si votre serveur a beaucoup de RAM libre, vous pouvez allouer plus de mémoire à PostgreSQL.
• Si vous avez beaucoup d'utilisateurs concurrents, allouer trop de mémoire par requête peut causer une instabilité du système.
• Exécutez EXPLAIN ANALYZE sur les requêtes lentes pour identifier les débordements disque et les inefficacités des jointures par hachage.

Si vous souhaitez tester des changements en toute sécurité sans affecter l'ensemble du système, appliquez les paramètres par requête au lieu de globalement :

✅ Changez les paramètres pour une seule requête lourde :

SET LOCAL work_mem = '300MB';
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Conclusion

Ajuster les paramètres mémoire de PostgreSQL peut faire une énorme différence en termes de performances, aidant vos requêtes à s'exécuter plus rapidement et à réduire les I/O disque inutiles. L'essentiel est de trouver le bon équilibre en fonction de la charge de votre base de données, de la RAM disponible et du comportement de vos requêtes.

Points clés à retenir :

• Connaissez votre charge de travail – Ne vous contentez pas d'augmenter aveuglément les paramètres mémoire ; analysez d'abord comment vos requêtes s'exécutent.
• Utilisez EXPLAIN ANALYZE – Cela vous aidera à repérer des problèmes comme les débordements disque et les jointures inefficaces.
• Ajustez work_mem avec précaution – Une valeur plus élevée peut rendre les jointures et le tri plus rapides, mais un réglage trop élevé peut consommer toute votre RAM.
• Changez les paramètres au bon niveau – Utilisez des paramètres globaux pour des améliorations générales, des paramètres de session pour des ajustements temporaires, et des paramètres par requête pour des opérations vraiment lourdes.
• Surveillez l'impact – Vérifiez les journaux, suivez les performances et ajustez au besoin.

Au final, le tuning de PostgreSQL est partie science, partie expérimentation. Commencez petit, suivez les résultats et ajustez au fur et à mesure. Avec les bons paramètres, votre base de données fonctionnera plus harmonieusement, plus rapidement et de manière beaucoup plus efficace. 🚀