MySQL – Performance, haute disponibilité et maintien en conditions opérationnelles

Publié: 5 février 2013 dans Debian, Haute disponibilite
Tags:, , , , , , , , , , , , , , , , ,

MySQL est un système de gestion de base de données distribué, à l’origine, sous licence GPL. Il offre de nombreuses options et fonctionnalités permettant de répondre aux besoins d’applications légères et performantes.

Depuis son acquisition par SUN Microsystems puis le rachat de SUN par Oracle (2009), MySQL est également disponible sous licence propriétaire.

  1. Performances MySQL : choisir le moteur

MySQL propose 2 moteurs de base de données : MyISAM et INNODB

  1. MyISAM

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
  • Système de fichiers MyISAM : une table est représentée par un fichier .frm, un fichier de données .myd (MYisamData) et un fichier d’index .myi (MYisamIndex).
    1. InnoDB

Les avantages :

  • il supporte ACID (ci-dessous) : 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
  • Système de fichiers InnoDB : chaque table est représentée par un fichier .frm.

Point particulier : le modèle ACID – Atomicité, Consistance, Isolation, Durabilité

 

Atomicité

L’atomicité est un point important dans le modèle ACID, qui apporte à InnoDB les fonctions suivantes :

  • Autocommit
  • COMMIT
  • ROLLBACK
  • Sécurisation du schéma de base de données

Si l’auto-validation est activée alors toute transaction débutée est considérée comme terminée. Dans le cas contraire, une commande SQL COMMIT ou ROLLBACK termine la transaction courante et en commence une autre. Ces deux commandes vont libérer tous les verrous InnoDB qui étaient posés durant la transaction. Un COMMIT signifie que les modifications durant la transaction seront enregistrés, et rendus visibles aux autres. Un ROLLBACK, d’un autre coté, annule toutes les modifications.

Si la connexion a activé l’auto-validation, l’utilisateur peut faire une transaction multi-commandes en commençant la transaction avec la commande START TRANSACTION ou BEGIN et en la terminant avec COMMIT ou ROLLBACK.

Consistance

La consistance dans le modèle ACID apporte au moteur InnoDB une protection des données en cas de crash. Pour ce faire, on configurera 2 options :

  • InnoDB doublewrite buffer.
  • InnoDB crash recovery

Isolation

L’isolation permet un cloisonnement des transactions.

Outre l’aspect sécurité, cette isolation assurera une stabilité du système pour chaque transaction.

Durabilité

La durabilité offre une indépendance du système de gestion de bases de données face aux évolutions matérielles.

    1. Choisir le bon moteur :

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.

  1. Haute disponibilité

Il existe 3 grandes méthodes pour mettre en place une architecture de haute disponibilité avec MySQL.

La disponibilité peut en fait se traduire par la formule suivante :

uptime / (uptime + downtime)

soit le rapport de l’uptime sur le temps pendant lequel le serveur aurait dû être disponible.

  • 90% soit 36,5 jours d’indisponibilité par an
  • 99,9999% soit 31 secondes d’indisponibilité par an
    1. MySQL Replication

 mysql-replication

Le serveur maître est le serveur à contacter pour l’insertion, la mise à jour et la suppression de données et de champs dans la base de données. En effet, chaque opération est enregistrée dans un log binaire (Statement Based ou Row Based) ce qui permet non-pas d’enregistrer uniquement la requête SQL mais en fait le résultat (ex : une requête avec une valeur aléatoire comme uniqid ne renvoie pas le même résultat en fonction du nœud sur lequel elle est exécutée). Les serveurs MySQL esclaves se contentent de ré-exécuter toutes les opérations d’écritures effectuées sur le maître.

Les requêtes de lecture peuvent donc être exécutées sur les esclaves.

Avantages :

  • Fonctionnalité native et stable depuis MySQL v3.0
  • Utilisation possible de SSL entre les nœuds

Inconvénients :

  • Copie asynchrone
  • SPOF
  • Load balancing non géré par MySQL
    1. MySQL Cluster

mysql-cluster (source : mysql.com)

 MySQL Cluster utilise trois différents types de nœuds :

  • Data node (processus ndbd) : ces nœuds contiennent les données des tables NDB.
  • Management node (processus ndb_mgmd) : utilisé pour la configuration et la supervision du cluster. Ils ne sont nécessaires que pour le démarrage d’un autre nœud (Data node ou SQL node).
  • SQL node (processus mysqld) : Un serveur MySQL tel qu’utilisé sur un serveur MySQL classique. Il se connecte aux Data nodes pour le stockage et la lecture des données.

Avantages :

  • Fail-over automatique
  • Backup possible à chaud
  • Mise à jour d’un nœud à chaud
  • Faible coût
  • Scalable en écriture comme en lecture

Inconvénients :

  • Requêtes complexes, notamment pour les jointures qui peuvent être longues
  • Moteur SGBD : pas de fulltext, ni de tests d’intégrité référentielle
    1. Shared Disk Clustering

Cette solution repose sur un ou plusieurs serveurs MySQL qui ont leurs données en commun sur un SAN réseau. Toutes les données sont donc exactement les mêmes sur tous les serveurs MySQL, si et seulement si le cache est désactivé.

Actif-Passif

Le mode Actif-Passif est mis en place pour qu’un autre serveur MySQL (passif) prenne le relai si le serveur actif venait à tomber

Avantage :

  • Disponibilité assurée par le serveur passif si l’actif tombe (avec un temps de transition)

Inconvénients :

  • Un seul serveur sur deux travaille malgré le fait que les deux soient en fonctionnement
  • Le basculement doit être géré par un programme externe

Actif-Actif

Dans ce cas, tous les serveurs travaillent.

Avantages :

  • Supporte de grands volumes de données avec connexion entre serveur applicatif et serveur de base de données importante
  • Les deux nœuds travaillent

Inconvénients :

  • Le cache est un problème très important dans ce cas là. Il ne peut quasiment pas être utilisé car chaque nœud a son propre cache

Dans ces deux méthodes (actif-passif, actif-actif), le gros problème est le SPOF au niveau du SAN.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s