Comment améliorer les performances du serveur MySQL avec une optimisation appropriée

5. Configuration de la mémoire pour le système d’exploitation

Calcul de la mémoire requise pour le système d’exploitation

Avant d’allouer de la mémoire à MySQL, il est important de s’assurer que le système d’exploitation (OS) dispose de suffisamment de mémoire pour effectuer ses tâches de base. Si le système d’exploitation manque de mémoire, cela peut entraîner de graves problèmes de performances, notamment l’échange de mémoire sur le disque, ce qui peut ralentir considérablement les performances de MySQL.

Pour calculer la mémoire requise pour le système d’exploitation, tenez compte des facteurs suivants :

Mémoire de base du système d’exploitation :

Le système d’exploitation nécessite une quantité de mémoire de base pour exécuter les processus et services de base. Sur les systèmes Linux, cela varie généralement de 200 Mo à 1 Go, selon la distribution et la configuration.

Processus et services supplémentaires :

Si le serveur exécute des services supplémentaires tels que des serveurs Web (Apache/Nginx), des serveurs d’applications ou des services de surveillance, de la mémoire supplémentaire doit leur être allouée.

Tampon et cache du système d’exploitation :

Les systèmes d’exploitation utilisent des tampons et des caches pour améliorer les performances d’E/S. Par exemple, Linux utilise la mise en cache des fichiers pour stocker les données fréquemment consultées. Cela nécessite suffisamment de mémoire supplémentaire.

En général, une bonne règle de base est de laisser environ 20 à 25 % de la mémoire physique totale pour le système d’exploitation et d’autres services. Par exemple, sur un serveur avec 16 Go de RAM :

Memory for OS =0.20 × 16GB = 3.2GB

Éviter l’échange de mémoire virtuelle vers le disque

L’échange est le processus par lequel les données de la mémoire physique sont déplacées vers le disque lorsque la mémoire physique est pleine. Cela peut être très préjudiciable aux performances de MySQL, car l’accès au disque est beaucoup plus lent que l’accès à la mémoire. Pour éviter l’échange, les étapes suivantes peuvent être prises :

Ajustement de l’échange :

Sur les systèmes Linux, le paramètre swappiness détermine l’agressivité avec laquelle le noyau utilisera les swaps. La valeur de swappiness peut être ajustée pour réduire l’utilisation des swaps. Des valeurs inférieures (par exemple, 10) réduisent la tendance du système à utiliser des swaps :

sudo sysctl vm.swappiness=10

Pour rendre ces modifications permanentes, ajoutez-les à /etc/sysctl.conf :

vm.swappiness=10

Surveillance de l’utilisation de la mémoire :

Utilisez des outils de surveillance pour garder un œil sur l’utilisation et les échanges de la mémoire système. Des outils comme htop, free ou la surveillance graphique comme Grafana peuvent aider à surveiller l’utilisation de la mémoire en temps réel.

Allouer judicieusement de la mémoire pour MySQL :

Assurez-vous que la configuration MySQL n’utilise pas toute la mémoire physique. Laissez suffisamment de mémoire pour le système d’exploitation et d’autres services. Par exemple, si vous disposez de 16 Go de RAM et que vous avez calculé que le système d’exploitation a besoin d’environ 3,2 Go, allouez environ 12 à 13 Go à MySQL.

Optimisation de l’utilisation de la mémoire MySQL :

Comme expliqué précédemment, ajustez les paramètres de mémoire MySQL tels que innodb_buffer_pool_size, key_buffer_size et query_cache_size pour vous assurer que l’utilisation totale de la mémoire MySQL ne dépasse pas la mémoire disponible.

6. Réglage de la mémoire pour le cache

Le cache est un composant important de MySQL qui permet d’améliorer les performances en stockant les données fréquemment consultées en mémoire, réduisant ainsi le besoin d’accéder à des disques plus lents. En utilisant efficacement la mise en cache, vous pouvez améliorer la vitesse des requêtes et réduire la charge sur le système de stockage. Les deux principaux types de caches utilisés dans MySQL sont le cache MyISAM et le cache InnoDB.

Configuration du cache pour MyISAM et InnoDB

MyISAM Cache

MyISAM est un ancien moteur de stockage MySQL et utilise key_buffer_size comme paramètre principal pour définir la taille de son cache d’index. Ce paramètre est très important, car les index mis en cache sont accessibles beaucoup plus rapidement que les index qui doivent être lus à partir du disque.

key_buffer_size : spécifie la taille du cache pour l’index MyISAM. Si cette taille est trop petite, vous pouvez ralentir fréquemment l’accès au disque, tandis que si vous la définissez sur une taille trop grande, vous pouvez laisser un peu de mémoire pour d’autres besoins. En règle générale, key_buffer_size doit être défini entre 25 et 30 % de la mémoire physique totale sur un serveur qui utilise uniquement MyISAM.

Exemples de paramètres dans le fichier de configuration MySQL (my.cnf) :

[mysqld]

key_buffer_size = 4G

InnoDB Cache

InnoDB est le moteur de stockage par défaut et le plus utilisé dans MySQL. InnoDB utilise innodb_buffer_pool_size pour configurer le cache principal qui stocke les données et les index des tables InnoDB.

innodb_buffer_pool_size : spécifie la taille du pool de mémoires tampons utilisé pour stocker les données et les index. Ce paramètre est essentiel pour les performances d’InnoDB, car les données fréquemment consultées sont stockées dans un pool de mémoires tampons, ce qui réduit l’accès au disque. En règle générale, innodb_buffer_pool_size doit être défini entre 60 et 80 % de la mémoire physique totale sur un serveur qui utilise uniquement InnoDB.

Exemples de paramètres dans le fichier de configuration MySQL (my.cnf) :

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances : Si le pool de mémoires tampons est volumineux (plus de 1 Go), vous pouvez le diviser en plusieurs instances pour augmenter la simultanéité. Par exemple, si innodb_buffer_pool_size est défini sur 12 Go, vous pouvez le diviser en 4 instances de 3 Go chacune :

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances =4

Cache de requêtes (général)

MySQL dispose également d’un cache de requêtes qui stocke les résultats des requêtes fréquemment répétées afin d’accélérer le temps d’exécution des requêtes suivantes. Cependant, dans la dernière version, le cache de requête est considéré comme obsolète et a été supprimé dans MySQL 8.0 en raison de problèmes de performances dans les environnements avec beaucoup d’écritures.

query_cache_size : Si vous utilisez une ancienne version de MySQL, vous pouvez définir la taille de la requête de cache avec ce paramètre. Toutefois, nous vous recommandons d’utiliser la mise en cache au niveau de l’application ou des proxys de cache pour une meilleure gestion.

[mysqld]

query_cache_size = 256M