To sidebar

dimanche 10 mai 2020

PostgreSQL & la mémoire : le swap, le tueur, l'overcommit et les grosses pages

Plongée dans le tuning mémoire d’une instance PostgreSQL, du basique work_mem aux entrailles du kernel Linux.

Le paramétrage par défaut d’un Linux n’est pas vraiment adapté à une base de données à qui on demande beaucoup. Le danger est réel qu’une ou plusieurs requêtes réclame(nt) trop de RAM d’une manière ou d’une autre. Or Linux attribue de la mémoire de manière un peu libérale, parce que beaucoup d’applications voient inutilement grand ; et puis il y a le swap, après tout. Mais, sur un serveur de base de données, la mémoire doit servir de cache pour le disque, et surtout pas l’inverse.

Pour paramétrer la mémoire partagée (shared_buffers), c’est facile, il y a la règle « 1/4 de la RAM, maximum 8-10 Go ». Mais le paramétrage de la mémoire de travail des sessions (avec work_mem) n’est pas une science exacte : entre le nombre de nœuds d’un plan, leur différents types, les processus parallèles, les erreurs d’estimation, et les produits cartésiens plus ou moins volontaires, la consommation mémoire peut partir en vrille. Le bon DBA sera donc prudent et conservateur — et supervisera la consommation mémoire ! Par défaut work_mem est à quelques dizaines de Mo, et peut être monté beaucoup plus haut au besoin, et si l’on est prudent. Si cette mémoire ne suffit pas, PostgreSQL se rattrapera sur les fichiers temporaires, toujours trop lents, mais au moins la requête se terminera. Il n’existe hélas pas de système de gestion de l’ensemble des work_mem au niveau de l’instance.

Cela ne fonctionne pas forcément sans problème du premier coup out of the box. Il y a même le cas de nombreuses requêtes individuellement gourmandes qui, en cumulé, consomment tout la mémoire. J’ai vu des serveurs mal paramétrés s’effondrer à cause de cela, alors que sacrifier juste les requêtes trop gourmandes aurait permis de maintenir le service.

Le cobaye

La machine qui me servira d’exemple est un vieux clou avec 5 Go de RAM, 1 Go de swap, Debian 9 et PostgreSQL12. Mais le comportement et le paramétrage seront les mêmes sur des monstres gorgés de mémoire. Et bien des machines virtuelles n’ont pas autant de RAM.

Config mémoire par défaut

$ cat /proc/meminfo  
 …
MemTotal:        5016896 kB
MemFree:         4506412 kB
…
SwapTotal:        974844 kB
SwapFree:         974844 kB
…
CommitLimit:     3483296 kB
Committed_AS:    1758260 kB
…
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Ce qui veut dire : 5 Go de RAM, 4,5 Go libres avant démarrage de PostgreSQL, pas d’utilisation des Huge Pages, presque 1 Go de swap, encore inutilisé. Laissons CommitLimit de côté pour le moment.

L’essentiel se retrouve ici, de manière plus lisible :

$ free -m
              total        used        free      shared  buff/cache   available
Mem:           4899         174        4400          50         324        4454
Swap:           951           0         951

Blinder le cache de PostgreSQL

Je suppose que le PostgreSQL a 1 Go de cache (shared_buffers = '1GB', redémarrage de l’instance nécessaire au besoin). Il est vide au démarrage. Pour le remplir complètement tout de suite, on crée une grosse table avec pgbench, qu’on demande à charger en RAM grâce à pg_prewarm :

 createdb  -h serveur -p 5432 pgbench
 pgbench -h serveur -p 5432 -d pgbench -i -s 200
 vacuumdb -h serveur -p 5432 -d pgbench --freeze --analyze --verbose
 psql
 # CREATE EXTENSION pg_prewarm ;
 CREATE 
 # SELECT pg_prewarm ('pgbench_accounts') ; 
  pg_prewarm 
 ──────────
  327869

327 869 blocs de 8 ko (soit 2,6 Go) ont été lus, mais pas tous conservés dans le cache de PostgreSQL (il est trop petit). Logiquement, la partie partagée de la mémoire (shared) est montée de 1 Go, pris sur available :

$ free -m
              total        used        free      shared  buff/cache   available
Mem:           4899         176         798        1074        3924        3424 
Swap:           951           0         951

Chargeons ensuite les petites tables en mémoire aussi pour finir de chauffer le cache pour les tests qui vont suivre  :

 # SELECT pg_prewarm ('pgbench_tellers') ; 
 # SELECT pg_prewarm ('pgbench_branches') ; 
 # SELECT pg_prewarm ('pgbench_history') ;

Performances

Un test pgbench rigoureux devrait durer plusieurs minutes, surtout sur un vieux tromblon comme ici, pour lisser les VACUUM, les checkpoints, l’activité de l’OS, etc. Ce qui suit se limite à des tests de 15 s et est donc totalement non scientifique, mais donne une idée :

$ for i in {1..100} ; do pgbench  -p5432-h serveur -d pgbench  -c15 -j2 -T15 2>/dev/null ; done|grep -E 'tps(.*)exclud'

Avec un disque lent, le cache de PostgreSQL et celui de l’OS jouent à plein. Cache chaud et par vent arrière, cette machine permet dépasse les 200 transactions par seconde. Ce pgbench tournera en arrière-plan dans la plus grande partie de ce qui suit.

Échauffement

Pour suivre la consommation mémoire, ouvrir un autre terminal :

 while true ; do free -m ; sleep 1 ; done

(Un sar -S -r 1 est possible aussi mais c’est moins lisible).

Demandons à PostgreSQL de trier 10 millions de lignes :

postgres=# \pset pager off
postgres=# \timing on
postgres=# SET max_parallel_workers_per_gather TO 0 ;
postgres=# SET jit TO off ;
postgres=# EXPLAIN (ANALYZE,BUFFERS,SETTINGS) SELECT i FROM generate_series(1,10000000) i ORDER BY i DESC;
                                                                 QUERY PLAN                                                                   
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Sort  (cost=1399395.34..1424395.34 rows=10000000 width=4) (actual time=10003.897..12591.954 rows=10000000 loops=1)
  Sort Key: i DESC
  Sort Method: external merge  Disk: 137008kB
  Buffers: temp read=34216 written=34233
  ->  Function Scan on generate_series i  (cost=0.00..100000.00 rows=10000000 width=4) (actual time=2182.951..4512.700 rows=10000000 loops=1)
        Buffers: temp read=17090 written=17090
Settings: jit = 'off', max_parallel_workers_per_gather = '0', work_mem = '40MB'
Planning Time: 0.061 ms
Execution Time: 13898.659 ms
(9 lignes)
Durée : 13899,705 ms (00:13,900)

Les 40 Mo de work_mem ne suffisant pas, le tri a débordé sur disque (137 Mo). Dans free -m, la colonne used est un peu monté puis redescendu. Il est quand même dommage de devoir déborder sur disque quand il y a plein de la RAM libre, on va donc monter work_mem.

Ne faites pas ça en prod

Pour l’exercice uniquement, on va aller au-delà de toute décence en autorisant le maximum de la valeur de work_mem imaginable :

SET work_mem TO '1.9995TB' ;

(Ne faites pas ça en production ! Et même si vous lisez ceci en 2035, vérifiez que PostgreSQL 28 sera capable d’en tirer parti).

Au final la requête est ici à peine plus rapide. Il n’y a plus le fichier temporaire mais 861 Mo de tri en mémoire :

Sort  (cost=1262674.84..1287674.84 rows=10000000 width=4) (actual time=8964.128..10398.453 rows=10000000 loops=1)
  Sort Key: i DESC
  Sort Method: quicksort  Memory: 861967kB
  ->  Function Scan on generate_series i  (cost=0.00..100000.00 rows=10000000 width=4) (actual time=1922.412..3464.625 rows=10000000 loops=1)
Settings: jit = 'off', max_parallel_workers_per_gather = '0', work_mem = '2047GB'
Planning Time: 0.059 ms
Execution Time: 12050.071 ms
(7 lignes)
Durée : 12051,198 ms (00:12,051)

En ce qui concerne la mémoire, on voit une pointe de consommation dans used puis un retour à la normale (la mémoire est bien rendue après la fin de la requête). Le swap ne frémit pas.

              total        used        free      shared  buff/cache   available 
Mem:           4899        1438         105        1104        3355        2124
Swap:           951           0         951
              total        used        free      shared  buff/cache   available
Mem:           4899         762         781        1104        3355        2799
Swap:           951           0         951
              total        used        free      shared  buff/cache   available
Mem:           4899         208        1335        1104        3355        3353 
Swap:           951           0         951

Torture

Trions beaucoup plus de données :

EXPLAIN (ANALYZE,BUFFERS,SETTINGS) SELECT i FROM generate_series(1,50000000) i ORDER BY i DESC;

Quelques secondes passent… et puis c’est le drame :

erreur SYSCALL SSL : EOF détecté
La connexion au serveur a été perdue. Tentative de réinitialisation : Échec.
Durée : 29998,948 ms (00:29,999)
: @!>? (standby)

La reconnexion aboutit après quelques secondes.

Post-mortem

Que s’est-il passé pendant ces 30 secondes ?

              total        used        free      shared  buff/cache   available  
Mem:           4899        1529         113        1110        3257        2027
Swap:           951           0         951
…
               total        used        free      shared  buff/cache   available
Mem:           4899        3656         105        1103        1137          37 
Swap:           951           8         943

La mémoire arrivée à saturation, le swap est donc entré en action :

              total        used        free      shared  buff/cache   available
Mem:           4899        4273          89         501         536          22 
Swap:           951         625         326
…
              total        used        free      shared  buff/cache   available
Mem:           4899        4596          90         186         212          19 
Swap:           951         951           0

Au final : saturation de la RAM utilisable et du swap. Cela ne pouvait que mal finir comme on l’a vu plus haut.

Le système dénonce un coupable : oom-killer. Ce dernier a tué un processus pour tenter de sauver le système.

$ dmesg -T
[dim. avril  5 18:24:23 2020] postgres invoked oom-killer: gfp_mask=0x24201ca(GFP_HIGHUSER_MOVABLE|__GFP_COLD), nodemask=0, order=0, oom_score_adj=0
 …
[dim. avril  5 18:24:23 2020] Mem-Info:
[dim. avril  5 18:24:23 2020] active_anon:1018624 inactive_anon:176283 isolated_anon:0
                               active_file:94 inactive_file:87 isolated_file:0
                               unevictable:2665 dirty:0 writeback:0 unstable:0
                               slab_reclaimable:5364 slab_unreclaimable:6041
                               mapped:11270 shmem:47735 pagetables:5558 bounce:0
                               free:22746 free_pcp:642 free_cma:0
…

Suit aussi une liste des processus susceptibles d’être sacrifiés pour sauver le sytème :

dim. avril  5 18:24:23 2020 pid   uid  tgid total_vm      rss nr_ptes nr_pmds swapents oom_score_adj name
dim. avril  5 18:24:23 2020 457     0   457    16741     2075      31       3       87             0 systemd-journal
dim. avril  5 18:24:23 2020 472     0   472    11890      439      24       3      552         -1000 systemd-udevd
…
dim. avril  5 18:24:23 2020 13654   119 13654   311019     9578      88       5        0          -900 postgres
dim. avril  5 18:24:23 2020 13655   119 13655   311052      813      61       5        0             0 postgres
dim. avril  5 18:24:23 2020 13660   119 13660   311019      514      59       5        0             0 postgres
dim. avril  5 18:24:23 2020 13661   119 13661   311019     2579      63       5        0             0 postgres
dim. avril  5 18:24:23 2020 13662   119 13662    36321      492      55       4        0             0 postgres
dim. avril  5 18:24:23 2020 13663   119 13663   313707      768      67       5        0             0 postgres
dim. avril  5 18:24:23 2020 14643   119 14643  1538169  1121892    2299      10        0             0 postgres
dim. avril  5 18:24:23 2020 19075  1000 19075     1833       34      10       3        0             0 sleep
dim. avril  5 18:24:23 2020 Out of memory: Kill process 14643 (postgres) score 750 or sacrifice child
dim. avril  5 18:24:23 2020 Killed process 14643 (postgres) total-vm:6152676kB, anon-rss:4484992kB, file-rss:2364kB, shmem-rss:212kB
dim. avril  5 18:24:23 2020 oom_reaper: reaped process 14643 (postgres), now anon-rss:0kB, file-rss:0kB, shmem-rss:212kB

Le premier processus postgres est le postmaster, son oom_score négatif le protège de l’OOM killer, comme certains autres processus système. Mais ses enfants ne sont pas, eux protégés. C’est le processus 14643, le backend associé à notre requête, qui est logiquement choisi par le système, vu sa consommation mémoire.

Pourquoi ce client emporte-t-il dans la mort toute l’instance PostgreSQL ? Voyons les traces de PostgreSQL. D’abord le processus 14643 est noté comme ayant été tué sans sommation (signal -9, par l’oom-killer) :

2020-04-05 18:24:24 CEST 13654: 9-1 user=,db=,app=,client= LOG:  server process (PID 14643) was terminated by signal 9: Killed
2020-04-05 18:24:24 CEST 13654: 10-1 user=,db=,app=,client= DETAIL:  Failed process was running: EXPLAIN (ANALYZE,BUFFERS,SETTINGS) SELECT i FROM generate_series(1,50000000) i ORDER BY i DESC;

Le postmaster de PostgreSQL (le processus père) ne sait pas pourquoi son processus fils a disparu, ni ce qu’il était en train de modifier. Or il y a de la mémoire partagée entre la victime et les autres processus de l’instance. Pour éviter qu’un travail non terminé n’y corrompe les données, le postmaster arrête tout brutalement :

2020-04-05 18:24:24 CEST 13654: 11-1 user=,db=,app=,client= LOG:  terminating any other active server processes
…
2020-04-05 18:24:25 CEST 13654: 12-1 user=,db=,app=,client= LOG:  all server processes terminated; reinitializing

La suite traite du passage en recovery, du rejeu des journaux depuis le dernier checkpoint valide, et de la réouverture du système.

Inutile de dire qu’une instance qui redémarre et annule au passage tous les traitements en cours est inacceptable. D’autant plus qu’il ne redémarrera pas forcément en quelques secondes s’il y a des dizaines de Go de journaux à rejouer. PostgreSQL agit ainsi parce que la corruption des données qu’on lui a confiées est encore plus inacceptable (il laisse cela aux développeurs).

J’ajoute : le problème ne se limite pas aux énormes requêtes avec des tris énormes. Une avalanche de nombreuses petites requêtes effectuant un tri en RAM peut mener au même problème. Même si elles sont unitairement raisonnablement rapides.

Et si on rajoutait plus de swap ?… En fait, non.

Le serveur est tombé après l’assèchement du swap et bien rajoutons-en ! Via swapon, nous arrivons à :

$ free -m
             total        used        free      shared  buff/cache   available
Mem:           4899         184         284        1121        4429        3302
Swap:          9143          60        9083

On relance la requête gourmande, toujours avec le work_mem délirant :

EXPLAIN (ANALYZE,BUFFERS,SETTINGS) SELECT i FROM generate_series(1,50000000) i ORDER BY i DESC;
...
 Execution Time: 304756.355 ms

Au bout de ces 5 minutes, la consommation mémoire avait atteint son pic :

              total        used        free      shared  buff/cache   available
Mem:           4899        4597         111         119         190          23
Swap:          9143        2825        6318

Notre requête se termine donc, lentement certes, en ayant tapé dans 2,6 Go de swap. Ça semble mieux.

Cependant, après le démarrage du tri, un effet très désagréable se produit pour les transactions qui tournent en parallèle :

tps = 231.544993 (excluding connections establishing)
tps = 233.958953 (excluding connections establishing)
tps = 95.440410 (excluding connections establishing)
tps = 17.352403 (excluding connections establishing)
tps = 28.886747 (excluding connections establishing)

Après la fin de notre requête gourmande, la remontée du débit de transaction est réelle mais très lente. La raison est simple : le cache de PostgreSQL est toujours là… mais il ne suffit pas, et une bonne moitié de la base était en fait dans le cache de Linux. Il a été rempli aussi lors de l’appel à pg_prewarm. Puis notre requête si gourmande en RAM a forcé la purge de ce cache (buff/cache descendu à 190 Mo), et le reconstituer ensuite est très progressif. Les performances remontent plus vite si on force la lecture complète des tables avec pg_prewarm.

Au fait, combien de temps durerait la requête si on avait utilisé les fichiers temporaires ? Avec un work_mem de 500 Mo (généreux mais bien en-deça de la RAM disponible) on obtient ce plan :

 Sort  (cost=7577451.69..7702451.69 rows=50000000 width=4) (actual time=55771.061..68988.155 rows=50000000 loops=1)
   Sort Key: i DESC
   Sort Method: external merge  Disk: 684960kB
   Buffers: shared hit=3, temp read=171070 written=171076
   ->  Function Scan on generate_series i  (cost=0.00..500000.00 rows=50000000 width=4) (actual time=12160.862..24639.870 rows=50000000 loops=1)
         Buffers: temp read=85450 written=85450
 Settings: jit = 'off', max_parallel_workers_per_gather = '0', work_mem = '500MB'
 Planning Time: 0.126 ms
 Execution Time: 76399.425 ms

Soit 1 minute 16, avec un fichier temporaire de 684 Mo, le tiers de l’écriture swap consommé : en gros, 3 fois mieux sur tous les plans. En effet, le tri sur disque est optimisé différemment, ce qui épargne des I/O appréciables. De plus, le débit du pgbench en parallèle a accusé le coup (suite à l’utilisation des disques et un CPU bien chargé) mais beaucoup moins : le cache a été mieux préservé et la machine est restée disponible.

Moralité : Ajouter du swap est une mauvaise idée. Quand il est utilisé, cela veut dire que le cache de l’OS est déjà épuisé, ce qui est désastreux pour les autres requêtes. De plus, cela ne protège pas contre une requête devant trier plus de données qu’il n’y a de swap. La machine se mettra alors à ramer pendant longtemps avant le crash.

Protection : éviter le swap

D’abord on redescend le swap à 1 Go. Le supprimer complètement est envisageable, mais il y a toujours quelques processus oubliés et inactifs qui traînent sur un serveur, autant qu’ils y finissent.

Un paramètre règle la tendance de Linux à utiliser le cache. La valeur en cours se retrouve dans le pseudo-système de fichier /proc :

cat /proc/sys/vm/swappiness 
60

Linux utilisera alors le swap de manière assez volontaire si la mémoire se raréfie. Une valeur de 10, voire 1, mènera à un swap bien plus tardif, presque en dernier recours. Ce paramètre se positionne avec sysctl : la technique la plus propre est de créer un fichier /etc/sysctl.d/99-postgresql.conf, qui a l’intérêt d’être pris en compte au reboot, et contenant ceci :

vm.swappiness=10

On recharge manuellement :

sudo sysctl --system
* Applying /etc/sysctl.d/99-postgresql.conf ...
vm.swappiness = 10

Vérification de la prise en compte :

cat /proc/sys/vm/swappiness 
10

La technique sera la même pour les autres paramètres évoqués ci-dessous.

Protection : interdire l‘overcommit

Ce qui précède n’interdit pas l’utilisation du swap en cas de manque de RAM. La manière d’éviter le massacre est de configurer l’overcommit. Il s’agit de ces valeurs vues dans /proc/meminfo :

CommitLimit:     3483296 kB
Committed_AS:    1758260 kB

Selon la doc du kernel :

CommitLimit: Based on the overcommit ratio ('vm.overcommit_ratio'),
             this is the total amount of  memory currently available to
             be allocated on the system. This limit is only adhered to
             if strict overcommit accounting is enabled (mode 2 in
             'vm.overcommit_memory').
             The CommitLimit is calculated with the following formula:
             CommitLimit = (total RAM pages - total huge TLB pages) *
                            overcommit_ratio / 100 + total swap pages
             For example, on a system with 1G of physical RAM and 7G
             of swap with a `vm.overcommit_ratio` of 30 it would
             yield a CommitLimit of 7.3G.
             For more details, see the memory overcommit documentation
             in vm/overcommit-accounting.
Committed_AS: The amount of memory presently allocated on the system.
             The committed memory is a sum of all of the memory which
             has been allocated by processes, even if it has not been
             "used" by them as of yet. A process which malloc()'s 1G
             of memory, but only touches 300M of it will show up as
             using 1G. This 1G is memory which has been "committed" to
             by the VM and can be used at any time by the allocating
             application. With strict overcommit enabled on the system
             (mode 2 in 'vm.overcommit_memory'),allocations which would
             exceed the CommitLimit (detailed above) will not be permitted.
             This is useful if one needs to guarantee that processes will
             not fail due to lack of memory once that memory has been
             successfully allocated.

Pour résumer ceci et cet autre chapitre : la valeur CommitLimit est un maximum pour Committed_AS, ie la mémoire allouée (réservée) . Si le paramètre vm.overcommit_memory est à 0 (le défaut), le contrôle est assez lâche, et un processus peut réserver beaucoup de RAM auprès du noyau (virtual space, avec malloc) alors même qu’il ne l’utilisera réellement que plus tard (utilisation de pages mémoire réelles). C’est une forme de sur-réservation, pratique pour les machines qui réservent pas mal de mémoire sans jamais toujours l’utiliser (machines Java, hyperviseur…). C’est aussi une manière d’éviter le gaspillage de la mémoire dans ces cas. Mais PostgreSQL n’est pas concerné. Le danger, on l’a vu, est d’allouer trop de mémoire qui n’existe pas, alors que PostgreSQL va réellement la consommer, et donc de déborder largement sur le swap. Par contre, CommitLimit va être le moyen de bloquer la consommation excessive de mémoire.

Pour changer ce comportement, nous allons mettre la valeur 2, qui permettra un contrôle strict. CommitLimit ne peut être défini directement mais la documentation indique qu’il est calculé ainsi (pour le moment il n’y a pas de Huge Pages) :

CommitLimit =  swap + RAM * vm.overcommit_ratio

Pour définir vm.overcommit_ratio, il faut se demander quelle valeur de CommitLimit on veut. Il faut non seulement éviter de déborder sur le swap, mais aussi préserver une partie du cache. En général on réserve pour cela un minimum de 20 %, mais cela peut se voir au cas par cas (certaines instances ont des données actives bien au-delà du cache de PostgreSQL, et le cache de l’OS doit rester très conséquent pour conserver les perfs). D’où :

 CommitLimit + Cache OS = RAM 
 Cache OS = 20% RAM

et la magie des maths en déduit :

 vm.overcommit_ratio = 80 * (1 - swap / RAM)

Donc, avec un serveur moins anémique, voire débordant de RAM, la limite tend vite vers vm.overcommit_ratio = 80. Mais pour notre petite machine ce sera :

vm.overcommit_memory = 2
vm.overcommit_ratio = 60

Après sysctl --system, CommitLimit vaut donc 1 Go (swap) + 5 Go * 60 % = 4 Go, ce que /proc/meminfo confirme :

CommitLimit:     3984980 kB

Attention : on suppose ici un serveur PostgreSQL seul sur la machine. La situation est beaucoup plus compliquée avec plusieurs logiciels dessus, dont certains qui ne supporteront pas un overcommit restrictif. C’est une situation à éviter.

Test de la protection

Il s’agit à présent de surveiller Committed_AS, la mémoire réservée :

while true ; do free -m ; grep Committed_AS /proc/meminfo ; sleep 0.1 ; done
              total        used        free      shared  buff/cache   available
Mem:           4899         188         120        1098        4590        3347
Swap:           951          82         869
Committed_AS:    1879100 kB

Relançons notre grosse requête de tri avec le work_mem démesuré. Au bout de quelques secondes, pas de miracle, elle échoue :

# EXPLAIN (ANALYZE,BUFFERS,SETTINGS) SELECT i FROM generate_series(1,50000000) i ORDER BY i DESC;
ERROR:  out of memory
DÉTAIL : Failed on request of size 536870912 in memory context "ExecutorState".

L‘overcommit atteint, l’OS a refusé une nouvelle allocation mémoire, et PostgreSQL a dû arrêter la requête. Mais la session reste ouverte, l’instance PostgreSQL est restée debout, les autres requêtes ne sont pas perturbées. En effet, on voit ci-dessous que malgré un Commited_AS presque au maximum, il restait encore pas mal de cache juste avant la fin de la requête :

              total        used        free      shared  buff/cache   available
Mem:           4899        1980         113        1098        2805        1559
Swap:           951          84         867
Committed_AS:    3719000 kB

Il est évident que ce mécanisme ne doit servir qu’à parer à des requêtes devenues folles ou des erreurs de paramétrage de work_mem, par exemple si un développeur l’a modifié ou qu’il a appris à écrire des requêtes trop complexes. Normalement, work_mem doit rester à une valeur raisonnable, et le tri sera assuré, faute de mieux, sur disque, pour que les requêtes n’échouent jamais. Mais il existe des cas problématiques : par exemple, avant PostgreSQL 13, le nœud HashAggregate qui ne devrait pas être utilisé s’il doit consommer plus que work_mem, mais ce n’est pas une limite en dur, et les statistiques et estimations sont parfois fausses…

Moralité : paramétrez l’overcommit et vos requêtes pourront partir en vrille sans provoquer une lente, douloureuse et fatale asphyxie du serveur. (Même si les fichiers temporaires ne sont pas un cadeau non plus.)

Si tous mes clients en étaient arrivés là, ce serait déjà beau.

Beaucoup de connexions

Passons à un autre problème moins grave, lié à un grand nombre de connexions sur la machine.

Après reboot, PostgreSQL arrêté, free -m renvoie ceci :

              total        used        free      shared  buff/cache   available
Mem:           4899         172        4439           8         286        4498
Swap:           951           0         951

et /proc/meminfo contient ceci :

Committed_AS:     673612 kB

Dans postgresql.conf, paramétrons max_connections à 500, une valeur sans doute trop élevée mais encore envisageable et rencontrée parfois en production. Tout le monde ne s’embête pas avec un pooler et ça peut passer si le nombre de requêtes simultanées est en phase avec le nombre de cœurs. Nous allons voir la consommation mémoire.

Après démarrage de PostgreSQL  :

              total        used        free      shared  buff/cache   available
Mem:           4899         180        4358          72         360        4425
Swap:           951           0         951
Committed_AS:    1835644 kB

La mémoire réservée a bondi d’1 Go et des poussières, ce qui est normal : PostgreSQL a réservé son cache et divers processus tournent. Par contre, le cache étant vide, la mémoire n’a pas encore été utilisée et used et shared restent très bas.

Si je charge le cache par pg_prewarm :

              total        used        free      shared  buff/cache   available
Mem:           4899         204         870        1095        3824        3374
Swap:           951           0         951
Committed_AS:    1852864 kB

Le shared est au maximum (essentiellement les shared buffers, la mémoire partagée de PostgreSQL). Le cache de l’OS (buff/cache) est plein aussi.

Si j’ouvre 500 connexions, on s’approche dangereusement du CommitLimit (3 984 980 ko, rappelons-le) :

for nb in {1..500} ; do echo $nb ; bash -c "psql -d pgbench -c 'SELECT pg_sleep(20000)' -h serveur -p 5432 & " ; done
              total        used        free      shared  buff/cache   available
Mem:           4899         936         136        1095        3825        2641
Swap:           951           0         951
Committed_AS:    3673840 kB

Soit 3600 ko réservés par session. Mais used montre que moins de la moitié a réellement été consommée.

Chargeons le cache à nouveau avec pg_prewarm. Ensuite, une requête va lire presque tout le cache :

explain (analyze, buffers) select count(*) from pgbench_accounts ;

Elle ne pose pas de problème. Puis, chacune des 500 sessions fait de même (pas en même temps !), puis reste ouverte :

 for nb in {1..500} ; do echo $nb ; s=$((10+nb*7)) ;  bash -xc "psql -d pgbench -c 'select pg_sleep ($s) ; explain (analyze, buffers) select count(*) from pgbench_accounts  ; SELECT pg_sleep(20000)' -h serveur -p 5432 &" ;  done 
              total        used        free      shared  buff/cache   available
Mem:           4899        1978          99        1138        2822        1551
Swap:           951          23         928
Committed_AS:    3763656 kB

La mémoire réservée reste similaire… mais le used monte de 1 Go ! évidemment aux dépens du cache, ce qui n’est pas bon du tout. Les sessions idle ne sont pourtant pas censées consommer quoi que ce soit.

Pour expliquer cela, réouvrons une seule session. À son ouverture, le backend associé sur le serveur a ces propriétés :

$ sudo cat  /proc/11710/status 
Name:   postgres
...
RssAnon:            2532 kB
RssFile:            9048 kB
RssShmem:           6628 kB --> mémoire partagée visitée
VmData:     2788 kB
VmStk:       132 kB
VmExe:      7624 kB
VmLib:     46132 kB
VmPTE:       280 kB  --> à noter
VmPMD:        16 kB
VmSwap:        0 kB
HugetlbPages:          0 kB
...

Après avoir lu tout le cache avec la requête précédente (qui affiche un Seq Scan et notamment Buffers: shared hit=130493 read=204423, soit 1 Go lu dans le cache de PostgreSQL, et 1,6 Go dans celui de l’OS ou sur disque ), on recommence. Certains compteurs ont monté parce que tout le cache de PostgreSQL a été lu :

RssShmem:        1066556 kB

D’autres ont peu ou pas bougé, mais l’un notamment a augmenté d’environ 2 Mo :

VmPTE:      2336 kB

Or 2 Mo x 500 connexions représente justement le gigagoctet disparu.

VmPTE

D’après la documentation, VmPTE représente la size of page table entries, soit la taille de la table des pages où le noyau mappe la mémoire virtuelle du processus sur la mémoire physique réelle. Au fur et à mesure qu’un processus backend de PostgreSQL demande des pages de mémoire partagée, l’adresse réelle est stockée dans ce cache. Les 2 Mo atteints finalement par le PTE listent donc les 262 000 entrées de la TLB (c’est-à-dire le gigaoctet de mémoire partagée, à raison d’une entrée par page de 4 ko), soit tout de même 8 octets par page (logique sur un processeur 64 bits). Et comme apparemment il s’agit de mémoire kernel, cela n’entre pas dans la comptabilité du CommitLimit.

Il existe une ligne de /proc/meminfo sommant l’ensemble de ces utilisations de TLB. Sa valeur explose entre le moment où PostgreSQL a démarré, celui où les 500 sessions sont ouvertes et celui où elles ont fini la requête :

PageTables:        13648 kB
PageTables:       169708 kB
PageTables:      1193040 kB

On retrouve notre gigaoctet perdu. La documentation désigne PageTables comme Amount of memory dedicated to the lowest level of page tables. This can increase to a high value if a lot of processes are attached to the same shared memory segment. — exactement notre cas.

Une fois une session terminée, son processus backend associé sur le serveur disparaît et la mémoire de cette TLB est libérée.

J’insiste : l’exemple ci-desus est un peu extrême, avec un nombre trop important de sessions touchant toutes à la quasi-totalité de la mémoire partagée et ne se terminant pas. J’ajoute que le phénomène est accéléré par le pg_prewarm. Cependant les connexions de très longue durée ne sont pas rares dans le monde réel, et j’ai pu reproduire en partie ce gonflement de PageTables avec un simple pgbench -c500 et de la patience. Sur des serveurs mieux dotés avec un shared_buffers de 8 Go, la table des pages gonfle proportionnellement : au pire on gaspille 16 Mo par connexion ouverte.

Il existe une parade : les Huge Pages.

Huge Pages

Sous Linux, les pages de mémoire de Linux sont par défaut à 4 ko, ce qui donne des tables de translation d’adresse TLB assez imposantes si on manipule beaucoup de données, comme on vient de le voir.

Les Huge Pages sont des pages beaucoup plus grandes, de 2 Mo voire 1 Go, ce qui permet de réduire la taille occupée par tous ces TLB (et accessoirement de réduire un peu le temps perdu à les administrer). Pour les détails, voir kernel.org.

Caveat Transparent Huge Pages

Attention, il existe une version dynamique des Huge Pages, les Transparent Hugepages (THP), qui impliquent que le kernel va chercher à en créer à la volée, et le mener à défragmenter la mémoire… ce qui peut générer de la latence. Toutes les documentations déconseillent donc les THP pour les bases de données même si certains sont en désaccord pour des noyaux récents (voir par exemple ce test, ou cet exemple de mesure avec perf). Bref, sauf à tester avec une charge réelle que les THP sont utiles dans la situation de production, on se limitera aux Huge Pages classiques décrites plus bas et on désactivera les THP.

Leur paramétrage en cours se voit ainsi :

$ cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise never
$ cat /sys/kernel/mm/transparent_hugepage/defrag
always defer madvise never

Un moyen « bourrin » mais efficace de les désactiver dès le démarrage est de modifier /etc/crontab :

@reboot                root    echo never > /sys/kernel/mm/transparent_hugepage/enabled
@reboot                root    echo never > /sys/kernel/mm/transparent_hugepage/defrag

On peut aussi définir GRUB_CMDLINE_LINUX_DEFAULT="(...) transparent_hugepage=never" mais pour ma part, moins je touche à grub, mieux je me porte. Les fans de la sur-configuration peuvent aussi faire un timer systemd.

Au final, on vérifie que les THP ne sont pas utilisées avec la ligne AnonHugePages de /proc/meminfo : elle doit valoir 0 kB.

Calcul du nombre de Huge Pages

Ne parlons que des Huge Pages de 2 Mo, les plus simples à mettre en place. (1 Go est possible mais là encore on doit modifier grub, pour un gain relativement plus faible — surtout ici. shared_buffers ne monte généralement qu’à quelques Go).

Il faut définir le nombre de Huge Pages à créer. Elles ne concerneront que la mémoire utilisée par le postmaster, essentiellement les shared buffers donc. La documentation de PostgreSQL fournit la formule rigoureuse :

sudo pmap $(sudo -iu postgres head -1 /var/lib/postgresql/12/main/postmaster.pid) | awk '/rw-s/ && /zero/ {print $2}'
1119376K

Soit 1 Go, ce qu’on divise par 2048 ko (la huge page) et on arrondit un peu par sécurité, d’où 550 pages de 2 Mo (1,1 Go) pour un shared_buffers de 1 Go (et typiquement 4500 pages pour un shared_buffers à 8 Go).

Si on suit toujours la documentation de PostgreSQL, il faudrait alors définir vm.nr_hugepages=550 dans /etc/sysctl.d/99-postgresql.conf, ce qui correspond à des Huge Pages persistantes. Elles seront affectées d’entrée au démarrage et donc bloquées. Ça peut donc poser problème dans quelques rares cas, comme une réduction de shared_buffers ou une erreur de calcul du nombre de pages.

L’alternative que je préfère sont les Huge Pages dynamiques : elles ne seront utilisées que si l’application le demande, dans la limite indiquée. De plus, en cas d’échec d’allocation (mémoire bien fragmentée…), on repasse aux pages normales sans gaspillage de RAM. (Il paraît que ce paramètre ne fonctionne pas pour des pages de 1 Go).

 vm.nr_hugepages=0
 vm.nr_overcommit_hugepages=550

Dans certains cas tordus, on pourrait mélanger les deux paramètres.

Mise en place des Huge Pages

PostgreSQL doit savoir utiliser les Huge Pages. Par défaut, le paramètre huge_pages vaut try, ce qui le permet , et aussi de démarrer s’il n’y en a pas. La valeur on peut servir pour ne pas démarrer pas en cas d’échec de l’utilisation des Huge Pages pour une raison ou une autre.

Il faut adapter aussi l‘overcommit : les Huge Pages doivent être retranchées de la RAM qui sert à son calcul (voir plus haut la doc du kernel sur le calcul de CommitLimit). Donc avec 1 Go de swap, 5 Go de RAM dont 1,1 Go de Huge Pages, pour conserver environ 20 % de la RAM physique pour le cache, on calcule :

CommitLimit = swap + (RAM - HP) * vm.overcommit 
CommitLimit + HP + Cache OS = RAM 
Cache OS = 20% RAM

d’où :

vm.overcommit = (80 % * RAM - swap - HP) / (RAM-HP)

et dans notre cas :

vm.overcommit_ratio = 50

CommitLimit descendra alors à 2920092 kB, soit presque 3 Go. Avec 1,1 Go de Huge Pages, restera environ 1 Go pour l’OS et son cache, donc les 20 % voulus.

Avant de charger ces paramètres par systctl --system, il est conseillé d’arrêter PostgreSQL. La première raison est que réduire le CommitLimit sur une machine déjà chargée peut tout bloquer :

$ cat /proc/meminfo 
-bash: fork: Ne peut allouer de la mémoire

La seconde est que les Huge Pages ne peuvent être attribuées que si la mémoire n’est pas fragmentée. En pratique, autant rebooter pour vérifier que les paramètres sont bien pris en compte.

Après reboot, PostgreSQL démarré :

              total        used        free      shared  buff/cache   available
Mem:           4899        1270        3322           7         306        3392
Swap:           951           0         951
Committed_AS:     643992 kB

Noter que le Committed_AS ne prend pas les Huge Pages en compte (heureusement, puisque CommitLimit non plus).

/proc/meminfo affiche entre autres :

AnonHugePages:         0 kB
HugePages_Total:     547
HugePages_Free:      515

Donc il n’y a pas de Transparent Huge Pages, et 1 Go est déjà « occupé » (used) par les Huges Pages dynamiques, même si l’essentiel est encore libre.

Après chauffage du cache avec pg_prewarm : pas de changement notable, si ce n’est que les Huge Pages demandées sont utilisées par les shared buffers :

              total        used        free      shared  buff/cache   available
Mem:           4899        1274         642          39        2982        3352
Swap:           951           0         951
HugePages_Total:     547
HugePages_Free:        4

Ouvrons une session :

 cat /proc/19471/status

qui contiendra notamment :

VmPTE:       220 kB
HugetlbPages:     135168 kB

Le VmPTE est un rien meilleur qu’auparavant. Puis la requête habituelle lit toute la table pgbench_accounts :

RssShmem:              0 kB
VmPTE:       224 kB
VmSwap:        0 kB
HugetlbPages:    1177600 kB

Le VMPTE évolue à peine, alors que toutes les Huge Pages du cache de PostgreSQL ont été lues. C’est normal, il n’y a plus guère besoin que de (1 Go / 2 Mo/page) = 512 entrées de 8 octets (= 4 ko) dans la PTE.

Reprenons le scénario précédent. Après la connexion de 500 sessions inactives :

              total        used        free      shared  buff/cache   available
Mem:           4899        1964         303          60        2631        2644
Swap:           951          26         925

Puis après exécution des 500 requêtes  :

              total        used        free      shared  buff/cache   available
Mem:           4899        2033         209          60        2656        2573
Swap:           951          26         925
Committed_AS:    2649024 kB
PageTables:       125564 kB

Là encore le Committed_AS est resté stable, mais le used aussi. La VmPTE n’ayant pas bougé, cette fois, la quantité de RAM indirectement consommée par chaque session est restée statique. PageTables le reflète et est resté raisonnable.

Est-ce que cela change quelque chose pour les autres sessions ? Le CommitLimit bloquera une requête tirant trop sur la RAM ni plus ni moins vite qu’avant. Par contre, la différence en cache peut jouer : on parle ici de 1 Go sur 5 ! Avec une machine à 32 Go de RAM, un shared_buffers à 8 Go, on perdrait encore un quart de la RAM dans ce cas un peu extrême.

Le gain des Huge Pages est donc essentiellement en RAM, qui peut se refléter sur les performances, essentiellement grâce au cache. Mais un impact direct sur les performances peut aussi se rencontrer (comme décrit dans cet excellent article de rjuju).

Conclusion - Résumé

  • Le swap est contre-productif sur un serveur de base de données. La mémoire doit servir au cache et au tri en mémoire. Au besoin, le tri doit déborder dans des fichiers temporaires de tri, pas dans le swap.
  • Le swap sur un serveur dédié à PostgreSQL doit être dimensionné au minimum (1 ou 2 Go).
  • Il doit être découragé :
vm.swappiness = 10
  • L’overcommit sert à limiter strictement la mémoire utilisée, et protéger le cache. Sur une machine bien dotée en RAM où on veut en préserver 20 % pour l’OS et son cache :
vm.overcommit_memory = 2
vm.overcommit_ratio = 80
  • Les Huge Pages peuvent servir à récupérer un peu de mémoire s’il y a beaucoup de sessions longues. Penser à désactiver les Transparent Huge Pages. Il faudra peut-être réduire vm.overcommit_ratio.
 vm.nr_hugepages = 4500        # shared buffers de 8 Go
 vm.nr_overcommit_hugepages = 0
 vm.overcommit_ratio = ?       # à recalculer, inférieur à celui sans Huge Pages
  • Et on supervise la consommation mémoire !

PS : Merci à mes collègues, plutôt mes maîtres, qui m’ont par le passé aiguillé dans cette jungle ! ainsi qu’à ioguix pour la relecture)

© SQL & éléphant, after the WP Dusk To Dawn theme Propulsé par Dotclear