[postgreSql 9.0.4] degradation performance update

Version imprimable

04/05/2011, 12h48
msomso

1 pièce(s) jointe(s)

[postgreSql 9.0.4] degradation performance update
Bonjour
Je suis à la recherche de pistes d'optimisation serveur pour le cas suivant:

Update en boucle "par colonne" de table ayant 70 colonnes et 700 000 enregistrements.
Scénario:
La table cible est tronquée, puis initialisation (insert) de l'identifiant de "PK". Ensuite le reste de colonnes est traité en boucle.

Le traitement de chaque colonne consiste à :
- récupérer sa valeur dans une table source
- effectuer une série de tests/transformations
- transférer la valeur finale dans la colonne correspondante de table cible
Problème : le temps du traitement de chaque colonne s'allonge au fur et mesure d'avancement (x 10 à la fin).
Ceci doit s'expliquer par le réarrangement des données dans la page suite au rallongement des enregistrements.

Serait-il possible de mieux gérer ce stockage initial, via quel paramètre ?

Merci d'avance
msomso

P.S.
En PJ le fichier log du traitement en cours
04/05/2011, 18h46
estofilo

Citation:

Envoyé par msomso

Problème : le temps du traitement de chaque colonne s'allonge au fur et mesure d'avancement (x 10 à la fin).
Ceci doit s'expliquer par le réarrangement des données dans la page suite au rallongement des enregistrements.

S'il s'agit de ça, le paramètre qui peut aider est le FILLFACTOR de la table et des index associés.
Ceci étant la mise à jour multiple d'une même ligne colonne par colonne est une stratégie pas du tout optimale. Le mieux serait de changer l'algorithme pour grouper les colonnes dans le même ordre UPDATE.
05/05/2011, 10h38
msomso

Bjr
Merci bien c'est exactement ce que je voudrais tester.
Sauriez-vous, comment identifier les enregistrements chainés ?

msomso
05/05/2011, 11h49
estofilo

Il n'y a pas à ma connaissance d'enregistrements chainés. Je sais que ça existe dans d'autres SGBD comme Oracle mais pas postgres, qui utilise un autre mécanisme qui s'appelle TOAST pour les champs qui prennent beaucoup de place.
En revanche lorsqu'une ligne est mise à jour, l'intégralité de la ligne est recopiée ailleurs pour en faire une nouvelle version. C'est le mécanisme MVCC pour permettre des lectures concurrentes de données qui n'ont pas la même valeur d'une transaction à l'autre.
Ensuite les anciennes versions des lignes sont recyclées par auto-vacuum, mais ça n'empêche pas les données utiles d'être éparpillées. C'est pourquoi mettre à jour une même ligne en plusieurs UPDATE est mauvais pour les perfs.
06/05/2011, 10h45
SQLpro

1) dans ce genre de traitement ligne à ligne il est normale que le système ralentisse au fur et à mesure de l'avancement dans le traitement des lignes, car un SGBDR n'étant pas fait pour ce genre de traitement, vous l'utilisez à l'envers (en le dégradant sciemment).
Essayez donc de faire ces traitements de manière ensembliste en adoptant la stratégie suivante :
1.1 créer une fonction qui fait toutes les transformation de manière unitaire
1.2 encapsulez cette fonction dans l'UPDATE ensembliste

2) désactivez tous les index avant de réaliser votre traitement,sauf l'index sous-jacent à la clef primaire, puis reconstruisez les en fin de traitement.

A +