Téléchager le rapport de recherhce - Cégep de Sainte-Foy

Aubin Jérôme
Gagnon Jean-Michel
Rapport de recherche
Performance d’Oracle et de MySQL
en fonction de leur
architecture fonctionnelle
Travail présenté à
Mme Sylvie Monjal
Nouvelles technologies
420-620-SF
10 février 2005
Département d’informatique
Cégep de Ste-Foy
Tables des matières
Introduction .............................................................................................................................. 1
Début de MySQL ...................................................................................................................... 3
Architecture de MySQL .......................................................................................................... 3
Les fichiers ............................................................................................................................. 3
Les processus .......................................................................................................................... 4
La mémoire ............................................................................................................................ 5
Les influences de l’architecture .............................................................................................. 5
L’exécution d’une requête ...................................................................................................... 5
Indexage : Pourquoi utiliser ce principe ................................................................................. 6
Comment MySQL utilise les index ........................................................................................ 6
Verrouillage .............................................................................................................................. 7
Le fonctionnement du verrouillage ........................................................................................ 7
Les inconvénients du verrouillage de MySQL ....................................................................... 8
Début d’Oracle ......................................................................................................................... 9
Architecture d’Oracle ............................................................................................................ 10
Les fichiers ........................................................................................................................... 10
La mémoire .......................................................................................................................... 11
Les processus ........................................................................................................................ 12
Les influences de l’architecture ............................................................................................ 13
Requêtes de consultation ...................................................................................................... 13
Requête de mise à jour ......................................................................................................... 13
Gestion de la mémoire SGA................................................................................................. 14
Verrouillage .......................................................................................................................... 15
Prototype ................................................................................................................................. 16
Description et but du prototype ............................................................................................ 16
Difficultés rencontrées ......................................................................................................... 16
Evaluation du prototype ....................................................................................................... 17
Résultat de différentes requêtes ........................................................................................ 17
Intégrité physique des données ........................................................................................ 18
Conclusion du prototype .................................................................................................. 19
Comparaison MySQL et Oracle ........................................................................................... 20
Annexe A : Glossaire .............................................................................................................. 23
Annexe B : Algorithme de verrouillage ................................................................................ 25
Algorithme de déverrouillage ............................................................................................... 25
Annexe C : Bibliographie ...................................................................................................... 26
Oracle ................................................................................................................................... 26
MySQL ................................................................................................................................. 26
Annexe D : Médiagraphie ...................................................................................................... 27
Rapport de recherche
Introduction
Les systèmes de gestion de bases de données sont utilisés partout. Nous les retrouvons dans
les instituts bancaires, les systèmes de réservations de salles, les systèmes hospitaliers, au
gouvernement et dans plusieurs autres institutions. Plusieurs transactions sont faites en même
temps dans plusieurs bases de données partout à travers le monde et une gestion très délicate
des données doit être mise en place pour qu’elles ne soient pas corrompues.
Puisque l'utilisation de systèmes de gestion de bases de données par plusieurs utilisateurs en
même temps est très fréquente et que c’est le principal objectif ceux-ci, nous nous sommes
tournés vers la performance des SGBDs en fonction de leur architecture. C’est à dire quelle
est l’architecture de traitement optimale des données. Bien entendu, nous allons parler du
traitement des données simples, mais aussi de la concurrence de deux utilisateurs sur une
même base de données. Les architectures évaluées seront MySQL et Oracle.
Les points importants de notre recherche seront d’expliquer la structure de traitement des
données de chacun des SGBDs. C’est-à-dire comment MySQL et Oracle gèrent les requêtes,
la mémoire et les fichiers. Cela influence directement la rapidité d'exécution de requête de
consultation et de mise à jour. Nous allons de même vous identifier les méthodes utilisées
pour conserver l’intégrité physique des données.
Nous aimerions, par cette recherche, identifier quel SGBD répond le mieux aux besoins des
programmeurs avancés qui recherchent toujours une performance plus accrue en terme de
technologie. Bien entendu, les SGBD n’arrête pas d'évoluer et la guerre entre MySQL et
Oracle est loin d’être finie. Pour l’instant, ces technologies peuvent être mises à l’épreuve sur
leur architecture et c’est ce que nous avons fait. Est-ce que MySQL, ce SGBD téléchargeable
gratuitement sur l'Internet, l’emportera sur le SGBD d’une grande firme très compétitive ?
Est-ce que le petit David l'emportera sur l'impressionnant Goliath ? C'est ce que nous allons
tenter de découvrir dans cette recherche.
Nous n’évaluerons pas les deux SGBDs sur les coûts d’acquisition, les coûts d’entretien et la
simplicité d’utilisation. La performance en fonction d’une plate forme d’utilisation ne sera pas
non plus évaluée (exemple : PHP pour MySQL). Pour MySQL nous n’évaluerons que la
structure de table MyISAM. Pour Oracle, la liaison entre la structure logique et la structure
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 1
Rapport de recherche
physique est très complexe. Nous n'en avons pas tenu compte puisque ce n'était pas nécessaire
pour notre recherche.
Pour effectuer notre recherche, nous avons établit la problématique. Cette problématique est
la difficulté de choisir un système de gestion de base de données qui répond le plus à nos
besoins en terme de performance. Nos objectifs étaient de trouver les points, pour chacun des
SGBDs, qui influencent leur performance. Nous avons donc comparé MySQL à Oracle sur
leur architecture fonctionnelle. Nous voulions réaliser un prototype permettant de vérifier la
performance de ces deux SGBDs. Nous verrons dans la section du prototype pourquoi il n’a
pas été possible de le réaliser comme nous le voulions. Nous avons, par la suite, analysé les
sources d’informations obtenues pour en sortir des conclusions face à la performance. La
partie de recherche sur MySQL se base essentiellement sur les informations fournies sur le
site officiel de MySQL. Celle d’Oracle se base principalement sur une présentation de
maîtrise de Michèle Raphalen.
Nous allons donner des informations techniques sur chacune des deux SGBDs. Nous
parlerons de leur historique, décrirons leur architecture générale et expliquerons comment ils
font pour gérer les requêtes et assurer l’intégrité des données. Ensuite, nous allons faire le
comparatif des deux SGBDs par rapport à leur architecture et aux performances liées à celleci. Nous montrerons les résultats de tests effectués sur un prototype testant différentes
fonctionnalités de MySQL.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 2
Rapport de recherche
Début de MySQL
MySQL AB, une société fondée en Suède par deux suédois et un
finlandais, David Axmark,
Allan Larsson et Michael "Monty"
Widenius, est la société fondatrice de MySQL. Cette société, qui a
été fondée en 1980, est une organisation virtuelle dont les employés
sont répartis à travers le globe.
Au tout début, un groupe d’utilisateurs
voulait se connecter à leurs tables en utilisant
mSQL. Étant un peu déçu de la flexibilité et
de la rapidité de ce programme, ils ont donc
décidé de créer leur propre interface SQL
avec
leurs
propres
algorithmes
pour
l’exploitation de leur base de données tout en gardant les mêmes APIs de mSQL. Depuis, le
succès de MySQL n'a cessé de grandir. Aujourd’hui, c’est un système de gestion de base de
données très souvent utilisé par les grandes entreprises. De plus, il est très souvent lié avec le
PHP ce qui permet de générer des pages Web dynamiques.
Architecture de MySQL
Les fichiers
La structure de fichiers de MySQL est constituée d'un fichier pour les données des tables de la
base de données et d'un autre fichier pour les index. Cette structure de fichiers est assez
simpliste. C’est peut-être un avantage pour des tables de petite taille, mais sûrement un
désavantage pour celles de grande taille.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 3
Rapport de recherche
Les processus
Voici le schéma de la structure de MySQL :
Le gestionnaire de requêtes sert au traitement de requête SQL en général. Il le traite,
l’optimise et prépare un plan d’exécution pour celle-ci.
Le gestionnaire de stockage permet à MySQL de gérer les fichiers sur le disque dur du
serveur. Il communique avec le système d’exploitation pour l’écriture et la lecture d’un
fichier.
La gestion de la mémoire permet de mettre en mémoire des informations sur la base de
données pour un accès beaucoup plus rapide aux données. Nous verrons dans la section
suivante comment la mémoire est gérée.
Le contrôle des transactions facilite les transactions et la concurrence des tables. C’est cette
partie qui gère l’intégrité des données physiques. Nous verrons dans la section
« Verrouillage » comment MySQL procède pour faire un verrou.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 4
Rapport de recherche
Le gestionnaire de fichier de reprise sert à ne pas perdre de données lorsque le serveur de base
de données tombe en panne. Il prévient la perte de données en gardant en copie toutes les
modifications effectuées sur celles-ci avant la panne.
La mémoire
Premièrement, MyISAM est le moteur de base de données qu’utilise MySQL. MyISAM
utilise un système de mémoire tampon pour garder les blocs de table les plus souvent utilisés.
La mémoire tampon est utilisée seulement pour les blocs d’index de MyISAM. La structure
est divisée en plusieurs sections de mémoire qui contient les blocs d’index les plus souvent
utilisés. Les blocs de données les plus souvent sollicités sont, quant à eux, stockés sur la
mémoire virtuelle du disque dur. La grosseur de l’espace mémoire tampon est modifiable
avec la variable système « key_buffer_size ». Si la variable système est à zéro, MySQL
n’utilise pas de mémoire tampon pour stocker les blocs d’index. Il utilise plutôt la mémoire
virtuelle du disque dur comme pour les blocs de données.
Les influences de l’architecture
L’exécution d’une requête
Un principe simple dans l’exécution de requêtes de MySQL est que si votre système de droits
des utilisateurs de la base est compliqué, plus votre performance en sera réduite. S’il n’y a
aucun droit utilisateur sur votre base de données, elle sera beaucoup plus efficace, car MySQL
n’aura pas à parcourir la liste des droits. Si la base de données est volumineuse, il est donc
préférable d’optimiser et de rendre moins complexe votre système de droits pour augmenter la
vitesse d’exécution des requêtes. Les droits d’accès sur les bases de données, les tables et les
colonnes sont enregistrés sur la table « tables_priv » et « columns_priv » de la base de
données de MySQL. À chaque requête de lecture, le système de base de données va vérifier
sur ces tables si l’utilisateur en cours peut accéder à la ressource requise. Donc, MySQL doit
aller consulter une autre table du serveur, c’est-à-dire une autre lecture dans un fichier ce qui
implique un ralentissement de la lecture.
Ce principe est le même pour les limites de ressource. Les limites de ressource sont le nombre
maximum de requêtes par heure, le nombre maximum de mises à jour par heure et le nombre
maximum de connexion par heure pour un utilisateur. Si l’utilisateur a ce genre de limite, le
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 5
Rapport de recherche
système de base de données doit aller mettre à jour ces données à chacune des requêtes que
l’utilisateur fait. Les requêtes de lecture sont aussi comptabilisées.
Indexage : Pourquoi utiliser ce principe
MySQL utilise des index afin de réduire le temps de recherche. Voici quelques applications
concrètes des index :

Les index permettent de trouver plus rapidement les enregistrements qui répondent à
une clause « Where ».

Les index permettent également d’enlever les lignes inutiles à la recherche. Si MySQL
à un choix à faire entre plusieurs index, il prend celui qui contient le moins
d’enregistrements.

Lire les lignes dans les autres tables lors des jointures.

Les valeurs d’une requête peuvent être lues directement sans consulter les données
d’une table si la requête porte sur d’autres index.

Permet de trouver le minimum et le maximum d’une colonne, si elle est indexée, avec
la fonction « MIN() » et « MAX() ». Par exemple:
SELECT MIN(noClient),MAX(noClient) FROM Client where noArticle=10
Si les colonnes noClient et noArticle sont indexées, la recherche se fera beaucoup plus
facilement.
Comment MySQL utilise les index
Les index de MySQL sont les clauses PRIMARY, UNIQUE et INDEX. Le principe des index
est de réduire le temps de recherche des résultats d’une requête de lecture. S’il n’y avait pas
d’index, MySQL devrait lire tous les enregistrements d’une façon successive, c’est-à-dire un
après l’autre, pour pouvoir en sortir les lignes qui répondent à la requête. Alors, plus la table
est grosse, plus la recherche est longue pour MySQL. L’utilité des index étant de réduire le
temps de recherche, il permet à MySQL de ne pas chercher partout dans la table où s’effectue
la requête. Si une table contient 1000 enregistrements, l’opération avec les index pourra être
100 fois plus efficace.
Les index sont stockés sous la forme de B-Trees. La structure B-Trees est utilisée depuis
longtemps dans les systèmes d'indexation des bases de données.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 6
Rapport de recherche
Verrouillage
Le fonctionnement du verrouillage
MySQL, avec l’architecture de table MyISAM, ne permet pas le verrouillage
d’enregistrement simple. Il verrouille plutôt toute la table entière. MyISAM utilise ce type de
verrouillage, car il est conçu pour répondre aux besoins les plus en demande d’une table de
données. Par exemple, les applications courantes font de nombreuses sélections, des ajouts sur
des tables spécifiques, des modifications en fonction des clés et peu de suppression
d’enregistrement.
Le schéma ci-dessous démontre comment MySQL utilise la queue de verrouillage de table :
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 7
Rapport de recherche
Celui-ci illustre le verrouillage d’une table lorsque celle-ci n’est pas verrouillée :
Pour une requête en écriture, si la table est verrouillée, MySQL soumet une demande de
verrouillage dans la queue de verrous d’écriture, sinon, il pose un verrou d’écriture sur la
table. Le principe n’est pas tout à fait le même pour une requête de lecture. Si la table est
verrouillée, MySQL soumet une demande dans la queue de verrous de lecture, sinon, il pose
un verrou d’écriture sur la table. Le verrou d’écriture sert à ne pas permettre aux autres
utilisateurs d’effectuer des requêtes d’écriture en même temps que celle de lecture. MySQL
donne toujours le verrou aux requêtes d’écriture avant celles de lecture. Alors, s’il y a
beaucoup de requêtes dans la queue des requêtes d’écriture, celle de lecture peut attendre un
certain moment.
Les inconvénients du verrouillage de MySQL
Le verrouillage de table comporte plusieurs inconvénients. Par exemple, si plusieurs
personnes sont en train d’effectuer plusieurs lectures sur une table en particulier, ce qui ne
verrouille pas la table, et qu’une requête d’écriture est lancée durant ce temps, celle-ci se
devra d’obtenir un accès exclusif à la table. Lorsque cette requête aura son accès exclusif, les
autres utilisateurs qui exécutent des requêtes de lecture devront attendre la fin des
modifications. De plus, si un autre utilisateur vient pour écrire sur la table, il se verra priorisé,
car MySQL priorise les requêtes en écriture. Toutes les commandes d’écriture se feront avant
celles de lecture ce qui implique un gros désavantage pour MySQL.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 8
Rapport de recherche
Début d’Oracle
Au début des années 70, les bases de données font tranquillement
leurs apparitions. Un chercheur du nom de Codd établit une base
mathématique et théorique solide pour le concept des bases de
données relationnelles. Ces concepts deviendront par la suite
essentiels pour le développement de SGBDs relationnels. Bien
sûr, les concepts de Codd continueront à évoluer avec le temps,
mais ils resteront quand même la base fondamentale.
Par la suite, en se fondant sur ces concepts, la société Relational Software Incorporated
développera le premier SGBD relationnel commercial qui sera appelé Oracle. Par la suite,
cette société changera de nom pour le nom de son propre produit : Oracle. Le premier
prototype d’Oracle sera développé complètement en assembleur. C’est en 1988 que sort
Oracle 6. Cette version d’Oracle est beaucoup plus complète que les versions précédentes.
Elle est plus performante, contient plus de fonctionnalités et peut être utilisée sur un plus
grand nombre de plates-formes. De plus, depuis Oracle 5, Oracle est basé sur une architecture
Client/Serveur.
Pendant les années 90, deux nouvelles
versions d’Oracle s’ajouteront. En 1992,
Oracle
7
est
développé
pour
Unix
principalement. Cette version possède une
architecture
différente
précédentes
ce
qui
des
fait
versions
en
sorte,
principalement, que la mémoire est gérée
différemment. Finalement, en 1996, Oracle 8
fait son apparition. Cette version d’Oracle est une version orientée objet. Au tout début, la
version 8.0 n’est qu’un beta, mais par la suite elle deviendra une version plus complète.
Aujourd’hui, Oracle est l’un des leaders principaux sur le marché des SGBDs. À lui seul,
Oracle possède 40% du marché.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 9
Rapport de recherche
Architecture d’Oracle
L’architecture d’une base de données Oracle contient trois niveaux qui sont les suivants : les
fichiers, la mémoire et le processus. Pour bien comprendre les étapes d’exécution des requêtes
de consultation et celles de mise à jour, il faut auparavant bien comprendre les différents
composants des trois niveaux de l’architecture et les fonctions de chacun.
Les fichiers
Il y a trois types principaux de fichier dans une base de données Oracle.
Il y a les fichiers de données qui contiennent toutes les informations de la base de données.
C’est dans ces fichiers que sont stockés les informations contenues dans les tables, les index
et les caractéristiques de la base de données. Ces informations forment en fait le dictionnaire
de la base de données. L’ensemble de ces fichiers constituent la structure logique de la base
de données Oracle.
Il y a les fichiers de journalisation qui permettent de conserver toutes les opérations de
modification qui ont été effectuées sur la base de données, mais qui n’ont pas encore été
enregistrées. Ils permettent donc en cas de panne matérielle ou logicielle de ne pas perdre les
modifications sur les données, car il y aura lecture dans les fichiers de journalisation ce qui
permettra de récupérer les modifications et données perdues. Une base de données Oracle
contient en général de 1 à 2 fichiers de journalisation.
Finalement, il y a les fichiers de contrôle qui sont en fait à l’opposé des fichiers de données.
Ceux-ci contiennent la description physique de la base de données. Cette description est en
fait le nom de la base de données, les chemins d’accès aux fichiers de données et de
journalisation.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 10
Rapport de recherche
La mémoire
La mémoire d’une base de données Oracle est subdivisée en deux mémoires principales. Il y a
la mémoire PGA(Program Global Area) et la mémoire SGA(System Global Area).
La mémoire PGA contient principalement les variables d’environnement de la session en
cours et est réservée aux processus utilisateurs.
La mémoire SGA(mémoire cache) est elle-même subdivisée en trois zones qui sont les
suivantes : le buffer cache de données, le buffer redo-log et la shared pool.
La première zone, le buffer cache de données, contient toutes les informations sur les données
en provenance de la base ou à destination de celle-ci. Cela veut dire que lorsque l’utilisateur
interroge la base pour avoir accès à des données ou que celui-ci désire insérer des données
dans la base, celles-ci seront conservées dans le buffer cache de données.
La deuxième zone, le buffer redo-log, permet de conserver un suivi des transactions
effectuées sur les données. Cette zone est étroitement liée avec les fichiers de journalisation,
car elle conserve toutes les modifications effectuées. Comment fait-elle pour reconnaître les
différentes modifications ? Elle mémorise le bloc de données modifié, la localisation des
données modifiées dans le bloc et les nouvelles valeurs attribuées aux données.
La troisième zone, la shared pool, contient les différentes informations sur les commandes
SQL. Elles contiennent aussi les informations du dictionnaire, donc le nom des tables et de
leurs attributs.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 11
Rapport de recherche
Les processus
La mémoire et les fichiers d’une base de données Oracle sont gérés par plusieurs processus. Il
serait inutile de les expliquer tous, puisqu’il y en a beaucoup trop et qu’il n’est pas nécessaire
de tous les comprendre pour comprendre les étapes d’exécution des requêtes de consultation
et de mise à jour. Il peut être par contre utile de connaître les deux processus principaux : le
processus utilisateur et le processus serveur. De plus, il est important de savoir que les
processus sont tous gérés par le processeur du serveur contenant la base de données Oracle.
Cela est une tâche très lourde pour le processeur puisque souvent pour une seule requête
plusieurs processus entrent en jeu. Par contre, Oracle a été conçu de façon à répartir plusieurs
tâches sur plusieurs processeurs. Ainsi donc, Oracle s’avère souvent plus rapide sur des
systèmes multiprocesseurs.
Architecture réduite d’une base de données Oracle avec ses trois niveaux : fichiers, mémoire et processus
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 12
Rapport de recherche
Les influences de l’architecture
Requêtes de consultation
Lorsqu’un utilisateur exécute une requête de consultation (Select), le processus utilisateur
prend en charge la requête. L’exécution d’une requête sur la machine de l’utilisateur se fait
toujours à l’aide d’une application, par exemple SQL Plus ou Developer/Forms 2000. Une
fois exécutée sur la machine de l'utilisateur la requête est envoyée au processus serveur pour
être analysée.
Le processus serveur vérifie si la construction de la requête est valide. Pour cela, il consulte la
zone shared pool de la mémoire SGA. Il sera donc possible de compiler la requête envoyée
par le processus utilisateur. Un message de succès ou d’erreur est envoyé par le processus
serveur au processus utilisateur pour indiquer si la syntaxe de la requête est valide.
Si la requête a été compilée correctement, les données recherchées seront envoyées au
processus serveur. Ces données proviennent des fichiers de données. Par la suite le processus
serveur renvoie les données au processus utilisateur.
De plus, le processus serveur envoie les données trouvées dans les fichiers de données à la
zone buffer cache de données. Ainsi donc, si une autre requête demande les mêmes données,
le processus serveur, au lieu d’aller les chercher dans les fichiers de données, ira les chercher
dans la zone buffer cache. Cela fera en sorte que le processus sera beaucoup plus rapide et
cela diminuera la tâche au processeur.
Requête de mise à jour
Lorsqu’un utilisateur exécute une requête de mise à jour (Update, Insert ou Delete), le
processus utilisateur prend en charge cette requête. Comme dans le cas d’une requête de
lecture, la requête, une fois exécutée sur la machine utilisateur, est envoyée au processus
serveur. Elle sera analysée, compilée et un message de succès ou d’erreur sera envoyé au
processus utilisateur.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 13
Rapport de recherche
Une fois que la requête a été compilée avec succès, le processus serveur vérifie si la zone
buffer cache de la mémoire SGA contient les données à modifier. Si elles ne sont pas
présentent dans le buffer cache, le processus serveur les transferts des fichiers de données au
buffer cache sinon il les prend directement dans le buffer cache. Il y a donc toujours une
image des données dans les fichiers de données. Ces images de données jouent un rôle
essentiel dans le fonctionnement d’Oracle. Leurs utilités seront expliquées dans la prochaine
section.
Par la suite, il met un verrou sur le bloc de données à modifier, ce qui empêche d’autres
utilisateurs de faire des modifications en simultanées sur les mêmes données. Après avoir
verrouillées les données, le processus serveur enregistre les modifications à apporter aux
données dans le buffer redo-log. Cela permet de garder une trace des opérations de
modification en cas de panne.
Finalement, le processus serveur modifie les données dans le buffer cache et active un flag
qui permet de savoir que les données en question ont été modifiées correctement.
Gestion de la mémoire SGA
Le processus DBWR est le processus qui s’occupe de gérer le buffer cache de données et de
transférer les données contenues dans celui-ci vers les fichiers de données. Ce processus
regarde constamment si le buffer cache n’est pas saturé de données. Il active le transfert des
données du buffer cache vers les fichiers de données dans deux cas principalement : lorsque le
buffer cache est saturé ou que le processus LGWR s’active. Ce processus sera expliqué plus
loin dans cette même section.
Il est donc important de comprendre que tant que les données ne sont pas transférées vers les
fichiers de données, une image des anciennes données est conservée dans ceux-ci. Il est ainsi
possible de les récupérer en effectuant l’instruction rollback qui permet d’annuler les
dernières transactions effectuées et non enregistrées.
Le processus LGWR gère quant à lui le buffer redo-log. Celui-ci contient, comme nous
l’avons dit précédemment, toutes les modifications qui ont été apportées aux données ce qui
permet de conserver un historique des transactions. Le processus LGWR s’occupe de
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 14
Rapport de recherche
transférer ces informations vers les fichiers de journalisation dans trois cas en particulier :
lorsqu’il y a exécution d’un commit ou d’un rollback, lors de l’activation du processus
DBWR ou encore lorsque le buffer redo-log est saturé.
Verrouillage
Le verrouillage des données se fait lorsqu’un utilisateur effectue une transaction, en d’autres
mots lorsqu’il y a exécution d'une requête de mise à jour (Update, Insert ou Delete) sur un
groupe de données. La lecture (Select) de données n’implique pas de verrous. Oracle permet à
l’administrateur d’une base de données de pouvoir poser des verrous. Il y a deux types de
verrou, les verrous exclusifs et les verrous partagés.
Un verrou exclusif verrouille de façon exclusive une ressource lors d’une demande de
transaction. Il ne pourra donc pas y avoir une autre transaction qui utilise les mêmes
ressources tant que la transaction en cours ne sera pas terminée. Par exemple, un utilisateur
effectue une requête de mise à jour sur la table « Facture », avec le verrou exclusif, aucun
autre utilisateur ne pourra effectuer de transactions de mise à jour sur les données de la table
« Facture », tant que la première transaction ne sera pas terminée.
Un verrou partagé permet de partager une ressource selon les types de transaction qui sont
effectuées sur celle-ci. Il sera alors possible de partager une même ressource, mais il ne sera
pas possible de faire des mises à jour sur les mêmes données. Par exemple, un utilisateur
effectue une transaction sur un groupe de données de la table « Facture », un autre utilisateur
pourra alors effectuer des transactions sur la même table, tant que celles-ci ne sont pas
effectuées sur les mêmes données.
Par défaut, Oracle pose un verrou sur le groupe de données sur lequel est effectuée une
transaction. Lors d’une transaction sur un groupe de données, un flag s’active indiquant
qu’une transaction est en train de s’effectuer sur ce groupe de données. Toutes autres
transactions voulant effectuer une transaction sur un groupe de données avec un flag actif
sont placées dans une file d’attente. Une fois la transaction en cours sur un groupe de
données terminée, le flag est désactivé et la première transaction dans la file d’attente
s’exécute. Des algorithmes simples pour le verrouillage et le déverrouillage sont présentés en
annexe B.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 15
Rapport de recherche
Prototype
Description et but du prototype
Le prototype devait être conçu afin d’évaluer la performance des SGBDs. Nous voulions
exécuter différentes requêtes pour pouvoir déterminer les facteurs qui influencent la vitesse à
laquelle les SGBDs retournent une réponse à une requête. Nous voulions aussi déterminer si
les SGBDs effectuaient une gestion adéquate de l’intégrité physique des données lors de
l’exécution de requêtes de mise à jour simultanées. De plus, nous pensions réaliser un autre
prototype qui consistait à développer notre propre système de gestion de base de données en
gérant un système d’écriture et de lecture. Nous voulions développer un système de protection
contre les interblocages (Deadlock), car il protège l’intégrité des données. Cela nous aurait
été utile pour analyser plus en détail comment un SGBD fonctionne. Nous n’avons pu réaliser
ce prototype à cause du manque de temps. Vous verrez dans la prochaine section les causes
qui ont faites en sorte que nous avons manqué de temps.
Difficultés rencontrées
Nous avons éprouvé plusieurs difficultés lors de la programmation du prototype.
Premièrement, nous n’avons pas été capable de nous connecter à une base de données Oracle
en C++. La seul manière de pouvoir se connecter relativement facilement à ces bases de
données serait de le faire en C++ manager. Cela nous a fait perdre beaucoup de temps de
recherche inutilement, car nous n’avions pas à évaluer la connexion aux bases de données
dans notre recherche. Nous avons donc décidé, vers la fin de la réalisation du prototype, de
réaliser un petit prototype sur « Form Builder », un logiciel permettant de réaliser des
interfaces pour une base de données Oracle. Finalement, nous n’avons pas été capable de
déterminer en millisecondes le temps d’exécution des requêtes. Nous ne réussissions qu’à
déterminer en seconde le temps d’exécution de requête ce qui n’était pas assez précis pour
répondre à nos objectifs.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 16
Rapport de recherche
Evaluation du prototype
Résultat de différentes requêtes
Voici les résultats obtenus lors des requêtes de lecture. Bien sûr, il n’y a pas de comparaison
avec Oracle.
Pour la requête de lecture exécutée plusieurs fois, le requête est : select * from client. Cette
requête est effectuée 10000 fois sur la base de données. La table client contient quatre
colonnes. Nous voyons, avec les résultats ci-dessous, que le temps d’exécution pour MySQL
est d’environ 28 secondes.
Pour la requête de gros volume, la requête est celle-ci :select * from facture. La table facture
contient six colonnes, c'est-à-dire deux de plus que la table client. La requête retourne 10000
enregistrements et nous voyons que le temps d’exécution de la requête est beaucoup moins
long que la précédente, c’est-à-dire près de 40 fois plus vite. Ceci nous confirme le fait qu’à
chaque lecture que MySQL fait, la gestion des droits d’accès et de la mémoire interne se fait
quand même assez lentement. Bien entendu, il y a le temps d’exécution de la boucle à tenir en
compte, mais les résultats comportent une grande différence.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 17
Rapport de recherche
Les autres requêtes sur MySQL n’ont pas vraiment de pertinence dans la recherche, car elles
n’ont pu être comparées avec Oracle.
Intégrité physique des données
Pour tester l’intégrité physique des données, le prototype doit être ouvert deux fois. Une des
instances de prototype prend le rôle du client un et la deuxième prend le rôle du client deux.
La requête d’écriture du client un est exécutée en premier sur la base de données. Puis, le
deuxième client exécute sa requête d’écriture pendant que celle du client un est en train de
s’exécuter
Voici les données initiales de la base de données MySQL :
noArticle
nomArticle
prixArti
cle
rabaisA
rticle
2005-01-01
30
10
Il y a deux
semelles
2005-01-01
30
10
Il y a deux
semelles
2005-01-01
30
10
…
…
…
…
Il y a deux
semelles
2005-01-01
30
10
descriptionArticle
dateAcquisitionArticle
1 Chaussure
Il y a deux
semelles
2 Chaussure
3 Chaussure
… …
10000 Chaussure
La requête du client un est : Update article set nomArticle = 'Espadrilles', descriptionArticle =
'Il sont de marques Nike', prixArticle = 100
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 18
Rapport de recherche
Celle du client deux est : Update article set nomArticle = 'Souliers', descriptionArticle = 'Ils
sont très beau', prixArticle = 50
Une fois le prototype lancé correctement, voici le résultat dans la base de données :
noArticle nomArticle descriptionArticle dateAcquisitionArticle prixArticle
rabais
Article
1 Souliers
Ils sont très
beaux
2005-01-01
50
10
2 Souliers
Ils sont très
beaux
2005-01-01
50
10
3 Souliers
Ils sont très
beaux
2005-01-01
50
10
…
…
…
…
Ils sont très
beaux
2005-01-01
50
10
… …
10000 Souliers
Le client deux semble partir lorsque le client un a fini complètement son écriture sur la table.
Ceci nous confirme que MySQL met un verrou sur la table en entier lorsqu’il exécute une
requête d’écriture.
Conclusion du prototype
Bien sûr, nous aurions aimé évaluer MySQL par rapport à Oracle, car c’est en fait le sujet de
notre recherche. Nous avons quand même été capable d’en sortir des résultats significatifs.
Notre prototype démontre bien le temps que MySQL prend pour allez vérifier, à chaque
requête de lecture, les droits d’accès aux tables. Notre prototype montre que MySQL fait un
verrou sur la table en entier lorsqu’il écrit des enregistrements dans cette même table.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 19
Rapport de recherche
Comparaison MySQL et Oracle
Nous avons vu précédemment l’architecture des bases de données Oracle et MySQL. Il est
donc maintenant possible de juger et de comparer plus adéquatement le niveau de
performance de ces deux SGBDs.
L’architecture
de
MySQL
permet
une
consultation rapide des données tant que le
nombre d’utilisateur utilisant la base de
données est réduit. À chaque requête de
lecture, le système de base de données doit
vérifier les droits des utilisateurs qui sont
conservés dans une table. Cela fait en sorte
que plus le nombre d’utilisateur est élevé
plus le temps d’attente pour la consultation est élevé. Par contre, si le nombre d’utilisateur est
réduit et que le principe d’indexage des données est appliqué de façon adéquate MySQL
s’avère une base de données très rapide pour la consultation de données.
Pour Oracle, la consultation de données est une procédure longue puisqu’elle implique
plusieurs étapes et engendre l’exécution de plusieurs processus ce qui demande beaucoup de
ressources au processeur du serveur. Cette procédure est grandement diminuée si les données
à consulter se trouvent déjà dans le buffer cache. Il faut se rappeler que des données se
retrouvent dans le buffer cache après avoir été consultées ou mises à jour. Cette gestion
efficace de la mémoire fait en sorte que plus il y a d’utilisateur plus le temps de consultation
peut-être diminué, car souvent la mémoire contient déjà les données à traiter. La façon dont
Oracle gère sa mémoire est en fait son point fort. Comparativement à MySQL, Oracle
n’utilise jamais la mémoire virtuelle du disque dur.
Maintenant, la gestion des données et des tables lors de requêtes de mise à jour est
complètement différente. MySQL effectue des verrouillages sur des tables entières tandis que
Oracle permet de verrouiller que les données concernées par une requête de mise à jour. Cela
peut donc ralentir l’exécution des requêtes d’écriture sur MySQL et avantager dans ce cas
Oracle.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 20
Rapport de recherche
De plus, MySQL empêche la lecture sur une table verrouillée, ainsi donc il est impossible de
visualiser toutes données concernant la table verrouillée. Oracle ne procède pas ainsi, il est
possible de visualiser un bloc de données verrouillé. Cela est un avantage et un inconvénient
pour les deux SGBDs. Pour Oracle, lorsqu’un utilisateur visualise des données qui sont en
cours de modification, celui-ci ne voit pas les données modifiées, mais il voit les données
avant les modifications cela ne respecte donc pas l’intégrité réelle des données. Pour MySQL,
puisque c’est la table au complet qui est verrouillée, il n’est pas possible de visualiser les
données qui ne sont pas en cours de modification et cela augmente le temps d'attente pour la
consultation.
Comparaison de Oracle et de MySql
Rapidité petites Bases
100%
Oracle
MySql
80%
Standard
60%
Rapidité grandes Bases
40%
20%
0%
Facilité de mise en œuvre
Fonctionnalitées
Sécurité des données
Tableau comparatif conçu par Alexandre Fachhin
Ce tableau compare MySQL et Oracle sur différents points. Il est facilement remarquable que
Oracle est plus rapide sur de grandes bases et MySQL beaucoup plus rapide sur de petites
bases. On entend par grande base, une base qui gère une importante quantité de données
(plusieurs millions d’enregistrement) et qui gère plusieurs utilisateurs(des centaines et même
des milliers). Ainsi donc, MySQL est plus efficace sur de petites bases et avec peu
d’utilisateur contrairement à Oracle qui est plus rapide sur de grande base. L’architecture et la
façon dont chacun traire et gère les données montre clairement que ces deux SGBDs n’ont pas
été développés dans le même but. Est-ce que David l’a emporté sur Goliath ? Il serait injuste
de favoriser l’un ou l’autre, il est seulement de savoir que ces deux SGBDs ne répondent pas
aux mêmes besoins.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 21
Rapport de recherche
En terminant, à l’aide des connaissances acquises, nous aurions grandement aimé développer
un système de gestion de base de données par nos propres moyens. Un système séparé en
plusieurs modules généralement utilisés dans un SGBD. Nous aurions pu essayer de
développer et d’optimiser les processus permettant la gestion des requêtes, la gestion de la
mémoire, la gestion des droits d’accès aux tables et aux enregistrements, la gestion des
fichiers de reprise et le stockage des données. Cette recherche prendrait beaucoup plus
d’heures, mais elle serait très constructive et permettrait de mieux comprendre toutes les
interactions qu'effectue un SGBD avec les autres composantes d'un ordinateur.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 22
Rapport de recherche
Annexe A : Glossaire
B-Tree :
Méthode de structuration pour le stockage et l'accès aux données. Elle consiste à diviser les
possibilités de choix en deux parts égales et établir des pointeurs vers les deux sous-blocs. De
cette façon, pour rechercher un élément, on le compare d'abord avec l'élément de la tête de
l'arbre. Si l'élément recherché est celui-là, la recherche est terminée, si non soit elle est plus
grande, soit plus petite ; dans l'un ou l'autre cas on continue la recherche dans le sous-bloc
correspondant en utilisant le pointeur approprié.
Commit :
Sous environnement Oracle, cette instruction permet d’enregistrer les transactions qui ont été
effectuées sur les données d’une base. Aussi longtemps que cette instruction n’est pas
exécutée les modifications apportées aux données ne sont pas enregistré de façon permanente.
Flag :
Variable qui permet de connaître l’état d’un élément (exemple : une table). Cette variable
peut, par exemple, prendre la valeur 0 lorsqu’une table est verrouillée et
la valeur 1 lorsque celle-ci est disponible.
Interblocage :
Un ensemble de processus est en interblocage si et seulement si tout processus de l'ensemble
est en attente d'un évènement qui ne peut être réalisé que par un autre processus de
l'ensemble.
Jointures :
Association entre champs de même type se trouvant dans des tables différentes.
Mémoire cache :
Cette mémoire sert de tampon pour le processeur. Le processeur y stocke les informations
qu’il utilise le plu souvent, ce qui permet de réduire le temps d’attente lors d’exécution de
processus.
MyISAM :
Moteur de base de données a accès séquentiel indexé.
Transaction :
Une transaction est une séquence de mise à jour effectuée sur les données d’une base.
L’exécution de requêtes de mise à jour (Update, Insert ou Delete) sont en fait des
transactions.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 23
Rapport de recherche
Rollback :
Sous environnement Oracle, cette instruction permet d’annuler les transactions qui ont été
effectuées sur les données d’une base. Aussi longtemps que l’instruction Commit n’est pas
exécutée, il est possible d’annuler les transactions effectuées.
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 24
Rapport de recherche
Annexe B : Algorithme de verrouillage
g : groupe de données
tr : transaction
Début
Si g n’est pas verrouillé Alors
Mettre un verrou sur g ;
Sinon
Mettre tr dans la file d’attente correspondant à g ;
Suspendre la transaction en attente ;
Finsi
Fin
Algorithme de déverrouillage
g : groupe de données
//Cet algorithme est appliqué à la fin d’une transaction
Début
Supprimer le verrou sur g ;
Pour chaque transaction dans la file d’attente associée à g faire
Si g n’est pas verrouillé alors
Enlever la transaction de la file d’attente ;
Exécuter la transaction qui vient d’être retirée de la file ;
Finsi
FinPour
Fin
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 25
Rapport de recherche
Annexe C : Bibliographie
Oracle
http://www.informatique.weka.fr/affichage/dispMain.asp?ngcmid=WK181020201
http://www-valoria.univ-ubs.fr/Michele.Raphalen/cours/bd2.pdf
MySQL
http://www.lephpfacile.com/manual_mysql/manual.fr_MySQL_Optimisation.php
http://images.google.fr/imgres?imgurl=http://www.swen.uwaterloo.ca/~rekram/files/imag
es/arch_assgn1_level1.gif&imgrefurl=http://www.swen.uwaterloo.ca/~rekram/files/cs798
_assignment_1.htm&h=377&w=509&sz=8&tbnid=RgJciuGK4Q8J:&tbnh=94&tbnw=127
&start=3&prev=/images%3Fq%3Darchitecture%2Bde%2BMySQL%26hl%3Dfr%26lr%3D
lang_fr%26sa%3DN
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 26
Rapport de recherche
Annexe D : Médiagraphie
http://www.moskalyuk.com/download/benchmarking_loaders.doc
http://sheikyerbouti.developpez.com/optimisation/optimisation.pdf
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/oreview/cursors.html
http://www.eweek.com/article2/0,4149,293,00.asp
http://fadace.developpez.com/sgbdcmp/
http://database.in2p3.fr/NewComparatif.html
Performance d’Oracle et de MySQL en fonction de leur architecture fonctionnelle
Page 27