Oracle, MySQL

NFE113 Administration et configuration des bases de données - 2010
Techniques de stockage
Oracle / MySQL
1
Eric Boniface
Oracle
Le système de représentation physique assez riche
Repose sur une terminologie qui porte à confusion
Les termes de « représentation physique » et
« représentation logique » pas employés dans le sens
vu jusqu’à présent
Pour des raisons de clarté, utilisation quand
nécessaire de la terminologie d’Oracle pour rester
cohérent avec celle déjà vue
Un système Oracle = instance, stocke les données
dans un ou plusieurs fichiers
Ces fichiers entièrement attribués au SGBD
2
Oracle
Divisés en blocs dont la taille peut varier de 1K à 8K
Au sein d’un fichier des blocs consécutifs peuvent
être regroupés pour former des extensions (extent)
Ensemble d’extensions pour stocker un objets
physiques (une table, un index) = un segment
Possibilité de paramétrer, pour un ou plusieurs
fichiers, le mode de stockage des données
La taille des extensions
Le nombre maximal d’extensions formant un segment
Le pourcentage d’espace libre laissé dans les blocs
Ces paramètres et les fichiers = tablespace
3
Oracle : fichiers et blocs
Création d’une base de données attribuer au
moins un fichier sur un disque
Ce fichier = l’espace de stockage initial qui
contiendra, au départ, le dictionnaire de données
La taille de ce fichier
Choisie par le DBA
Dépend de l’organisation physique qui a été choisie
Possibilité d’allouer un seul gros fichier et y placer
toutes les données/les index
Ou bien restreindre ce fichier initial au stockage du
dictionnaire et ajouter d’autres fichiers, un pour les
index, un pour les données, etc.
4
Oracle : fichiers et blocs
Le deuxième type de solution est préférable, mais
plus complexe
Permet en plaçant les fichiers sur plusieurs disques,
de répartir la charge des contrôleurs de disque
Pratique courante – recommandée par Oracle : placer
un fichier de données sur un disque, un fichier d’index
sur un autre
La répartition sur plusieurs disques permet aussi,
grâce au paramétrage des tablespaces de régler
finement l’utilisation de l’espace en fonction des
informations – données ou index – qui y sont
stockées
5
Oracle : fichiers et blocs
Le bloc = la plus petite unité de stockage
La taille d’un bloc peut être choisie au moment de
l’initialisation d’une base, et correspond
obligatoirement à un multiple de la taille des blocs du
système d’exploitation
Exemple : un bloc sous Linux occupe 1024 octets, et
un bloc ORACLE occupe typiquement 4 096 ou 8 092
octets
6
Oracle : fichiers et blocs
Structure d’un bloc, identique quel que soit le type
d’information
Cinq parties suivantes
L’entête (header) = l’adresse du bloc et son type
Le répertoire des tables = la liste des tables pour lesquelles
des informations sont stockées dans le bloc
Le répertoire des enreg. = les adresses des enreg. du bloc
Un espace libre : pour faciliter l’insertion d’enregistrements,
ou l’agrandissement des enregistrements du bloc (p. ex. un
attribut à NULL auquel on donne une valeur par UPDATE)
L’espace des données contient les enregistrements
7
Oracle : fichiers et blocs
Les trois premières parties : espace de stockage pas
directement dédié aux données = l’overhead
Cet espace, environ 100 octets. Le reste permet de
stocker les données des enregistrements.
Les paramètres PCTFREE et PCTUSED
La quantité d’espace libre laissée dans un bloc : paramètre
PCTFREE, lors de la création d’une table ou d’un index
Exemple 30% indique que les insertions se feront dans le
bloc jusqu’à ce que 70% du bloc soient occupés, les 30%
restant réservés aux agrandissements des enregistrements
Une fois que cet espace disponible de 70% est rempli,
ORACLE considère qu’aucune nouvelle insertion ne peut se
faire dans ce bloc
8
Oracle : fichiers et blocs
Des modifications (mise à NULL p. ex.) peuvent faire
baisser le taux d’occupation du bloc
Si ce taux < paramètre PCTUSED le bloc à
nouveau disponible pour des insertions
PCTFREE = le taux d’utilisation maximal au-delà
duquel les insertions deviennent interdites
PCTUSED = le taux d’utilisation minimal en deçà
duquel ces insertions sont à nouveau possibles
Les valeurs de ces paramètres dépendent de
l’application, ou plus précisément des caractéristiques
des données stockées dans une table particulière
9
Oracle : fichiers et blocs
Une petite valeur pour PCTFREE les insertions
remplissent plus complètement le bloc meilleure
exploitation de l’espace disque
Choix valable pour des données rarement modifiées
Une valeur plus importante de PCTFREE plus de
blocs pour les mêmes données, offre plus de
flexibilité pour des mises à jour fréquentes
Deux scénarios possibles pour PCTUSED et
PCTFREE
Dans le premier, PCTFREE=30%, PCTUSED=40%
(PCTFREE+PCTUSED <= 100%)
Les insertions ok jusqu’à ce que 70% du bloc soit occupé
10
Oracle : fichiers et blocs
Le bloc retiré de la liste des blocs disponibles et
seules des destructions / modifications peuvent
affecter son contenu
Si, après mises à jour, l’espace occupé tombe en
dessous de 40% bloc disponible pour insertions
Acceptation d’avoir beaucoup d’espace inoccupé, au
pire 60%
Avantage : coût de maintenance de la liste limité
Second scénario, PCTFREE=10% (valeur par défaut)
PCTUSED=80%
Si bloc plein à 90%, plus d’insertions
11
Oracle : fichiers et blocs
Mais reprennent dès que le taux d’occupation tombe
sous 80%
Bonne utilisation de l’espace, mais travail du
SGBD plus important (et donc pénalisé) car gestion
des blocs disponibles/indisponibles plus intensive
De plus, en ne laissant que 10% de marge pour
d’éventuelles extensions des enregistrements, risque
de nécessité de chaîner les enregistrements sur
plusieurs blocs
12
Oracle : enregistrements
Suite de données stockées, à quelques variantes
près, comme vu
Exemple CHAR(n) = un tableau de n+1 octets
Le premier octet indique la taille de la chaîne, qui doit
donc être comprise entre 1 et 255
Les n octets suivants : les caractères de la chaîne,
complétés par des blancs si la longueur de cette
dernière est inférieure à la taille maximale
VARCHAR(n) : seuls les octets utiles de la chaîne
sont stockés. Cas où une mise à jour élargissant la
chaîne entraîne une réorganisation du bloc
13
Oracle : enregistrements
Chaque attribut précédé de la longueur de stockage
Les NULL représentés par une longueur de 0
Cependant, si les n derniers attributs d’un
enregistrement sont NULL une marque de fin
d’enregistrement, ce qui permet d’économiser de
l’espace
Chaque enregistrement identifié par un ROWID,en
trois parties
1.
2.
3.
Le numéro du bloc au sein du fichier
Le numéro de l’enregistrement au sein du bloc
L’identifiant du fichier
14
Oracle : enregistrements
Un enregistrement peut occuper plus d’un bloc,
notamment s’il contient les attributs de type LONG
chaînage vers un autre bloc
Situation comparable à celle de l’agrandissement
Migration : enregistrement déplacé en totalité dans un
autre bloc, un pointeur dans le bloc d’origine pour ne
pas modifier l’adresse de l’enregistrement (ROWID)
ROWID peut être utilisée par des index, et une
réorganisation totale serait trop coûteuse
Migration et chaînage sont pénalisants pour les
performances
15
Oracle : extension et segments
Extension = suite contiguë de blocs
En général, extension liée à un seul type de données
Cette contiguïté = facteur essentiel d’efficacité de
l’accès aux données car évite les déplacements des
têtes de lecture, ainsi que le délai de rotation
Nombre de blocs spécifié par l’administrateur
Extensions de tailles importantes bonnes
performances, mais
si la table n’a que peu d’enreg inutile d’allouer une
extension contenant des milliers de blocs
Utilisation/Réorganisation plus difficiles si extensions de
grande taille
16
Oracle : extension et segments
Extensions = l’unité de stockage constituant les
segments
Par exemple si la taille des extensions = 50 blocs un segment = n extensions de 50 blocs chacune
Quatre types de segments
de données = enreg. des tables, 1 par table
d’index = enreg. des index ; il y a un segment par index
temporaires = utilisés pour des données pendant
l’exécution des requêtes, par exemple pour les tris
rollbacks = informations permettant d’effectuer une
reprise sur panne ou l’annulation d’une transaction; il
s’agit typiquement des données avant modification, dans
une transaction qui n’a pas encore été validée
17
Oracle : extension et segments
Une extension initiale est allouée à la création d’un
segment
De nouvelles extensions allouées dynamiquement au
segment au fur et à mesure des insertions
Pas de garantie de contiguïté
Mais une fois affectée à un segment : commande
explicite du DBA ou destruction de la table/de l’index,
pour que cette extension redevienne libre
Sur création d’une extension, pas assez d’espace libre
une erreur au DBA d’affecter un nouveau fichier à
la base, ou de réorganiser l’espace dans les fichiers
existant
18
Oracle : tablespace
Tablespace = espace physique de un ou plusieurs
fichiers
Une base de données ORACLE = un ensemble de
tablespace
Toujours un créé lors de l’initialisation de la base, et nommé
SYSTEM
Contient le dictionnaire de données, y compris les
procédures stockées, les triggers, etc.
L’organisation au sein d’un tablespace : nombreux
TBS SYSTEM
paramètres
taille des extensions
nombre maximal d’extensions, etc.
Data
Idx
RBS
Dict
19
Oracle : tablespace
Définis à la création du tablespace et modifiable
Au niveau de tablespace (pas du fichier) que le DBA
peut décrire le mode de stockage des données
Plusieurs tablespaces, avec des paramètres de
stockage individualisés nombreuses possibilités
Adaptation du mode de stockage en fonction d’un type de
données particulier
Affectation d’un espace disque limité aux utilisateurs
Contrôle sur la disponibilité de parties de la base, par mise
hors service d’un ou plusieurs tablespaces
Surtout, répartition des données sur plusieurs disques performances
20
Oracle : tablespace
Exemple typique : séparation des données et des index,
si possible sur des disques différents optimisation de
la charge des contrôleurs de disque
Possibilité de créer des tablespaces dédiés aux
données temporaires évite de mélanger les
enregistrements et ceux temporairement créés lors
d’une opération de tri
Exemple d’un tablespace en mode de lecture, les
écritures étant interdites
Toutes ces possibilités flexibilité pour la gestion des
données, aussi bien dans un but d’améliorer les
performances que pour la sécurité des accès
21
Oracle : tablespace
Lors de la création d’un tablespace, paramètres de
stockage par défaut des tables/index
« par défaut » = si pas de paramètres spécifiés à la
création d’une table les paramètres du tablespace
Les principaux paramètres de stockage sont
Taille de l’extension initiale, défaut 5 blocs
Taille de chaque nouvelle extension, défaut 5 blocs
Nombre maximal d’extensions avec la taille des
extensions, nombre max de blocs pour une table/index
Taille des extensions peut croître progressivement, selon un
ratio indiqué par PCTINCREASE; valeur de 50% chaque
nouvelle extension a une taille supérieure de 50% à la
précédente
22
Oracle : tablespace
Exemple de création
CREATE TABLESPACE TB1
DATAFILE ’fichierTB1.dat’ SIZE 50M
DEFAULT STORAGE (
Initial 100K
INITIAL 100K
NEXT 40K
MAXEXTENTS 20,
PCTINCREASE 20);
Extent 1
Next 40K
Extent 2
Extent 2 +
PCTINCREASE
= 48K
Extent 3
Création d’un tablespace TB1, premier fichier de 50 Mo
Paramètres du DEFAULT STORAGE
Taille de la première extension allouée à une table/un index
Taille de la prochaine extension
Nombre maximal d’extensions, ici 20
Nouvelle extension 20% plus grande que la précédente
23
Oracle : tablespace
Si la taille d’un bloc est 4K
Première extension de 25 blocs
Une seconde de 10 blocs
Une troisième de 10 x 1,2 = 12 blocs
etc.
La taille maximale
l’espace utilisé sera limité et sous contrôle du DBA
Mais, le DBA doit être prêt à prendre des mesures pour
répondre aux demandes des utilisateurs si une table a
atteint sa taille limite
24
Oracle : tablespace
Exemple de tablespace avec paramétrage plus souple
Pas de limite au nombre d’extensions d’une table
Mode « auto-extension » = automatiquement par 5 Mo
La taille du fichier limitée à 500 Mo
CREATE TABLESPACE TB2
DATAFILE ’fichierTB2.dat’ SIZE 2M
AUTOEXTEND ON NEXT 5M MAXSIZE 500M
DEFAULT STORAGE (
INITIAL 128K
NEXT 128K
MAXEXTENTS UNLIMITED);
25
Oracle : tablespace
Modification des paramètres pour les futures tables
Exemple : modification de TB1 pour extensions de
100K, et nombre maximal d’extensions à 200
ALTER TABLESPACE TB1 DEFAULT STORAGE (
NEXT 100K
MAXEXTENTS 200);
Actions possibles
« hors-service » : sauvegarder une partie de la base ou
rendre cette partie de la base indisponible
ALTER TABLESPACE TB1 OFFLINE;
Lecture seule
ALTER TABLESPACE TB1 READ ONLY;
Ajout d’un fichier afin d’augmenter sa capacité
ALTER TABLESPACE ADD DATAFILE ’fichierTB1-2.dat’ SIZE 300 M;
26
Oracle : tablespace
Création d’une base : donner la taille et l’emplacement
du premier fichier pour tablespace SYSTEM
Création d’un nouveau tablespace création d’un
fichier d’espace de stockage initial pour les données
Un fichier n’appartient qu’à un seul tablespace, et son
contenu est exclusivement géré par ORACLE, même si
partiellement utilisé
Ne pas affecter un fichier de 1 Go à un tablespace
qui contiendra seulement 100 Mo de données, car les
900 Mo restant ne servent alors à rien
27
Oracle : tablespace
Utilisation de l’espace disponible dans un fichier pour de
nouvelles extensions si la taille des données augmente,
ou de nouveaux segments sur création tables/index
Si fichier plein (= plus assez d’espace disponible pour
créer un nouveau segment/nouvelle extension) message d’erreur au DBA
Plusieurs solutions
Création d’un fichier et affectation au tablespace
Modification de la taille d’un fichier existant
Permettre à un ou plusieurs fichiers de croître
dynamiquement en fonction des besoins simplification de la gestion de l’espace
28
Oracle : tablespace, inspection
Un certain nombre de vues dans le dictionnaire de
données pour consulter l’organisation physique d’une
base, et l’utilisation de l’espace
Vue DBA_EXTENTS donne la liste des extensions
Vue DBA_SEGMENTS donne la liste des segments
Vue DBA_FREE_SPACE pour mesurer l’espace libre
Vue DBA_TABLESPACES la liste des tablespaces
Vue DBA_DATA_FILES donne la liste des fichiers
Ces vues sont gérées sous le compte utilisateur SYS
qui est réservé à l’administrateur de la base
29
Oracle : tablespace, inspection
Exemples de requêtes pour inspecter une base
Supposition : deux tablespace, SYSTEM avec un fichier
de 50M, TB1 deux fichiers de 100Mo et 200Mo
Affichage des informations sur les tablespaces
SELECT tablespace_name "TABLESPACE",
initial_extent "INITIAL_EXT",
next_extent "NEXT_EXT",
max_extents "MAX_EXT"
FROM sys.dba_tablespaces;
TABLESPACE
---------SYSTEM
TB1
INITIAL_EXT
----------10240000
102400
NEXT_EXT
-------10240000
50000
MAX_EXT
------99
200
30
Oracle : tablespace, inspection
Liste des fichiers d’une base, avec le tablespace
SELECT file_name, bytes, tablespace_name
FROM sys.dba_data_files;
FILE_NAME
-----------fichier1
fichier2
fichier3
BYTES
---------5120000
10240000
20480000
TABLESPACE_NAME
------------------SYSTEM
TB1
TB1
Espace disponible dans chaque tablespace
SELECT tablespace_name, file_id, COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM", MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE", SUM(blocks) "TOTAL"
FROM sys.dba_free_space
WHERE tablespace_name = ’SYSTEM’
GROUP BY tablespace_name, file_id;
31
Oracle : tablespace, inspection
TBS
---------SYSTEM
FILE_ID PIECES MAX MIN
------- ------------ ------1
2
2928 115
AVG SUM
------- -----1521.5 3043
SUM = nombre total de blocs libres
PIECES = fragmentation de l’espace libre
MAXIMUM = espace contigu maximal
Ces informations sont utiles pour savoir s’il est possible
de créer des tables volumineuses pour lesquelles on
souhaite réserver dès le départ une extension de taille
suffisante
32
Oracle : création de tables
Tout utilisateur avec droits suffisants
Notion d’utilisateur et celle de base de données liées
Un utilisateur (avec des droits appropriés) a un espace
permettant de stocker des tables
Tout ordre CREATE TABLE par cet utilisateur une
table et des index qui appartiennent à cet utilisateur
Possibilité lors de la création d’un profil utilisateur
d’indiquer
Les tablespaces où il a le droit de placer des tables
Son espace total sur chacun de ces tablespaces
Son tablespace par défaut
33
Oracle : création de tables
Possible dans la commande CREATE TABLE des
paramètres de stockage
Exemple
CREATE TABLE Film (...)
PCTFREE 10
PCTUSED 40
TABLESPACE TB1
STORAGE ( INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25 );
La table stockée dans TB1
Paramètres de stockage spécifiques à la table Film
34
Oracle : création de tables
Par défaut une table est organisée séquentiellement sur
une ou plusieurs extensions
Les index sur la table sont stockés dans un autre
segment, et font référence aux enregistrements grâce
au ROWID
Il est également possible d’organiser sous forme d’un
arbre, d’une table de hachage ou d’un regroupement
cluster avec d’autres tables
Cf. chapitre sur l’indexation
35
MySQL : principes
L’architectue MySQL est différente des autres
MySQL est suffisament flexible pour pouvoir fonctionner
dans des environnements intenses
MySQL tourne sur des matériels très différents et
supporte des types de données très variés
Plusieurs moteurs ou engins de stockage existent
Possible d’en charger au lancement
Choix table par table
Permet de choisir le type de performance, de
fonctionnalités, etc.
Les connaître
36
MySQL : principes
Première couche
Contient les composants nécessaires à toute application
client/serveur
Gestion des connexions, authentification, sécurité, etc.
La deuxième couche
« L’intelligence » de MySQL
Parser des requêtes, analyse, optimisation
Cache, toutes les fonctions internes (e.g., dates, math,
cryptage)
Fonctionnalités trans moteur de stockage : procédure
stockées, triggers et vues par exemple
37
MySQL : principes
Les moteurs de stockage
Responsables du stockage et de la récupération
Chacun a des avantages et des inconvénients
Le serveur communique avec eux par l’API du moteur
Utilisation transparente pour la couche gestion
requêtes
L’API : plusieurs opérations bas niveau, telles que
« début de transaction » ou « récupère la ligne avec
cette clé primaire »
N’interprètent pas SQL* et ne dialoguent pas entre eux
SHOW ENGINES
38
MySQL : moteurs de stockage
Source : High
performance
MySQL (2008)
39
MySQL : moteurs de stockage
Base de données (ou schéma) stockée dans un sous
répertoire du répertoire de données
Création d’une table la définition dans un fichier
table.frm
La casse dépend du système d’exploitation
Chaque moteur gère les données et index différemment
Mais le serveur gère la définition
40
MySQL : moteurs de stockage
Déterminer le moteur utilisé pour une table
SHOW TABLE STATUS
mysql> SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)
41
MySQL : moteurs de stockage
Table MyISAM
Plusieurs informations & statistiques
Name = nom de la table
Engine = le moteur de stockage
Row_format = le format de la ligne, pour MyISAM, peut être
Dynamic, Fixed, ou Compressed
Rows = nombre de lignes; sur table non transactionnelle, ce
nombre est exact, sinon estimation
Avg_row_length = nombre d’octets moyens
Data_length = nombre d’octets total de la table
Max_data_length = volume max de données pour la table
Index_length = espace disque utilisé par les index
42
MySQL : moteurs de stockage
Informations (suite)
Data_free = espace alloué mais non utilisé
Auto_increment = prochaine valeur AUTO_INCREMENT
Create_time = date de création
Update_time = dernière date de mise à jour
Check_time = vérification via CHECK TABLE ou myisamchk
Collation = le jeu de caractères
Checksum = somme de contrôle de tout le contenu
Create_options = toute option spécifiée à la création
Comment = informations complémentaires
Si MyISAM, commentaires à la création
Si InnoDB, espace disque libre total du tablespace
Si une vue, le commentaire = « VIEW »
43
MySQL : MyISAM
Source : http://dev.mysql.com
44
MySQL : MyISAM
Moteur par défaut
Bon compromis performance/fonctionnalités
Indexage de tout le texte
Compression
Fonctions spatial (GIS)
Pas de transactions ou de lock sur enregistrement
Une table un fichier de données (.MYD), un fichier
index (.MYI)
Le format est indépendant d’une plateforme
Copie possible d’un serveur type Intel vers un
PowerPC ou Sun SPARC
45
MySQL : MyISAM
Tables contiennent soit enregistrements dynamiques ou
statiques
Le choix suivant la définition
Le nombre de ligne max
Fonction de l’espace disque disponible
Du plus grand fichier créable par l’OS
Tables MyISAM sous MySQL 5.0 en taille variable
Max 256 To de données
Utilise des pointeurs par défaut sur 6 octets
Les versions précédents : 4 octets 4 Go
MySQL peut gérer un pointeur jusqu’à 8 octets
46
MySQL : MyISAM, fonctionnalités
Verrous et accès concurrents
Automatic repair
Verrou sur toute une table
En lecture, verrou partagé sur toutes les tables requises
En écriture, verrou exclusif
Mais, possible d’insérer pendant l’exécution de SELECT
(inserts concurrents)
Contrôle et réparation automatique de tables
Manual repair
CHECK TABLE matable et REPAIR TABLE matable
Myisamchk en ligne de commande « offline »
47
MySQL : MyISAM
Index features
Création d’index sur les 500 premiers caractères d’une
colonne BLOB ou TEXT
Index « full-text » = mots individuels pour recherches
complexes
Ecritures de clé retardées, option DELAY_KEY_WRITE
Pas d’écriture des index changés à la fin d’une requête
Mise en mémoire des changements
Vidage sur disque si purge du buffer/fermeture de la table
Amélioration de performance sur table très utilisées et
changeant souvent
48
MySQL : MyISAM
Ecritures de clé retardées (suite)
MAIS, si crash, index corrompu
Utilisation d’un script avec myisamchk avant le
démarrage de MySQL
Configurable globalement ou table par table
Tables compressées
Pour tables ne changeant jamais, ex. applications sur
CD/DVD ou embarquées
Pour compresser ou « packer » : myisampack
Non modifiable a posteriori (sauf à décompresser/modifier/
recompresser)
Moins d’espace sur disque, meilleure performance
Peuvent avoir des index, mais lecture seule
49
MySQL : MyISAM Merge
Variation du moteur MyISAM
Une table dite « fusionnée » = combinaison de tables
MyISAM identiques dans une table virtuelle
Très utile lorsque MySQL sert à
Tracer
De la collection de données (datawarehouse)
50
MySQL : InnoDB
Créé pour le traitement des transactions
Principalement, de transactions courtes qui
généralement se terminent correctement (pas de rollback)
Le moteur le plus utilisé pour les transactions
Ces performances et récupération automatique après
incident en font un bon moteur pour des besoins sans
transactions
Stockage dans un ou plusieurs fichiers de données =
tablespace
Un tablespace est une boîte noire gérée par InnoDB
51
MySQL : InnoDB
Depuis MySQL 4.1, InnoDB peut stocker les données et
les index dans des fichiers séparés
Peut utiliser des partitions « raw » pour ces tablespace
InnoDB utilise MVCC gestion de la concurrence + les 4
standards d’isolation SQL (cf. cours dédié)
Les tables utilisent un index dit « clustered index »
La structure des index est vraiment différente des
autres moteurs fourni une recherche très rapide sur
les clés primaires
Mais, les index secondaires contiennent la clé primaire;
si clé primaire volumineuse autres index larges aussi
52
MySQL : InnoDB
InnoDB apporte les contraintes sur les clés étrangères
non fournies par le serveur MySQL lui-même
Plusieurs optimisations internes
Lecture « en-avant » prédictive pour précharger les
données du disque
Un index « adaptive hash » = construit
automatiquement en mémoire pour des recherches
très rapides
Un tampon d’insertion pour accélérer les insertions
Moteur complexe, nécessitant une bonne connaissance
pour un usage optimisé
53
MySQL : Memory
Source : http://dev.mysql.com
54
MySQL : Memory
Tables « Memory » (ou tables HEAP) pour accès très
rapides aux données ne changeant pas ou qui peuvent
être perdues
Un fichier sur disque : la définition, table.frm
si redémarrage, la structure est gardée, mais pas les
données
Exemple d’utilisations
Tables de recherches ou de liens, telles que lien codes
postaux -> villes
Cache des résultats d’une donnée régulièrement recalculée
Résultats immédiats pendant l’analyse de données
55
MySQL : Memory
Support des index « HASH » très rapides pour des
requêtes de recherche
Bien que très rapides, ne peuvent remplacer
systématiquement des tables sur disque
Verrous au niveau table faible concurrence en écriture
Pas de support des types TEXT et BLOB
Uniquement enregistrements taille fixe VARCHARs
stockés comme CHARs perte d’espace
Utilisé en interne pour les tables temporaires et les
résultats intermédiaires
Si devient trop grand, ou comporte des colonnes TEXT
ou BLOB conversion en table MyISAM
56
MySQL : Archive
Source : http://dev.mysql.com
57
MySQL : Archive
Supporte seulement INSERT et SELECT
Pas d’index
Moins d’I/O que MyISAM « bufferise » les écritures
et compresse avec zlib lors de l’insertion
Chaque SELECT balayage total de la table
Idéal pour tracer et l’acquisition de données
Où l’analyse doit balayer toute la table
Ou si besoin d’INSERT rapides sur un serveur principal de
réplication. Les serveurs secondaires peuvent utiliser un
autre moteur pour la même table utilisation d’index pour
meilleure performance sur analyse
Fichiers de données .ARZ; .ARN pendant les optimisations
58
MySQL : Archive
Verrou sur enregistrement + tampon dédié insertion
avec haute concurrence
Lecture logique = arrêt d’un SELECT après
récupération du nombre de lignes qui existaient au
début de la réquêtes
Insertion retardée = attend que les insertions soient
complètes Simule quelques aspects des transactions
et de la concurrence MVCC
Mais pas un moteur transactionnel
Moteur optimisé pour les insertions haute-vitesse et
compressées
59
MySQL : Moteur fédéré
Ne stocke pas les données localement
Toutes les tables « fédérées » font référence à des
tables sur un serveur MySQL distant connexion à ce
serveur distant pour toutes les opérations
Permet de « bidouiller », par exemple la réplication
Beaucoup d’inconvénients et de limitations en l’état
Surtout utile pour une recherche par clé primaire d’un
simple enregistrement ou pour des insertions à distance
Peu performant/efficace pour des requêtes
d’agrégation, des jointures ou d’autres opérations
basiques
60
MySQL : autres moteurs
Le moteur Blackhole
Aucun mécanisme de stockage
Supprime chaque INSERT plutôt que de les stocker
Mais trace les requêtes dans son journal traçabilité ou
réplication vers un esclave
Utile pour des configurations de réplication originales ou
journalisation
Le moteur NDB Cluster
Acquisition en 2003, provient de Sony Ericsson
Créé pour de la haute vitesse – temps réel, avec
redondance et équilibrage de charge
Bien qu’il trace sur disque, toutes les données sont en
mémoire et optimisé pour les recherches sur clé primaire
61
MySQL : autres moteurs
Le moteur NDB Cluster
Ajout depuis d’autres méthodes d’indexation et
d’optimisation
Architecture unique; un cluster NDB cluster est
incomparable à un cluster Oracle par exemple
Basé sur un concept « ne partage rien »
Pas de baie de stockage réseau ou autre grosse solution de
stockage
Consiste en des nœuds de données, de gestion et des
nœuds SQL (instance MySQL)
Chaque nœud data comporte un segment (« fragment »)
des données du cluster
62
MySQL : autres moteurs
Le moteur NDB Cluster
Les fragments sont dupliqués copies multiples d’une
même données sur différents nœuds
Un serveur physique est habituellement dédié à chaque
nœud pour redondance et haute disponibilité
Principe similaire au RAID
Les nœuds de gestion récupèrent la configuration
centralisée, pour la surveillance et le control des nœuds
Tous les nœuds data communiquent entre eux et tous les
serveurs MySQL se connectent aux nœuds data
Une faible latence réseau est importante
63
MySQL : autres moteurs
Le moteur NDB Cluster
Technologie très attirante ,mais
Complexe dans la mise en place et la compréhension
N’est pas un moteur de stockage à tout faire
Par exemple, les jointures complexes récupération des
données à travers le réseau de tout le cluster extrêmement lent
Mais la récupération d’une seule table peut être très rapide
plusieurs serveurs fournissent en parallèle les données
En général pas la solution
64
MySQL : autres moteurs
Le moteur Falcon
Jim Starkey, un pionnier des DB, inventeur d’Interbase, de
MVCC et du type BLOB a créé le moteur Falcon
MySQL AB le récupère en 2006
Créé pour du matériel moderne, des serveurs avec plusieurs
processeurs 64 bits et plein de mémoire
Mais peut aussi fonctionner sur des serveurs plus modestes
Utilise MVCC et essaye de faire les transactions
entièrement en mémoire
Rollbacks et récupération extrêmement rapides
Toujours en cours de développement
65
MySQL : autres moteurs
Le moteur solidDB
Développé par Solid Information Technology
(http://www.soliddb.com)
Moteur transactionnel utilisant MVCC
Supporte le control de concurrence pessimiste et optimiste,
qu’aucun autre moteur fait
Support complet des clés étrangères
Très proche d’InnoDB
Capable de sauvegarde en ligne sans charge
Existe depuis plus de 15 ans; lié à MySQL depuis 2006
Sous licence GPL, propose une double licence commerciale
Inclus dans un paquet avec le serveur MySQL et MyISAM
66
MySQL : choisir
Pour une application basée sur MySQL choisir le
moteur
Sinon, risque de voir après coup, qu’une fonctionnalité
vous manque (transactions, verrouillage inadéquate,
etc.)
Avoir une vision claire de l’usage de chaque table
Si possible, éviter de mixer les moteurs dans une base
Critères
Transactions : InnoDB est le plus stable et le mieux intégrer.
Si pas de besoin, MyISAM est un bon choix, surtout si
requêtes SELECT/INSERT
67
MySQL : choisir, critères
Transactions
Accès concurrents
Si besoin, InnoDB est le plus stable et le mieux intégrer
Sinon, MyISAM bon choix, surtout si SELECT/INSERT
Dépend de la charge prévue
Si SELECT/INSERT concurrents MyISAM
Si mélange d’opérations avec accès concurrents critiques l’un des moteurs avec verrouillage d’enregistrement
Sauvegardes
Si votre serveur peut être arrêté à intervalles réguliers n’importe quel moteur
Online plus difficile
68
MySQL : choisir, critères
Récupération après incident
Attention : combien de temps prendra la récupération
suivant le volume ?
MyISAM plus fragile et plus long à réparer que les tables
InnoDB tables
Fonctionnalités particulières
Certains moteurs proposent des fonctionnalités ou des
optimisations nécessaires
Par exemple, une application qui repose sur l’optimisation
par des index « clusturisés » InnoDB et solidDB
MyISAM supporte la recherche texte complet
69
MySQL : cas pratique
Journalisation
Supposons : base MySQL pour tracer tous les appels
téléphoniques d’un central en temps réel ou trace de toutes
les visites de votre site Web (module mod_log_sql Apache)
critère principal : la vitesse
Les moteurs MyISAM et Archive car INSERT rapides
Problème : vous décidez de faire des rapports ralentissement des INSERT
1ère solution : réplication
2ème solution : table MERGE
Gestion de commandes InnoDB
Transactions obligatoires
Support des clés étrangères
70