Mysql MyIsam & Innodb
De BlaxWiki
MyISAM[modifier]
Les avantages :
très rapide pour les requêtes de type SELECT ou INSERT
il supporte les index fulltext : permet d'effectuer des recherches sur des mots en se basant sur un index spécifique, accélérant ainsi les recherches
il gère les conflits d'accès (ou lock) : permet de verrouiller une table pour des opérations bien précises
très facile à administrer : possibilité de recopier directement les fichiers d'un serveur vers un autre, support des tables compressées ...
Les inconvénients :
il ne supporte pas les transactions
il ne supporte pas les clés étrangères
Note : en plus du fichier .frm, chaque table est représentée par un fichier de données .myd (MYisamData) et un fichier d'index .myi (MYisamIndex).
InnoDB[modifier]
Les avantages :
il supporte ACID : permet d'assurer que chaque enregistrement sera complètement réussi, ou complètement échoué, ainsi les risques d'erreurs sont impossibles, même en cas de panne
il gère les transactions (instructions sql BEGIN, COMMIT, ROLLBACK ...)
il supporte les clés étrangères et les intégrités référentielles
il possède un système de récupération automatique en cas de crash
Les inconvénients :
il ne permet pas les index fulltext
son administration est un peu plus complexe (gestion de tablespace, paramètres supplémentaires dans le my.cnf ...)
le moteur de stockage est plus lent que d'autres et gourmand en ressources mémoires et en espace disque
Note : chaque table est représentée par un fichier .frm.
Erreur log "the age of the last checkpoint is[modifier]
- Origine de l'alerte : Cette erreur survient quand il y'a beaucoup d’opérations sur une base MySQL de type innoDB. De base, les fichiers des logs de transaction InnoDB font 10MB en tout et environ
toute les heures, le service MySQL purge ces fichiers. Toutefois, s'il se remplit trop vite on obtient une erreur dans les logs: "InnoDB: ERROR: the age of the last checkpoint is 9436551" Cela n'a pas d'impact sur le bon fonctionnement du service, sauf si cela revient trop souvent.
- Résolution de l'alerte
Calcul de la taille "optimal" du logfile InnoDB "ib_logfile"
Se connecter au service MySQL.
Exécuter la requête SQL:
pager grep sequence;
show engine innodb status\G select sleep(60); show engine innodb status\G
Ce qui retournera un résultat du type:
Log sequence number 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 3838334638
1 row in set (0.05 sec)
Calculer le taux d'ecriture dans le logfile:
Taux d’écriture en MB/Minutes = (("Log sequence number plus recent" - "Log sequence number plaus ancien") /1024)/1024
En prenant en exemple le résultat type juste au-dessus: ((3838334638-3836410803)/1024)/1024) Soit "1.83 MB par minutes"
En déduire la taille des logfiles:
Sachant que la "purge" automatique du fichier a lieu a peu près toutes les heures et que MySQL par défaut créé 2 fichiers pour les logs InnoDB ... On multiplie donc le taux de MB/Minutes par 30.
Dans notre exemple, on aboutirai a une taille par fichier "ib_logfile" de 55MB.
Si il y a de la place sur le disque, ne pas hésiter à mettre une taille "large" dans le my.cnf (par rapport à la taille calculée), si on a un chiffre qui est en dessous de 30M, mettre tout de même 30M
- Ajustement et prise en compte de la configuration MySQL
Modifier le fichier "my.cnf" Il faut éditer le fichier my.cnf pour que la nouvelle taille des fichiers "ib_logfile" soit prise en compte. Pour cela il faut ajouter ou modifier la ligne: [mysqld] innodb_log_file_size = xxxM Dans notre exemple, cela donnerait: innodb_log_file_size = 55M Pour cela, on lance les commandes: mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;" /etc/init.d/mysql stop Supprimer les fichiers "ib_logfile0" et "ib_logfile1" dasn le "datadir" de MySQL. Relancer le service MySQL
- Sources
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
Changement du moteur de stockage[modifier]
On gardera à l'esprit que le choix du moteur n'est jamais définitif. Il peut être changé dynamiquement avec la commande ALTER TABLE comme ceci :
ALTER TABLE maTable1 ENGINE=INNODB;
ALTER TABLE maTable2 ENGINE=MYISAM;
Ce système permet notamment d'utiliser plusieurs types de stockage pour différentes tables d'une même base de données. Cela permettra dans certains cas d'obtenir les meilleurs
performances et avantages.
Conclusion[modifier]
Pour conclure, on optera pour InnoDB principalement lorsque l'on utilisera un système d'information qui n'admet pas les erreurs ou qui doit utiliser des clés étrangères ou des intégrités référentielles.
MyISAM restera quant à lui le meilleur choix dans le cas où l'on fait principalement des requêtes de lecture ou d'insertion.
