15 conseils de réglage des performances et d’optimisation MySQL/MariaDB les plus utiles

MySQL est un puissant Système de Gestion de Base de Données Relationnelle, ou en abrégé SGBDR, open source. Il a été lancé en 1995 (il a donc 20 ans). Il utilise le Langage de Requête Structuré, qui est probablement le choix le plus populaire pour gérer le contenu d’une base de données. La dernière version de MySQL est la 5.6.25, sortie le 29 mai 2015.

Un fait intéressant à propos de MySQL est que son nom vient de la fille de Michael Widenius (le créateur de MySQL), My. Même s’il existe de nombreux faits intéressants sur MySQL, cet article vise à vous montrer quelques pratiques utiles pour vous aider à gérer votre serveur MySQL.

En avril 2009, le projet MySQL a été racheté par Oracle. En conséquence, une version alternative de MySQL appelée MariaDB a été créée. La principale raison de cette création était de maintenir le projet libre sous la Licence Publique Générale.

Aujourd’hui, MySQL et MariaDB sont parmi les SGBDR les plus utilisés (si ce n’est les plus utilisés) pour les applications web telles que WordPress, Joomla, Magento et autres.

Cet article vous montrera quelques astuces de base, mais utiles, pour optimiser et ajuster les performances de MySQL/MariaDB. Veuillez noter que cet article suppose que vous avez déjà installé MySQL ou MariaDB. Si vous vous demandez toujours comment les installer sur votre système, vous pouvez suivre nos guides détaillés ici :

Important: Avant de commencer – ne suivez pas ces suggestions aveuglément. Chaque configuration MySQL est unique et nécessite une réflexion supplémentaire avant d’apporter des modifications.

Choses que vous devez savoir:

Le fichier de configuration MySQL/MariaDB se trouve dans /etc/my.cnf. Chaque fois que vous modifiez ce fichier, vous devrez redémarrer le service MySQL pour que les nouvelles modifications prennent effet.
Pour rédiger cet article, la version MySQL 5.6 a été utilisée comme modèle.

1. Activer le mode file-per-table pour InnoDB

Il est important de commencer par expliquer que InnoDB est un moteur de stockage. MySQL et MariaDB utilisent InnoDB comme moteur de stockage par défaut. Dans le passé, MySQL conservait les tables et les index de la base de données dans un espace de table système. Cette approche était destinée aux serveurs dont le seul but est le traitement de la base de données et dont le disque de stockage n’est utilisé à aucune autre fin.

Le moteur InnoDB offre une approche plus flexible et chaque base de données est conservée dans un fichier de données .ibd. Chaque fichier .ibd représente un espace de table distinct. De cette manière, les opérations de base de données telles que « TRUNCATE » peuvent être effectuées plus rapidement et vous pouvez également récupérer de l’espace inutilisé lors de la suppression ou de la troncation d’une table de base de données.

Un autre avantage de cette configuration est la possibilité de conserver certaines tables de la base de données sur un périphérique de stockage distinct. Cela peut grandement améliorer la charge d’E/S sur vos disques.

La directive innodb_file_per_table est activée par défaut dans MySQL 5.6 et versions ultérieures. Vous pouvez vérifier cela dans le fichier /etc/my.cnf. La directive ressemble à ceci :

innodb_file_per_table=1

2. Stocker les données de la base de données MySQL sur une partition distincte

Note : Cette configuration ne fonctionne qu’avec MySQL, mais pas avec MariaDB.

Parfois, les opérations de lecture/écriture du système d’exploitation peuvent ralentir les performances de votre serveur MySQL, notamment s’ils sont situés sur le même disque dur. Dans ce cas, je recommanderais d’utiliser un disque dur distinct (de préférence un SSD) pour le service MySQL.

Pour cela, vous devrez connecter le nouveau disque à votre ordinateur/serveur. Dans le cadre de cet article, je partirai du principe que le disque sera identifié sous le nom /dev/sdb.

Le prochain pas consiste à préparer le nouveau disque :

# fdisk /dev/sdb

Maintenant, appuyez sur “n” pour créer une nouvelle partition. Ensuite, appuyez sur “p” pour rendre la nouvelle partition principale. Après cela, configurez le numéro de partition à 1-4. Après cela, vous serez invité à sélectionner la taille de la partition. Appuyez sur Entrée ici. Au prochain étape, vous devrez configurer la taille de la partition :

Si vous souhaitez utiliser le disque entier, appuyez une fois de plus sur Entrée. Sinon, vous pouvez définir manuellement la taille de la nouvelle partition. Appuyez sur “w” pour enregistrer les modifications quand vous êtes prêt. Maintenant, nous allons devoir créer un système de fichiers pour notre nouvelle partition. Cela peut être facilement réalisé avec :

# mkfs.ext4 /dev/sdb1

Maintenant, nous allons monter notre nouvelle partition dans un dossier. J’ai nommé mon dossier “ssd” et j’ai créé dans le répertoire racine :

# mkdir /ssd/

Nous sommes prêts à monter la nouvelle partition que nous venons de créer dans le nouveau dossier :

# mount /dev/sdb1  /ssd/

Vous pouvez effectuer le montage au démarrage en ajoutant la ligne suivante dans le fichier /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Maintenant, vous êtes prêt à déplacer MySQL vers le nouveau disque. Premièrement, arrêtez le service MySQL avec :

# service mysqld stop

I would recommend you stopping Apache/nginx as well to prevent any attempts to write in the databases:

# service httpd stop
# service nginx stop

Maintenant, copiez le répertoire MySQL entier sur le nouveau disque :

# cp /var/lib/mysql /ssd/ -Rp

Cela peut prendre un certain temps en fonction de la taille de vos bases de données MySQL. Une fois ce processus terminé, renommez le répertoire MySQL :

# mv /var/lib/mysql /var/lib/mysql-backup

Ensuite, nous créerons un lien symbolique.

# ln -s /ssd/mysql /var/lib/mysql

Maintenant, vous êtes prêt à démarrer MySQL et votre service Web :

# service mysqld start
# service httpd start
# service nginx start

À ce stade, vos bases de données MySQL seront accessibles à partir du nouveau disque.

Pages: 1 2 3 4