Forums PostgreSQL.fr

jdig

Membre

Utilisation de la mémoire

Bonjour,

J'ai installé postgresql9.5 sur un serveur CentOS avec 32Go de RAM
Au niveau de postgresql.conf j'ai modifié temp_buffers : 8Go, shared_buffer : 8Go ...
Mais qd je lance une requête sur de grosses tables (10-50 millions de lignes), la mémoire n'est quasiment pas utilisée et tout se passe sur les accès disques

Je ne trouve pas le bon paramètre pour forcer postgresql à travailler en mémoire autant que possible ...

Je vous remercie par avance de votre aide sur le sujet,
Bonne journée
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

Bonjour,

Pouvez vous nous donner le résultat de cette requête dans votre instance postgresql :

select name, setting, unit from pg_settings where name like '%buffer%';

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Voici le résultat de la requete :
"shared_buffers";"1048576";"8kB"
"temp_buffers";"1048576";"8kB"
"wal_buffers";"2048";"8kB"

Merci par avance de votre retour
Julien

rjuju

Administrateur

Re : Utilisation de la mémoire

Vous devriez remettre le temps_buffers à sa valeur par défaut.

Quelle est la volumétrie de votre base, et de vos grosses tables ?

Vous devriez essayer de précharger le shared_buffers avec l'outil pg_prewarm, cela devrait résoudre (au moins en partie) votre problème : http://docs.postgresql.fr/9.5/pgprewarm.html

---

Julien.
https://rjuju.github.io/

jdig

Membre

Re : Utilisation de la mémoire

Bonjour
Les grosses tables ont les tailles suivantes :
- t1 28GB
- t2 71GB et son index principal 43GB
- t3 2,6GB
Malgré les tailles des tables, ce que je ne comprends pas c'est pourquoi, aucune de mes requêtes ne consomme de la mémoire ...

Merci encore de votre retour,
Bonne journée
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

Bonjour,

comment savez-vous que vos requêtes ne consomme pas le shared_buffers ?

Vous pouvez examiner le cache en utilisant cette requête :
select * from pg_buffercache;

ou cette requête qui est encore plus claire :
SELECT c.relname, count(*) AS buffers
               FROM pg_buffercache b INNER JOIN pg_class c
               ON b.relfilenode = pg_relation_filenode(c.oid) AND
                  b.reldatabase IN (0, (SELECT oid FROM pg_database
                                        WHERE datname = current_database()))
               GROUP BY c.relname
               ORDER BY 2 DESC
               LIMIT 10;

Il faut pour cela installer l'extension pg_buffercache :
CREATE EXTENSION pg_buffercache;

lien vers la doc :
http://docs.postgresql.fr/9.4/pgbuffercache.html

Cordialement.

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Bonjour Sébastien,
Le shared_buffer est bien utilisé mais il semble "monté" sur le disque et non en mémoire de CentOS
En effet, je surveille la mémoire utilisée par le système et elle ne bouge pas malgré l'utilisation du shared_buffer
par contre, il y a énormément I/O sur le disque
Tout se passe comme si postgresql utilisait de la mémoire sur disque et non la RAM
Merci encore de votre retour,
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

étrange...
donnez nous quand même les résultats des requêtes sur pg_buffercache.

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Voici la liste des shared_buffers :
"idx_1";946935
"t1";100593
"t1_pkey";1

Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

Donc le cache est bien utilisé.
Pouvez-vous faire un explain d'une de vos requête comme ceci :
explain (analyze, buffers) ma_requete;

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Voici l'explain analyse d'une requete qui prend qq ms après la 2e execution et qq dizaines de secondes lors de la premiere execution

"Limit  (cost=651126.69..651126.94 rows=100 width=76) (actual time=214.215..214.227 rows=100 loops=1)"
"  ->  Sort  (cost=651126.69..651308.67 rows=72792 width=76) (actual time=214.214..214.218 rows=100 loops=1)"
"        Sort Key: (sum((((((((((((((((((100 * CASE WHEN (index_item.item18008 IS NULL) THEN 0 ELSE index_item.item18008 END) + (50 * CASE WHEN (index_item.item348472 IS NULL) THEN 0 ELSE index_item.item348472 END)) + (50 * CASE WHEN (index_item.item312890  (...)"
"        Sort Method: top-N heapsort  Memory: 29kB"
"        ->  HashAggregate  (cost=647616.71..648344.63 rows=72792 width=76) (actual time=213.438..213.848 rows=2464 loops=1)"
"              Group Key: index_item.individual_id"
"              ->  Nested Loop  (cost=1597.14..641247.41 rows=72792 width=76) (actual time=191.974..208.089 rows=2464 loops=1)"
"                    ->  Bitmap Heap Scan on corpus_individual  (cost=1596.70..99230.31 rows=72792 width=8) (actual time=191.953..192.280 rows=2464 loops=1)"
"                          Recheck Cond: (corpus_id = 1146329)"
"                          Heap Blocks: exact=8373"
"                          ->  Bitmap Index Scan on pk_corpus_individual  (cost=0.00..1578.50 rows=72792 width=0) (actual time=161.861..161.861 rows=1534364 loops=1)"
"                                Index Cond: (corpus_id = 1146329)"
"                    ->  Index Scan using index_item_pkey on index_item  (cost=0.43..7.44 rows=1 width=76) (actual time=0.005..0.005 rows=1 loops=2464)"
"                          Index Cond: (individual_id = corpus_individual.individual_id)"
"Planning time: 0.535 ms"
"Execution time: 214.947 ms"

.
Est-ce qu'il existe au niveau CentOS un qcq paramètre qui empeche à postgresql d'utiliser la mémoire ?
.
Merci encore de votre aide,
Julien

jdig

Membre

Re : Utilisation de la mémoire

Par exemple, lors de l'exécution d'une requête (pour la première fois), j'ai les infos suivantes :
.
en utilisant iotop sur centos :
I/O : 98%
.
en utiilisant htop sur centos :
MEM : 4%
CPU : 11%
SWAP : 0.1%
.
Est-ce normal que postgresql utilise le disque plutot que la mémoire ?
.
.
Merci de votre aide,
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

pouvez exécuter l'explain avec les options suivantes :
explain (analyze, buffers) ma_requete;
Les options entre parenthèse sont importantes pour avoir les buffers.

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Voici l'explain (analyse,buffers) d'une requete :
"Sort  (cost=1525752.55..1525753.07 rows=205 width=20) (actual time=8746.767..8746.770 rows=50 loops=1)"
"  Sort Key: cpij.jobfunction_id, t.trait_id"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=215537"
"  ->  HashAggregate  (cost=1525741.61..1525744.68 rows=205 width=20) (actual time=8746.725..8746.746 rows=50 loops=1)"
"        Group Key: cpij.jobfunction_id, t.trait_id"
"        Buffers: shared hit=215537"
"        ->  Hash Join  (cost=747269.14..1440297.48 rows=11392550 width=20) (actual time=8737.549..8745.257 rows=8446 loops=1)"
"              Hash Cond: (iis.item_id = t.item_id)"
"              Buffers: shared hit=215537"
"              ->  Hash Join  (cost=747246.64..1035839.45 rows=2278510 width=20) (actual time=8733.643..8739.192 rows=19649 loops=1)"
"                    Hash Cond: (cpij.individual_id = iis.individual_id)"
"                    Buffers: shared hit=215537"
"                    ->  Bitmap Heap Scan on corpus_pattern_individual_jobfunction cpij  (cost=3722.54..17859.94 rows=85675 width=16) (actual time=2.109..2.774 rows=4449 loops=1)"
"                          Recheck Cond: (corpus_pattern_id = 878067)"
"                          Filter: (jobfunction_id > 0)"
"                          Rows Removed by Filter: 977"
"                          Heap Blocks: exact=155"
"                          Buffers: shared hit=220"
"                          ->  Bitmap Index Scan on pk_corpus_pattern_individual_jobfunction  (cost=0.00..3701.12 rows=106760 width=0) (actual time=1.605..1.605 rows=11274 loops=1)"
"                                Index Cond: (corpus_pattern_id = 878067)"
"                                Buffers: shared hit=65"
"                    ->  Hash  (cost=450075.71..450075.71 rows=23475871 width=20) (actual time=8595.259..8595.259 rows=32131423 loops=1)"
"                          Buckets: 33554432  Batches: 1  Memory Usage: 2019332kB"
"                          Buffers: shared hit=215317"
"                          ->  Seq Scan on index_item_score iis  (cost=0.00..450075.71 rows=23475871 width=20) (actual time=0.006..3231.016 rows=32131423 loops=1)"
"                                Buffers: shared hit=215317"
"              ->  Hash  (cost=10.00..10.00 rows=1000 width=16) (actual time=3.895..3.895 rows=5 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                    ->  Function Scan on dblink t  (cost=0.00..10.00 rows=1000 width=16) (actual time=3.886..3.888 rows=5 loops=1)"
"Planning time: 0.674 ms"
"Execution time: 8772.140 ms"

ruizsebastien

Membre

Re : Utilisation de la mémoire

l'explain nous indique bien que les données utilisées sont prises dans le cache (donc dans la RAM) : c'est l'information donnée par les lignes "shared hit".

Maintenant je n'ai jamais rencontré de problème avec la zone de tri partagée (shared buffers), elle est toujours créée dans la RAM. Si la RAM est trop petite pour supporter la configuration de shared-buffers alors ça va dans la swap. Mais ce n'est pas votre cas puisque vous avez 32Go de RAM.

Au fait : quelle valeur avez vous pour work_mem ?

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

dans le paramètre work_mem j'ai mis 4GB

En vous remerciant  de votre retour,
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

votre work_mem est beaucoup trop élevé à première vue.

Combien avez vous de connexions active simultanée (à vue de nez) en moyenne ?

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

aujourd'hui 2 à terme une 50aine

ruizsebastien

Membre

Re : Utilisation de la mémoire

imaginez 50 utilisateurs qui utilisent (par chance) une seule fois chacun 4 Go de work_mem (par chance car ils pourraient utiliser plusieurs fois chacun 4 Go de work_mem, selon les requêtes exécutées), ça donnerait 200Go de RAM... autant dire que ça swap sévère ;-)

Il faut construire la mémoire alouée à chaque utilisateur (temp_buffer + work_mem + maintenance work_mem) en fonction du nombre d'utilsateurs simultanés potentiel et de la RAM dont vous disposez sur le serveur.
En laissant de la RAM pour l'OS et éventuellement les autres applis qui pourraient être présentes sur la machine.

Dernière modification par ruizsebastien (20/04/2016 14:48:25)

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Je suis conscient de cela mais, à cette heure, notre serveur n'utilise pas de mémoire pour postgresql ce qui semble occasionner des temps de réponses assez mauvais avec 1 seul utilisateur !
.
Quand postgresql utilisera la mémoire voire le swap je pourrais changer la config.
A ce jour, il utilise uniquement 4% de la mémoire et je pense qu'il y a un pb que je n'arrive pas à identifier soit sur la conf postgresql (ce dont je doute) soit sur conf CentOS/Postgresql ...

Qu'en pensez vous ?

en vous remerciant de nouveau pour votre retour,
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

dans vos log postgresql avez vous des messages de ce genre :
FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget

?

et que vous donne la commande "ulimit" pour votre user postgres ?

Dernière modification par ruizsebastien (20/04/2016 15:56:35)

---

Cordialement,

Sébastien.

jdig

Membre

Re : Utilisation de la mémoire

Sebastien,
Dans aucun fichier log je n'ai trouvé FATAL ... ou Failed system ...
Par ailleur ulimit pour postgres est unlimited
.
Merci à nouveau pour votre aide,
Julien

ruizsebastien

Membre

Re : Utilisation de la mémoire

alors je ne vois pas.

Vous pouvez toujours checker la doc suivante au cas où :
http://docs.postgresql.fr/9.4/kernel-resources.html

---

Cordialement,

Sébastien.

gleu

Administrateur

Re : Utilisation de la mémoire

En lisant ce thread complet, la seule information réelle vient du EXPLAIN (ANALYZE, BUFFERS). Ce dernier indique que tous les blocs sont lus en mémoire. De plus, parler du work_mem ne sert pas à grand chose vu que ça ne correspond pas au cache. Bref, il n'y a aucune preuve tangible que PostgreSQL fait des lectures ou écritures sur disque.

Comment voyez-vous la mémoire utilisée par processus ?

---

Guillaume.

jdig

Membre

Re : Utilisation de la mémoire

Bonjour Guillaume,
.
Lorsque je lance la première fois une requête, elle va prendre qq secondes.
Durant ces secondes, l'observation des ressources consommées du serveur indique que la RAM (ni le SWAP) ne sont mis à contribution, par contre les IO sur le disque sont à 95%.
Par contre, à la seconde exécution, la même requête prend qq ms.
.
Merci encore de votre aide,
Julien