Lost somewhere…

Après être passer de MySQL à DB2 pour des raisons de rapidité sur des volumes de données conséquent (i.e. avec plus de 20Go MySQL commençait à être vraiment lent sur des requêtes simples et se bloquer totalement sur des requêtes plus complexes), DB2 commence maintenant à ramer méchamment. Malgré le QuadCore 1.6Ghz avec 4Go de RAM, le point de ralentissement de mon architecture se trouve être DB2. Voila donc les modifications que j’ai effectué sur ma base pour l’accélérer. Attention, certains paramêtres sont propres à mon utilisation.

Donc le but était d’optimiser la base afin qu’elle utilise au mieux les ressources de la machine et les buffers dont elle dispose. Et il faudra garder en tête que seuls les quelques grosses modifications au début vont amener un réel apport de performance, les autres risquent d’avoir un impact plus mineur. En conséquence, pas besoin de modifier l’ensemble des variables de configuration pour avoir une bonne base de donnée. Bien sur, comme d’habitude en informatique, on modifira un paramêtre à la fois pour localiser rapidement un paramêtre provoquant une erreur ou ralentissement/ne servant à rien pour l’optimisation du système. Il ne faudra pas non plus oublier les capacités de tuning en dehors de DB2 dans l’OS et sur le hardware. Finalement, il est important de toujours pouvoir revenir en arrière.

Les tunings proposées ici sont fait pour une base de donnée précise pour avoir des meilleurs résultats, vous devez les adapter à votre de base. Mais certains paramêtres sont génériques et peuvent être utiliser sur un plus large ensemble de base.

DB2 dispose d’un certain nombre de niveau de cache et d’agent permettant de bien séparer chaque comportement. EDU (Engine Dispatched Unit) correspond aux agents de la base comme les DB2 Agent chargés (db2sysc) des requêtes SQL d’autres sont chargés du nettoyages des mémoires partagées, etc. Il faut donc bien configurer les comportements pour qu’il y est suffisament d’agent en simultanée pour utiliser l’ensemble des processeurs mais pas trop pour éviter de ralentir l’ensemble du système. Les piscines de buffer (buffer pool) sont des espaces mémoires pour stocker temporairement les données extraites de la base, en effet, la mémoire vive étant beaucoup plus rapide que le disque dur, il est important d’utiliser des valeurs de buffer pool suffisament grande pour stocker un grand nombre de donnée et accélérer le traitement mais aussi pas trop grande pour éviter de faire swapper la machine (utiliser plus de mémoire vive qu’elle n’en dispose) ce qui aura pour effet de ralentir le processus. Afin de bien optimiser l’accés aux données, on devra donc faire attention à bien configurer les EDU correspondant aux prefetchers qui sont les agents qui vont charger les données depuis le disque vers la mémoire vive et les Pages Cleaner qui sont la pour nettoyer les buffers des données anciennes et/ou non utilisées mais également écrit les modifications faites en mémoire sur le disque.

Afin de bien comprendre le fonctionnement de DB2 et savoir comment améliorer ces performances, il faut commencer par connaitre chacun de ses composants:

• client (db2agent) est le point de contact entre un client et le serveur, un agent sera créé par chaque nouvelle connexion.
• listener i.e. ce qui écoute les points de connexions à DB2: db2ipccm (local), db2tcpcm (distant TCP/IP), db2tcpdm (distant TCP/IP, service répondant aux demandes de découverte de services).
• base de donnée quand un utilisateur se connecte, un client db2agent est créé et va faire l’ensemble des traitements sauf si (comme dans mon cas), vous avez une base de données réparties sur plusieurs disques (i.e. DPF Database Partition Feature) et/ou plusieurs processeurs (i.e. intra_query parallélisme activé). Dans ce cas, db2agent va distribué les tâches à db2agntp (DPF) et db2agnts (intra_query) respectivement. C’est l’agent db2agntp qui va effectivement calculer les requêtes SQL.
• Les sous agents qui sont actuellement lié avec une application mais qui ne sont pas utilisés sont nommés db2agnta.
• db2agnti a comme tâche d’éxecuter les tâches de monitoring d’événements.
• db2agnsc permet de parallélisé le redémarrage d’une base de donnée après un arrêt brutal.
• db2fmp execute les procédures.
• db2vend permet de lancer le logging d’un agent.
• db2pfchr est l’administrateur de la piscine de buffer réservés au prefetch (mise en mémoire de données issues de la base de données).
• db2pclnr permet de gérer les buffer réservés aux pages cleaner (écriture des modifications en mémoire sur le disque).
• db2loggr permet la manipulation de logs afin de permettre le traitement et la reprise en cas de panne.
• db2loggw permet d’écrire les logs dans les fichiers de logs.
• db2logts permet de tracer dans quels fichiers de logs sont stockés les informations relatives à chaque table et le résultat est stocké dans un fichier (DB2TSCHG.HIS). Cela va permettre d’accélérer les étapes de rollfoward.
• db2dlock est l’agent en charge de la détection des deadlocks. Dans un environnement multipartitions, un agent db2glock est également présent pour coordonner les informations de chaque db2dlock sur chaque partition de la base de donnée. db2glock tourne lui au niveau du catalog.
• db2taskd est l’agent en charge de la distribution des agents de fond de tâches. Ces tâches sont exécutées de manière threader par db2taskp.
• db2hadrp est le thread du serveur primaire HADR (High Availability Disaster Recovery i.e. reprise sur panne avec support de la disponibilté).
• db2hadrs est le thread du serveur secondaire HADR.
• db2lfr est l’agent en charge de la lecture de la lecture des fichiers de logs.
• db2shred permet de traiter individuellement chaque ligne des fichiers de logs.
• db2redo (pour redo master) permet pendant une phase de reprise sur pannes d’éxecuter les tâches de reprise à proprement dite.
• db2redow (pour redo worker) exécute les instructions que lui donne db2redo.
• db2logmgr prend en charge le management des fichiers de logs pour la restauration de la base de données.
• db2wlmd permet le calcul automatique de statistiques sur la base de données et sur les calculs effectués.
• un ensemble d’agent de monitoring d’événements nommé db2evm$1$2$3 avec $1 égale à g pour moniteur d’événements globaux, l pour moniteur d’événements locaux, t pour table, gp pour les tubes globaux, lp pour les tubes locaux. %2 permet de désigner si l’agent est coordinateur (i) ou non (p). Et finalement $3 est le nom du moniteur d’événements.
• un ensemble d’agent de sauvegarde et restauration pour les contrôleurs de données db2med$1$2 et pour les manipulations de buffer db2bm$1$2. Dans ces deux cas, $1 correspond à l’EDU ID de l’agent qui contrôle la sauvegarde ou la restauration et $2 permet de différencier les threads les uns par rapport aux autres.
• db2resync est l’agent en charge de la lecture des listes de demande de resynchronisation.
• db2wdog est un “watchdog” Unix/Linux permettant de gérer les fins de programmes anormales.
• db2fcms est le daemon d’envoi du protocole de communication rapide de management.
• db2fcmr est le daemon de réception du protocole de communication rapide de management.
• db2pdbc est l’agent de contrôle du parallélisme qui va prendre en charge les demandes de requêtes provenant de neouds distants. Il est utilisé uniquement dans le cas de base de données multipartitions.
• db2cart est l’agent chargé de l’archivage des fichiers de logs quand on accéde à la base de données en utilisant le mode USEREXIST.
• db2fmtlg est en charge du formatage des fichiers de logs quand on accéde à la base de donnée en utilisant le mode LOGRETAIN et sans le mode USEREXIST.
• db2panic permet de prendre en charge les requêtes urgentes après quand le nombre d’agent limite est atteint sur un noeud particulier (utiliser uniquement dans le cas d’une base de donnée monopartition).
• db2srvlst prend en charge la liste des adresses dans les systèmes tel que DB2 for z/ OS.
• db2fmd est le moniteur d’erreurs.
• db2disp est le dispatcheur de connexion client.
• db2acd est l’agent qui calcule automatiquement la quantité de ressources et le bon fonctionnement de la base mais également il surveille le bon fonctionnement des agents de maintenances automatiques.
• db2licc est en charge du management des licenses.
• db2thcln permet de réutiliser/recycler les ressources quand un agent a fini son exécution.
• db2aiothr permet de gérer les demandes d’entrées/sorties asynchrones dans la partition de la base de données.
• db2alarm notifie les autres EDU quand leur timer de requête a expiré.
• db2sysc est l’agent principal en charge du contrôle des autres EDU et qui prend en charge les erreurs critiques sur le serveur.

Quand un agent n’est plus utilisé, il retourne à la piscine des agents en attente. Il faut donc avoir un nombre d’agent en idle en adéquation avec son utilisation ce qui permettra d’avoir toujours des agents disponibles lors de demande de nouvelles connexions mais également ne pas en avoir trop pour éviter de perdre des ressources pour une tâche non utilisée.

Afin d’éviter les deadlocks, un agent est en charge de vérifier la non existance de ces derniers et va régler le problème si c’est le cas i.e. arrêter une des requêtes en cause et provoquer le roll-back de l’ensemble des tâches qu’elle a effectué puis la faire finir avec le code erreur 911. Pour avoir des bonnes performances en détection de deadlock et donc une utilisation inutile de ressources, il faut régler l’agent de détection des deadlock pour tourner à un interval relativement court mais pas trop pour éviter de surcharger le système inutilement par l’utilisation de cet agent. On peut désactiver les rapports avancées des deadlocks via la commande DROP EVENT MONITOR db2detaildeadlock. Pour créer un moniteur de deadlock, on utilise la commande suivante db2 create event monitor db2detaildeadlock for deadlocks with details write to file ’db2detaildeadlock’ maxfiles 20 maxfilesize 512 buffersize 17 blocked append autostart. Dans ce cas, le moniteur va écrire maximum 20 fichiers ayant chacun une taille de 2M (i.e. 512 pages de 4K). Le moniteur va s’arrêter automatiquement quand il aura atteint ces limites.

Au niveau des disques, plusieurs facteurs sont à prendre en compte. Tout d’abord, la division de l’espace disque entre les données en elle-même et les indexes mais également, la répartition de l’espace entre les espaces de tables. De plus, on doit surveiller le gaspillage d’espace disque car même si ca n’influence pas le système, cela pourrait qu’en même l’améliorer en les utilisant d’une meilleur manière. Dans le cas où l’on dispose de plusieurs disques et controleurs, il est intéressant de bien répartir l’ensemble des demandes d’entrées / sorties entre ces disques. Enfin, finalement, il faut bien surveiller à ne pas atteindre la limite d’espace disponible.

On doit également prendre en compte les variables internes de DB2 comme la taille des enregistrements dans chaque table et la taille des pages qui vont stockées ces enregistrements, les indexes vont également être stocker sous la forme de B-Tree dans ces pages. La taille des pages est variable mais, en moyenne, on va préférer l’utilisation de grande page mémoire quand les accés aux données sont séquentiels et des petites quand on a un accés plus aléatoire. Certaines valeurs tel que MINPCTUSED permettent de définir la quantité de places minimum que peut utiliser un index.

Il faut également faire attention aux tables multi-dimensionnelles et penser à les optimiser comme les tables bi-dimension. Dans ce cadre, Asynchron Index Cleanup (AIC) permet de desynchroniser la table et les indexes qui correspondent pendant une mise à jour afin de ne pas ralentir la modification de donnée, la modification de l’index se fera plus tard (i.e. des que possible). Pour activer le AIC, on peut ou modifier la variable DB2_MDC_ROLLOUT à DEFERED ou lancer la commande suivante SET CURRENT MDC ROLLOUT MODE. On peut également modifier la fréquence de nettoyage des indexes en utilisant la commande SET UTIL_IMPACT_PRIORITY X avec X compris entre 0 et 100. Il faudra bien faire attention à ne pas utiliser les indexes avant qu’ils soient à jour, pour cela, on peut utiliser la commande LIST UTILITIES.

Lors de la création d’un index, il est intéressant de spécifier ALLOW REVERSE SCANS qui va permettre de parcourir le B-Tree représentant l’index dans les 2 sens.

Réduire l’utilisation de ressources dû aux logs pour augmenter la rapidité des requêtes

Tout d’abord, toutes les bases de données ont des logs leur permettant de revenir en arrière mais plus largement de garder une trace de tous les opérations effectuées. On peut trouver deux grands types de gestion de logs: tout d’abord, la gestion rotative ou circulaire, c’est-à-dire, que quand le fichier de logs est plein, on va écraser les données qui se trouvent au début et ainsi de suite. Le deuxième type de gestion est la rétention, où quand un fichier de logs est plein, on en créé un nouveau. C’est utile pour des roll-backs assez massifs.

De plus, sur le disque se trouve également les logs buffer qui contiennent les derniers données modifiés avant qu’elle soit écrite sur le disque dans la base à proprement parlé (e.g. lors d’un COMMIT). Si on fait beaucoup de petites requêtes suivis d’un commit, on sera amené à souvent attendre ce log buffer (i.e. le temps de transfére entre le log buffer et la base sur le disque). Pour améliorer ce point, on peut modifier la variable mincommit à une valeur plus grande que 1. Dans ce cas, lorsqu’un commit aura lieu, il regroupera un certain nombre de commit et même si cela va ralentir la propagation de donnée, cela permettra de faire des commits avec plus de données en une seule opération et non plusieurs.

Il faut également savoir que les modifications sur les LOBs et les LONG VARCHAR ne sont pas logger (sauf si on le demande). On peut également demander que les modifications ne soient pas logger même si cela est dangereux en créant une table avec la chaine NOT LOGGED.

Améliorer la rapidité des requêtes d’insertion

En augmentant la valeur de DB2MAXFSCRSEARCH, on va optimiser DB2 pour réutiliser au maximum de l’espace et donc limiter la taille de la base de donnée. En diminiuant la valeur, on va optimiser la rapidité des INSERT mais avoir une augmentation de la taille de la base plus rapide.

Il est également possible d’utiliser des algorithmes d’insertion alternatifs comme APPEND MODE qui va ajouter le nouvel enregistrement à la fin de la table, pour cela, on utilise la commande ALTER TABLE APPEND ON. Mais également, l’algorithme de clustering index qui va ajouter l’enregistrement dans la même page si possible que les autres enregistrements étant dans la même branche du B-Tree de l’index, pour cela, il faut utiliser la commande ALTER TABLE … PCTFREE.

Ajuster les paramêtres de connexion client/server

Tout d’abord, afin d’utiliser pleinnement le nombre de connection maximum de client définit par la variable max_connections, il faut qu’il est une valeur supérieur à celle de max_coordagents i.e. le nombre maximum d’agent de coordination. Puisque chaque agent coordinateur peut traiter plusieurs clients, en ayant un bon nombre, on pourra servir un grand nombre de client avec un nombre limité d’agents et donc réduire la quantité des ressources utilisées (ie max_connections > max_coordagents).

La commande SET CLIENT CONNECT_NODE permet de définir si les agents de coordination vont gérer une ou plusieurs connections en sous traitant les tâches à des sous agents.

Les premiers points de tuning

Après la création de la base de donnée, il faut lancer le Configuration Advisor qui va permettre de configurer une première fois de maniére basique les variables de la base de donnée par rapport au hardware de la machine.

Il faut également utiliser les autres wizards comme dans le Control Center pour faire une première étape de tuning.

On peut également utiliser le Design Advisor pour aider à la prise de décision et prendre les décisions qui auront le plus d’impact sur le système.

Une autre méthode est d’utiliser la configuration ACTIVATE DATABASE afin d’activer les bases de données au lancement et pas quand le premier client se connecte ce qui réduira le délais de connection au premier lancement.

Tuning: L’allocution de mémoire

La taille de la mémoire totale utilisé pour la base est spécifiée par database_memory. Par défault, ce paramêtre est configuré automatiquement mais on peut le spécifier pour décider avec quelles quantités de RAM, la base va démarrer.

Si sheapthres est à 0 (par défault) alors la mémoire utilisée par les clients sera prise sur la mémoire globale allouée à la base. La variable applheapsz permet de décider combien de mémoire maximum est alloué à une application. On peut également définir combien de mémoire maximum l’ensemble des applications pourra utilisé via appl_memory. Il faut donc bien penser à mettre ces valeurs en adéquation avec les nombres de clients et d’agents possible pour ne pas allouer trop de mémoire qui serait une utilisation inutile ou trop peu et dans ce cas le nombre maximum de clients risquent de poser des graves problèmes de disponibilité de mémoire.

La variable numdb permet de configurer combien de connexion actives maximum seront autorisé sur la base. Si on augmente la taille mémoire disponible, on peut donc augmenter cette valeur.

La variable maxappls permet de définir le nombre maximum d’applications qui peuvent se connecter à la base et cette valeur est bien sur à mettre en corrélation avec le nombre de connection autorisé et les autres paramêtres qui en découlent.

La mémoire partagée

La variable audit_buf_sz permet de définir la taille du buffer relatif aux agents d’audits.

La variable mon_heap_sz permet de définir la taille de buffer alloué aux agents de monitoring.

La variable fcm_num_buffers permet de définir le buffer disponible pour le fast communication manager. Cette aspect est particulièrement important dans le cas des systèmes avec une base de donnée sur plusieurs partitions. Il faut suffisament de mémoire pour que les partitions puissent communiquer de manière fluide.

locklist permet de définir la taille de buffer disponible pour les listes de lock relatif aux tables.

dbheat définit à proprement parler la taille mémoire utilisée par la base de donnée.

util_heap_sz définit la taille du buffer disponible pour les utilitaires.

pckcachesize définit la taille du buffer pour le cache des packages.

Finalement, catalogcache_sz permet de définir la taille buffer du catalog.

La taille du buffer de statement (état) pour les applications est définit par la variable stmtheap et celle pour les statistiques par stat_heap_sz.

La taille de la pile des agents est définit par agent_stack_sz.

La taille du buffer de la couche de support des applications est définit par aslheapsz et celle des entrées/sorties des clients par rqrioblk.

Le nombre de buffer FCM est définit par la variable fcm_num_buffers et est valable pour chaque partition de la base. Par défault, cette valeur est initialisé à automatique (AUTOMATIC). Pour améliorer ce paramêtre, il faut également prendre en compte buff_free et buff_free_bottom. Il est également essentiel de bien configurer le nombre de canaux FCM en utilisant la variable fcm_num_channels et en prenant en compte ch_free et ch_free_bottom.

Tuning de la mémoire

Tout d’abord, une des régles principales du tuning mémoire est de ne jamais mettre la valeur la plus haute sauf si cela a bien été réflechi à l’avance. En effet, en mettant des valeurs trop grandes, DB2 risque d’utiliser la totalité de la mémoire disponible est provoquée des problèmes plus ou moins graves sur le système. De plus, plus on utilise de la mémoire, plus il faut de ressources pour gérer cette grande quantité de donnée. Il ne faut pas oublier de prendre en compte la swap dans les calculs d’allocation de mémoire. Dans la plupart des cas, la mémoire est alloué suivant les besoins mais dans le cas des listes de verrou (locklist), des Applications Support Layer Heap Size (aslheapsz), des buffers FCM, des canaux FCM et des piscines de buffer, la mémoire maximum est allouée dès le début.

Il est très important de faire un benchmark approfondi pour vraiment choisir le meilleur cas, pour cela, il faut prendre des requêtes SQL en tant que test et en modifiant les paramêtres trouver l’ensemble de variable pour lequel la puissance est maximum et s’y arrêter avant que l’ajout de ressources supplémentaires soit sans conséquences sur les performances de la base de donnée.

On peut alloué plus de mémoires que disponible sur le système mais cela dans le pire des cas peut provoquer un fort ralentissement si des données fréquemment consultées sont mises dans le swap.

Configuration Automatique: Self-Tuning

Pour se faciliter la tâche, on peut utiliser les outils de configuration automatique des paramêtres d’utilisation des ressources mémoires. Quand il est activé, le self-tuning va alloué dynamiquement suivant les besoins et les ressources disponibles les différents variables de la gestion de la mémoire.

Depuis DB2 9.0, un outil de tuning automatique de la mémoire utilisée par DB2 est présent par défault. Par exemple, le paramêtre database_memory sera alloué automatiquement suivant les besoins et sera modifié dynamiquement suivant les besoins de la base. Dans ce cas, la variable database_memory est initialisé à la valeur AUTOMATIC, on peut également la spécifier à la main dans ce cas, l’ensemble de la base de donnée utilisera la quantité de RAM spécifié, sinon on peut également mettre la variable à COMPUTED et dans ce cas, la valeur spécifié pour database_memory sera l’addition de l’ensemble des variables d’utilisation de mémoire (buffers, locklist, etc).

Nous pouvons également utiliser le self-tuning pour d’autres variables de la base de données:

• pour les piscines de buffer via les commandes ALTER BUFFERPOOL et CREATE BUFFERPOOL.
• pour le cache des packets, la variable pckcachesz.
• pour la liste de lock, les variables locklist et maxlocks.
• pour la mémoire partagée, les variables sheapthres_shr et sortheap.

Mettre en place le self-tuning

Pour activer le self-tuning, il faut passer la variable self_tuning_mem a ON. Pour cela, nous pouvons utiliser la commande UPDATE DATABASE CONFIGURATION mais aussi l’API SQLFUPD ou dans le contrôle center via la fenêtre Change Database Configuration Parameter.

Ensuite, il faut activer le self-tuning pour chaque paramêtre dont on souhaite qu’il bénéficie de cette configuration automatique (i.e. buffers, locklists, etc), pour cela, on doit passer à AUTOMATIC les variables correspondantes en utilisant la commande UPDATE DATABASE CONFIGURATION, l’API SQLFUPD ou le contrôle center via la fenêtre Change Database Configuration Parameter.

Finalement, pour mettre en place le self-tuning sur les piscines de buffers, il faut passer à AUTOMATIC la taille des piscines de buffer en utilisant la commande ALTER BUFFER POOL pour ceux déjà existant ou CREATE BUFFER POOL pour en créer de nouveaux.

Pour que tout cela fonctionne, il faut qu’au moins deux paramêtres soient spécifiés en AUTOMATIC. De plus, pour que locklists soit réellement en self-tuning il faut que les variables locklist ET maxlocks soient initialisées à AUTOMATIC. Même chose avec la mémoire partagée et les variables sortheap et sheapthres_shr et encore, la configuration automatique de ces variables est autorisé uniquement si sheapthres est initialisé à 0.

Désactiver la configuration automatique

Pour désactiver la configuration automatique, il faut passer la variable self_tuning_mem a OFF. Pour cela, on peut utiliser les mêmes méthodes que pour l’activation (voir ci-dessus).

En pratique, la configuration automatique et dynamique est désactivée si seulement un parametre est configuré en AUTOMATIC, en effet, le programme ne peut pas répartir les ressources vers une seule variable, il en faut minimum deux.

Par exemple, pour désactiver la configuration automatique sur sortheap et le configurer à 200, on va taper les commandes suivantes:

UPDATE DATABASE CONFIGURATION USING sortheap MANUAL

UPDATE DATABASE CONFIGURATION USING sortheap 200

Dans certains cas, comme expliquer ci-dessus, il y a des inter-dépendances à gérer entre les variables pour que la configuration automatique soit réellement activée ou non.

Pour les piscines de buffers, il est possible de les configurer à la main et plus en automatique en utilisant, par exemple, la commande suivante:

ALTER BUFFERPOOL bufferpool1 SIZE 1000

Déterminer quelles variables sont en configuration automatique

Pour voir quelles variables sont configurées, on utilise les commandes suivantes GET DATABASE CONFIGURATION avec SHOW DETAIL mais également la commande db2cfgget API en regardant les flags SQLF_OFF à 0, SQLF_ON_ACTIVE à 2 et SQLF_ON_INACTIVE à 3. Finalement, vous pouvez également avoir cette information via la fenêtre Database Configuration du Contrôle Center.

Pour voir les bufferpools qui sont en configuration automatique, vous devez utiliser la requête SQL suivante SELECT BPNAME, NPAGES from sysibm.sysbufferpools quand la configuration automatique est activé, NPAGES doit avoir pour valeur -2 et quand elle est désactivé la taille actuelle de la piscines de buffer. Quand la configuration automatique est activée, on peut monitorer la taille actuelle de la piscine de buffer en utilisant la commande suivante GET SNAPSHOT FOR bufferpools ON db_name. Finalement, avec le centre de contrôle, il faut séléctionner via le click droit la piscine de buffer et regarder les attributs objets de cette dernière.

Comment marche le self-tuning et quelles sont ces limitations

Un algorithme a été développé pour faire fonctionner de manière optimale le self tuning, pour cela, chaque composant de DB2 envoit ces demandes en mémoire à un composant central, ces demandes de mémoire sont calculés localement par chaque composant en comparant les gains obtenues avec un gain et une réduction de mémoire. Ensuite, suivant l’importance du composant et ses priorités, le composant central va alloué ou reprendre de la mémoire à chaque composant.

Une fonction tourne en fond de tâche pour vérifier périodiquement de cette répartition et comment l’améliorer. La fréquence de lancement de cette fonction va dépendre du type de système, en effet, sur un système avec un taux de charges très changeant, la fonction tournera toutes les 10 minutes pour se baser sur des moyennes plus stables alors qu’avec un système avec un taux de charges stables, elle tournera toutes les 30 secondes.

En général, il faut une heure pour atteindre une configuration automatique de bonne qualité et on arrive à l’optimale au maximum après une dizaine d’heure d’utilisation. Le pire des cas étant avec des requêtes très changeante et où le système va avoir du mal à trouver l’optimal.

En conclusion, on peut dire que la configuration automatique est optimisé pour les systèmes ayant un comportement relativement stable et ne sera pas trouver la bonne configuration stable pour un système changeant.

Dans le cas d’un système multi-partitions

Dans le cas d’un système multi-partitions, certains paramêtres sont critiques pour rendre le fonctionnement de l’autoconfiguration réellement efficace. En effet, dans ce cas, le self-tuning est calculé pour l’ensemble de la base sur le comportement d’une seule partition. Une fois des modifications faite sur une partition, elles sont distribuées à l’ensemble des partitions. Si chaque partition a le même comportement et le même besoin, alors la configuration self-tuning expliqué avant, est totalement fonctionnelle mais dans le cas où les ressources sont différentes pour chaque partition (i.e. disques de différentes vitesses, comportements des requêtes différents, etc), il faut modifier la méthode expliquée ci-dessus.

Dans certains cas, le self-tuning est recommandé pour les systèmes multi-partitions dans le cas, où toutes les partitions ont les mêmes besoins mémoires, tournent sur le même matériel.

Dans d’autres cas, il est conseillé de désactiver la configuration automatique sur certaines parties de la configuration comme les catalogues et les partitions de coordinations des autres partitions car elles ont des comportements différents des partitions de stockage à proprement dite.

Enfin, quand la configuration matériel et/ou les besoins de chaque partition sont différentes, il est très déconseillé d’utiliser la configuration automatique.

Pour pouvoir savoir, si une configuration optimale entre les différentes partitions est possible, nous utilisons la commande GET SNAPSHOT FOR DATABASE ON db_name et pour les piscines de buffers GET SNAPSHOT FOR BUFFERPOOL ON db_name.

Quand le self-tuning est activé dans un système multi-partition, l’une des partitions sert de référence afin de calculer les différents paramêtres optimales pour l’ensemble des partitions du système. Cette partition est choisi suivant le nombre de partition et de piscines de buffer. On peut savoir quelle partition est utilisé pour le tuning en tapant la commande CALL.SYSPROC.ADMIN_CMD(‘get stmm tuning dbpartitionnum’) et pour changer la partition utilisée CALL.SYSPROC.ADMIN_CMD(‘update stmm tuning dbpartitionnum <db_partition_num>’). En entrant comme db_partition_num la valeur -1, on relance le choix automatique de la partition selectionnée pour le tuning.

Dans un environement multi-partition, il faut activer explicitement le self-tuning sur chaque partition en utilisant la commande ACTIVATE DATABASE.

Comme dans un environement mono-partition, on peut désactiver tout ou une partie des paramêtres en autoconfiguration (voir ci-dessus).

Finalement, on peut dire qu’idéalement dans un système multi-partition, on disposera des mêmes ressources pour chaque partition et que la charge sera répartie de manière équitable entre elles.

Le management des piscines de buffer

Les piscines de buffer fournissent la mémoire et le cache pour les pages de la base de donnée. Cela permet d’améliorer la rapidité du système en permettant un accés au donnée plus rapide via la mémoire que via le disque dur. Puisque l’accés à disque dur est beaucoup plus lent, on tentera de tendre vers un cas où le disque sera le moins accéder possible afin d’améliorer les performances de l’ensemble de la base. Puisque l’ensemble de ces manipulations en mémoire est faite à partir des piscines de buffer, c’est le point le plus important à configurer dans le système.

Quand un client fait une requête, les données sur lesquelles cette requêtes opére, sont chargées en mémoire puis la requête est effectuée. Les piscines de buffer peuvent être créer, modifier et supprimer via les commandes ALTER BUFFERPOOL et CREATE BUFFERPOOL. La commande db2mtrk -d permet de suivre en temps réel les ressources alloués à chaque piscine de buffer.

Les pages qui se trouvent dans les piscines de buffer peuvent être en utilisation, non utilisées et sales ou non utilisées et propres. Les pages en utilisation sont celles qui sont actuellement utiliser en modification ou lecture, celle non utilisées et sales sont celles qui ont subit des modifications qui ne sont pas encore répercuter sur le système et qui ne sont plus en utilisation et celle qui sont non utilisées et propres sont celles qui sont prêtes pour la réutilisation. La page reste en mémoire jusqu’à ce que la base de donnée soit arrêté, qu’elle soit réoccupé par une autre page ou purger explicitement par la piscine de buffer. Pour décider quelles pages sont supprimées de la mémoire, on se base sur la dernière fois que la page a été accéder, la probabilité qu’elle le soit une nouvelle fois, le type de donnée contenu et finalement si la page contient des données modifiées qui ne sont pas encore répercutées sur le disque. Quand une page est écrite sur le disque, elle est supprimée de la mémoire automatiquement.

L’effacement et la mise à jour du disque par rapport aux données mémoires est effectué par l’agent page-cleaner. On peut optimiser son comportement dans le cas où on se trouve dans un système ayant des modifications fréquentes, ce point sera aborder par la suite.

Si, après un crash, la plupart des pages était écrite sur le disque alors la récupération sera beaucoup plus rapide car elle n’aura plus qu’à recharger les piscines de buffer par rapport aux données stockées sur le disque plutôt que devoir rejouer toutes les requêtes afin de retrouver l’état de la base lors du crash. Si pool_lsn_gap_clns est à 0 alors l’écriture pro-active des pages est activée. De plus, si log_held_by_dirty_pages est constament plus grand que logfilsiz (la taille des fichiers de logs) multiplié par softmax (le pourcentage de fichiers de logs à récupérer en cas de crash, par exemple, pour 450, 4.5 logs files seront dédiés à la récupération en cas de crash) alors il faut ou augmenter le nombre d’agent page-cleaner ou modifier la valeur de la variable softmax.

Le management d’une base contenant plusieurs piscines de buffer

On peut créer plusieurs piscines de buffer ayant des tailles de pages différentes pour chaque base. Il faudra dans ce cas veiller à la bonne configuration de chaque piscine de buffer comme on l’a expliqué ci-dessus.

Avoir de grandes piscines de buffer permet de stocker les pages qui sont souvent accéder dans la piscine de buffer et donc d’avoir un temps d’accés grandement améliorer mais permettent également de pouvoir effectuer plus de requêtes dans un même temps et permettent finalement de limiter les ralentissements dû aux temps d’accés sur le disque de données souvent accéder tel que les indexes.

Si vous avez moins de 10 000 pages de 4K ou que personne ne connait le tuning d’application ou que vous êtes sur un système de test, vous ne devriez pas utiliser plusieurs piscines de buffers.

Sinon vous pouvez en utiliser plusieurs dans les cas suivants: les espaces de tables temporaires peuvent être alloués à une piscine de buffer spécifique pour fournir de meilleurs performances pour les requêtes qui demandent un assez large espace mémoire et tout particulièrement celles où l’on utilise des requêtes organisées/classées (sort). Si des données sont accédées fréquemment par un grand nombre de petites requêtes, il est intéressant de les placer un espace de tables qui est sa piscine de buffer spécifique afin de réduire les temps de recherche des données avec une réduction des couts de recherche. Mais on peut également isoler dans des piscines de buffer différentes, les différents composants de DB2 comme les parties applicatives, données et indexes. Par exemple, on placera les données et les indexes les plus fréquemment accéder dans des piscines de buffer spécifiques afin de réduire l’impact de ces accés par rapport à la charge totale du système et donc laisser plus de temps machine pour les autres requêtes.

On peut également utiliser des petites piscines de buffer pour les données qui sont accéder de manière très aléatoire et qui n’ont pas besoin de rester plus longtemps que la requête dans la mémoire. Comme cela, on aura plus d’espace disponible pour le reste des composants de DB2.

La somme totale de la taille des différentes piscines de données doit être disponible au lancement de DB2 sinon la base ne se lancera tout simplement pas.

Depuis la version 8.1.4, un nouveau système de nettoyage pro-active des pages sales existe, il est différent selon deux principales axes, tout d’abord, plutôt que de garder un certain pourcentage des pages propres (chngpgs_thresh), les agents page-cleaners sont prévenus de l’adresse des pages à nettoyer et donc ils n’ont pas à chercher lesquels doivent l’être. L’autre aspect est une meilleur gestion de la répartition dans le temps de l’écriture des modifications effectuées sur les données. Cette nouvelle méthode est activable en passant à ON la variable DB2_USE_ALTERNATE_PAGE_CLEANING.

Pré-charger les données dans les piscines de buffer

Pré-charger des données dans les piscines de buffer permet de charger certaines données en mémoire avant même qu’elles soient demandées et donc augmenter la vitesse globale du système. Il y a deux méthodes: la méthode séquentielles qui va chargées en mémoire la page suivante de celle actuellement accéder et la méthode de listes de pré-chargement qui va permettre de charger des pages qui ne sont pas les unes à la suite des autres.

Le pré-chargement est plus important encore dans le cas des systèmes multi-partitions, le préchargement des indexes devient quelques choses de très critiques pour augmenter la rapidité du système.

Pour définir, le nombre de page pré-chargé de manière séquentielle est défini par PREFETCHSIZE quand on lancer les commandes CREATE TABLESPACE ou ALTER TABLESPACE, la valeur est accessible via la variable PREFETCHSIZE dans SYSCAT.TABLESPACES du catalogue.

Le bon comportement est d’initialiser la valeur de PREFETCHSIZE à un multiple du nombre de partitions actuellement présente sur le système, mais également le nombre de disques sur laquelle la partition repose (dans le cas de partition RAID) et finalement la valeur de EXTENTSIZE (qui est le nombre de pages écrites dans un containeur avant qu’on en utilise un autre). Par exemple, si le EXTENTSIZE est initialisé à 16, que l’espace de tables a 2 containeurs, il faut définir à 32 la variable PREFETCHSIZE. Si l’on à 5 disques physiques par containeur, alors il faut la définir à 160.

Pour éviter que des données en accés soient supprimées de la mémoire par les pré-chargements, l’agent de monitoring va surveiller que ce cas ne puisse pas arrivé en réduisant par moment le nombre de pages pouvant être pré-charger (i.e. PREFETCHSIZE).

Ce fonctionnement de pré-chargement permet de bien meilleur performance, particulièrement dans le cas de très grandes tables. Comme d’habitude, utilisez les outils de monitoring afin de trouver la meilleur valeur pour PREFETCHSIZE, pour cela, on pourra voir si il y a des accés entrée/sortie en attente via les outils de monitoring propres au systèmes et voir si le pré-chargement a lieu en regardant le contenu de la variable pool_async_data_reads.

Si il y a des accés en attente et que la requête est entrain de pré-charger des données, il faut augmenter la valeur de PREFETCHSIZE. Si le pré-chargement n’est pas la cause de l’attente d’accés, augmenter sa valeur ne changera rien.

Dans tous les cas, il est préférable d’utiliser des containeurs utilisant eux-mêmes des disques physiques différents.

Dans certains cas, il est difficile de savoir si le pre-chargement séquentiel va réellement améliorer les performances, dans ce cas, on peut activer un moniteur qui va activer ou désactiver le pre-chargement suivant ce qu’il observe dans la base. Pour activer ce comportement, on utilisera la variable seqdetect.

Bien entendu, on ne doit pas perdre de vue que pre-charger des données à un cout en terme de ressources, en effet, il provoque un grand nombre d’accés en entrée/sortie.

Via l’utilisation des indexes, les optimizers pourront calculer automatiquement des pages non séquentielles à pré-charger par exemple quand on a une requête du type WHERE NAME BETWEEN ‘A’ and ‘I’.

Afin de pouvoir charger de manière paralléle des données depuis le disque et donc améliorer la rapidité du système, on peut modifier la valeur de num_ioservers qui correspond aux nombres d’agents travaillant sur l’intéraction lecture/ecriture avec le disque et la mémoire. Il faut avoir une valeur de num_ioservers au moins égale au nombre de disques physiques. Il faut mieux surestimer le nombre de serveurs d’entrée/sortie nécessaire que le sous-estimer, en effet, la base utilise toujours le nombre minimal de serveurs d’entrée/sortie possible et donc les autres seront tout simplement inutilisés. Pour avoir une encore meilleur approximation du nombre d’agent d’entrée/sortie nécessaire, il faut prendre en compte le nombre d’agent provoquant des pré-chargement et le nombre maximum d’agent d’entrée/sortie pouvant travailler en paralléle.

Si il existe plusieurs containeurs pour le même espace de table alors il est possible d’avoir un accés paralléle aux accés d’entrées/sorties, on pourra donc avoir plusieurs containeurs travaillant en paralléle ce qui permettra de bien meilleurs performances. Bien entendu, au final, on se retrouvera limiter par le nombre de disques physiques se trouvant sur le système.

Maintenir l’organisation des tables et des indexes

Avec le temps, nous nous retrouvons souvent avec des tables fragmentées, des indexes se trouvant sur plusieurs pages mémoires, etc ce qui va provoqué une augmentation du nombre de pages lues pour trouver une donnée. La réorganisation permettra donc d’améliorer les performances mais également de limiter l’utilisation de place inutile.

Il faut donc procéder en plusieurs étapes: tout d’abord, déterminer quels indexes et tables ont besoin de cette réorganisation, choisir la méthode de réorganisation, faire la réorganisation sur les objets choisis, surveiller la progression de la reconstruction, dans le cas de réorganisation des tables en lignes, il faut pouvoir garder la possibilité de la stopper pour la reprendre plus tard, évaluer les chances de réussite et surveiller la réussite, garder la possibilité de revenir en arrière, récupérer des stastiques sur la réorganisation et relancer les applications qui étaient en attente des données réorganisées.

La réorganisation des tables

Avant de lancer une réorganisation d’une table, il faut déjà mettre à jours les stastiques via la commande RUNSTATS et si cela n’a pas d’effet sur l’amélioration alors on lance la commande REORG TABLE.

On doit réorganiser une table quand il y a un fort taux de insert, update et delete, des changements de performances sur des tables utilisant des indexes, RUNSTATS n’aide pas à améliorer les performances, REORGCHK indique qu’il faut une réorganisation de la table, finalement, il faut prendre en compte le gain de temps provoqué par la réorganisation par rapport au temps que va prendre la réorganisation.

Pour éviter à avoir à réorganiser trop fréquemment les tables, on peut utiliser ALTER <table_name> pour ajouter PCTFREE, créer des indexes clusterisés avec PCTFREE sur l’index, classer les données, charger les données. Cela va permettre de placer dans les bonnes pages les données afin qu’elles restent classé, bien sur, pour cela, il doit y avoir suffisament de pages mémoires disponibles.

Si vous créez des tables multi-dimensionnelles, vous aurez moins besoin de réorganiser ces tables de part leur conception.

Choisir une méthode de réorganisation

Il y a deux méthodes: classic (ou hors ligne) et inplace (ou en ligne). Par défault, c’est la méthode offline qui est choisi. Chaque méthode ayant ces avantages et ces inconvénients, on doit choisir celle qui est le meilleur compromis pour notre utilisation.

Les avantages de la réorganisation hors ligne:

• plus rapide pour les champs de données longs comme les LOB et les LONG.
• les tables et les indexes sont parfaitement réorganiser.
• les indexes sont reconstruits une fois la table réorganiser et non pas plusieurs fois (i.e. à chaque étape) comme la méthode en ligne.
• permet de réduire la taille utilisée par la table et les indexes.
• permet de spécifier un index différent de celui déjà existant pour réorganiser différement la table.

Les désavantages de la réorganisation hors ligne:

• durant la reconstruction, la table est presque totalement inacessible.
• utilisation d’un grand espace de mémoire pendant la reconstruction.
• impossibilité de mettre en pause et de relancer la réorganisation

Les avantages de la réorganisation en ligne:

• permet un accés continu aux données pendant la réorganisation.
• permet de stopper, reprendre le processus.
• permet de reprendre après une erreur
• utilise moins de ressources pendant la reconstruction
• permet de voir les bénéfices de la réorganisation au fur et à mesure du processus en temps réel.

Les désavantages de la réorganisation en ligne:

• peut mener à des données et indexes imparfaits.
• les pages du début restent plus fragmenté.
• moins rapide que la méthode offline (10 à 20 fois plus lent).
• demande plus de place dans les fichiers de logs.
• les indexes sont maintenus et pas reconstruits, il faudra donc peut etre les reconstruires.

La réorganisation des LOB et LONG ne permet pas de gagner en performance mais uniquement en espace utilisé.

On peut surveiller le processus de réorganisation via la commande db2 list history

Fonctionnement de la méthode hors ligne

Elle se base sur une copie totale de la table en la reconstruisant de manière organiser.

1. Classement des lignes de la table suivant un index si spécifié, sinon on peut faire un scan des indexes pour choisir lequel utiliser ou finalement un scan de la table complet pour décider de la réorganisation.
2. Reconstruction organiser de la table dans l’espace de tables ou dans l’espace temporaire.
3. Remplacement de l’ancienne table par la copie organisée.
4. Recréation des indexes.

Nous pouvons surveiller le processus via le monitoring de snapshot ou les vues administrateur des snapshots.

Pour pouvoir faire une reconstruction hors ligne, vous avez besoin des droits SYSADM, SYSCTRL, SYSMAINT ou DBADM ou le privilége CONTROL sur la table.

Quelques exemples:

db2 reorg table <dbname>.<tablename>

db2 reorg table <dbname>.<tablename> index <myindex> use <temp_table_space>

La méthode hors ligne va marcher totalement ou pas du tout, il n’y a pas de demi-mesure. De plus, pour améliorer les performances, vous pouvez empêcher l’accés totalement à la table pendant la réorganisation. Vous pouvez également utiliser le même espace de table pour la table source et la table reconstruite afin de limiter les intéractions entre espace de tables. Vous pouvez également détruire les indexes inutiles avant de lancer la reconstruction, vérifier que la variable de pré-chargement est correctement défini, activer INTRA_PARALLEL ce qui permet de reconstruire de manière paralléle (et donc plus rapidement) les indexes, modifier les valeurs de sortheap et sheapthres tel que sortheap multiplié par le nombre de processeur soit égale à sheapthres, vérifier le bon fonctionnement des agents de pages-cleaner.

Fonctionnement de la méthode en ligne

La réorganisation ne se fait pas en une fois mais par bloc en utilisant un espace temporaire.

1. Sélection d’un nombre N de pages.
2. Les pages sélectionnées sont déplacées dans l’espace temporaire et le processus attend que toutes les données ne soient plus accéder.
3. Les données sont réécrites de manière organisées dans la table.
4. Enfin la table est coupée afin de récupèrer l’ensemble de l’espace non utilisée.

Les permissions permettant d’effectuer une réorganisation en ligne sont les mêmes que dans le cas du hors ligne.

Un exemple: db2 reorg table <dbname>.<tablename> inplace.

Contrairement à la méthode hors ligne, cette méthode permet de stopper et de reprendre la réorganisation ce qui lui permet de gérer les erreurs. De plus, on peut stopper manuellement le processus via la commande db2 reorg table <dbname>.<tablename> inplace pause puis la relancer db2 reorg table <dbname>.<tablename> inplace resume.

Réorganisation des indexes

Vous avez plusieurs choix pour réorganiser les indexes suite à une réorganisation de table: en supprimant et en reconstruisant l’index, en utilisant la commande REORG INDEXES (ce qui permet une reconstruction en ligne), ou utiliser les arguments permettant la réorganisation de la table et des indexes de REORG TABLE.

Comme pour les tables, on peut améliorer les performances en bloquant l’accés en lecture et/ou écriture à l’index durant le processus de reconstruction en utilisant les arguments correspondant de la commande REORG INDEXES (i.e. NO ACCESS READ, etc).

Comment déterminer quand réorganiser les tables et les indexes

Tout d’abord, on peut regarder si il y a des problèmes d’overflow en faisant une requête sur la colonne OVERFLOW de la table SYSSTAT.TABLES. Mais également, vérifier le bon fonctionnement des procédures de pré-chargement pour cela, il faut regarder AVERAGE_SEQUENCE_FETCH_PAGES (si le chiffre est trop faible alors il y a un problème de fragmentation, en théorie, il devrait être proche de PREFETCHSIZE), AVERAGE_RANDOM_FETCH_PAGES (une augmentation de ce chiffre dans le temps permet de mettre en lumière un problème de fragmentation) et AVERAGE_SEQUENCE_FETCH_GAP (un grand nombre signifie que la table est fragmenté) dans les tables SYSCAT.INDEXES et SYSSTAT.INDEXES. Nous pouvons également voir si le nombre de feuille vide dans l’arbre représentant l’index n’est pas trop grand en regardant NUM_EMPTY_LEAFS dans SYSCAT.INDEXES et SYSSTAT.INDEXES. NLEAF de SYSCAT.INDEXES permet d’avoir une idée du nombre de feuille de l’arbre de l’index, un nombre trop grand par rapport aux nombres de colonnes est également révélateur d’une fragmentation de la table. Finalement, le nombre de page vide consultable par FPAGES et NPAGES dans SYSCAT.TABLES, en soustraiant NPAGES à FPAGES, on va avoir le nombre de page utilisé mais vide, plus ce nombre augmente plus la base est fragmenté.

Les couts d’une réorganisation

Comme nous avons pu le voir ci-dessus, réorganiser une table va utiliser un grand nombre de ressources, il faut donc savoir si cela permettra d’avoir une rapidité supérieur à la fin de la réorganisation que si nous n’avions rien fait et donc pas dépenser des ressources dans cette réorganisation.

Il faut également être sur d’avoir suffisament de ressources pour effectuer cette réorganisation, pour le mode offline comme pour le mode online, il faut suffisament de place dans les espaces de tables où la réorganisation va avoir lieu.

La réorganisation automatique

On peut activer la réorganisation automatique des tables et des indexes pour cela, il faut aller dans le Controle Center puis cliquer sur Configure Automatic Maintenance dans cette aide, on peut activer la réorganisation automatique. Sinon on peut également passer à ON les variables AUTO_MAINT, AUTO_TBL_MAINT et AUTO_REORG.

Utiliser les indexes relationnels pour augmenter la rapidité

Nous ne reviendrons pas sur l’utilité des indexes qui sont essentielle pour une bonne optimisation de toute requête SQL répétée. On peut améliorer la rapidité de la recherche dans les indexes en ayant un espace mémoire application (utility heap). Mais également, augmenter la valeur de sheapthres tout en vérifiant que aucun overflow ne soit en cours. Un des points permettant de grandement augmenter la rapidité des indexes et de ne pas les placer dans le même espace de table que les tables en elle-même afin de séparer les accés entre ces deux types de données. Bien entendu, il faut aussi penser à garder les indexes à jours et à les réorganiser.

En créant un index avec MINPCTUSED, on peut spécifier que cet index sera automatiquement réorganiser au besoin.

Quelques conseils d’optimisation SQL

Tout d’abord, il faut toujours éviter l’utilisation de *, même si cela facilite certaines requêtes, elle provoque une augmentation de l’utilisation des ressources inutiles, il est préférable de juste lister les colonnes dont on a vraiment besoin. Si on a pas besoin de l’ensemble des lignes retourner, on peut optimiser le nombre de ligne de retour en utilisant OPTIMIZE FOR. On peut également utiliser optimize for afin de récupérer rapidement les premiers lignes puis autoriser un temps de latence pour les autres. Par exemple:

SELECT EMPNAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 100 ROWS ONLY
OPTIMIZE FOR 20 ROWS

Suivant les besoins, on peut également améliorer la rapidité d’un select en utilisant FOR READ ONLY ou FOR FETCH ONLY, de plus, cela permet de limiter le nombre de lock créé. Dans la même optique, FOR UPDATE OF permet d’autoriser le système une meilleur précision dans les données à verrouiller.

Il faut également éviter de comparer des données qui ne sont pas du même type. Il ne faut pas utiliser DISTINCT ou ORDER BY sauf si on en a besoin car ils sont très gourmand en ressources.

Pour compter le nombre de ligne d’une table, il ne faut pas utiliser SELECT COUNT(*) mais plutôt que * une colonne dont on sait qu’elle reflettera le nombre de ligne.

This entry was posted on Tuesday, December 18th, 2007 at 11:46 am and is filed under database, lifo-sds. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.