Forums PostgreSQL.fr

Neofit

Membre

Fuite mémoire psql?

Bonjour,

Je suis DBA Oracle, mais on m'a demandé de regarder un problème que l'on a sur une base Postgres. Mes connaissance en postgres sont très limitées, et je vais donc chercher des conseils chez les pros.

On a une base postgres 8.4.0 sur laquelle un serveur d'applis fait du transactionnel, et des batches qui tournent la nuit. Depuis quelque temps ces batches nocturnes "mettent la machine à genoux": toutes les ressources sont consommées par une requête, le machine rame, les threads du serveur d'appli tombent en timeout et le plantent, il faut relancer ce dernier après le passage du batch. Voici pour les symptomes.

J'ai trouvé la requête qui cause tout ça, et arrive à reproduire le phénomène sur la machine de recette qui est isoprod (8GB RAM, 2xIntel(R) Xeon(R) CPU E5504  @ 2.00GHz = 8 coeurs, RHEL 5.3). La requête n'est pas compliquée, sur deux tables moyennement grosses. Un .sql est lancé dans psql, on extrait en csv tout le contenu d'une table en l'enrichissant avec le contenu d'une autre, soit 17M de lignes en jointure externe avec 124.000 lignes. Ceci génère un csv de 1.2 Go en 20 minutes.

Lors de l'exécution je vois que la RAM occupée par psql explose:

  PID USER      VIRT SWAP  RES CODE DATA  SHR %CPU %MEM    TIME+  S COMMAND
30849 postgres  181m 126m  54m 4776  35m  24m 51.1  0.7   4:58.62 D postgres
30847 postgres 5857m 359m 5.4g  304 5.7g 1700  2.4 68.9   0:12.40 D psql
30771 postgres 12868  11m 1256   56  616  824  0.6  0.0   0:01.71 R top
 1067 postgres  147m 144m 3624 4776 2576 2388  0.0  0.0   0:00.00 S postgres

à comparer à ça avant le lancement de la requête:

  PID USER      VIRT SWAP  RES CODE DATA  SHR %CPU %MEM    TIME+  S COMMAND
30847 postgres 52936  49m 2196  304  700 1728  0.0  0.0   0:00.00 S psql

La composante DATA monte jusqu'à 7.9Go.

La machine commence à swapper dur:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1 10 3702352  27168  25976 518012  139 3368  7100  3471 1633 3382 13  1 63 23  0
 2  7 3709728  27428   6928 492160  179 3496  6543  3671 1608 11107 13  2 49 37  0
 1  7 3717468  28092   6676 441920  160 3569  6995  3680 1590 21876 13  2 48 38  0

$ free -m
             total       used       free     shared    buffers     cached
Mem:          7980       7955         24          0          5        282
-/+ buffers/cache:       7667        312
Swap:         8001       3691       4309

Lorsque la machine est dans cet état il faut une quinzaine de secondes pour se logger, pour faire un ls dans un répertoire avec 10 fichiers, etc.

D'où ma question: est-ce le fonctionnement normal de psql? Quand Oracle exécute une requête de ce type, et que sqlplus, l'équivalent de psql, se connecte à la base, oracle crée un processus séparé pour cette connexion, et tous les tris et jointures sont effectués par ce processus utilisateur dans un pool partagé, en dehors de sqlplus lui-même. Et si la jointure ou tri ont besoin de plus de place, la base swappe les morceaux dans le tablespace temporaire. Si un process a besoin de 8Go pour une jointure il ne va pas demander 8Go de RAM. D'ailleurs j'ai évidemment fait le test, j'ai importé les deux tables dans une base oracle 11g, la requête s'est terminée en 2 mins en ne consommant qu'un coeur "sans en bouger d'autres", pas d'explosion de RAM ou quoi que ce soit de menaçant.

D'après ce que j'ai lu de postgres, il est sensé fonctionner de la même façon. J'ai ouvert pour cette requête un work_mem de 300M, et normalement, que ce buffer soit alloué à psql ou partagé parmi les processes "postgres", si la jointure a besoin de plus de RAM elle devrait swapper dans des fichiers temporaires, pas saturer la RAM et laisser l'OS s'occuper du swap.

D'où question: est-ce une fuite mémoire de psql? Si oui, jusqu'où devrait-on patcher sans trop perturber la prod ni déclencher un chantier de non-régression?

Merci.

Marc Cousin

Membre

Re : Fuite mémoire psql?

Par défaut, psql met tout en RAM avant de l'afficher, pour pouvoir paginer l'affichage. C'est pas un problème de postgres, c'est un problème de psql.

On peut en savoir plus sur ce que vous faites ? (il y a des moyens de s'en sortir, à partir du moment où on sait ce que vous faîtes exactement).

Le mieux évidemment, ça serait le script que vous passez à psql.

---

Marc.

Postgres.0

Membre

Re : Fuite mémoire psql?

Que vaut le shared_buffers ?

Neofit

Membre

Re : Fuite mémoire psql?

Merci pour les réponses.

Ici on ne demande as à psql de paginer mais d'être un dumb terminal et juste nous sortir le résultat du select dans un fichier. Que faut-il faire pour changer le défaut de psql et qu'il arrête de bufferiser tout avant de paginer? J'ai essayé "\pset pager off" au cas où, mais ce n'est pas ça.

La requête, si l'on enlève la grosse liste de colonnes et les prédicats, se résume à:

with select_annonce as (
select *, max_datecreation
       from (
              select *,
                     max( DATECREATION) over ( partition by NUMERO ) AS max_datecreation
                     FROM prod.ANNONCE_UNITAIRE
            ) vue_annonce
       where vue_annonce.DATECREATION = vue_annonce.max_datecreation
)
select *
from select_annonce right outer join prod.REFERENTIEL_COURANT select_referentiel on (select_annonce.NUMERO = select_referentiel.NUMERO)
;

                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=14642.17..1751367.72 rows=17901284 width=851)
   Hash Cond: ((select_referentiel.numero)::text = (select_annonce.numero)::text)
   CTE select_annonce
     ->  Subquery Scan vue_annonce  (cost=0.00..14623.90 rows=562 width=744)
           Filter: (vue_annonce.datecreation = vue_annonce.max_datecreation)
           ->  WindowAgg  (cost=0.00..13218.23 rows=112454 width=426)
                 ->  Index Scan using ak_annonce_unitaire on annonce_unitaire  (cost=0.00..11531.42 rows=112454 width=426)
   ->  Seq Scan on referentiel_courant select_referentiel  (cost=0.00..438876.84 rows=17901284 width=99)
   ->  Hash  (cost=11.24..11.24 rows=562 width=752)
         ->  CTE Scan on select_annonce  (cost=0.00..11.24 rows=562 width=752)
(10 rows)

Les params de RAM (quoique je ne vois pas ce qu'ils ont a avoir avec psql qui explose):

             name             | setting | unit |  context   
------------------------------+---------+------+------------
 default_statistics_target    | 100     |      | user
 effective_cache_size         | 16384   | 8kB  | user
 maintenance_work_mem         | 16384   | kB   | user
 max_stack_depth              | 2048    | kB   | superuser
 random_page_cost             | 4       |      | user
 shared_buffers               | 4096    | 8kB  | postmaster
 temp_buffers                 | 1024    | 8kB  | user
 track_activity_query_size    | 1024    |      | postmaster
 work_mem                     | 1024    | kB   | user

Neofit

Membre

Re : Fuite mémoire psql?

Vu qu'apparemment c'est un problème de psql, j'ai essayé autre chose: enrober la requête dans un COPY.  Ca marche sans aucun problème, 170 sec sans exploser la RAM, comme il se doit. Il se trouve qu'ici c'est ce qu'il nous faut. Mais je trouve étrange que psql jour le "smart terminal" et bufférise tout le result set. J'aimerais quand même savoir comment on pourrait l'éviter.

Marc Cousin

Membre

Re : Fuite mémoire psql?

Oui, utiliser COPY c'est une des solutions. L'autre c'est de positionner la variable FETCH_COUNT pour indiquer le nombre d'enregistrements à récupérer à la fois. On peut le positionner dans le .psqlrc ou en entête de script. Par exemple:

\set FETCH_COUNT=10000

mon_ordre_sql_de_la_mort

Ça demandera à psql de récupérer en mémoire les enregistrements 10000 par 10000 avant de les écrire.

Habituellement, j'ai un FETCH_COUNT positionné dans mon .psqlrc pour ne pas être embêté. Le seul détail qui me gêne quelquefois, c'est que la taille des colonnes, dans l'affichage, change tous les FETCH_COUNT… ce qui est logique, puisqu'il ne connait pas dès le départ les tailles maxi de chaque colonne.

---

Marc.

Neofit

Membre

Re : Fuite mémoire psql?

Le FETCH_COUNT est une bonne idée, merci pour ça, mais ça ne marche pas avec ma requête. D'après ce que j'ai trouvé sur le sujet, ça ne régule pas juste une taille de tableau de fetch comme le "arraysize" de sqlplus de Oracle (comme je le pensais au début), mais ça déclenche toute une fonctionnilté, ou plutôt une "usine à gaz" pour pallier à l'autre usine à gaz qu'est la bufférisation de tout le resultset . Ca crée un curseur implicite autour de la requête, mais ça ne marche pas à tous les coups. Notamment si la requête contient un WITH. Le WITH n'est pas pris en compte avec la 8.4, et j'ai essayé aujourd'hui, pas avec la 9.2.2 non plus.

Par contre si je ré-écris la requête sans le WITH, là le FETCH_COUNT marche et psql ne sature plus la RAM.

Merci pour le FETCH_COUNT.

Marc Cousin

Membre

Re : Fuite mémoire psql?

Tiens, oui, je ne connaissais pas cette limitation de psql, c'est intéressant.

Il y a donc encore un espoir, si vraiment vous vous trouvez dans cette situation… c'est de faire vous même le curseur, en écrivant une appli cliente, en perl, python, java… qui extraie le résultat pour vous.

---

Marc.