Forums PostgreSQL.fr

Redge

Membre

Requête anormalement longue

Bonjour,

J'ai actuellement une requête SQL qui fonctionne très bien sur ma base et pour une raison X, elle passe de 1s à 10mn de fonctionnement sans raison particulière. Je voulais savoir la meilleure méthodologie pour essayer de comprendre ce qui passe dans PostgreSQL.

Actuellement, j'ai fait loguer PostgreSQL avec ces paramètres: log_min_messages = debug1 et log_error_verbosity = verbose
mais tout ce que j'obtient dans mon fichier de log c'est la requête en question comme quoi elle a durée 10mn:

"Feb  3 20:01:28 localhost postgres[23168]: [1-1] user=postgres,db=mabase LOG:  00000: duration: 536821.381 ms  statement:"

et pas plus d'infos. Du coup je sais pas où chercher et comment m'y prendre.

Plus d'infos sur le fonctionnement de mon application:

J'ai une application qui tourne 24h/24h et 7j/7j et utilise la base en permanence.
L'application fait de l'acquisition de données et utilise la base comme historique.
Quand je démarre l'application, la base est vide et toutes les 5minutes, j'insère environ 40 000 lignes dans plusieurs tables.
Ce qui fait qu'au bout de 2 jours, j'ai des tables a 30 millions d'enregistrements.
Ensuite une purge horaire se met en place (avec les vacuum qui vont bien) pour que les tables stagnent à 30 millions d'enregistrements environ, ce qui représente la taille de l'historique de l'application.

En parallèle de ce fonctionnement, toutes les 5mn, je lance une requête qui analyse certaines valeurs dans les tables de données de 30 millions d'enregistrement.

En partant base vide, pendant 2h de fonctionnement, la requête fonctionne très bien et s'exécute en moins d'1s.
Et arrivé à 2h de fonctionnement environ (donc 1 millions d'enregistrements dans mes tables), la requête passe de 1s a 10mn sans raison particulière et peut même aller jusqu'à 1h de fonctionnement.

La requête fait un INSERT d'un SELECT qui comporte une jointure sur 2 tables en utilisant 2 champs indexés pour la jointure et un WHERE sur une colonne numérique non indexée.

flo

Membre

Re : Requête anormalement longue

Bonjour,

Et arrivé à 2h de fonctionnement environ (donc 1 millions d'enregistrements dans mes tables), la requête passe de 1s a 10mn sans raison particulière et peut même aller jusqu'à 1h de fonctionnement.

La requête fait un INSERT d'un SELECT qui comporte une jointure sur 2 tables en utilisant 2 champs indexés pour la jointure et un WHERE sur une colonne numérique non indexée.

Sans raison particulière??? Attention à ce que vous écrivez, il y en a qui sont susceptibles ici il y a bien une raison (mais vous ne l'avez pas encore trouvée... )

La première chose à faire, est de faire un explain (de préférence un explain analyze ) de la requête avec un nombre significatif de données, dans les conditions de votre problème  (dans les 1 millions d'enregistrement dans les 2 tables, donc, et dans les mêmes conditions au niveau des vacuum et des statistiques).
D'ailleurs c'est l'INSERT ou le SELECT qui est anormalement long?
(si vous cherchez un coup de main, donnez la requête, et le résultat de l'explain)

Redge

Membre

Re : Requête anormalement longue

A noter que quand j'exécute la requête sur la même base et donc meme contenu mais à partir de pgadmin, l'exécution est instantanée.

Voici le resultat du explain analyse sur la requete:

"Subquery Scan "*SELECT*"  (cost=5926.77..5931.53 rows=238 width=36) (actual time=21.061..21.061 rows=0 loops=1)"
"  ->  Sort  (cost=5926.77..5927.36 rows=238 width=46) (actual time=21.058..21.058 rows=0 loops=1)"
"        Sort Key: t0.na"
"        ->  Hash Join  (cost=3854.95..5917.37 rows=238 width=46) (actual time=21.050..21.050 rows=0 loops=1)"
"              Hash Cond: (t1.na = t0.na)"
"              ->  Index Scan using index1 t1  (cost=0.00..1973.62 rows=232 width=50) (actual time=21.048..21.048 rows=0 loops=1)"
"                    Index Cond: (1102041250 = "minute")"
"                    Filter: (subtotal < 50::double precision)"
"              ->  Hash  (cost=3330.04..3330.04 rows=41993 width=50) (never executed)"
"                    ->  Index Scan using index2 t0  (cost=0.00..3330.04 rows=41993 width=50) (never executed)"
"                          Index Cond: ("minute" = 1102041250)"
"                          Filter: ((na IS NOT NULL) AND (total >= 100::double precision))"
"Total runtime: 21.169 ms"

La requête étant un insert d'un select, je ne sais pas si c'est le select ou l'insert. Mais je penche plutôt pour le select car il attaque des tables conséquentes. Tandis que l'insert, insert dans une table qui contient 3000 lignes.

Dernière modification par Redge (04/02/2011 15:03:54)

gleu

Administrateur

Re : Requête anormalement longue

Vous devriez installer le module auto_explain et le configurer pour qu'il fasse un EXPLAIN automatique des requête très longue (par exemple supérieur à 1 ou 2, voire 5 minutes). Comme ça, vous aurez le EXPLAIN de la requête longue et vous pourrez le comparer au EXPLAIN de la requête rapide.

---

Guillaume.

flo

Membre

Re : Requête anormalement longue

A noter que quand j'exécute la requête sur la même base et donc meme contenu mais à partir de pgadmin, l'exécution est instantanée.

Curieux. Vous n'avez pas de base de test ou de pré-production pour faire des tests plus approfondis? À moins que vous ne parliez déjà d'une telle base?
Les seules raisons que je vois pour cela sont :
1. vous ne passez pas les mêmes paramètres que votre application.
2. lorsque vous effectuez la requête "à la main", les conditions sont différentes : données en cache, statistiques plus à jour...

Voici le resultat du explain analyse sur la requete:

"Total runtime: 21.169 ms"

Evidemment, ce n'est pas long.
Question con, mais on ne sait jamais : vous avez le code de l'application? Vous êtes certain que c'est bien une requête unitaire qui est aussi longue, et non un traitement qui exécute une requête en boucle? (Je ne connais pas votre rôle, votre niveau d'expérience, etc... et ce sont des choses que j'ai déjà vues)
Autre question : vous avez tenté de faire l'insert à la main également? Parce que là, vous ne savez pas ce  qui pose problème, et vous risquez de partir sur une fausse piste.

La priorité pour vous, à mon avis, est soit d'isoler le problème. En fonction de votre rôle, de vos autorisations, vous pouvez soit commencer par prendre le problème du côté de l'application : que fait-elle exactement? regardez le code, les traces, ajoutez des timer dans les logs. Soit suivre le conseil de gleu et utiliser auto_explain. Si vous n'avez pas le droit d'ajouter des modules en production, faites-le en préproduction, ou recette, ou développement, du moment que vous arrivez à avoir le même volume de données, et le même environnement (insertion, purges, vacuum...)

Lorsque vous reviendrez sur le forum, essayez également d'être plus précis :
donnez le texte de votre requête, la description des tables impliquées (colonnes, index, contraintes) dans la requête, ainsi que la description précise des 2 autres requêtes dont vous parlez : celle d'insertion des données acquises et celle de purge (sur quelles tables travaillent-elles? À quelle fréquence?). Afin de comprendre laquelle fait quoi, sur quelles tables, et quelles sont les tailles respectives des tables. Faites le nécessaire pour repérer si la durée extraordinairement longue se produit à un instant particulier (après la purge? après l'insertion? pendant le vacuum? ...)

De toute manière, il faut absolument que vous arriviez à repérer et recrééer les conditions qui produisent la requête trop longue...

Dernière modification par flo (05/02/2011 08:51:44)

Redge

Membre

Re : Requête anormalement longue

Merci pour vos réponses
Gleu, c'est exactement le genre de tip que je cherchais mais je suis en PostgreSQL 8.2 et je n'ai apparemment pas ce module.

Plus d'infos sur la requete:

INSERT INTO table2 (id_alarm,critical_level,acknowledgement,ta,ta_value,up_down,na,na_value,alarm_type)
SELECT
'sdas.0983a5406846f981070700ed15e818cd',
'major',
'0',
'minute',
1102031950,
'up',
'sgsnrncipggsnip',
t0.na,
'static'
FROM table0 t0, table1 t1
WHERE t0.na = t1.na
AND t0.minute = t1.minute
AND t0.minute = 1102031950
AND t0.na IS NOT NULL
AND t1.subtotal<50
AND t0.total>=100 ORDER BY na

Avec t0.na et t1.na indexé (champ text), t0.minute et t1.minute indexé (champ text).
t1.subtotal et t0.total sont de type real et non indexé.
Et table2 (18 champs) qui va accueillir les inserts, il y a 10 index dont certains sur plusieurs champs en même temps. (Sic, ce n'est pas moi qui a conçu le modèle de données...)

Plus d'info sur moi:

Je suis le développeur de l'application et je travaille sur une base de test,
je stress l'application (un maximum de données à traiter) pour valider sa tenue en charge.

La requete que j'ai lancé dans pgadmin est un copier coller de la requête issu des logs de l'application.

Un peu plus d'infos sur l'application:

Une purge des données est lancé toutes les heures et un vacuum puis analyse est effectué.
La purge ne fonctionne qu'à partir de 2 jours (taille de l'historique)

Mon problème survenant dés la deuxième heure environ, cela signifie qu'aucun vacuum n'a été effectué
mais seulement 1 ou 2 ANALYSE complet sur toute la base.

En partant du principe que ca peut être un élement exterieur qui perturbre ma requete, j'ai décidé de désactiver la purge.
(Tache dans la crontab)
J'ai lancé l'application tout le week end et je n'ai pas eu le problème.

Donc maintenant j'ai 2 pistes:

Soit c'est le ANALYSE l'élément perturbateur.
Soit c'est la requete de purge qui travaille sur les memes tables que la requete l'élément perturbateur.

Je relance mon application avec la tache de purge et les vacuums / analyse qui vont se déclencher mais sans la requête de purge qui travaille sur les memes tables que ma requete.

J'ai l'impression qu'il y a une histoire de lock sur les tables.

Il y a pas possibilité de rendre PostgreSQL encore plus verbeux, par exemple DEBUG5 pour savoir ce qu'il est en train de faire ou ce qui le gène pendant le problème ?

A part pgfouine et le module auto-explain que je ne dispose pas, vous me conseillez quoi d'autre comme outil ?

gleu

Administrateur

Re : Requête anormalement longue

Vous pouvez mettre PostgreSQL en mode DEBUG5 mais je vous mets au défi d'en tirer quelque chose d'utile dans votre cas

Avec cette version, vous n'avez pas d'autres outils disponibles. Pour ce qui concerne les locks, il peut être intéressant de surveiller leur nombre et leur niveaux. Je ne vois pas quelle table est régulièrement purgée mais à priori, ça ne sera pas elle qui sera la cause des verrous. Il serait certainement bon de surveiller certains catalogues systèmes de PostgreSQL avec un outil comme check_postgres.pl.

---

Guillaume.

Redge

Membre

Re : Requête anormalement longue

bon, j'ai lancé en DEBUG5 et j'avoue que je commence déja à regretter

Donc j'ai relancé la tache de purge avec les vacuum et analyse mais sans la requête qui purge la table concernée par ma requête récalcitrante, cela fait maintenant plus de 2h et le problème n'est pas revenu.
Je laisse tourner ca aujourd'hui et cette nuit et demain je remet la requête de purge en priant que le problème reviennent.

Ce qui voudrais dire que ma requête de purge (lancer une fois par heure) fait déconner la requête "INSERT de SELECT" lancée toutes les 5 mn

Et si ma piste est un cul de sac ou pas, j'ai encore comme solution de vérifier les locks avec check_postgres.pl, merci

Dernière modification par Redge (07/02/2011 16:21:41)

Redge

Membre

Re : Requête anormalement longue

Bon j'ai suivi une fausse piste, je recommence mes investigations à 0.

En espérant etre le plus compréhensible possible:

Je démarre mon application base vide.
j'insere 40 000 enregistrements toutes les 5 minutes dans table1 et table2.
Ce qui fait qu'au bout d'1 heure, j'ai environ 480 000 enregistrements dans ces tables.

Toutes les 5 minutes, je fais également une requete (qu'on va appeler requete1) de calcul sur ces tables en les joignant (insert d'un select join)
ce qui m'insere environ 1000 enregistrements dans une autre table qu'on va appeler table3

Toutes les heures, je fais un ANALYSE sur toute la base de données pour mettre à jour les statistiques.
Cette commande prend environ 25s

Pendant presque 2 heures, ma requete1 marche sans soucis et s'execute en moins d'1 seconde.

Juste apres (pas pendant) le 2ème ANALYSE qui prend environ 25s encore, ma requete1 prend cette fois 9mn sans raison particulière.

Pendant qu'elle a prit 9mn, j'ai observé le nombre de lock sur la base avec la commande suivante
check_postgres.pl --action=locks --dbname=mabase --warning=1 --critical=5
que j'ai lancé toutes les minutes:

POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.01  'mabase.total'=17;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.00  'mabase.total'=17;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 28 | time=0.00  'mabase.total'=28;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 24 | time=0.00  'mabase.total'=24;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.00  'mabase.total'=18;10;20
POSTGRES_LOCKS CRITICAL: DB "mabase" total locks: 28 | time=0.01  'mabase.total'=28;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.00  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 18 | time=0.01  'mabase.total'=18;10;20
POSTGRES_LOCKS WARNING: DB "mabase" total locks: 17 | time=0.01  'mabase.total'=17;10;20

Marc Cousin

Membre

Re : Requête anormalement longue

Bon je prends le train en marche, gleu n'étant pas dispo aujourd'hui :

Si la requête se met à prendre 9 minutes d'un coup après le analyze, on peut partir de l'hypothèse que cela provient de nouvelles versions de statistiques qui font changer de plan (c'est une des deux choses que je vois qui peuvent ralentir votre application, l'autre étant effectivement un problème de verrouillage).

-> Quand les performances sont bonnes, à quoi ressemble le plan (la partie SELECT, ne vous embêtez pas avec l'insert), avec un EXPLAIN ANALYZE?
-> Que devient ce plan quand ça s'effondre ?
-> Question subsidiaire : est-ce exactement la requête que vous envoyez au moteur ? Ou bien envoyez vous une requête préparée ?

---

Marc.

Redge

Membre

Re : Requête anormalement longue

Marc, tu m'as mit sur une superbe piste qui rejoint celle de Guillaume:

A défaut d'avoir un autoexplain en 8.2, j'avais pas tilté que je pouvais modifier le code pour qu'il exécute la requête avec un EXPLAIN ANALYSE

Donc la j'ai relancé mon appli base vide avec l'EXPLAIN ANALYSE de positionné sur la requête récalcitrante.
Plus qu'à attendre que le bug arrive. (environ 2 heures)

Comme ca, on verra le plan d'exécution avant l'ANALYSE et après l'ANALYSE

Merci !

Redge

Membre

Re : Requête anormalement longue

Depuis que j'ai relancé avec cette modif, j'ai des temps de traitement complètement bizarre alors que j'ai mit un explain analyse que sur la requete récalcitrante.
Bref. J'ai quand même pu isoler dans mes logs la requête qui s'exécute rapidement et 5 minutes après, exactement la même mais qui prend 9mn.
Et en effet, on voit bien que le plan a changé:

Alors la requete:

INSERT INTO table3
(id_alarm,critical_level,acknowledgement,ta,ta_value,up_down,na,na_value,alarm_type)
SELECT
'sdas.0983a5406846f981070700ed15e818cd',
'major',
'0',
'minute',
1102081945,
'up',
'na',
t0.na,
'static'
FROM table1 t0, table2 t1
WHERE t0.na = t1.na
AND t0.minute = t1.minute
AND  t0.minute = 1102081945
AND  t0.na IS NOT NULL
AND t1.subtotal<50
AND t0.total>=100 ORDER BY na

Version rapide

Subquery Scan "*SELECT*"  (cost=2808.17..2808.29 rows=6 width=36) (actual time=16.787..16.787 rows=0 loops=1)
  ->  Sort  (cost=2808.17..2808.18 rows=6 width=46) (actual time=16.784..16.784 rows=0 loops=1)
        Sort Key: t0.na
        ->  Nested Loop  (cost=4.57..2808.09 rows=6 width=46) (actual time=16.768..16.768 rows=0 loops=1)
              ->  Index Scan using idx_table2_minute on table2 t1  (cost=0.00..2527.91 rows=5 width=50) (actual time=2.362..16.453 rows=3 loops=1)
                    Index Cond: (1102081945 = "minute")
                    Filter: (subtotal < 50::double precision)
              ->  Bitmap Heap Scan on table1 t0  (cost=4.57..55.87 rows=13 width=50) (actual time=0.100..0.100 rows=0 loops=3)
                    Recheck Cond: (t0.na = t1.na)
                    Filter: (("minute" = 1102081945) AND (total >= 100::double precision))
                    ->  Bitmap Index Scan on idx_table1_na  (cost=0.00..4.57 rows=13 width=0) (actual time=0.054..0.054 rows=13 loops=3)
                          Index Cond: (t0.na = t1.na)
Total runtime: 16.921 ms

Version lente

Subquery Scan "*SELECT*"  (cost=16.70..16.72 rows=1 width=36) (actual time=524459.648..524459.648 rows=0 loops=1)
  ->  Sort  (cost=16.70..16.70 rows=1 width=46) (actual time=524459.644..524459.644 rows=0 loops=1)
        Sort Key: t0.na
        ->  Nested Loop  (cost=0.00..16.69 rows=1 width=46) (actual time=524459.620..524459.620 rows=0 loops=1)
              Join Filter: (t0.na = t1.na)
              ->  Index Scan using idx_table1_minute on table1 t0  (cost=0.00..8.34 rows=1 width=50) (actual time=0.022..164.323 rows=39988 loops=1)
                    Index Cond: ("minute" = 1102081950)
                    Filter: ((na IS NOT NULL) AND (total >= 100::double precision))
              ->  Index Scan using idx_table2_minute on table2 t1  (cost=0.00..8.34 rows=1 width=50) (actual time=1.014..13.107 rows=1 loops=39988)
                    Index Cond: (1102081950 = "minute")
                    Filter: (subtotal < 50::double precision)
Total runtime: 524461.695 ms

Redge

Membre

Re : Requête anormalement longue

Dans mes tables de données, la colonne minute est la plus discriminante car un filtre dessus représente que 40 000 enregistrements au lieu de plusieurs millions.

Ce que j'en conclu rapidement, c'est dans la version rapide, il filtre en premier sur la colonne minute, donc il ne garde que 40 000 enregistrements pour appliquer la suite de la requête. Alors qu'il ne le fait pas dans la version lente.

gleu

Administrateur

Re : Requête anormalement longue

Dans les deux cas, il filtre sur la colonne minute, via l'index.

Dans le cas rapide, PostgreSQL pense récupérer 13 lignes dans la table t0 et 5 dans la table t1. Il va donc commencer par la table t1, qui lui en renvoie réellement 3. Le parcours d'index sur la table t0 ne devra donc être fait que trois fois. Simple et rapide.

Dans le cas lent, PostgreSQL pense ne récupérer qu'une ligne dans la table t0 et dans la table t1. Il va donc utiliser l'un des deux, peu importe, pour commencer. Malheureusement, cette table a en réalité dans les 40000 lignes, ce qui fait que la recherche d'élément dans l'index pour le parcours de la table t1 ne se fait pas une seule fois mais 40000 fois, ce qui est beaucoup beaucoup plus lent.

La conclusion à tout cela est que les statistiques pour la table t1 sont fausses dans le cas de l'exécution lente. Suivant la taille de la table, il faudra soit demander un échantillon statistique plus important pour avoir une meilleure représentativité des données, soit mettre à jour plus fréquemment les statistiques pour avoir des infos plus fraîches.

---

Guillaume.

Marc Cousin

Membre

Re : Requête anormalement longue

Sachant qu'en 8.2, le default_statistics_target est à 10, qui est très insuffisant. Il serait bon de commencer par le mettre à 100, comme pour les versions plus récentes de PostgreSQL.

Par ailleurs les temps sont aussi très longs parce qu'il n'y a pas d'index composé sur minute et subtotal sur t1. Parce que même avec 40 000 enregistrements, on ne devrait pas être à 524s.

Mais c'est clair. Commencez par augmenter la finesse des statistiques.

---

Marc.

Redge

Membre

Re : Requête anormalement longue

Alors mon paramètre default_statistics_target est déja à 100.
Pour l'index composé, je peux pas car je n'ai pas seulement subtotal comme colonne sur laquelle je peux faire des filtres mais plein d'autres.

Donc la j'ai relancé mon application. Et au lieu de faire un ANALYSE sur toute la base une fois par heure.
Je fais un ANALYSE sur mes tables table1 et table2 juste après l'insertion des 40 000 lignes qui a lieu toutes les 5 minutes.

Et pour l'instant, ca a l'air de plutôt pas mal fonctionner

Marc Cousin

Membre

Re : Requête anormalement longue

Je crois que j'ai compris ce qui te fait mal…

C'est une table du genre table de log sur laquelle tu insères des nouvelles données en permanence, et tu récupères en permanence les données les plus récentes de la table ? Des données qui n'étaient vraisemblablement pas là au moment où les stats ont été passées ?

---

Marc.

Redge

Membre

Re : Requête anormalement longue

Toutes les 5 minutes, je parse des fichiers, j'en extrait des données et j'insère ca dans PostgreSQL. Ca représente 40 000 lignes environ des mes tables.
C'est une sorte d'application "temps réel" à 5mn (:lol:) avec un historique de 2 jours.
Une fois 2 jours passés, pour que la base arrête de grossir, je supprime toutes les heures les anciennes données (donc un delete de 40 000 x 12)

Voila pour le fonctionnement grossomodo.

J'avais déja fait appel à vos services il y a maintenant presque 1 an pour m'aider à mettre en place une bonne politique de VACUUM, ANALYSE
http://forums.postgresql.fr/viewtopic.php?id=758

Ensuite sur ces données acquises, je fais claquer des alarmes si des valeurs dépassent des seuils (c'est la requête en question)

Donc en effet, la plupart du temps, ma requête travaille sur des données qui étaient non présentes quand l'ANALYSE a été effectué.

Maintenant que j'ai mit l'ANALYSE juste après l'insertion des 40 000 lignes, ca va beaucoup mieux !
Ca va faire maintenant 4 heures que tourne mon application et les temps de traitement sont très bons.
A voir dans 2 jours quand mes tables de données seront a 30 000 000 d'enregistrements...

Si ca se dégrade pas, je crois que vous avez mérité tous les deux une bouteille de vin

Marc Cousin

Membre

Re : Requête anormalement longue

Oui, c'est tout à fait normal comme comportement. C'est une limitation connue de l'optimiseur. En 9.0, il utiliserait l'index pour 'extrapoler' les statistiques, voyant que la requête recherche des données en dehors des zones couvertes par les histogrammes. Ça n'est toujours pas parfait, mais le plan reste logique au lieu d'être totalement absurde comme ce que vous avez vu…

Donc oui, ça va tenir, si vous faites un analyze avant chaque requête.

Mais si vous avez la possibilité, mettez une 9.0 c'est encore plus simple

---

Marc.

Redge

Membre

Re : Requête anormalement longue

Ah bah ca, je voudrais bien être en 9.0 mais le soft a plein de limitations qui font qu'on est bloqué en 8.2, bref, c'est une autre histoire.

Donc la je suis presque à 1 journée de traitement, mes tables de données sont à 10 760 000.
Et l'analyse prend de plus en plus de temps: 38s pour analyser une table toutes les 5 minutes, ca commence à devenir critique.
Je vais jamais réussir à tenir 2 jours d'historique (30 000 000 d'enregistrements dans mes tables)

Il y a pas une technique pour booster un peu l'analyse ou lui dire d'analyser que les enregistrements les plus récents.
ou alors je diminue le default_statistics_target ? mais je sais pas si le gain sera significatif ?

Le choix de refaire le schéma de base avec du partitionnement devient de plus en plus évident mais pour l'instant je suis bloqué avec le schéma actuel

Marc Cousin

Membre

Re : Requête anormalement longue

L'analyse va se stabiliser : il fait un échantillonnage. Avec un default_statistics_target à 100, il va prendre 30 000 enregistrements d'échantillons environ.

Sinon, tu peux diminuer le default_statistics_target, ça va accélérer les choses. Il faut juste que l'échantillon reste suffisant pour que tes performances ne se dégradent pas.

---

Marc.

Redge

Membre

Re : Requête anormalement longue

J'ai extrait le temps que prend l'analyse toutes les 5 minutes depuis hier.

On voit bien que l'analyse se stabilise mais il se stabilisait à un timing un peu trop élévé à mon gout.

Quand la courbe chute et revient à un temps raisonnable c'est parce que j'ai passé default_statistics_target de 100 à 10

A voir les impacts sur mes requetes mais pour l'instant, tout va bien.

Redge

Membre

Re : Requête anormalement longue

Bonjour,

Je relance un peu mon post

Ca marche un peu près bien le fait d'insérer 40 000 enregistrements toutes les 5 minutes et faire aussitôt après un ANALYSE sur la table
(A condition d'avoir default_statistics_target à 10 sinon l'ANALYSE est trop long).
Ma purge est 1 fois par heure: DELETE de 480 000 enregistrements suivit d'un VACUUM mais pas besoin d'ANALYSE puisqu'il est fait toutes les 5 minutes.

Mais maintenant je suis confronté à un nouveau problème: pendant un DELETE ou un VACUUM, je peux très bien être en train de faire un ANALYSE vu qu'il a lieu toutes les 5 minutes.
Ce qui me donne des temps de traitement loin d'être constant et je ne maitrise pas la non parallélisation de ces commandes.

Du coup j'ai changé de technique:

J'insère 40 000 enregistrements toutes les 5 minutes et aussitôt après un ANALYSE, donc comme avant.
Par contre, Une fois tous les douze cycles, aussitot après l'insert et avant l'analyse, je fais un DELETE des 480 000 enregistrements les plus anciens puis VACUUM sur la table.
Comme ca, je suis sur que le DELETE ou VACUUM n'a pas lieu pendant l'INSERT ou l'ANALYSE.

Ca a l'air de marcher pas trop mal mais je suis confronté à un nouveau problème.

L'insert de 40 000 enregistrements dure 18s en général dans ma table de 20 millions.
Quand a lieu le DELETE puis VACUUM et enfin ANALYSE, l'insert du cycle d'apres dure 267s
et le cycle suivant, l'insert revient à 18s.

J'ai mit un EXPLAIN ANALYSE sur la requete en question car c'est un INSERT d'un SELECT mais le résultat est le même que ce soit pour la requete de 18s
ou celle de 267s.

Requete de 18s
Subquery Scan "*SELECT*"  (cost=7200.00..7690.00 rows=4000 width=400) (actual time=320.344..692.228 rows=40000 loops=1)
  ->  HashAggregate  (cost=7200.00..7640.00 rows=4000 width=392) (actual time=320.332..596.716 rows=40000 loops=1)
        ->  Seq Scan on table_minute_1102221930  (cost=0.00..2400.00 rows=40000 width=392) (actual time=0.014..24.270 rows=40000 loops=1)
Total runtime: 17921.476 ms

Requete de 267s
Subquery Scan "*SELECT*"  (cost=7200.00..7690.00 rows=4000 width=400) (actual time=294.598..714.618 rows=40000 loops=1)
  ->  HashAggregate  (cost=7200.00..7640.00 rows=4000 width=392) (actual time=294.586..592.264 rows=40000 loops=1)
        ->  Seq Scan on table_minute_1102221935  (cost=0.00..2400.00 rows=40000 width=392) (actual time=0.014..22.726 rows=40000 loops=1)
Total runtime: 267104.377 ms
Delete result:
Insert result: 0
Execution time: 267

Donc j'imagine que c'est l'INSERT qui pose problème.

Une idée ?

gleu

Administrateur

Re : Requête anormalement longue

Une idée pour ?

Il est évident que deux opérations qui touchent le disque en même temps seront plus lentes qu'une seule

Si la question porte sur le moyen d'accélérer les opérations, ça manque singulièrement d'informations. Par exemple, les journaux de transactions sont-ils sur le même disque que les fichiers de données ?

---

Guillaume.

Redge

Membre

Re : Requête anormalement longue

Oui c'est sur 2 raids différents.
Ma question c'était pourquoi les performances de mon INSERT chute juste après que la table a eu un DELETE puis VACUUM/ANALYSE