MySQL et MariaDB sont les systèmes de gestion de bases de données relationnelles (RDMS) les plus largement utilisés en matière d'hébergement de sites Web et de systèmes CMS tels que Joomla, WordPress, Drupal et Typo 3. Dans cet article, je vais vous expliquer comment accélérer et optimiser votre Serveur de base de données MySQL et MariaDB.
Stockez les données MySQL dans des partitions séparées
En matière d'optimisation et d'assurance, il est toujours préférable de stocker les données de la base de données dans un volume séparé. Les volumes sont spécifiquement destinés aux volumes de stockage rapides tels que SSD, NVMe. Même si votre système tombe en panne, votre base de données sera en sécurité. Comme le volume de partition est composé de volumes de stockage rapides, les performances seront plus rapides.
Définissez le nombre maximum de connexions MySQL
MySQL/MariaDB utilise une instruction max_connections qui spécifie le nombre de connexions simultanées actuellement autorisées sur le serveur. Trop de connexions entraînent une consommation de mémoire élevée ainsi qu'une charge élevée du processeur. Pour les petits sites Web, les connexions peuvent être spécifiées à 100-200 et les plus grandes peuvent nécessiter 500-800 et plus. Les max_connexions peut être modifié dynamiquement à l'aide de la requête SQL. Dans cet exemple, j'ai défini la valeur sur 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Sortie :
Activer le journal des requêtes lentes MySQL
La journalisation des requêtes qui prennent beaucoup de temps à s'exécuter facilite le dépannage des problèmes de base de données. Le journal des requêtes lentes peut être activé en ajoutant les lignes suivantes dans le fichier de configuration MySQL/MariaDB.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Où la première variable active le journal des requêtes lentes
La deuxième variable définit le répertoire du fichier journal
La troisième variable définit le temps nécessaire pour terminer une requête MySQL
Redémarrez le service mysql/mariadb et surveillez le journal
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Définir le paquet maximum autorisé par MySQL
Les données sont divisées en paquets dans MySQL. Max_allowed_packet définit la taille maximale des paquets pouvant être envoyés. Configurer le max_allowed_packet trop bas peut rendre la requête trop lente. Il est recommandé de définir la valeur du paquet sur la taille du plus grand paquet.
Configuration de la capacité de la table temporaire
Tmp_table_size est l'espace maximal utilisé pour la table de mémoire intégrée. Si la taille de la table dépasse la limite spécifiée, elle sera convertie en une table MyISAM sur le disque. Dans MySQL/MariaDB, vous pouvez ajouter les variables suivantes dans le fichier de configuration pour configurer la taille de la table temporaire. Il est recommandé de définir cette valeur sur le serveur 64M par Go de mémoire.
[mysqld] tmp_table_size=64M
Redémarrez le service mysql
$ systemctl restart mysql
$ systemctl restart mariadb
Configurez la capacité maximale de la table de mémoire.
Max_heap_table_size est la variable utilisée dans MySQL pour configurer la capacité maximale de la table mémoire. La taille de la capacité maximale de la table mémoire doit être la même que la capacité de la table temporaire pour éviter les écritures sur disque. Il est recommandé de définir cette valeur sur le serveur à 64 Mo par Go de mémoire. Ajoutez la ligne suivante dans le fichier de configuration MySQL et redémarrez le service.
[mysqld] max_heap_table_size=64M
Pour appliquer les modifications, redémarrez le serveur de base de données.
$ systemctl restart mysql
$ systemctl restart mariadb
Désactiver la recherche DNS inversée pour MySQL
Lorsqu'une nouvelle connexion est reçue, MySQL/MariaDB effectuera une recherche DNS pour résoudre l'adresse IP de l'utilisateur. Cela peut entraîner un délai lorsque la configuration DNS n'est pas valide ou qu'il y a un problème avec le serveur DNS. Pour désactiver la recherche DNS, ajoutez la ligne suivante dans le fichier de configuration MySQL et redémarrez le service MySQL.
[mysqld] skip-name-resolve
Redémarrez le service :
$ systemctl restart mysql
$ systemctl restart mariadb
Évitez d'utiliser Swappiness dans MySQL
Le noyau Linux déplace une partie de la mémoire vers une partition spéciale du disque appelée espace "swap" lorsque le système manque de mémoire physique. Dans cette condition, le système écrit des informations sur le disque plutôt que de libérer de la mémoire. Comme la mémoire système est plus rapide que le stockage sur disque, il est recommandé de désactiver la permutation. L'échange peut être désactivé à l'aide de la commande suivante.
$ sysctl -w vm.swappiness=0
Sortie :
Augmenter la taille du pool de tampons InnoDB
MySQL/MariaDB possède un moteur InnoDB qui dispose d'un pool de mémoire tampon pour mettre en cache et indexer les données dans la mémoire. Le pool de tampons aide les requêtes MySQL/MariaDB à être exécutées relativement plus rapidement. Le choix de la taille appropriée du pool de mémoire tampon InnoDB nécessite une certaine connaissance de la mémoire système. La meilleure idée est de définir la valeur de la taille du pool de mémoire tampon InnoDB à 80 % de la RAM.
Exemple.
- Mémoire système =4 Go
- Taille du pool de mémoire tampon =3,2 Go
Ajoutez la ligne suivante dans le fichier de configuration MySQL et redémarrez le service
[mysqld] Innodb_buffer_pool_size 3.2G
Redémarrez la base de données :
$ systemctl restart mysql
$ systemctl restart mariadb
Traitement de la taille du cache de requête
La directive de cache de requête dans MySQL/MariaDB est utilisée pour mettre en cache toutes les requêtes qui se répètent sans cesse avec les mêmes données. Il est recommandé de définir la valeur sur 64 Mo et d'augmenter le temps pour les petits sites Web. Il n'est pas recommandé d'augmenter la valeur de la taille du cache des requêtes en Go, car cela peut dégrader les performances de la base de données. Ajoutez la ligne suivante dans le fichier my.cnf.
[mysqld] query_cache_size=64M
Vérifier les connexions inactives
Les ressources sont consommées par les connexions inactives de sorte qu'elles doivent être interrompues ou actualisées si possible. Ces connexions restent dans l'état "veille" et peuvent rester pendant une longue période de temps. Vérifiez les connexions inactives à l'aide de la commande suivante.
$ mysqladmin processlist -u root -p | grep “Sleep”
La requête répertorie les processus qui sont en état de veille. Généralement en PHP, l'événement peut se produire lors de l'utilisation de mysql_pconnect. Cela ouvre la connexion MySQL, exécute les requêtes, supprime les authentifications et laisse la connexion ouverte. Utiliser wait_timeout directive, les connexions inactives peuvent être interrompues. La valeur par défaut pour wait_timeout est de 28800 secondes qui peuvent être réduites à une plage de temps minimale comme 60 secondes. Ajoutez la ligne suivante dans le fichier my.cnf
[mysqld] wait_timeout=60
Optimiser et réparer la base de données MySQL
Si le serveur est arrêté de manière inattendue, il est possible que les tables de MySQL/MariaDB se bloquent. Il existe d'autres raisons possibles pour obtenir le plantage de la table de base de données, comme l'accès à la base de données pendant que le processus de copie est en cours d'exécution, le système de fichiers est soudainement planté. Dans cette situation, nous avons un outil spécial appelé "mysqlcheck ” qui vérifie, répare et optimise toutes les tables des bases de données.
Utilisez la commande suivante pour effectuer les activités de réparation et d'optimisation.
Pour toutes les bases de données :
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Pour une base de données spécifique :
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Remplacez dbname par le nom de votre base de données
- Vérifiez les performances de MySQL/MariaDB à l'aide d'outils de test
Il est recommandé de vérifier régulièrement les performances de la base de données MySQL/MariaDB. Cela facilitera l'obtention du rapport de performance et du point d'amélioration. Il existe de nombreux outils disponibles parmi lesquels mysqltuner est le meilleur.
Exécutez la commande suivante pour télécharger l'outil
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Décompressez le fichier
$ tar xvzf master
Accédez au répertoire du projet et exécutez le script suivant.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Sortie :
Conclusion
Dans cet article, nous avons appris à optimiser MySQL/MariaDB en utilisant différentes techniques. Merci d'avoir lu.