Vous n'êtes pas identifié(e).
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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/
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
étrange...
donnez nous quand même les résultats des requêtes sur pg_buffercache.
Cordialement,
Sébastien.
Hors ligne
Voici la liste des shared_buffers :
"idx_1";946935
"t1";100593
"t1_pkey";1
Julien
Hors ligne
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.
Hors ligne
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
Hors ligne
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
Hors ligne
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.
Hors ligne
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"
Hors ligne
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.
Hors ligne
dans le paramètre work_mem j'ai mis 4GB
En vous remerciant de votre retour,
Julien
Hors ligne
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.
Hors ligne
aujourd'hui 2 à terme une 50aine
Hors ligne
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.
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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.
Hors ligne
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.
Hors ligne
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
Hors ligne
