Discussion :

[rockley]

Bonjour à tous,

Je dois réaliser une optimisation et je ne sais pas comment faire car je ne suis pas expert en DB2.
C'est pour cette raison que je m'adresse à vous

Voici le topo :
J'ai une table de 100 millions d'enregs.
La clé fait 8 et il y a envions 100 colonnes.

Pour les traitements nous faisons un appareillage entre un fichier et la table.
A chaque MAJ la table est totalement rafraîchis. Oui oui les 100 millions de lignes
S'il y a des diff on insert, update et delete les lignes de la table.
Sinon on met juste à jour la dernière date de traitement.

Actuellement nous faisons un SELECT de la ligne puis l'action qu'il faut.
Pour optimiser les choses nous voudrions :
1. Décharger complétement la table.
2. faire un traitement qui créé 3 fichiers (delete, update et insert).
3. Lancer les MAJ de la table en trois fois.


Pourriez-vous me donner des conseilles pour mettre en place les meilleurs requêtes ?
Les petites clauses (ex: with ur, for fetch only,...) qui pourrait faire gagner en perf ...
J'avais pensé a faire des insert multi row.
J'avais pensé également à mettre en place le with HOLD et faire des curseur spécifique pour les updates.

Merci d'avance pour votre aide.
Cordialement,

[kolodz]

As-tu fait un monitoring sur les traitements qui utilisent cette table ?
Sais-tu ce qui prendre le plus de temps dans ces traitements ?
Est-ce la recherche ? -> Index + partition de la table ?
Est-ce le parcours des données ? (Saturation des I/O) Toutes les colonnes retournées sont nécessaires ? Toutes les lignes lues sont elles réellement à retourner au niveau du traitement ?
Les traitements de mise à jour de masse sont-ils fait dans une boucle "for" ? Ou via une seule requête ?

Cordialement,
Patrick Kolodziejczyk.

[rockley]

Bonjour,

As-tu fait un monitoring sur les traitements qui utilisent cette table ?

Je ne sais pas ce que c'est

Sais-tu ce qui prendre le plus de temps dans ces traitements ?

D'après l'analyse du DBA (strob) ... Rien et un petit peu tout.
On fait 100 millions de lectures et 100 millions de MAJ (Update, delete, insert) en une heure ou deux. Je pense que ce n'est pas dégueulasse comme perf. Mais encore une fois ce n'est pas mon domaine.
Malgré cela le temps CPU doit être divisé par 5. Les traitements cobol prennent 10/15 % de la CPU. Le reste c'est les accès DB2.

Est-ce la recherche ? -> Index + partition de la table ?

La recherche et les MAJ du coup.
La lecture se fait par index cluster.

Est-ce le parcours des données ? (Saturation des I/O) Toutes les colonnes retournées sont nécessaires ? Toutes les lignes lues sont elles réellement à retourner au niveau du traitement ?

Les 100 colonnes fonctionnent comme un système de carrousel. On a période 1, 2, 3, ... Quand une période est créé on le met dans le 1 et on décale le reste.
On lit tout et on met à jour tout oui.

Les traitements de mise à jour de masse sont-ils fait dans une boucle "for" ? Ou via une seule requête ?

Je ne comprends pas bien la question.
Dans le pgm cobol on ouvre le curseur, on fait des lectures suite puis une lecture fin.
Si la question est si on ouvre/ferme un curseur à chaque fois c'est non.

Cordialement,

[escartefigue]

Voici le topo :
J'ai une table de 100 millions d'enregs.
La clé fait 8 et il y a envions 100 colonnes.

1ère remarque, une table de 100 colonnes est symptomatique d'un problème de conception. C'est une table dite "obèse", qui favorise les contentions.
Puis une question : Est-ce DB2 for Z/OS ?

Pour les traitements nous faisons un appareillage entre un fichier et la table.
A chaque MAJ la table est totalement rafraîchis. Oui oui les 100 millions de lignes

Que voulez vous dire par là ? il s'agit d'un curseur qui est refermé après chaque ordre update, insert ou delete ?

Pour optimiser les choses nous voudrions :
1. Décharger complétement la table.
2. faire un traitement qui créé 3 fichiers (delete, update et insert).
3. Lancer les MAJ de la table en trois fois.

Puisque vous horodatez la mise à jour, pourquoi ne travaillez vous pas tout simplement par delta entre 2 traitements ?
Quelle proportion de mises à jour représente votre fichier mouvements ?
Votre table est elle partitionnée, si oui sur quel critère ?
Votre fichier mouvement contient il toutes les colonnes de la table ?

Pourriez-vous me donner des conseilles pour mettre en place les meilleurs requêtes ?
Les petites clauses (ex: with ur, for fetch only,...) qui pourrait faire gagner en perf ...
J'avais pensé a faire des insert multi row.
J'avais pensé également à mettre en place le with HOLD et faire des curseur spécifique pour les updates.

Si la solution est via un curseur, il faut évidemment utiliser with-hold pour ne pas le ré-ouvrir à chaque fois ! C'est l'open cursor qui coûte (résolution du chemin d'accès), pas le fetch
Mais la solution curseur n'est à privilégier que si vous avez une proportion faible à mettre à jour, si c'est la totalité de la table ou une très forte proportion, alors d'autres solutions seront beaucoup plus efficientes !
La clause with UR est dangereuse : vous allez ramasser des records non commités, utilisable seulement si vous êtes seul sur votre database (pas de TP au d'autre batch concurrent)
for fetch only ne peut pas être utilisé puique vous faites des MàJ....

[escartefigue]

D'après l'analyse du DBA (strob) ... Rien et un petit peu tout.
On fait 100 millions de lectures et 100 millions de MAJ (Update, delete, insert) en une heure ou deux. Je pense que ce n'est pas dégueulasse comme perf. Mais encore une fois ce n'est pas mon domaine.
Malgré cela le temps CPU doit être divisé par 5. Les traitements cobol prennent 10/15 % de la CPU. Le reste c'est les accès DB2.

Ceci répond à l'une de mes questions, nos, posts ce sont croisés, qui dit strobe, dit Z/OS
Le temps CPU n'est pas tout, il faut aussi relever les wait, peut être passez vous (beaucoup) plus de temps à attendre des ressources qu'à traiter

Les 100 colonnes fonctionnent comme un système de carrousel. On a période 1, 2, 3, ... Quand une période est créé on le met dans le 1 et on décale le reste.
On lit tout et on met à jour tout oui.

Ca augmente le nombre de données transportées, avec 100 colonnes ce n'est pas anodin, surtout si les colonnes sont larges (notamment cout réseau pour transporter tout ça)
Attention : si vous avez des colonnes varchar, ca peut couter très cher si les longueurs augmentent lors des update ! (déplacement dans les pages, très couteux)

[escartefigue]

On fait 100 millions de lectures et 100 millions de MAJ (Update, delete, insert) en une heure ou deux. Je pense que ce n'est pas dégueulasse comme perf. Mais encore une fois ce n'est pas mon domaine.

Donc a priori chaque ligne est modifiée, en ce cas la stratégie curseur n'est pas la bonne, surtout si les insert et delete sont nombreux, en ce cas faites comme suit

Hors période d'activité sur la table (pas de TP, pas de batch concurrent, pas d'utilitaire)
- unload de la table
- tri de l'unload selon la clef cluster s'il existe
- tri du fichier selon les mêmes colonnes
- appareillage et sélection de de qui doit être conservé de l'un ou de l'autre
- rechargement en mode replace avec l'option statistics et log no
- rebind des package utilisant la table
==> votre table est chargée, réorganisée (grace au tri) et les stats sont à jour, avec 100 million de lignes, cette solution sera beaucoup plus rapide qu'un curseur, selon la cpu dont vous disposez ça peut prendre moins de 20 minutes elapsed tout compris, voire beaucoup moins (ca dependra aussi du nombre d'index sur la table, avec 100 colonnes tout est possible , car c'est la phase de load qui est longue d'autant plus qu'il y a beaucoup d'index)

[Luc Orient]

...
Pour les traitements nous faisons un appareillage entre un fichier et la table.

Est ce que l'appareillage se fait bien dans l'ordre de l'index cluster ?

[Luc Orient]

...
Hors période d'activité sur la table (pas de TP, pas de batch concurrent, pas d'utilitaire)
- unload de la table
- tri de l'unload selon la clef cluster s'il existe
- tri du fichier selon les mêmes colonnes
- appareillage et sélection de de qui doit être conservé de l'un ou de l'autre
- rechargement en mode replace avec l'option statistics et log no
- rebind des package utilisant la table
==> votre table est chargée, réorganisée (grace au tri) et les stats sont à jour, avec 100 million de lignes ...

... manque juste l'IMAGE COPY ... le LOAD avec LOG NO va provoquer un état restreint sur le TS qui sera en COPY PENDING et une IC est alors indispensable ...

[escartefigue]

... manque juste l'IMAGE COPY ... le LOAD avec LOG NO va provoquer un état restreint sur le TS qui sera en COPY PENDING et une IC est alors indispensable ...

Exact, merci pour cet ajout

[rockley]

Bonjour,

1000x pardon pour le temps que j'ai mis à répondre. J'étais en congés.

Je vais tenter de répondre à tous vos questions :

C'est une table dite "obèse", qui favorise les contentions.

La longueur pour les 100 colonnes est de 827. Ce qui reste plus que correcte pour le nombre d'information je pense.

Le temps CPU n'est pas tout, il faut aussi relever les wait, peut être passez vous (beaucoup) plus de temps à attendre des ressources qu'à traiter

Il n'y a aucun problème sur les wait.

Puisque vous horodatez la mise à jour, pourquoi ne travaillez vous pas tout simplement par delta entre 2 traitements ?

Quand nous affichons les informations pour un client nous vérifions que la date de dernier MAJ est cohérent avec les autres dates des autres tables DB2.
En cas de plantage il n'y a que les clients qui sont en déphasage qui sont bloqué. Les autres marchent correctement.
Travailler avec un delta risquerait de dégrader le service.

Attention : si vous avez des colonnes varchar, ca peut couter très cher si les longueurs augmentent lors des update !

Pas de varchar à l'horizon

Est ce que l'appareillage se fait bien dans l'ordre de l'index cluster ?

Yes, il fait un order by après la sélection.


Voilà pour les questions.

D'après ce que j'ai compris, l'application a été fait pas un gars qui avant était un référent DB2. Du coup, à ce niveau on n'arrive pas à trouver quelque chose à lui reprocher.
J'ai étudié son code et on sent l'amour qu'il a eu a faire l'application (si vous voyez ce que je veux dire). Pas de commentaire inutile, pas de code mort, c'est propre, c'est net.

Hors période d'activité sur la table (pas de TP, pas de batch concurrent, pas d'utilitaire)
- unload de la table
- tri de l'unload selon la clef cluster s'il existe
- tri du fichier selon les mêmes colonnes
- appareillage et sélection de de qui doit être conservé de l'un ou de l'autre
- rechargement en mode replace avec l'option statistics et log no
- rebind des package utilisant la table
==> votre table est chargée, réorganisée (grace au tri) et les stats sont à jour, avec 100 million de lignes, cette solution sera beaucoup plus rapide qu'un curseur, selon la cpu dont vous disposez ça peut prendre moins de 20 minutes elapsed tout compris, voire beaucoup moins (ca dependra aussi du nombre d'index sur la table, avec 100 colonnes tout est possible , car c'est la phase de load qui est longue d'autant plus qu'il y a beaucoup d'index)

Notre DBA nous a dit la même chose. La prod refuse les RELOAD.

Donc la seule solution qu'on peut avoir pour optimiser les traitements et déjà de sortir tous les select de lecteur => Comme tu l'as proposé :

- unload de la table
- tri de l'unload selon la clef cluster s'il existe
- tri du fichier selon les mêmes colonnes
- appareillage et sélection de de qui doit être conservé de l'un ou de l'autre

A la sortie on aura trois fichier. Les DEL, UPD et INS.
Et du coup je cherche a faire les requêtes DB2 les plus performantes pour les 3.

Cordialement,

[rockley]

Au final il n'y a pas vraiment de solution si ?
Tous vos idées sont les bienvenu

[Luc Orient]

Autre piste possible : est ce que la table est partitionnée ?

[escartefigue]

La longueur pour les 100 colonnes est de 827. Ce qui reste plus que correcte pour le nombre d'information je pense.

Le terme de table "Obèse" ne tient pas tant compte de la largeur en caractères, que du nombre de colonnes dans la même table.
Beaucoup de colonnes - et avec 100 on y est - est symptomatique d'une modélisation baclée, il est très peu probable que le modèle respecte les formes normales,
- vous avez probablement un grand nombre de colonnes facultatives (à blanc, null, ou contenant des valeurs bidon)
- certaines applications veulent mettre à jour une partie des colonnes, d'autres applis d'autre sous-ensembles de la même table, d'où contentions qu'un découpage plus adéquat aurait évité.

Travailler avec un delta risquerait de dégrader le service.

Tout dépend de la proportion, mais dans votre cas, le taux de mise à jour semble proche de 100%, auquel cas le delta ne se justifie bien évidemment pas

Yes, il fait un order by après la sélection.

Justement ! faire un curseur sur toute la table avec un order by coute très cher, car même si l'index cluster correspond à l'order by, le cluster-ratio est rarement de 100% il faut donc effectuer le tri
C'est pourquoi la solution par fichier est beaucoup, beaucoup, beaucoup plus rapide pour des volumes conséquents.

Notre DBA nous a dit la même chose. La prod refuse les RELOAD.

Et quels sont les arguments ?
Les utilitaires sont fournis par IBM, les outils IBM ne sont pas toujours très ergonomiques, mais en terme de fiabilité, je pense qu'on peut faire confiance les yeux fermés !
Faire des insert/update pour 100 millions de lignes est absurde et interdire l'usage d'un unload reload l'est tout autant. Quelles sont les raisons invoquées ?

[rockley]

Bonjour,

Avant tout 1000 merci pour vos réponses. C'est cool de savoir que je peux compter sur votre aide.

Voici les réponses à vos question :

Autre piste possible : est ce que la table est partitionnée ?

Oui, celle dont je vous parle a 10 partitions physique et 100 partitions logique.

Le terme de table "Obèse" ne tient pas tant compte de la largeur en caractères, que du nombre de colonnes dans la même table.
Beaucoup de colonnes - et avec 100 on y est - est symptomatique d'une modélisation baclée, il est très peu probable que le modèle respecte les formes normales,
- vous avez probablement un grand nombre de colonnes facultatives (à blanc, null, ou contenant des valeurs bidon)
- certaines applications veulent mettre à jour une partie des colonnes, d'autres applis d'autre sous-ensembles de la même table, d'où contentions qu'un découpage plus adéquat aurait évité.

En gros nous avons qu'un programme qui tourne pour mettre à jour la table.
La table contient la clé plus X fois les même champs par période.
Donc on a

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 [clé][val1_mois1,  val2_mois1, val3_mois1, val4_mois1, ...] [val1_mois2,  val2_mois2, val3_mois2, val4_mois2, ...][mois trois, ...][...]

On fait à jour tous les champs du mois1 puis le système de carousel décale les valeur quand on change de mois.
Quand notre DBA a vu le système il a dit que c'était pas logique. Mais après analyse on fait un gros UPDATE au lieux de 11 petits. Il a finit par dire qu'en l'état c'était plus performant finalement.

Et quels sont les arguments ?
Les utilitaires sont fournis par IBM, les outils IBM ne sont pas toujours très ergonomiques, mais en terme de fiabilité, je pense qu'on peut faire confiance les yeux fermés !
Faire des insert/update pour 100 millions de lignes est absurde et interdire l'usage d'un unload reload l'est tout autant. Quelles sont les raisons invoquées ?

La réponse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Les LOAD sont bannis en production (hors chargement initial et tables mortes) car la table est inaccessible (y compris en lecture) pendant toute la durée du load.

[escartefigue]

Evidemment qu'un load replace rend la table inaccessible pendant la phase de chargement, mais il en va de même pour une réorg, une restaure, et d'autres utilitaires, est-ce que pour autant la prod interdit les utilitaires dans leur ensemble ?...
L'intérêt du load est qu'il va certes bloquer la table dans sa totalité mais la durée sera tellement plus courte qu'une modification par SQL que la gêne envers les autres applis sera nettement moindre.
Donc sauf si vous êtes contraints de passer le traitement de mise à jour en masse pendant les heures de dispo de la table (par exemple pendant le TP) et que la table doit rester accessible sans la moindre interruption, l'argument ne tient pas.

Comme votre table est partitionnée, vous pouvez paralléliser une partie des opérations, mais les build index s'appuient sur l'ensemble des partitions, quoi qu'il en soit vous serez très largement gagnant en temps par rapport à du SQL.

[Luc Orient]

...
Oui, celle dont je vous parle a 10 partitions physique et 100 partitions logique.

On peut donc imaginer un traitement par partition physique ( une ou N ) et donc un certain degré de parallélisme.

En gros nous avons qu'un programme qui tourne pour mettre à jour la table.
La table contient la clé plus X fois les même champs par période.
Donc on a

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 [clé][val1_mois1,  val2_mois1, val3_mois1, val4_mois1, ...] [val1_mois2,  val2_mois2, val3_mois2, val4_mois2, ...][mois trois, ...][...]

On fait à jour tous les champs du mois1 puis le système de carousel décale les valeur quand on change de mois.

Effectivement et comme il a déjà été écrit, la modélisation sous-jacente est pour le moins curieuse ...
Quel est l'intérêt de décaler ?

[rockley]

Bonjour

Encore merci pour vos réponses.

En fait c'est le temps "élapse" (c'est comme ça qu'on dit ?) que nous devons diminuer (diviser par 5...). Ou autrement dit la conso CPU global.
Une parallélisassions ne changerai pas la conso final.

Effectivement et comme il a déjà été écrit, la modélisation sous-jacente est pour le moins curieuse ...
Quel est l'intérêt de décaler ?

C'est très étrange et ça ma même choqué la première fois. Mais au final pour les besoins du projet c'est vraiment pas mal.
En gros notre structure c'est une période.
Période courante, Période - 1, Période - 2 ...
Du coup quand on change de période courante, l'ancienne période courante deviens la perode -1, ...
Comme ça on a tous les infos sur une lignes au lieux de les éclater à 10 ou 11.
On est sûr qu'il n'y a pas de lignes en trop et/ou morte. Nous sommes également sûr de la cohérences. La maintenant et l'analyse des cas d'erreur est plus simple.
Bref, même si ça m'a choqué au départ, je suis à présent convaincu.

Les MAJ doivent faire -5 % de DELETE, -5 % d'insert et tout le reste des UPDATE.
Donc nous avons 1 gros update à faire au lieu de 10/11 petits.


------------

La j'ai une idée mais il faut que je vois si ça peut marcher et qui contacter surtout. En gros on peut mettre des ressources sur des JOBs de traitements de MAJ de table pour empêcher la concurrence.
Si mes deux JOBs mettent à jour la même table DB2. Je mets la même ressources sur les deux JOBS. Comme ça dès qu'un des deux job se lance, on lock la ressources et l'autre job entends que la ressources se libère pour se lancer à son tours.
Si côté TP je pouvais tester cette ressource il me suffirait juste d'afficher une page "Serveur en cours de MAJ. Veuillez réessayer plus tard" ou un truc du genre et là vraiment l'argument de la prod ne tiendrait plus !!!!

[escartefigue]

En fait c'est le temps "élapse" (c'est comme ça qu'on dit ?) que nous devons diminuer (diviser par 5...). Ou autrement dit la conso CPU global.
Une parallélisassions ne changerai pas la conso final.

Ce que vous dites est contradictoire, le temps CPU et le temps ELAPSED ne sont pas directement corrélés
Le temps ELAPSED c'est le temps CPU + le temps I/O + les wait (et quelques pouillèmes d'autres paramètres)
En d'autres termes, à temps CPU égal, vous pouvez avoir des temps elapsed très différents selon que vous attendez beaucoup ou pas les ressources, que vous faites + ou - d'i/o etc...
Si vous parallélisez, vous divisez le temps ELAPSED sans consommer plus de CPU totale (mais plus de CPU pendant l'exécution)

C'est très étrange et ça ma même choqué la première fois. Mais au final pour les besoins du projet c'est vraiment pas mal.
En gros notre structure c'est une période.
Période courante, Période - 1, Période - 2 ...
Du coup quand on change de période courante, l'ancienne période courante deviens la perode -1, ...

Il y avait d'autres façons de procéder autrement plus simples qui vous auraient permis d'économiser toutes ces mises à jour inutiles, par exemple ne stocker qu'une seule période par ligne, mais en ajoutant un attribut "rang" que vous incrémentez à chaque mise à jour périodique, le rang le plus haut pour un identifiant est celui qui peut être purgé, les autres attributs sont inchangés.
Ce point confirme une modélisation conceptuelle négligée, un MCD peaufiné aurait évité de tomber dans cet écueil

La j'ai une idée mais il faut que je vois si ça peut marcher et qui contacter surtout. En gros on peut mettre des ressources sur des JOBs de traitements de MAJ de table pour empêcher la concurrence.
Si mes deux JOBs mettent à jour la même table DB2. Je mets la même ressources sur les deux JOBS. Comme ça dès qu'un des deux job se lance, on lock la ressources et l'autre job entends que la ressources se libère pour se lancer à son tours.

Cette méthode est fréquemment utilisée dans les automates d'exploitation (CA7, TWS ...), elle est facile à mettre en œuvre via des ressources fictives de l'automate. Contactez les responsables prod qui sauront vous guider.

Si côté TP je pouvais tester cette ressource il me suffirait juste d'afficher une page "Serveur en cours de MAJ. Veuillez réessayer plus tard" ou un truc du genre et là vraiment l'argument de la prod ne tiendrait plus !!!!

Rien de plus facile : vous testez le code SQL -904 + le reason code qui indique un utility pending et vous avez votre diagnostic !

[rockley]

Merci pour tous vos réponses

Ce que vous dites est contradictoire, le temps CPU et le temps ELAPSED ne sont pas directement corrélés
Le temps ELAPSED c'est le temps CPU + le temps I/O + les wait (et quelques pouillèmes d'autres paramètres)
En d'autres termes, à temps CPU égal, vous pouvez avoir des temps elapsed très différents selon que vous attendez beaucoup ou pas les ressources, que vous faites + ou - d'i/o etc...
Si vous parallélisez, vous divisez le temps ELAPSED sans consommer plus de CPU totale (mais plus de CPU pendant l'exécution)

effectivement. C'est la conso CPU totale que nous devons baisser. Dsl pour l''erreur de terminologie.


Actuellement je dois préparer un doc et prendre RDV avec le grand chef de la prod pour tenter de le convaincre.
J'ai entendu dire que si on leur montrait clairement et proprement ce qu'on veut faire ça pouvait potentiellement passer. Ils ont fixé des règles pour tous et pour avoir une dérogation il va falloir construire un dossier solide.

Si côté TP je pouvais tester cette ressource il me suffirait juste d'afficher une page "Serveur en cours de MAJ. Veuillez réessayer plus tard" ou un truc du genre et là vraiment l'argument de la prod ne tiendrait plus !!!!

En fait on a même pensé à plus simple. Une table DB2 avec une valeur ON/OFF. Deux programmes cob très simple à la fois TP et batch. Un pour le ON et un pour le OFF.
On les places où on veut (Avant et après le Load par exemple) et on bloque le TP quand on le souhaite.

Merci à tous pour votre aide.
Cordialement,

[escartefigue]

Profitez en pour faire une double proposition
- la solution du load pour le court terme : fiable, facile à mettre en œuvre et beaucoup plus performante que de l'insert (vous allez aussi gagner en journalisation, 100 millions de logs ce n'est pas néglieable)
- la refonte du modèle de données pour la partie concernée à moyen terme

Et si vous avez la possibilité de prototyper la solution du load en volume réel, comparativement à votre architecture actuelle, vous aurez des arguments de poids pour votre entretien

Tenez nous informés de la suite