Il y a peu j’ai réalisé une mission d’optimisation sur une architecture RAC. La problématique provenait du profil de l’activité correspondant à de nombreuses insertions simultanées sur la même table, provoquant de fait un vrai point de contention. Sur une base de données RAC, le trafic généré par la gestion et la mise à jour des indexes fait chuter rapidement les performances. Pour chaque insertion, il faut que la priorité d’accès aux blocs d’indexes soit établie entre les instances des nœuds, et comme ce sont les mêmes blocs qui sont en jeu, cela provoque des délais importants, incompatibles avec un environnent hautement transactionnel, et ce, même si un seul index est à maintenir. Heureusement, plusieurs possibilités existent pour éliminer cette contention et nous allons voir certaines de ces solutions, particulièrement l’utilisation du partitionnement qui aura ainsi toute son utilité même avec un profil d’activité de type OLTP.
Voyons les solutions possibles :
1) Dédier les écritures sur un nœud du RAC et distribuer les lectures sur les nœuds restant; C’est la méthode la plus simple et la plus efficace, mais on perd tout l’avantage de l’équilibrage de la charge et de l’évolutivité liée à l’architecture et cela nécessite de reprendre le code applicatif;
2) Comme suggéré par Oracle dans les notes MOS « Troubleshooting ‘enq: TX – index contention’ Waits in a RAC Environment. (Doc ID 873243.1) » et « Top 5 Database and/or Instance Performance Issues in RAC Environment (Doc ID 1373500.1) », on peut créer un index inversé (reverse key index); Cela fonctionne plutôt bien, mais le gros désavantage c’est que les indexes inversés ne peuvent pas être utilisés par l’optimiseur pour une opération de type « Range scan ». De plus, comme nous le voyons plus bas, l’évolutivité n’est pas au rendez-vous, spécialement quand on couple l’index avec une séquence (il y a seulement 10 points de démarrage différents pour un index). En cas de mixité de charge entre de fortes lectures et de fortes écritures, oublier les performances pour les requêtes qui filtre sur les colonnes de type TIMESTAMP pour identifier les enregistrements les plus récents (sauf à être sur un appliance Exadata);
3) Ma méthode préférée : Partitionner la table avec la méthode hash (Hash partition) sur la clé primaire et utiliser des index locaux; Ainsi pendant les insertions simultanées sur plusieurs noeuds, les blocs d’insertion sont différents entre chacune des instances qu’ils soient pour la table ou les indexes locaux associés, évitant toute la gestion globale des ressources, donc le trafic et les attentes inter-nœuds. La solution supporte vraiment les contraintes de charge, les tests ci-dessous montrent que l’on peut monter jusqu’à 1000 partitions pour supporter des taux d’insertion élevé.
Bien, arrêtons là la théorie et voyons ce qui ce passe en pratique. J’ai mis en place un test avec les éléments suivants :
  – Création d’une table TEST basée sur la table DBA_OBJECTS (CTAS ;
  – Création d’une clé primaire (PK) sur la colonne OBJECT_ID;
  – Outillage d’injection qui réalise les opérations suivantes :
    * Création de 100 connexions vers le cluster RAC (RAC deux nœuds, 16 processeurs chacun);
    * Utilisation d’un pool de 1000 threads résidentes;
    * 50000 boucles dans le pool avec les actions:
      + Prise d’une connexion disponible vers la base de données RAC du cluster;
      + Insertion d’une ligne dans la table TEST en utilisant une séquence;
      + Attente minimal de 50 ms;
      + commit.
Voici le tableau des résultats. Deux critères pour l’analyse : Le temps pris pour l’insertion « Time spent inserting » et le nombre maximum de sessions active sur la base « Maximum run queue size ». Déclinaison des différentes conditions du test : base non RAC , table TEST non partitionnée, index sur la PK (no RAC, no partition and regular index on PK), base RAC, table TEST non partitionnée, index sur la PK (RAC, no partition and regular index on PK), base RAC , table TEST non partitionnée, index inversé sur la PK (RAC, no partition and REVERSE index on PK), suivi des variations sur le nombre de partitions pour base RAC, table TEST partitionnée, index local sur la PK (RAC, nn partitions with regular local index on PK) :

‘

En constatation globale : l’utilisation du partitionnement par hashage et un index local permettent d’avoir les meilleures performances et la meilleure évolutivité, surtout dans une architecture RAC.
Comme nous le verrons prochainement dans un autre post, il est aussi possible d’améliorer les choses lors de fortes charges en lecture sur un RAC en utilisant des indexes de type locaux sur les autres colonnes que la PK : on remplace ainsi de nombreuses opérations de « petit » parcours d’index (small range scan) par un seul « grand » parcours de la partition de l’index local.
Traditionnellement, on utilise plutôt les indexes de type locaux pour faciliter les opérations de maintenance pour les applications type DWH (échange, ajout ou suppression de partition) et pour les requêtes parallèles, alors que les indexes de type globaux sont liés aux applications de profil OLTP. Mais nous avons pu voir que les indexes globaux nuient aux performances dans une architecture RAC avec une forte charge d’écriture, sauf à utiliser des index globaux partitionnés, ce qui est encore le sujet d’une autre histoire.
Dernier conseil, notez que le nombre de partitions utilisées dans le mode par haschage doit être une puissance de 2 pour bénéficier de la meilleure répartition des lignes dans les différentes partitions (ref : Expert Oracle Database Architecture par Thomas Kyte).
Merci de votre attention , à bientôt.
(cet article est une traduction libre du post anglais de notre contributeur Lionel Magallon , qu’il m’excuse si je n’ai pas été assez fidèle à son propos)