15 conseils utiles pour l’optimisation et l’optimisation des performances MySQL/MariaDB
MySQL est un puissant système de gestion de bases de données relationnelles open source ou en abrégé SGBDR. Il est sorti en 1995 (20 ans). Il utilise le langage de requête structuré qui est probablement le choix le plus populaire pour la gestion du contenu au sein d’une base de données. La dernière version de MySQL est la 5.6.25 et a été publiée le 29 mai 2015.
Un fait intéressant à propos de MySQL est le fait que le 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 a pour but de 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, un fork de la communauté MySQL appelé MariaDB a été créé. La raison principale de la création du fork était de garder le projet libre sous la licence publique générale.
Aujourd’hui, MySQL et MariaDB sont l’un des SGBDR les plus fréquemment utilisés (si ce n’est le plus) utilisé pour les applications Web telles que WordPress, Joomla, Magento et autres.
Cet article vous montrera quelques conseils de base, mais utiles, sur la façon d’optimiser les performances de MySQL/MariaDB. Veuillez garder à l’esprit que cet article suppose que MySQL ou MariaDB sont déjà installés. Si vous vous demandez toujours comment les installer sur votre système, vous pouvez suivre nos guides détaillés ici :
- Installation de LAMP sur RHEL/CentOS 7
- Installation de LAMP sur Fedora 22
- Configuration de LAMP sur Ubuntu 15.04
- Installation de MariaDB sur Debian 8
- Installer MariaDB sur Gentoo Linux
- Installer MariaDB sur Arch Linux
Important : Avant de commencer, n’acceptez pas ces suggestions aveuglément. Chaque configuration MySQL est unique et nécessite une réflexion supplémentaire avant d’apporter des modifications.
Ce 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 puissent prendre effet. - Pour la rédaction de cet article, la version 5.6 de MySQL a été utilisée comme modèle.
1. Activer le fichier InnoDB par table
Tout d’abord, il est important d’expliquer qu’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 table système. Cette approche était destinée aux serveurs dont le seul but est le traitement de bases de données et dont le disque de stockage n’est pas utilisé à d’autres fins.
L’InnoDB offre une approche plus flexible et chaque information de base de données est conservée dans un fichier de données .ibd. Chaque fichier .ibd représente un espace table qui lui est propre. De cette façon, les opérations de base de données telles que « TRUNCATE » peuvent être effectuées plus rapidement et vous pouvez également récupérer l’espace inutilisé lors de la suppression ou de la troncature d’une table de base de données.
Un autre avantage de cette configuration est le fait que vous pouvez conserver certaines tables de base de données dans un périphérique de stockage distinct. Cela peut considérablement améliorer la charge d’E/S sur vos disques.
Le innodb_file_per_table est activé par défaut dans MySQL 5.6 et supérieur. Vous pouvez le voir 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 séparée
Remarque : Cette configuration ne fonctionne qu’avec MySQL, mais pas avec MariaDB.
Parfois, les lectures/écritures du système d’exploitation peuvent ralentir les performances de votre serveur MySQL, surtout s’il se trouve sur le même disque dur. Au lieu de cela, je recommanderais d’utiliser un disque dur séparé (de préférence SSD) pour le service MySQL.
Pour ce faire, vous devrez connecter le nouveau disque à votre ordinateur/serveur. Pour les besoins de cet article, je supposerai que le lecteur sera sous /dev/sdb.
L’étape suivante consiste à préparer le nouveau disque :
# fdisk /dev/sdb
Appuyez maintenant sur « n » pour créer une nouvelle partition. Appuyez ensuite sur « p » pour rendre la nouvelle partition primaire. Après cela, définissez le numéro de partition de 1 à 4. Après cela, vous sélectionnerez la taille de la partition. Appuyez sur Entrée ici. À l’étape suivante, vous devrez configurer la taille de la partition.
Si vous souhaitez utiliser l’intégralité du disque, appuyez à nouveau sur Entrée. Sinon, vous pouvez définir manuellement la taille de la nouvelle partition. Lorsque vous êtes prêt, appuyez sur « w » pour écrire les modifications. Maintenant, nous allons devoir créer un système de fichiers pour notre nouvelle partition. Cela peut être facilement fait avec :
# mkfs.ext4 /dev/sdb1
Maintenant, nous allons monter notre nouvelle partition dans un dossier. J’ai nommé mon dossier « ssd » et créé dans le répertoire racine :
# mkdir /ssd/
Nous sommes prêts à monter la nouvelle partition que nous venons de faire 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
Vous êtes maintenant prêt à déplacer MySQL vers le nouveau disque. Arrêtez d’abord le service MySQL avec :
# service mysqld stop
Je vous recommande également d’arrêter Apache/nginx pour éviter toute tentative d’écriture dans les bases de données :
# service httpd stop
service nginx stop
Copiez maintenant l’intégralité du répertoire MySQL dans le nouveau lecteur :
# cp /var/lib/mysql /ssd/ -Rp
Cela peut prendre un certain temps en fonction du site 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 allons créer un lien symbolique.
# ln -s /ssd/mysql /var/lib/mysql
Vous êtes maintenant prêt à démarrer votre 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.