Vous n'êtes pas identifié(e).
Pages : 1
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 psqlLa 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 4309Lorsque 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.
Hors ligne
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.
Hors ligne
Que vaut le shared_buffers ?
Hors ligne
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 | userHors ligne
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.
Hors ligne
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.
Hors ligne
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.
Hors ligne
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.
Hors ligne
Pages : 1