Vous n'êtes pas identifié(e).
Pages : 1
Explain analyse en production :
"Bitmap Heap Scan on table_cible aa (cost=1705.16..11308019.02 rows=124449 width=60) (actual time=150.471..6904607.438 rows=119066 loops=1)"
" Recheck Cond: ((annee_semaine)::text = '2012S01'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1674.05 rows=124449 width=0) (actual time=146.611..146.611 rows=122400 loops=1)"
" Index Cond: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26) (actual time=57.981..57.982 rows=1 loops=119066)"
" -> Result (cost=0.00..90.07 rows=21 width=26) (actual time=6.759..57.941 rows=39 loops=119066)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26) (actual time=6.757..57.891 rows=39 loops=119066)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.63 rows=1 width=22) (actual time=3.993..3.994 rows=1 loops=119066)"
" Index Cond: (((dwh_gtin)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..65.18 rows=21 width=26) (actual time=3.009..58.705 rows=42 loops=109205)"
" Index Cond: (((b.dwh_gtin)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
"Total runtime: 6908342.499 ms"
Merci de votre réponse.
Ce que j'ai omis de préciser avant de poster mon commentaire, c'est que j'avais déjà créé l'index sur table_cible en production, et que c'était en cours.
Ca dure entre 1h30 et 3h par semaine avec cet index sur table_cible. Trop long pour 50 semaines à calculer...
Mon avis pour expliquer la longueur de ce traitement est que la requête parcours 1/5 de table_source (42M de ligne sur 200M) pour sommer l'indicateur sur les 52 dernières semaines. Le moteur Postgre doit donc sûrement considérer qu'il ne sert à rien d'utiliser index_source_4 qui est juste sur annee_semaine.
Le explain analyse en production est en cours.
Le explain analyse en recette donne ceci :
"Bitmap Heap Scan on table_cible aa (cost=1513.65..23612788.82 rows=80654 width=60) (actual time=10.061..4533599.672 rows=80317 loops=1)"
" Recheck Cond: ((annee_semaine)::text = '2011S20'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1493.49 rows=80654 width=0) (actual time=9.766..9.766 rows=80435 loops=1)"
" Index Cond: ((annee_semaine)::text = '2011S20'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..292.50 rows=1 width=26) (actual time=56.440..56.440 rows=1 loops=80317)"
" -> Result (cost=0.00..292.04 rows=59 width=26) (actual time=4.691..56.410 rows=44 loops=80317)"
" One-Time Filter: (($1)::text = '2011S20'::text)"
" -> Nested Loop (cost=0.00..292.04 rows=59 width=26) (actual time=4.690..56.392 rows=44 loops=80317)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.68 rows=2 width=22) (actual time=3.139..3.139 rows=1 loops=80317)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2011S20'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..133.32 rows=29 width=26) (actual time=1.568..53.914 rows=44 loops=79292)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2010S21'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
"Total runtime: 4538565.077 ms"
Le même explain analyse est en cours en production.
Définition des tables :
-- Table: table_source
CREATE TABLE table_source
(
col_seq integer NOT NULL DEFAULT nextval('sequence'::regclass),
code character varying(13), <--- colonne utilisée dans la jointure
dimension1 integer,
dimension2 character varying(3),
indicateur integer, <--- indicateur sommé dans la requête
indicateur1 real,
indicateur2 real,
indicateur3 real,
indicateur4 integer,
indicateur5 real,
indicateur6 real,
indicateur7 real,
indicateur8 real,
indicateur9 real,
indicateur10 real,
indicateur11 real,
indicateur12 real,
indicateur13 real,
dimension3 character varying(8),
indicateur14 real,
indicateur15 real,
indicateur16 real,
indicateur17 real,
indicateur18 real,
indicateur19 real,
indicateur20 real,
indicateur21 real,
indicateur22 real,
dimension4 character varying(2),
dimension5 character varying(10),
indicateur23 real,
indicateur24 integer,
indicateur25 integer,
indicateur26 real,
indicateur27 real,
indicateur28 real,
indicateur29 real,
indicateur30 real,
indicateur31 real,
indicateur32 real,
dimension6 character varying(4),
debut_valeur date,
fin_valeur date,
date_maj date,
dimension7 integer,
indicateur33 real,
indicateur34 real,
indicateur35 integer,
annee_semaine character varying(7), <--- colonne utilisée dans la jointure
indicateur36 real
);
-- Index: index_source_1
CREATE INDEX "index_source_1"
ON table_source
USING btree
(code);
-- Index: index_source_2
CREATE INDEX "index_source_2"
ON table_source
USING btree
(dimension2);
-- Index: index_source_3
CREATE INDEX "index_source_3"
ON table_source
USING btree
(dimension2, dimension1);
-- Index: index_source_4
CREATE INDEX "index_source_4"
ON table_source
USING btree
(annee_semaine);
-- Index: index_source
CREATE INDEX "index_source"
ON table_source
USING btree
(code, annee_semaine); <--- index source utilisé dans la requête
-- Index: index_source_5
CREATE INDEX "index_source_5"
ON table_source
USING btree
(code, dimension1, dimension2);-- Table: table_cible
CREATE TABLE table_cible
(
annee_semaine character varying(7) NOT NULL,
code character varying(13) NOT NULL,
indicateur1 numeric(15,4),
indicateur2 numeric(15,5),
indicateur3 real,
indicateur4 real,
indicateur5 integer,
indicateur_moyen_mensuel integer, <--- indicateur calculé
indicateur6 real
);
-- Index: index_cible
CREATE INDEX index_cible
ON table_cible
USING btree
(annee_semaine); <--- index cible utilisé dans la requêteMerci d'avance
Bonjour,
J'utilise un base postgreSQL 8.4 sur un serveur Linux redhat 4.1.2.
Je dois exécuter en production une requête 50 fois avec des paramètres différents. Le problème est que cette requêtes dure 3h à chaque fois :
update table_cible aa
set indicateur_moyen_mensuel = (select sum(b.indicateur)/12
from table_source a
join table_source b on (a.code = b.code and '2011S02' <= b.annee_semaine and b.annee_semaine <= a.annee_semaine)
where a.annee_semaine = '2012S01'
and aa.code = a.code
and aa.annee_semaine = a.annee_semaine
group by a.code,a.annee_semaine)
where annee_semaine = '2012S01';
Présentation de la requête : Calcul de table_cible.indicateur_moyen_mensuel qui est la moyenne de table_source.indicateur sur les douze derniers mois. On fait donc une auto-jointure.
La clé fonctionnelle en source et en cible est le code et l'annee_semaine.
La table_source fait 35 Go et contient 200M de lignes. Pour une annee_semaine donnée, le nombre de lignes sur les 12 derniers mois est de 42M. C'est donc le nombre de lignes lues dans table_source.
La table_cible fait 530 Mo et contient 6,5M de lignes.
Le plan d'exécution utilise un index btree sur table_source(code, annee_semaine) (nommé index_source).
Le plan était le suivant :
"Seq Scan on table_cible aa (cost=0.00..11236640.54 rows=122839 width=60)"
" Filter: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26)"
" -> Result (cost=0.00..90.07 rows=21 width=26)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26)"
" -> Index Scan using index_source on table_source a (cost=0.00..24.63 rows=1 width=22)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on table_source b (cost=0.00..65.18 rows=21 width=26)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
En recette, elle dure entre 20mn et 1h30 selon les semaines (alors que la volumétrie est la même sur toutes les semaines et similaire à celle de production).
La seule piste d'optimisation que j'ai trouvé est de créer un index btree sur table_cible.annee_semaine (nommé index_cible).
Le plan d'exécution devient le suivant :
"Bitmap Heap Scan on table_cible aa (cost=1587.86..10889632.89 rows=119904 width=60)"
" Recheck Cond: ((annee_semaine)::text = '2012S01'::text)"
" -> Bitmap Index Scan on index_cible (cost=0.00..1557.89 rows=119904 width=0)"
" Index Cond: ((annee_semaine)::text = '2012S01'::text)"
" SubPlan 1"
" -> GroupAggregate (cost=0.00..90.25 rows=1 width=26)"
" -> Result (cost=0.00..90.07 rows=21 width=26)"
" One-Time Filter: (($1)::text = '2012S01'::text)"
" -> Nested Loop (cost=0.00..90.07 rows=21 width=26)"
" -> Index Scan using index_source on agr_produit a (cost=0.00..24.63 rows=1 width=22)"
" Index Cond: (((code)::text = ($0)::text) AND ((annee_semaine)::text = '2012S01'::text))"
" -> Index Scan using index_source on agr_produit b (cost=0.00..65.18 rows=21 width=26)"
" Index Cond: (((b.code)::text = ($0)::text) AND ('2011S02'::text <= (b.annee_semaine)::text) AND ((b.annee_semaine)::text <= (a.annee_semaine)::text))"
En, recette, la requête a duré 45mn. C'est toujours beauoup trop.
Je suis à court d'idée. Quelqu'un pourrait-il m'aider ?
Merci d'avance
Jibc
Bon, j'ai pris mon courage à 2 mais et j'ai lancé le pg_resetxlog en version 8.4...
Et là c'est bon, le redémarrage de la base a fonctionné. C'est tout bon.
Merci rjuju pour m'avoir aiguillé sur la bonne voie !
Oui, c'est juste. Je n'avais pas fait attention, mais les binaires qui sont dans /usr/bin/ sont en 8.2 mais ceux qui sont dans /app/pgsql/bin sont en 8.4 :
$ /app/marjorie/pgsql/bin/pg_ctl --version
pg_ctl (PostgreSQL) 8.4.5
Par défaut, évidemment, quand on appelle un binaire en chemin relatif, il prend celui de /usr/bin.
Or, lorsque j'ai appelé pg_resetxlog, je n'ai pas utilisé le chemin absolu. Il a donc pris la version 8.2 et je lui ai indiqué un chemin PGDATA en version 8.4.
Pensez-vous qu'il suffit de relancer la commande pg_resetxlog en utilisant bien la bonne version cette fois ?
Merci de votre message.
La base fonctionnait bien ce matin, et rien n'a été installé depuis plus d'1 an sur cette version de base de données.
Je ne connais pas tout l'historique du projet (et les gens qui en savent un peu plus là dessus sont absents aujourd'hui), mais je comprends qu'un changement de version a été fait, peut-être de manière impropre.
Apparemment, la version des binaires est 8.2.13 et non 8.4.
$ initdb --version
initdb (PostgreSQL) 8.2.13
$ pg_ctl --version
pg_ctl (PostgreSQL) 8.2.13
$ pg_resetxlog --version
pg_resetxlog (PostgreSQL) 8.2.13
Par contre :
$ cat /app/pgsql/data/PG_VERSION
8.4
Bonjour,
Avant tout, voici les infos de version de notre environnement :
PostgreSQL : 8.4
Taille de la base : 250 Go
Serveur Red Hat 5.3
Autre un information : Je n'y connais quasiment rien à l'administration PostgreSQL. Ceux qui ont fait notre install sont partis depuis bien longtemps et n'ont pas laissé beaucoup de doc. Donc on se débrouille avec les moyens du bord, comme on dit.
Le problème : impossibilité de démarrer la base. Message obtenu :
FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 822, but the server was compiled with PG_CONTROL_VERSION 843.
HINT: It looks like you need to initdb.
L'historique :
Suite à un problème de saturation sur le serveur de base de données, j'ai perdu la connexion à la base.
Pour libérer de la place, j'ai supprimé des fichiers dans $PGDATA/pg_log.
Cela n'a pas suffi, je ne pouvais toujours pas me connecter à la base (bon, à cet endroit, j'aurais dû tenter de démarrer la base, et j'aurais sans doute évité les ennuis suivants).
J'ai déplacé les fichiers $PGDATA/pg_xlog à un autre endroit (et c'est là que normalement, tout le monde se dit : "Quel boulet..." Je sais.)
Suite à cela, impossible de redémarrer la base. Message :
LOG: logger shutting down
Je remets les logs dans $PGDATA/pg_xlog , je redémarre la base, même message.
Ni une, ni deux, je lance la commande :
pg_resetxlog -f /app/marjorie/pgsql/data (encore une grosse connerie sans doute)
Impossibilité de redémarrer la base, message :
FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 822, but the server was compiled with PG_CONTROL_VERSION 843.
HINT: It looks like you need to initdb.
J'arrête là le massacre et je vous demande de l'aide.
Merci d'avance
Jibc
Pages : 1