Bonjour à tous,
je me propose aujourd'hui de mettre en ligne un case study que j'ai écrit pour la Newsletter du User Group Informix France ( petit nom UGIF ), auquel je vous recommande de vous abonner car elle contient plein d'infos intéressantes. Pour s'abonner, c'est très simple: envoyez un email à ifmxnewsletter@fr.ibm.com avec "ABONNER" dans le sujet.
Bonne lecture et bons tests.
Eric
Etude de cas : Objectif performance…
"Bonjour, merci de vous être connecté sur notre station. Votre mission est, si vous l'acceptez, d'implémenter une nouvelle table dans la base de données, d'en faire un chargement initial avec un temps d'opération inférieur à 10 minutes, et de fournir à l'équipe de développeurs une manière de gérer au mieux les temps de réponses sur une requête imposée qui, selon le comité des testeurs de l'application reportant au Directeur des Opérations, a un temps de réponse inacceptable. Vous disposez d'IBM Informix Dynamic Server V11.70 FC2 Innovator-C Edition, un serveur Linux avec un processeur 4core, 16 Gb de RAM entièrement à votre disposition. Ah! J’oubliais, la table à charger comporte environ 70 millions de rangées, mais n'est pas très complexe quant à son schéma et son indexation. Il faudra de plus y charger environ 500 000 rangées tous les soirs"
Ce genre de demande vous rappellera très certainement des situations vécues soit en tant que DBA, soit en tant que développeur. On constate généralement, à ces instants précis, un certain flottement dans le regard du destinataire de cette requête, de même que des symptômes caractéristiques du découragement ou d'une féroce envie de décliner la demande sous le chef de « ça n'est pas réaliste ». Il est vrai que dans les versions antérieures d'IDS cette demande représentait un véritable défi, difficile à relever.
Mis à part peut-être l'utilisation de High Performance Loader, pas trop simple à mettre en oeuvre dans l'urgence, il faut avouer qu'il y avait au minimum de quoi être troublé par cette demande. Fort heureusement, nous sommes en 2011 et les laboratoires IBM Informix nous ont concocté un certain nombre d'améliorations très utiles, qui vont nous permettre de relever le défi avec IDS 11.70.
Analysons tout d'abord la demande: 1) La nouvelle table contient tous les billets d’avion sur les liaisons entre la France, l’Allemagne, l’Angleterre, l’Italie, l’Espagne et les Etats-Unis depuis le 1er janvier 2005. en voici le schéma : create table tickets (
ticketno bigint, # N° unique de ticket
lastname varchar(60), # Nom voyageur
firstname varchar(30), # Prénom voyageur
gender char(3), # MR/MRS/INF
flightdate date, # Date du vol
flightno char(6), # N° du vol
fileno char(8), # N° de dossier
price money(8,2), # Prix du vol
carrier char(2), # Compagnie aérienne
departfrom char(3), # aéroport de départ
destination char(3) # aéroport de destination );
create index tickets3 on tickets (flightdate);
create index tickets7 on tickets (destination);
Et enfin la requête qui pose des problèmes à nos amis développeurs:
select destination , count(*)
from tickets,airports
where tickets.destination = airports.code
and tickets.destination between "AAA" and "ZZZ"
and airports.country = 'France'
and flightdate between "21/03/2006" and "31/07/2010"
group by 1
J’avais au préalable réussi à convaincre les développeurs de fournir des indexes mono-colonne, de façon à éviter l’obtention de trop nombreux niveaux de B-Tree affaiblissant la performance. L'évocation du chiffre de 70 millions de rangées initiales, grossissant environ d'un million tous les 2 jours, est par contre de nature à susciter au minimum de l'intérêt, au maximum de l'inquiétude quant à l'atteinte des objectifs définis.
Nous ne parlerons pas aujourd’hui de fragmentation de cette table qui pourrait nous apporter une solution ultérieurement en cas de détection de problèmes d'IO sur celle-ci. Nous ne parlerons pas non plus de la compression des tables, technique provenant de XPS et implémentée à partir de la version 11.50.xC6 qui, tout en réduisant environ de moitié (ou plus) l'espace disque, permet d'augmenter de façon significative les performances sur les "grosses" tables. Cette fonctionnalité est pour l'instant « optionnelle » (lire payante), et ne fait donc pas partie des options à envisager dans notre cas.
Concentrons-nous donc sur la première partie du défi : le chargement de 70 millions de rangées en moins de 10 minutes. Nous avons pensé à High Performance Loader, qui est un outil très efficace, mais un peu complexe à mettre en oeuvre pour une première fois et dans le temps imparti.
Je voulais donc évoquer ici l'utilisation des tables externes, provenant également de XPS et implémentées depuis la version 10.50. Le principe est très simple, vous créez votre table, sauf qu'au lieu de résider dans l'instance IDS, celle-ci est constituée par un fichier sur file system, structuré soit par l'existence de délimiteurs de champs, soit par une structure fixe. Vous créez la table externe qui pointe sur ce(s) fichier(s) et qui en décrit la structure comme une table « normale ». Nous avons donc appliqué ceci pour le cas d'aujourd'hui :
CREATE EXTERNAL TABLE ext_tickets
SAMEAS tickets
USING (DATAFILES ("DISK:/home/eric/StagingArea/ObjectifPerf/tickets.unl"), DELIMITER "|",EXPRESS,MAXERRORS 10,REJECTFILE "/tmp/x_tickets.out")
Expliquons :
« SAMEAS tickets » veut dire créer la table à l'image du schéma de la table déjà existante tickets.
« DATAFILES DISK » : notre fichier est sur disque et se nomme...
« DELIMITER » : c'est une structure avec délimiteurs de champs, ici le « pipe »
« EXPRESS » : le mode de chargement. Il s'oppose au mode DELUXE. Ce mode évite l'utilisation des pages de buffers lors de la lecture dans cette table, ce qui va muscler fortement la performance de notre chargement.
« MAXERRORS » : au-delà de ce nombre d'erreurs, la création de la table s'arrête.
« REJECTFILE » : le fichier où sont consignées les erreurs de chargement.
Une table externe peut donc être manipulée par des tous types de requêtes SELECT ( order by, group by, prédicats) , mais ne peut pas être UPDATED,DELETED ni INSERTED. Résultat , avec table raw la commande de chargement est :
INSERT INTO tickets SELECT * FROM ext_tickets
70000000 row(s) inserted.
real 6m20.937s user 0m0.005s sys 0m0.005s
Nous avons comparé avec la méthode insert into table load from file, et le résultat est sans appel : nous avons arrêté le chargement au bout de 24 minutes : seulement 24 Millions de rangées étaient chargées.
Autre point important à relever : nous avons crée une table de type « RAW ». Ce type de table a la particularité de désactiver la journalisation de la table incriminée. En conséquence, la performance de chargement est drastiquement améliorée du fait de l'absence d'IO à cause de la journalisation (logical logs). La différence est plus que notoire. Le gros intérêt est qu'il est possible de changer en type STANDARD (journalisation) après le chargement par un simple ALTER TABLE, puis repasser en RAW pour les chargements consécutifs.
Nous évitons également l’écueil de la ‘long transaction’.
Nous avons donc atteint le premier objectif de passer sous les 10mn de temps de chargement, et restons sereins pour les chargements journaliers.
Passons maintenant au deuxième défi : la requête La table tickets a un nombre de rangées respectable ( 70 millions de rangées ), et à cette échelle il convient de veiller à optimiser les ressources ainsi que les accès.
Nous partirons de la baseline proposée par l'équipe des développeurs et prendre une mesure de performance.
Nous avons de fait renoncé à la baseline, comprenant les plaintes des développeurs : la requête a été abortée au bout de 60 mn sans qu’elle soit terminée, voici son plan de requête :
Estimated Cost: 3126460
Estimated # of Rows Returned: 775663
Temporary Files Required For: Group By
1) eric.airports: INDEX PATH
Filters: (eric.airports.code >= 'AAA' AND eric.airports.code <= 'ZZZ' )
(1) Index Name: eric.i_airp3
Index Keys: country (Serial, fragments: ALL)
Lower Index Filter: eric.airports.country = 'France' 2) eric.tickets:
INDEX PATH Filters: (eric.tickets.flightdate >= 21/03/2006 AND eric.tickets.flightdate <= 31/07/2010 )
(1) Index Name: eric.tickets7
Index Keys: destination (Serial, fragments: ALL)
Lower Index Filter: eric.tickets.destination = eric.airports.code
NESTED LOOP JOIN
Nous obtenons donc une nested loop (boucle imbriquée pour la jointure ), avec utilisation de l’index sur les pays pour la table airports ( 10000 rangées ), et l’index date du vol pour la table des tickets. Onstat - p nous donne 7509528 rangées lues (read).
C’est à ce moment que nous introduisons la nouvelle directive d’optimiseur MULTI_INDEX, qui va nous permettre d’utiliser plusieurs indexes sur une même table, contrairement à ce qui se faisait auparavant. La syntaxe :
SELECT --+multi_index(tickets tickets3 tickets7)
distinct(destination) , count(*) ....
from tickets,airports etc....
Nous allons dire à l’optimiseur d’utiliser les index tickets3 ET tickets7 pour la table tickets afin de lire un minimum de pages de données, et par conséquence réduire les IO sur cette requête. Voici le résultat du set explain :
DIRECTIVES FOLLOWED: MULTI_INDEX ( tickets tickets3 tickets7 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 113176408
Estimated # of Rows Returned: 775663
Temporary Files Required For: Group By
1) eric.tickets: MULTI INDEX PATH (SKIP SCAN)
(1) Index Name: eric.tickets7
Index Keys: destination (Serial, fragments: ALL)
Lower Index Filter: eric.tickets.destination >= 'AAA'
Upper Index Filter: eric.tickets.destination <= 'ZZZ' AND
(2) Index Name: eric.tickets3 Index Keys: flightdate (Serial, fragments: ALL)
Lower Index Filter: eric.tickets.flightdate >= 21/03/2006
Upper Index Filter: eric.tickets.flightdate <= 31/07/2010
2) eric.airports: INDEX PATH
Filters: Table Scan Filters: (eric.airports.code >= 'AAA' AND eric.airports.code <= 'ZZZ' )
(1) Index Name: eric.i_airp3
Index Keys: country (Serial, fragments: ALL)
Lower Index Filter: eric.airports.country = 'France'
DYNAMIC HASH JOIN
Dynamic Hash Filters: eric.tickets.destination = eric.airports.code
La physionomie du plan de requête a totalement changé : nous voyons l’activation de la directive multi-index sur la table tickets, qui a pour effet l’utilisation de l’index tickets7 + l’index tickets3 et finalisant le plan par un HASH JOIN pour la jointure.
Onstat -p nous indique que seulement 2.294.947 de rangées ont été lues, ce qui devrait signifier un progrès sensible. Et le temps de réponse ????
Le voici :
155 row(s) unloaded
real 5m22.672s user 0m0.005s sys 0m0.008s
Oui ! c’est bien 5 mn 22s. L’objectif d’amélioration du temps de réponse est atteint. N’ayant pas de données implémentées sur plusieurs disques, nous ne pourrons pas profiter du multiplexage des scan threads qui auraient pu améliorer encore notre performance. N’ayant pas lancé ce test sur la Ultimate Edition, nous ne pouvons pas bénéficier du PDQPRIORITY qui aurait sans doute amélioré légèrement les résultats.
Nous avons clairement profité de la directive MULTI INDEX, qui a diminué le temps d’attente de plus d’une heure (peut-être beaucoup plus ) à 5 mn 22s.
Il faut savoir également que cette technique est extrêmement efficace dans des requêtes avec prédicat « OR » portant sur des indexes différents d’une même table. Combien de fois nous a-t-il fallu réécrire une requête de ce type en incluant une UNION ? Avec cette technique, plus la peine de tout réécrire, il suffit d’ajouter la directive d’optimiseur qu’il faut, et le tour est joué !
Nous vous encourageons également à utiliser l’option de onmode ‘-Y sessionid 1 output file’, qui produira le fichier d’explain de la session ‘sessionid’ sans avoir à rajouter le ‘SET EXPLAIN ON’ dans le code de l’application. A utiliser toutefois avec modération en environnement production, car ce mode est très volubile et peut générer une charge d’IO qui pourra parasiter la performance.
Voilà, mission accomplie ! Merci aux techniques de tables externes en mode EXPRESS, de table de type RAW qui suppriment temporairement la journalisation, et la nouvelle directive d’optimiseur MULTI_INDEX. A bientôt sur notre station pour de nouveaux défis.
