Vous n'êtes pas identifié(e).
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)
Hors ligne
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.
Hors ligne
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/
Hors ligne
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)
Hors ligne
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 msje vous rajoute la requete.
Dernière modification par Postgres.0 (08/08/2012 15:23:47)
Hors ligne
Qu'est-ce que vous entendez pas "ça ne marche pas" ?
Guillaume.
Hors ligne
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.
Hors ligne
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.
Hors ligne
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.
Hors ligne
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'
Hors ligne
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.
Hors ligne
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.
Hors ligne
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 msHors ligne
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.
Hors ligne
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.
Hors ligne
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)
Hors ligne
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/
Hors ligne
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.
Hors ligne
La ligne
Buffers: shared hit=145 read=2281montre 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/
Hors ligne
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.
Hors ligne
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.
Hors ligne
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
Hors ligne
Y a t-il une piste pour savoir pourquoi les données ne tiennent pas en mémoire.
Hors ligne
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.
Hors ligne
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.
Hors ligne