Forums PostgreSQL.fr

Postgres.0

Membre

problèmes avec les STAS

Bonjour,

j'ai de plus en plus de problèmes de performances sur mes tables.
En fait, j'ai une table partitionnée qui contient 13391132  lignes

Quand je faits select * from pg_stats where tablename='aus.par_transaction_2012_03';
j'ai le resultat suivant :

 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
(0 rows)

Est-ce-que ça explique les problèmes de performances que j'ai.
Comment je peux faire pour que cette requete me retourne un tableau qui contient des données.

Merci

Dernière modification par Postgres.0 (08/08/2012 14:14:12)

gleu

Administrateur

Re : problèmes avec les STAS

Sur les performances, pas grand chose.

Essayez avec ceci, ça devrait aller mieux :

select * from pg_stats where schemaname='aus' and tablename='par_transaction_2012_03'

---

Guillaume.

rjuju

Administrateur

Re : problèmes avec les STAS

Bonjour,
cela ressemble plutôt à un problème d'autorisation de l'utilisateur qui lance cette requête, à moins que vous n'ayez désactivé les paramètres track_* dans le postgresql.conf.

Si vous avez un exemple de requête de plus en plus longue avec son explain analyze, cela aiderait à vous aider.

Edit: Effectivement je n'avais pas vu que le nom du schéma était passé dans le tablename

Dernière modification par rjuju (08/08/2012 14:41:52)

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

Essayez avec ceci, ça devrait aller mieux :

select * from pg_stats where schemaname='aus' and tablename='par_transaction_2012_03'

ça ne marche pas.

Dernière modification par Postgres.0 (08/08/2012 15:01:56)

Postgres.0

Membre

Re : problèmes avec les STAS

Voilà un plan d'exécution, j'en profite pour vous demander de m'aider à améliorer les perfs de cette requete dont  voici le plan

Nested Loop  (cost=0.00..4026.82 rows=2 width=2008) (actual time=9558.355..311970.675 rows=39071 loops=1)
   ->  Append  (cost=0.00..4010.26 rows=2 width=1996) (actual time=9531.171..311651.298 rows=39071 loops=1)
         ->  Seq Scan on ftransac ft  (cost=0.00..0.00 rows=1 width=1996) (actual time=0.003..0.003 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=2647.42..4010.26 rows=1 width=1996) (actual time=9531.167..311639.494 rows=39071 loops=1)
               Recheck Cond: ((id_client = 196) 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))
               Filter: (is_offline 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[])))
               ->  BitmapAnd  (cost=2647.42..2647.42 rows=335 width=0) (actual time=8626.991..8626.991 rows=0 loops=1)
                     ->  Bitmap Index Scan on par_ftransac_2012_03_ndx2  (cost=0.00..1239.89 rows=66956 width=0) (actual time=3930.464..3930.464 rows=11877763 loops=1)
                           Index Cond: (id_client = 196)
                     ->  Bitmap Index Scan on par_ftransac_2012_03_ndx1  (cost=0.00..1407.28 rows=66956 width=0) (actual time=4166.593..4166.593 rows=13391132 loops=1)
                           Index Cond: ((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))
   ->  Index Scan using org_pkey on org os  (cost=0.00..8.27 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=39071)
         Index Cond: (id_org = ft.id_org)
 Total runtime: 311985.884 ms

je vous rajoute la requete.

Dernière modification par Postgres.0 (08/08/2012 15:23:47)

gleu

Administrateur

Re : problèmes avec les STAS

Qu'est-ce que vous entendez pas "ça ne marche pas" ?

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

 SELECT ft.id_org
  , org_name
  , date_transaction
  , date_transaction_one
  , 5
  , merid
  , po
  , cashier
  , currency_code_alpha
  , amount
  , cashamount
  , tipamount
  , idref_transaction_type as transaction_type
  , is_authorized
  , authorization_number
  , id_ref_cvm
  , transaction_mode
  , is_offline
  , user_data1
  , user_data2
  , id_application_db
  , id_ref_account_type
  , transaction_id
  , stan
  , rrn
  , roc
  , em_aid
  , em_tc_aac
  , em_tvr
  , is_reconciled
  , original_date_transaction
  , original_id_application_db
 FROM aus.ftransac ft, org os
  WHERE ft.id_org = os.id_org AND
date_transaction BETWEEN '2012-03-01 00:00:00'  AND '2012-03-31 23:59:59'
AND ft.id_client = 196
AND ft.id_org = ANY (ARRAY[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])
AND cashier = '00000001'
AND is_offline = TRUE ;

La table est parttionné par mois, chaque parttion contient en moyene 12000000 d'enregistrements.

Postgres.0

Membre

Re : problèmes avec les STAS

 CREATE INDEX par_ftransac_2012_03_idxstan
  ON aus.par_ftransac_2012_03
  USING btree
  (stan)
TABLESPACE pg_default;


CREATE INDEX par_ftransac_2012_03_ndx1
  ON aus.par_ftransac_2012_03
  USING btree
  (date_transaction)
TABLESPACE tbl_index;



CREATE INDEX par_ftransac_2012_03_ndx2
  ON aus.par_ftransac_2012_03
  USING btree
  (id_client)
TABLESPACE tbl_index;



CREATE INDEX par_ftransac_2012_03_ndx3
  ON aus.par_ftransac_2012_03
  USING btree
  (id_org)
TABLESPACE tbl_index;






CREATE INDEX par_ftransac_2012_03_ndx5
  ON aus.par_ftransac_2012_03
  USING btree
  (date_transaction_one)
TABLESPACE tbl_index;

Voilà les index que j'ai crées, les perfs sont vraiment mauvaises, aidez moi s-il vous plait.

Postgres.0

Membre

Re : problèmes avec les STAS

Qu'est-ce que vous entendez pas "ça ne marche pas" ?

Je veux dire par la que la table retournée est vide.

Voilà les caracteristiques que j'ai pu collecter : work_mem = 1GB
shared_buffers = 8GB
max_connections = 100
maintenance_work_mem = 16MB
effective_cache_size = 16GB

Avec 28GB de RAM.

Postgres.0

Membre

Re : problèmes avec les STAS

Bonjour,
cela ressemble plutôt à un problème d'autorisation de l'utilisateur qui lance cette requête, à moins que vous n'ayez désactivé les paramètres track_* dans le postgresql.conf.

Si vous avez un exemple de requête de plus en plus longue avec son explain analyze, cela aiderait à vous aider.

Edit: Effectivement je n'avais pas vu que le nom du schéma était passé dans le tablename

Je n'ai pas bien compri mais dans le .conf j'ai :

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_functions = none                 # none, pl, all
#track_activity_query_size = 1024       # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

gleu

Administrateur

Re : problèmes avec les STAS

Sur la ligne: Bitmap Index Scan on par_ftransac_2012_03_ndx2  (cost=0.00..1239.89 rows=66956 width=0) (actual time=3930.464..3930.464 rows=11877763 loops=1)

et sur la ligne: Bitmap Index Scan on par_ftransac_2012_03_ndx1  (cost=0.00..1407.28 rows=66956 width=0) (actual time=4166.593..4166.593 rows=13391132 loops=1)

On voit que les statistiques sont complètement fausses (66956 lignes estimées contre 11877763 lignes réelles pour le premier par exemple). Soit les statistiques ne sont pas calculées pour cette table, soit la précision n'est pas suffisante.

Commencez par faire un ANALYZE de par_ftransac_2012_03, puis refaites un EXPLAIN ANALYZE.

---

Guillaume.

gleu

Administrateur

Re : problèmes avec les STAS

Si la table retournée est vide, c'est que vous n'avez pas de statistiques pour cette table. Ça ne peut pas être bon.

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

Après avoir fait un analyze, voila le nouveau plan d'exécution :

Hash Join  (cost=289.87..1561748.27 rows=28517 width=775) (actual time=1063.749..170240.572 rows=39071 loops=1)
   Hash Cond: (ft.id_org = os.id_org)
   ->  Append  (cost=0.00..1560816.77 rows=28517 width=763) (actual time=1046.972..170140.773 rows=39071 loops=1)
         ->  Seq Scan on ftransac ft  (cost=0.00..0.00 rows=1 width=1996) (actual time=0.002..0.002 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=12391.03..1560816.77 rows=28516 width=763) (actual time=1046.968..170130.653 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))
               ->  Bitmap Index Scan on par_ftransac_2012_03_ndx3  (cost=0.00..12383.90 rows=672761 width=0) (actual time=812.923..812.923 rows=812583 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[]))
   ->  Hash  (cost=184.94..184.94 rows=8394 width=16) (actual time=16.707..16.707 rows=8394 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 410kB
         ->  Seq Scan on org os  (cost=0.00..184.94 rows=8394 width=16) (actual time=9.120..14.528 rows=8394 loops=1)
 Total runtime: 170251.452 ms

Postgres.0

Membre

Re : problèmes avec les STAS

Même si il est meilleur que l'autre il faut que je l'amiliore.
Et puis bizarrement la requête sur les stat donne des resultats  maintenant.

Comment pourrais-je faire pour améliorer les perfs de cette requette.

gleu

Administrateur

Re : problèmes avec les STAS

Il est clairement meilleur. Il est quand même pratiquement 2 fois plus rapide (1.8 exactement).

Il n'est pas bizarre que la requête sur les stats donne des résultats. Vous faites un ANALYZE, PostgreSQL calcule les stats et les enregistre. Vous ne faites que les lire avec votre requête. Donc soit vous avez désactivé l'autovacuum, soit vous devez mieux le configurer pour qu'il calcule plus fréquemment les statistiques.

Quant aux performances de la requête, sur 170 secondes d'exécution, 169 sont passées sur le nœud "Bitmap Heap Scan". J'ai un peu peur qu'il fasse beaucoup de lecture disque. Pourriez vous faire un "EXPLAIN (ANALYZE on, BUFFERS on) SELECT..." pour en savoir plus sur les lectures réalisées par cette opération ? (attention, l'option BUFFERS n'est disponible qu'à partir de la 9.0).

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

Oui effectivement l'autovacuum est désactivé (j'ai pas le choix car en PROD il est désactivé aussi).
Comment je peux faire pour calculer plus frequemment les statistics.

est-il pértinent de cibler quelques colonnes par des calculs de stats ?

Faut-il indexer autrement ?

voici le plan avec l'option BUFFER on du EXPLAIN ANALYZE :

 Hash Join  (cost=289.87..1561748.27 rows=28517 width=775) (actual time=1066.060..254907.763 rows=39071 loops=1)
   Hash Cond: (ft.id_org = os.id_org)
   Buffers: shared hit=148 read=661551
   ->  Append  (cost=0.00..1560816.77 rows=28517 width=763) (actual time=1049.268..254811.975 rows=39071 loops=1)
         Buffers: shared hit=146 read=661450
         ->  Seq Scan on ftransac ft  (cost=0.00..0.00 rows=1 width=1996) (actual time=0.002..0.002 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=12391.03..1560816.77 rows=28516 width=763) (actual time=1049.264..254798.861 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=146 read=661450
               ->  Bitmap Index Scan on par_ftransac_2012_03_ndx3  (cost=0.00..12383.90 rows=672761 width=0) (actual time=816.328..816.328 rows=812583 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[]))
                     Buffers: shared hit=145 read=2281
   ->  Hash  (cost=184.94..184.94 rows=8394 width=16) (actual time=16.736..16.736 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=9.091..14.467 rows=8394 loops=1)
               Buffers: shared read=101
 Total runtime: 254918.304 ms

.

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

rjuju

Administrateur

Re : problèmes avec les STAS

L'autovacuum sert justement à avoir des statistiques à jour. Je vous conseillerai de le réactiver, cela devrait améliorer beaucoup de requêtes.

Pour cette requête en particulier, on voit que la lecture sur par_ftransac_2012_03 prend presque tout le temps d'exécution, et que les blocs ne sont pas récupéré en cache mais lus sur les disques pour la plus grande partie. C'est un peu étonnant d'après les informations de votre machine (28 Go de ram et shared_buffers de 8Go), à moins qu'il ne s'agisse d'un serveur différent ?

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

j'ai fait "cat /proc/meminfo" pour avoir les 28GB de la mémoire.

Comment pouvez vous savoir que les données sont lues sur le disque ?

Avez des pistes pour savoir pourquoi il lit les donées sur le disque.

Autovacuum est désactivé par la PROD, si je le reactive en DEV, je ne serai pas dans les mêmes conditions qu'en PROD.

rjuju

Administrateur

Re : problèmes avec les STAS

La ligne

Buffers: shared hit=145 read=2281

montre que seuls 145 blocs sont récupérés en mémoire vive, 2281 blocs sont lu depuis le disque. Si vous refaîtes 2 fois l'explain analyze, le nombre de blocs récupérés en mémoire vive devrait augmenter. Sinon, c'est qu'il y a surement un problème.

Il serait préférable d'activer l'autovacuum en DEV et en PROD, cela fait plusieurs années que cet outil est fonctionnel et mature, et tenter de faire son travail à la main ne vous rendra pas service.

---

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

Postgres.0

Membre

Re : problèmes avec les STAS

Je vais demander à la PROD pourquoi il désactive l'autovacuum.
Au fait la base est-une base dans laquelle y a beaucou d'INSERT d'UPDATE et de SELECTS.

Mais même si je réactive l'autovacuum, le temps d'exécution ne sera pas meilleur.

gleu

Administrateur

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.

---

Guillaume.

Postgres.0

Membre

Re : problèmes avec les STAS

SELECT pg_size_pretty (pg_relation_size('aus.par_ftransac_2012_03)) donne  18 GB

et SELECT pg_size_pretty (pg_total_relation_size('ausemv.par_financial_transaction_2012_03')) donne 20GB

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.

gleu

Administrateur

Re : problèmes avec les STAS

OK, donc il ne lit qu'un quart de la table grâce à l'index utilisé. Peut-être qu'avec l'index à trois colonnes indiqué ci-dessus, il en parcourerait moins. En effet, le parcours de l'index récupère 812583 lignes alors que le résultat du Bitmap Scan est plutôt de 39071 lignes. Reste à savoir s'il utiliserait un tel index, mais le test est intéressant en tout cas.

---

Guillaume.

gleu

Administrateur

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.

---

Guillaume.