15 conseils utiles pour le réglage et l'optimisation des performances MySQL/MariaDB

Cet article vous montrera quelques conseils basiques mais utiles pour 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 :

1. Installation de LAMP sur RHEL/CentOS 7
2. Installer LAMP sur Fedora 22
3. Configuration de LAMP sur Ubuntu 15.04
4. Installer MariaDB sur Debian 8
5. Installer MariaDB sur Gentoo Linux
6. Installer MariaDB sur Arch Linux

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

Choses que vous devez savoir :

1. 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.
2. Pour rédiger cet article, la version MySQL 5.6 a été utilisée comme modèle.

1. Activer le fichier par table InnoDB

Il est d’abord important d’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 objectif est le traitement de la base de données et leur disque de stockage n'est utilisé à aucune autre fin.

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 son propre tablespace. 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 de 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 sur un périphérique de stockage distinct. Cela peut grandement améliorer la charge 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. Stockez 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 terminer, vous devrez connecter le nouveau disque à votre ordinateur/serveur. Pour les besoins de cet article, je suppose 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 principale. Après cela, définissez le numéro de partition entre 1 et 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. Nous devrons maintenant créer un système de fichiers pour notre nouvelle partition. Cela peut être facilement réalisé avec :

mkfs.ext4 /dev/sdb1

Nous allons maintenant 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 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

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 empêcher 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 selon le 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 lecteur.