Forums PostgreSQL.fr

Postgres.0

Membre

Re : problèmes avec les STAS

Expliquez moi s'il vous plait pourquoi les données doivent êre dans le cache.
Dans une base,  les données sont bien sur le disque, alors on me dit que c'est tout à fait normal qu'on aie chercher les données sur dique.

Dernière modification par Postgres.0 (08/08/2012 18:30:17)

Postgres.0

Membre

Re : problèmes avec les STAS

Y a t-il une piste pour savoir pourquoi les données ne tiennent pas en mémoire

Non, on ne peut pas répondre à cette question à partir de cet EXPLAIN. Nous ne savons pas comment est utilisé la machine, s'il y a d'autres serveurs (PostgreSQL ou non), s'il y a beaucoup d'utilisateurs, si même les données sont réellement lues sur le disque et pas dans le cache du système d'exploitation. Très difficile de deviner ça à distance.

il n y a qu'un seul serveur postgres sur la machine.

Avant le lancement de la requete, je netoie le cache systeme d'exploitation et le cache postgres.
"echo 3 > /proc/sys/vm/drop_caches; /etc/init.d/postgresql-9.1 restart"

il y a au Max 10 connexion sur Postgres.

rjuju

Administrateur

Re : problèmes avec les STAS

Les taux de transfert d'un disque dur n'ont rien à voir avec ceux de la mémoire vive, c'est pourquoi il est préférable que les données soient en cache.

Il est inutile de vider le cache et de redémarrer postgresql, ça serait plutôt contre productif. Quand la base est utilisée depuis un certain temps, les mécanismes internes de postgresql font que les données les plus souvent accédées se trouvent en cache. Il faudrait donc que vous regardiez si en lançant 2 fois l'explain analyze sans redémarrer postgres ni vider le cache amènent bien à un gain de performances conséquent.

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

Il faudrait donc que vous regardiez si en lançant 2 fois l'explain analyze sans redémarrer postgres ni vider le cache amènent bien à un gain de performances conséquent.

Je l'ai fait est ça donne Total runtime: 2061.855 ms.
C'est clairement un gain enorme.
Sauf que ce n'est pas la réalité :

Ce qui est intéréssant, c'est que la requete retourne des resultat acceptable la première fois.

Donc, je repose s'il vous plait ma question :

Pourquoi c'est pas normal que les données soient lues sur le disque, quand je lance ma requete pour la première fois.

Merci.

rjuju

Administrateur

Re : problèmes avec les STAS

Il y a eu un quiproquo. Il est normal que les données soient lu sur disque lors du premier accès par la base de données. Mais en général, la base de donnée n'est pas redémarrée toutes les heures, et donc les données restent en cache pendant plusieurs jours voire beaucoup plus. Si vous voulez que le premier accès au données par disque soit rapide, le seul moyen est d'y mettre des ssd.

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

Merci, je vais abuser une dernière fois

rjuju

1 . quelle est la bonne formule :

a) work_meme*nbr_connexion + shared_buffer + nbr_connexion*maintenance_work_mem < taille_memoire_totale

b) work_meme*nbr_connexion + shared_buffer + nbr_connexion*maintenance_work_mem + effective_cache_size  < taille_memoire_totale.
 
2. est-t-il vrai que le shared buffer est inclus dans le effective_cache_size.

3. pour quoi quand je crée un index B-tree, postgres dans le plan d'execution me cree toujours un index bitmap.

gleu

1. j'ai vu que plusieurs fois sur ce forum que vous disiez "en gros" que Bitmap Index Scan construit l'index Bitmap qui servira à parcourir la table pour retrouver les bonnes lignes ( Bitmap Heap Scan).
Pouvez vous s-il vous plait expliquez un peu plus en détail, sachant que pour ce genre d'index postgres est censé tenir compte de la cardinalité des valeurs de la colonne indexée.
Ce qui n'est pas le ca pour ma requête.

2 Mar cousin disais "effective_cache_size aide PostgreSQL à estimer la probabilité qu'une donnée soit dans le cache système, ou y reste"
si je le mets par exemple à 8GB, je ne vois pas pourquoi les donnée seront dans le cache.

merci et désolé pour ces spams.

gleu

Administrateur

Re : problèmes avec les STAS

1. Ni l'un ni l'autre, c'est autrement plus complexe que ça.
2. Non, c'est faux.
3. Il utilise l'index Btree et crée en mémoire un index Bitmap. Il fait un IndexScan quand il y a très peu de lignes à lire, il fait un BitmapIndexScan quand le nombre de lignes est plus important, et il fait un SeqScan quand il y a beaucoup de lignes à lire.

1. Je ne comprends pas la question.
2. PostgreSQL ne connaît pas la quantité de mémoire sur le serveur. Si vous lui indiquez seulement le cache de PostgreSQL (configuré avec shared_buffers), il aura l'impression que la mémoire est toute petite. Si vous précisez le cache du système (avec effective_cache_size), son idée de la mémoire cache globale est beaucoup plus réelle, et donc la probabilité que l'index et la table soient en cache est bien plus importante. Rien ne dit qu'elles sont réellement en cache mais la probabilité est plus importante. C'est toute une question de statistiques et de probabilités.

---

Guillaume.

rjuju

Administrateur

Re : problèmes avec les STAS

1) Aucune des deux

Cela serait plutôt :
(work_mem*nb_connexion) + shared_buffers + (maintenance_work_mem*autovacuum_max_workers) < taille_ram

Mais il faut garder en mémoire que le work_mem peut-être utilisé plusieurs fois dans une requête si nécessaire (une fois par noeud), de même qu'un utilisateur peut lancer manuellement une ou plusieurs requête de maintenance (reindex ...) qui pourront également utiliser maintenance_work_mem.

2) Non

3) Cela dépend du nombre de lignes. À partir d'un certain nombre de ligne, le BitmapIndexScan est plus rentable.

Dernière modification par rjuju (09/08/2012 11:12:34)

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

3. Il utilise l'index Btree et crée en mémoire un index Bitmap. Il fait un IndexScan quand il y a très peu de lignes à lire, il fait un BitmapIndexScan quand le nombre de lignes est plus important, et il fait un SeqScan quand il y a beaucoup de lignes à lire.

C'est justement la où ce n'est pas clair :

un B-tree c'est un arbre dynamique equilibré et le bit-map est un tableau à deux dimensions. Je ne vois pas l'interêt de creer un index-Btree et puis se servir uniquement d'un bitmap en mémoire.

1. Je ne comprends pas la question.

Ma question concerne l'itulisation du bit-map :

comment à partir du bit-map en mémoire, il va chercher les lignes de la table ou les blocs ?
Le heap scan, c'est quoi?

gleu

Administrateur

Re : problèmes avec les STAS

OK, alors allons dans le détail.

Quand PostgreSQL fait un IndexScan sur un index btree, il parcourt (complètement ou partiellement) l'index et pour chaque valeur de l'index correspondant à sa recherche, il va lire la ligne ciblée dans la table pour savoir si cette ligne est visible pour la transaction en cours d'exécution. Donc, en gros, il fait un tas de lectures non séquentielles dans l'index et dans la table. Ce sont les lectures non séquentielles de l'index et de la table qui, réclamant des déplacements de la tête de lecture, font que, si une majorité de la table est à lire, un parcours d'index est une mauvaise idée.

Les développeurs de PostgreSQL se sont dit qu'il serait possible d'éviter la lecture non séquentielle dans la table en parcourant en un coup l'index, en stockant en mémoire chaque position à vérifier dans la table et en parcourant ensuite la table (forcément partiellement) de façon séquentielle sur chaque bloc intéressant. Ainsi est né le parcours de bitmaps. Le bitmap, c'est simplement une structure en mémoire indiquant les blocs ou les lignes à vérifier. Du coup, dans le plan d'exécution, la ligne BitmapIndexScan correspond au parcours non séquentiel de l'index, et la ligne BitmapHeapScan correspond au parcours séquentiel mais partiel de la table.

Tout ceci explique pourquoi un parcours séquentiel est sélectionné quand une grande portion de la table est à lire, ensuite ce sera un parcours de bitmap et enfin, quand il y a très peu de lignes, un parcours d'index.

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

Merci beaucoup, je comprends maintenant.

Postgres.0

Membre

Re : problèmes avec les STAS

Le temps d'exécution passe de 4 min à 2 min en mettant à jour les statistiques, donc si, ça sera meilleur. Pas forcément les perfs que vous attendez, mais meilleur.

Pour les améliorer encore plus, le gros soucis de vos perfs vient surtout de ça : Buffers: shared hit=146 read=661450. Lecture sur disque de 661450 blocs, soit 5,1 Go sur disque en 2 min 40. Comme le dit rjuju, c'est très étonnant que les données ne tiennent pas en mémoire. En tout cas, PostgreSQL ne les a pas dans son cache et il semblerait que le système non plus. Cette partition ne ferait pas justement dans les 5 Go justement ?

Un index sur les trois colonnes (id_org, is_offline, date_transaction) pourrait peut-être améliorer les choses. À tester.

Bonjour gleu,

j'ai créé un index sur (id_org,date_transaction,is_offline), et les perfs sont améliorées.
Je vous mets le nouveau plan d'exécution.

Hash Join  (cost=289.87..143373.45 rows=29072 width=775) (actual time=396.318..115782.691 rows=39071 loops=1)
   Hash Cond: (ft.id_org = os.id_org)
   Buffers: shared hit=189 read=42470
   ->  Append  (cost=0.00..142429.47 rows=29072 width=763) (actual time=370.332..115660.991 rows=39071 loops=1)
         Buffers: shared hit=187 read=42369
         ->  Seq Scan on ftransac ft  (cost=0.00..0.00 rows=1 width=1996) (actual time=5.618..5.618 rows=0 loops=1)
               Filter: (is_offline AND (date_transaction >= '2012-03-01 00:00:00'::timestamp without time zone) AND (date_transaction <= '2012-03-31 23:59:59'::timestamp without time zone) AND (id_client = 196) AND ((cashier)::text = '00000001'::text) AND (id_org = ANY ('{1686,1495,502,708,10691,921,1219,707,704,1494,710,1576,1536,1539,506,1218,504,500,1540,1545,1510,1217,1578,822,1489,1530,1738,13920,719,797,715,1506,1544,1548,1114,1493,712,717,1825,1523,713,923,1519,706,1512,922,1524,1525,1322,14901,711,1521,1568,1811,1491,705,1490,1533,1574,1527,714,718,1488,709,1492,791,716}'::integer[])))
         ->  Bitmap Heap Scan on par_ftransac_2012_03 ft  (cost=20115.05..142429.47 rows=29071 width=763) (actual time=364.713..115636.834 rows=39071 loops=1)               Recheck Cond: (id_org = ANY ('{1686,1495,502,708,10691,921,1219,707,704,1494,710,1576,1536,1539,506,1218,504,500,1540,1545,1510,1217,1578,822,1489,1530,1738,13920,719,797,715,1506,1544,1548,1114,1493,712,717,1825,1523,713,923,1519,706,1512,922,1524,1525,1322,14901,711,1521,1568,1811,1491,705,1490,1533,1574,1527,714,718,1488,709,1492,791,716}'::integer[]))
               Filter: (is_offline AND (date_transaction >= '2012-03-01 00:00:00'::timestamp without time zone) AND (date_transaction <= '2012-03-31 23:59:59'::timestamp without time zone) AND (id_client = 196) AND ((cashier)::text = '00000001'::text))
               Buffers: shared hit=187 read=42369
               ->  Bitmap Index Scan on par_ftransac_ndx11  (cost=0.00..20107.79 rows=32809 width=0) (actual time=342.191..342.191 rows=39071 loops=1)
                     Index Cond: ((id_org = ANY ('{1686,1495,502,708,10691,921,1219,707,704,1494,710,1576,1536,1539,506,1218,504,500,1540,1545,1510,1217,1578,822,1489,1530,1738,13920,719,797,715,1506,1544,1548,1114,1493,712,717,1825,1523,713,923,1519,706,1512,922,1524,1525,1322,14901,711,1521,1568,1811,1491,705,1490,1533,1574,1527,714,718,1488,709,1492,791,716}'::integer[])) AND (is_offline = true))
                     Buffers: shared hit=187 read=4082
   ->  Hash  (cost=184.94..184.94 rows=8394 width=16) (actual time=25.924..25.924 rows=8394 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 410kB
         Buffers: shared read=101
         ->  Seq Scan on org os  (cost=0.00..184.94 rows=8394 width=16) (actual time=18.627..23.660 rows=8394 loops=1)
               Buffers: shared read=101
 Total runtime: 115795.459 ms

gleu

Administrateur

Re : problèmes avec les STAS

Logique, moins de lecture = moins de lenteur.

Par contre, vous êtes sûr que l'index par_ftransac_ndx11 est sur les trois colonnes ? parce que la condition d'index n'indique que is_offline et id_org. Cela étant dit, le nombre de ligne est identique dans le Bitmap Index Scan et dans le Bitmap Heap Scan, donc même à trois colonnes, ça ne changera pas grand-chose.

En fait, il y a de fortes chances que les lignes soient très réparties dans la table (ça fait en gros une ligne par bloc, ce qui doit être assez rare comme distribution et est en tout cas très désavantageux). Autrement dit, je ne pense pas que vous puissiez améliorer cette requête sauf à réorganiser la table autour de l'index par_ftransac_ndx11 (commande CLUSTER). Ça peut vous faire gagner beaucoup sur cette requête, ça peut aussi vous faire perdre beaucoup sur les autres.

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

Vous avez raison,  il n'a pas utilisé l'index11 (id_org,date_transaction,is_offline),  mais il as utilisé juste  cet index sur id_org et is_offline.
Je pensais tout simplement que ce n'était pas possible.

Postgres.0

Membre

Re : problèmes avec les STAS

PG dois se dire qu'il est dans une partition faite en fonction de date_transaction, et donc ce n'est pas la peine de l'utiliser dans l'iindex.

Dernière modification par Postgres.0 (10/08/2012 15:00:17)

gleu

Administrateur

Re : problèmes avec les STAS

Ah oui, je croyais que le filtre était seulement sur une journée mais en fait, il couvre le mois complet, comme la partition. Donc en effet, ce filtre supplémentaire ne sera à rien (et du coup vous pouvez virer la colonne date_transaction de l'index).

---

Guillaume.