Vous n'êtes pas identifié(e).
Bonjour,
j'ai un soucis avec l'éxécution d'une requete, lors de la première exécution,
le temps d'éxécution est très long puis pour toutes les rééxécutions suivantes, le temps de réponse est instané.
Qu'est ce qui peux provoquer ce genre de comportement ?
Une mise en cache des données ?
Une mauvaise config de postgresql ?
Une mauvaise conscruction des index ?
Une mauvaise conscruction de la requete ?
ma config postgresql :
shared_buffers = 512MB
temp_buffers = 100MB
work_mem = 50MB
maintenance_work_mem = 64MB
max_stack_depth = 6MB
max_fsm_pages = 153600
bgwriter_delay = 400ms
Structure de la table_primaire
Column | Type | Modifiers
--------------------+-----------------------+-----------
time_idx | bigint
param_1 | integer
param_2 | integer
param_3 | integer
champs_8_bigint | bigint
champs_9_bigint | integer
champs_6_bigint | bigint
champs_7_bigint | integer
start_time | bigint
non_use_1 | integer
non_use_2 | integer
stop_time | bigint
param_4 | integer
param_5 | integer
end_code | integer
non_use_3 | integer
non_use_4 | integer
non_use_5 | smallint
non_use_6 | smallint
non_use_7 | integer
non_use_8 | bigint
non_use_9 | integer
non_use_10 | integer
non_use_11 | character varying(6)
non_use_12 | smallint
champs3_char | character varying(24)
champs_10_bigint | bigint
non_use_13 | smallint
non_use_14 | smallint
Indexes:
"table_primaire__otdi__index" btree (champs3_char) WHERE champs3_char::text <> ''::text
"table_primaire__start_time_desc__index" btree (time_idx, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time DESC)
"table_primaire__subs_idx__index" btree (champs_8_bigint, champs_9_bigint)
"table_primaire__time_idx__index" btree (time_idx)
Toutes les colonnes de l'index table_primaire__start_time_desc__index ont une valeur de la propriété statistics à 100
Structure de la table_secondaire
Column | Type | Modifiers
----------+-----------------------------+---------
end_code | integer | not null
txt | character varying(255) |
Indexes:
"table_secondaire_pkey" PRIMARY KEY, btree (end_code)
Volume :
table_primaire : 108 127 662 rows
table_secondaire : 377 rows
la requete :
explain analyze select date_trunc( 'second', TIMESTAMP WITH TIME ZONE 'epoch' + start_time_bigint/1000 * interval '1 second') as "Day",
to_char(TIMESTAMP WITH TIME ZONE 'epoch' + (a.stop_time_bigint/1000) * interval '1 second', 'HH24:MI:SS') as "->",
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
champs_4_char,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
left join table_secondaire as c on ( a.end_code_integer = c.end_code_integer )
WHERE
a.time_idx_bigint= 1266879600
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer IN (SELECT temp_param FROM params.table_txt WHERE mode= 'value' )
order by start_time_bigint desc
limit 50;
première exécution :
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=334.10..334.20 rows=38 width=89) (actual time=25025.295..25025.416 rows=50 loops=1)
-> Sort (cost=334.10..334.20 rows=38 width=89) (actual time=25025.290..25025.332 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=14.04..333.10 rows=38 width=89) (actual time=33.003..24984.522 rows=14794 loops=1)
Hash Cond: (a.end_code_integer = c.end_code_integer)
-> Hash Join (cost=1.56..319.15 rows=38 width=61) (actual time=24.922..24822.676 rows=14794 loops=1)
Hash Cond: (a.param_1_integer = table_txt.temp_param)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..316.61 rows=155 width=65) (actual time=24.827..24764.690 rows=14794 loops=1)
Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
-> Hash (cost=1.51..1.51 rows=4 width=4) (actual time=0.073..0.073 rows=9 loops=1)
-> HashAggregate (cost=1.47..1.51 rows=4 width=4) (actual time=0.054..0.062 rows=9 loops=1)
-> Seq Scan on table_txt (cost=0.00..1.45 rows=9 width=4) (actual time=0.014..0.027 rows=9 loops=1)
Filter: ((mode)::text = 'value'::text)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=5.548..5.548 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=0.008..0.359 rows=377 loops=1)
Total runtime: 25025.577 ms
(17 rows)
Time: 25026.833 ms
Deuxième éxécution et exécution suivante:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=334.10..334.20 rows=38 width=89) (actual time=165.959..166.087 rows=50 loops=1)
-> Sort (cost=334.10..334.20 rows=38 width=89) (actual time=165.957..166.010 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=14.04..333.10 rows=38 width=89) (actual time=0.850..143.952 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Hash Join (cost=1.56..319.15 rows=38 width=61) (actual time=0.098..62.936 rows=14794 loops=1)
Hash Cond: (a.param_1_integer = table_txt.temp_param)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..316.61 rows=155 width=65) (actual time=0.037..28.264 rows=14794 loops=1)
Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
-> Hash (cost=1.51..1.51 rows=4 width=4) (actual time=0.054..0.054 rows=9 loops=1)
-> HashAggregate (cost=1.47..1.51 rows=4 width=4) (actual time=0.033..0.042 rows=9 loops=1)
-> Seq Scan on table_txt (cost=0.00..1.45 rows=9 width=4) (actual time=0.006..0.018 rows=9 loops=1)
Filter: ((mode)::text = 'value'::text)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=0.732..0.732 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=0.006..0.360 rows=377 loops=1)
Total runtime: 166.223 ms
(17 rows)
Merci d'avance pour vos explications
Dernière modification par Mekhanik (01/03/2010 16:05:14)
Hors ligne
Premier plan : http://explain.depesz.com/s/eSr
Deuxième plan : http://explain.depesz.com/s/bDh
Très clairement, le problème est le parcours de l'index. Ce qui sous-entend que l'index n'était pas en cache lors de la première exécution. Un REINDEX peut améliorer les choses. Un agrandissement du cache peut aussi le faire. Il serait intéressant de connaître la taille de l'index (table_primaire__start_time_desc__index).
Guillaume.
Hors ligne
Si le limit se fait toujours sur start_time_bigint desc, vous pourriez aussi le rajouter dans votre index, en dernière colonne, trié en ordre descendant.
Ça pourrait éviter la récupération de 15000 enregistrements et le tri dans la requête. Avez vous param1 aussi dans cet index ?
Marc.
Hors ligne
J'ai tenté un reindex, le résultat est identique.
L'index fait 5 129 MB, j'imagine que c'est énorme ...
Je tente l'indexation sur limit.
non param1 n'est pas dans l'index mais j'avais déjà fait le test avant en obtenant des résultats identiques.
Hors ligne
En fait start_time_bigint fait bien parti de l'index :
"table_primaire__start_time_desc__index" btree (time_idx, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time_bigint DESC)
Hors ligne
Tout dépend de votre version de PostgreSQL, mais vous devriez tenter la création d'index autonomes : un pour time_idx, un pour param_2_integer, un pour... etc. PostgreSQL est capable, dans les dernières versions, de les combiner.
D'autre part, quelle est la taille de la RAM ? parce que 5Go, pour un index, c'est beaucoup, surtout avec un shared_buffers de 512 Mo qui laisse supposer une RAM de 2 Go.
Guillaume.
Hors ligne
Ma version de postgresql est la 8.3.
Le serveur correspondant possède 4Go de ram
Hors ligne
Donc pas suffisant pour conserver l'index en mémoire. D'où plus d'1 Go à lire à chaque parcours d'index.
Mon avis : diviser votre index en plusieurs petits index. Et tester voir ce que cela donne comme performances.
Guillaume.
Hors ligne
Autre hypothèse : à mon avis le IN (SELECT temp_param FROM params.table_txt WHERE mode= 'value' ) gêne la bonne exécution de la requête. Pouvez vous déjà la tester en remplaçant le IN par la liste des valeurs qu'il génère ?
Marc.
Hors ligne
Limit (cost=328.74..328.86 rows=50 width=89) (actual time=66242.212..66242.324 rows=50 loops=1)
-> Sort (cost=328.74..328.90 rows=66 width=89) (actual time=66242.209..66242.248 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=12.48..326.74 rows=66 width=89) (actual time=90.535..66191.382 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..311.70 rows=66 width=61) (actual time=74.016..65965.591 rows=14794 loop) Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (a.param_1_integer = ANY ('{12,13,19,1,2,21,22,32,31}'::integer[]))
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=16.473..16.473 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=7.120..16.068 rows=377 loops=1)
Total runtime: 66242.448 ms
Voilà le résultat avec les valeur de l'ensemble
Hors ligne
je n'avais pas vu, param_1 ne fait pas partie de l'index.
"table_primaire__start_time_desc__index" btree (time_idx, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time_bigint DESC)
Mais de toutes façons, je ne pense pas qu'il puisse faire un range scan sur le start_time si on a plusieurs valeurs différentes pour param_1, donc ça ne résoudra pas le problème, qui est qu'on récupère physiquement 15 000 enregistrements alors qu'on en veut 50.
J'aurais bien une solution pour cette requête, mais elle est un peu tirée par les cheveux, pas forcément simple de mise en oeuvre, et pas forcément applicable au reste de votre application, donc je ne vous la proposerai que quand vous aurez essayé ce que propose gleu.
Marc.
Hors ligne
Du coup j'ai créé un index pour chacune des colonnes, voici le résultat :
Mais du coup, je me dis que la construction d'index n'est peut-être pas optimum.
Dans ce cas chaque index fait quasiment 2 giga.
Limit (cost=277098.81..277098.93 rows=50 width=89) (actual time=215412.684..215412.807 rows=50 loops=1)
-> Sort (cost=277098.81..277099.36 rows=223 width=89) (actual time=215412.681..215412.728 rows=50 loops=1)
Sort Key: a.start_time
Sort Method: top-N heapsort Memory: 23kB
-> Hash Left Join (cost=183558.79..277091.40 rows=223 width=89) (actual time=203689.855..215370.352 rows=14794 loops=1)
Hash Cond: (a.end_code = c.end_code)
-> Bitmap Heap Scan on table_primaire a (cost=183546.31..277070.28 rows=223 width=61) (actual time=203652.352..215159.757 rows=14794 loops=1)
Recheck Cond: ((param_5_integer = 371) AND (param_2_integer = 329728))
Filter: ((time_idx = 1267225200) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (stack = ANY ('{12,13,19,1,2,21,22,32,31}'::integer[])))
-> BitmapAnd (cost=183546.31..183546.31 rows=25360 width=0) (actual time=120291.092..120291.092 rows=0 loops=1)
-> Bitmap Index Scan on table_primaire__param_5_integer__index (cost=0.00..4059.09 rows=248713 width=0) (actual time=81.654..81.654 rows=263916 loops=1)
Index Cond: (param_5_integer = 371)
-> Bitmap Index Scan on d_cms_activity__param_2_integer__index (cost=0.00..179486.86 rows=11026282 width=0) (actual time=120071.308..120071.308 rows=11102682 loo) Index Cond: (param_2_integer = 329728)
-> Hash (cost=7.77..7.77 rows=377 width=36) (actual time=16.658..16.658 rows=377 loops=1)
-> Seq Scan on table_secondaire c (cost=0.00..7.77 rows=377 width=36) (actual time=8.223..16.259 rows=377 loops=1)
Total runtime: 215422.419 ms
Hors ligne
En dehors d'un ajout de mémoire ou d'un changement de schéma, je ne vois pas trop ce qu'on peut faire.
Guillaume.
Hors ligne
Ok, alors j'enclenche le mode tordu 
Un point que je relève au passage : à quoi sert la jointure sur c, je ne la vois nulle part dans le select (mais il est tôt )
Le problème de la requête, c'est qu'elle demande 50 enregistrements, mais a plusieurs critères sur param_1, et donc ne peut pas utiliser un parcours d'index pour trouver les 50 plus récents correspondant au critère demandé. L'autre problème, mais on ne pourra rien y faire, c'est qu'il estime à 223 le nombre d'enregistrements rapportés par le parcours d'index, au lieu de 15000. Donc que les critères sont davantage liés entre eux qu'il ne le pense (corrélation entre les colonnes, on n'a pas de stats là dessus).
Pour bien faire, la requête, si on avait un index sur (time_idx, param_1_integer, param_2_integer, param_3_integer, param_4_integer, param_5_integer, start_time_bigint DESC) devrait pouvoir répondre quasi instantanément si on lui demandait son résultat pour un seul param_1.
Commencez par vérifier ce point (créez un index composé comme indiqué, puis lancez la requête avec un seul param_1).
Si c'est bien le cas, vous pouvez contourner le problème en écrivant :
SELECT date_trunc( 'second', TIMESTAMP WITH TIME ZONE 'epoch' + start_time_bigint/1000 * interval '1 second') as "Day",
to_char(TIMESTAMP WITH TIME ZONE 'epoch' + (a.stop_time_bigint/1000) * interval '1 second', 'HH24:MI:SS') as "->",
tmp.* FROM
(select a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
champs_4_char,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx_bigint= 1266879600
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 12
order by start_time_bigint desc
LIMIT 50)
UNION ALL
(
(select a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
champs_4_char,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx_bigint= 1266879600
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 13
order by start_time_bigint desc
LIMIT 50)
UNION ALL
…
LIMIT 50) AS tmp
LEFT JOIN table_secondaire as c on ( tmp.end_code_integer = c.end_code_integer )
order by start_time_bigint desc
limit 50;
(j'ai probablement fait une erreur de syntaxe, n'ayant pas pu la tester)
Si ça marche et passe par l'index, vous n'aurez que 450 enregistrements à aller chercher, au lieu de 15000.
Dernière modification par Marc Cousin (02/03/2010 08:36:44)
Marc.
Hors ligne
Bonjour,
j'ai fait le test mais cela ne donne qu'une légère amélioration.
Le problème c'est la mise en cache de l'information je pense :
à la première exécution c'est très long
à la deuxième aucun soucis
si je relance une autre requete c'est très long
si je reviens sur la première c'est très long
....
Pensez - vous que l'utilisation d'une vue donnant une table réduite pourrait améliorer les performances ?
Ou carrément la création d'une sous-table ?
Je n'ai pas d'autre piste pour l'instant.
Nous allons bientot passer en 8Giga de ram et peut-être plus est-ce un moyen de gagner en performance dans ce cas ci ?
Où est-ce que le nombre de ligne + la requete ne donneront rien de mieux niveau perf ?
Hors ligne
Pensez - vous que l'utilisation d'une vue donnant une table réduite pourrait améliorer les performances ?
Non, pas de vue matérialisée avec PostgreSQL. Enfin, pas en natif.
Ou carrément la création d'une sous-table ?
Possible.
Nous allons bientot passer en 8Giga de ram et peut-être plus est-ce un moyen de gagner en performance dans ce cas ci ?
Oui, à coup (pratiquement) sûr. Il faudra certainement modifier la conf de PostgreSQL, notamment shared_buffers et effective_cache_size.
Où est-ce que le nombre de ligne + la requete ne donneront rien de mieux niveau perf ?
Guillaume.
Hors ligne
Je viens de remarquer que mon effective_cache_size est configuré à 128 MB.
Puis-je l'augmenter vu ma config ?
Qu'apporte ce paramètre ?
Merci pour toutes vos réponses en tout cas.
Hors ligne
Est-ce qu'il y aurait moyen d'avoir la requête exacte que vous avez tentée, ainsi que le plan et la définition de l'index créés ? Par ce que l'augmentation de la RAM résoudra probablement le problème, mais seulement tant que le volume de données ne croit pas.
Marc.
Hors ligne
Tout est plus au début du post,
j'ai juste anonymisée les champs mais la requête est exactement celle que j'ai tenté.
La table_primaire grossis de 1 Millions de lignes environs toutes les nuits et ne bougent plus après, les index sont détruits avant insertion et recréés après, un analyze est ensuite lancé
La table_secondaire ne change jamais
Hors ligne
Ok, donc l'augmentation de la RAM ne résoudra pas le problème je pense. Ou du moins ne le résoudra pas très longtemps.
Par contre, je vous demaindais la requête réécrite comme je vous l'avais proposée (juste une des sous-requêtes suffira, avec une seule valeur de param_1), ainsi que le plan qu'elle génère et la déclaration d'index que vous avez faite.
Afin de savoir si la sous requête utilise ou non l'index, et peut donc aller chercher directement les 50 enregistrements qiu vous intéressent.
Marc.
Hors ligne
Voici les résultats du test proposé :
explain analyze
select date_trunc( 'second', TIMESTAMP WITH TIME ZONE 'epoch' + tmp.start_time/1000 * interval '1 second') as "Day",
to_char(TIMESTAMP WITH TIME ZONE 'epoch' + (tmp.stop_time/1000) * interval '1 second', 'HH24:MI:SS') as "->",
tmp.* FROM
((SELECT
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx= 1267225200
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 12
order by start_time desc
limit 50)
UNION ALL
(SELECT
a.param_4_integer,
a.param5_integer,
COALESCE(a.champs3_char,'') ,
a.champs_6_bigint,
a.champs_7_bigint,
a.champs_8_bigint,
a.champs_9_bigint,
a.champs_10_bigint as proc_id
from
table_primaire as a
WHERE
a.time_idx= 1267225200
AND a.param_2_integer = 329728
AND a.param_3_integer = 255
AND a.param_4_integer = 3
AND a.param_5_integer = 371
AND a.param_1_integer = 13
order by start_time desc
limit 50)) AS tmp order by start_time desc limit 50;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=656.87..656.90 rows=10 width=88) (actual time=54675.783..54675.783 rows=0 loops=1)
-> Sort (cost=656.87..656.90 rows=10 width=88) (actual time=54675.781..54675.781 rows=0 loops=1)
Sort Key: "*SELECT* 1".start_time
Sort Method: quicksort Memory: 17kB
-> Result (cost=0.00..656.71 rows=10 width=88) (actual time=54675.770..54675.770 rows=0 loops=1)
-> Append (cost=0.00..656.46 rows=10 width=88) (actual time=54675.768..54675.768 rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..328.27 rows=9 width=88) (actual time=54661.435..54661.435 rows=0 loops=1)
-> Limit (cost=0.00..328.18 rows=9 width=57) (actual time=54661.433..54661.433 rows=0 loops=1)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..328.18 rows=9 width=57) (actual time=54661.430..54661) Index Cond: ((time_idx = 1267225200) AND (param_2_integer = 329728) AND (param_3_integer = 255) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (stack = 12)
-> Subquery Scan "*SELECT* 2" (cost=0.00..328.19 rows=1 width=88) (actual time=14.329..14.329 rows=0 loops=1)
-> Limit (cost=0.00..328.18 rows=1 width=57) (actual time=14.327..14.327 rows=0 loops=1)
-> Index Scan using table_primaire__start_time_desc__index on table_primaire a (cost=0.00..328.18 rows=1 width=57) (actual time=14.324..14.324 r) Index Cond: ((time_idx = 1267225200) AND (type = param_2_integer) AND (subtype = param_3_integer) AND (param_4_integer = 3) AND (param_5_integer = 371))
Filter: (stack = 13)
Total runtime: 54675.899 ms
(17 rows)
Time: 54790.519 ms
Dernière modification par Mekhanik (02/03/2010 16:18:51)
Hors ligne
Il vous manque param_1 (je présume qu'en vrai il s'appelle stack) dans votre index composé pour que la réponse puisse être performante. Je pense que cette solution va marcher vu le plan.
Il vous faut un index composé sur param_1, param_2, param_3, param_4, pararm_5, time_idx, start_time desc
Si l'index est dédié à cette requête, et que vous le reconstruisez tout les jours, essayez de classer les 6 premiers paramètres du plus discriminant au moins discriminant, cela diminuera le nombre de pages d'index à aller chercher.
Marc.
Hors ligne
Bonjour je vous remercie pour votre aide mais les différentes solutions n'ont rien donné par contre en divisant la table_primaire en plusieurs tables nous obtenons des satisfaisaites, nous allons nous orienter vers cette solution.
Merci encore
Hors ligne
