Optimisation des performances MySQL/MariaDB avec Mysqltuner

MySQL & MariaDB sont des systèmes de gestion de bases de données relationnelles open source les plus populaires. Il alimente bon nombre des services Web et des applications les plus visités au monde. À mesure que l’utilisation augmente, l’optimisation des performances devient essentielle pour optimiser MySQL et éviter des problèmes comme les requêtes lentes.

Mysqltuner est un script Perl open source qui aide à analyser et à optimiser les performances de MySQL. Il fournit des recommandations de configuration et de performances pour optimiser MySQL. Dans ce guide complet, nous couvrirons :

Présentation de mysqltuner

mysqltuner est un script Perl écrit et maintenu par Major Hayden. Il se connecte à MySQL, exécute des diagnostics et fournit des conseils de configuration et d’optimisation pour améliorer les performances.

Les principales fonctionnalités de mysqltuner incluent :

• Diagnostiquer rapidement les problèmes de performances MySQL
• Suggérer des modifications de configuration pour de meilleures performances
• Recommander des mises à niveau matérielles pour les grandes installations
• Surveiller la mémoire, les tables temporaires, le taux de succès du cache de tables, les fichiers ouverts et d’autres mesures
• Estimer les tendances de croissance à l’aide de statistiques historiques
• Générer un rapport avec toutes les mesures et tous les conseils

mysqltuner est facile à utiliser et fournit des recommandations claires et exploitables. C’est un outil précieux pour la surveillance et l’optimisation des performances MySQL.

Installation de mysqltuner

mysqltuner est un script Perl autonome. Pour l’installer :

• Installez Perl s’il n’est pas déjà installé :

$ sudo apt install perl

• Téléchargez le dernier script mysqltuner :

$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

• Rendez le script exécutable :

$ chmod +x mysqltuner.pl 

• Renommez et déplacez éventuellement le script :

$ sudo mv mysqltuner.pl /usr/local/bin/mysqltuner

Mysqltuner est maintenant installé et prêt à être utilisé !

Utiliser mysqltuner pour optimiser les performances

L’utilisation de mysqltuner est simple. Exécutez simplement le script avec vos informations d’identification MySQL :

$ mysqltuner --host localhost --user root --pass password

Cela se connectera au serveur MySQL sur localhost en tant qu’utilisateur root, exécutera des diagnostics et imprimera un rapport.

Pour enregistrer le rapport dans un fichier :

$ mysqltuner --host localhost --user root --pass password > mysqlreport.txt

Les principales options incluent :

• --host – Nom d’hôte MySQL
• --user – Nom d’utilisateur MySQL
• --pass – Mot de passe MySQL
• --silent – Masquer les points de progression pendant le traitement
• --nobad – Supprimer les mauvais conseils du rapport final
• --nogood – Supprimer les bons conseils du rapport final
• --forcemem – Quantité de RAM installée en mégaoctets

Consultez le rapport pour obtenir des conseils sur les performances et appliquez les recommandations. Exécutez à nouveau mysqltuner périodiquement pour surveiller les modifications au fil du temps.

Interpréter les recommandations mysqltuner

Le rapport mysqltuner contient de nombreuses mesures et suggestions. Voici quelques domaines clés sur lesquels se concentrer :

Mesures de performance

• Uptime – Durée pendant laquelle MySQL a fonctionné sans interruption. Des uptimes longs indiquent une stabilité.
• Total buffers – Mémoire totale allouée pour mettre en cache les index et les tables. Plus c’est élevé, mieux c’est.
• Fichiers ouverts – Nombre de fichiers ouverts. De nombreux fichiers ouverts peuvent indiquer des problèmes d’index.
• Taux de succès du cache de tables – Recherchez un taux de succès élevé, idéalement supérieur à 99 %. Des taux inférieurs indiquent que des réglages sont nécessaires.

Suggestions de configuration

• Utilisation maximale de la mémoire – Augmenter si la mémoire disponible le permet pour améliorer les performances.
• Taille du tampon de clés – Augmenter si possible pour améliorer l’utilisation des index.
• Cache de tables – Augmenter pour éviter les limites d’ouverture de fichier.
• Taille du cache de threads – Augmenter pour éviter les frais généraux de création de thread.

Suggestions matérielles

• RAM – Ajouter plus de RAM si nécessaire pour supporter des tampons de mémoire supplémentaires.
• CPU – Réduire la charge CPU ou ajouter des cœurs si l’utilisation du processeur est élevée.
• Stockage – Un stockage rapide aide à améliorer les performances E/S.

Performance des requêtes

• Requêtes lentes – Optimiser les requêtes coûteuses et ajouter des index si nécessaire.
• Tables temporaires – Un nombre élevé de tables temporaires peut indiquer des index sous-optimaux.

Mettre en œuvre les recommandations mysqltuner

Voici quelques étapes pour agir sur les recommandations mysqltuner et optimiser les performances MySQL :

1. Ajuster l’allocation de mémoire – Si plus de RAM est disponible, augmenter la mémoire tampon de clés, la taille du cache de requêtes, la mémoire tampon de pool innodb et le cache de tables. Cela permet à MySQL de conserver plus d’index et de données en mémoire.
2. Optimiser les requêtes lentes – Examiner les requêtes lentes, ajouter des index et optimiser les jointures ou sous-requêtes coûteuses. Cela améliore considérablement le débit global.
3. Améliorer les index – Inspecter l’utilisation des index avec des outils comme EXPLAIN. Ajouter les index nécessaires et supprimer les inutilisés. Porter une attention particulière à la taille du tampon de clés.
4. Optimisation de la concurrence – Optimiser la taille du cache de threads, les connexions maximales, les threads de travail et d’autres paramètres de concurrence. Trouver les valeurs optimales.
5. Optimisation du stockage – S’assurer que le système de stockage dispose d’un débit E/S et d’une bande passante adéquats pour la charge de travail. Le stockage SSD offre d’énormes gains.
6. Dimensionnement du serveur – Dimensionner correctement les ressources du serveur comme les cœurs CPU, la RAM, le stockage à mesure que la base de données augmente. Surveiller les tendances de croissance.
7. Bonnes pratiques de configuration – Examiner les paramètres par rapport aux valeurs recommandées comme le cache de tables, la taille du tampon de jointure, la taille du cache de requêtes, etc.
8. Examen périodique – Exécuter mysqltuner toutes les quelques semaines. Mettre en œuvre des recommandations supplémentaires selon les besoins.

Avec un réglage diligent guidé par mysqltuner, vous pouvez optimiser MySQL pour gérer des charges de travail élevées et offrir des performances maximales. La clé est de passer régulièrement en revue les mesures et d’appliquer progressivement les recommandations au fil du temps.

Explication du script MySQLTuner

Le script mysqltuner.pl est bien structuré et largement commenté. Parcourons les principales sections pour comprendre ce qu’il fait sous le capot :

Connexion initiale

• Importe des modules pour les connexions de base de données, la mise en forme de texte, les statistiques
• Traite les arguments de ligne de commande
• Établit la connexion à la base de données à l’aide des informations d’identification fournies

Collecte de données

• Initialise les variables pour les calculs
• Exécute les instructions SHOW pour collecter la configuration
• Exécute des requêtes sur INFORMATION_SCHEMA pour les mesures
• Calcule les ratios, les succès, les échecs, les totaux sur les mesures

Analyse et création de rapports

• Vérifie les mesures par rapport aux seuils recommandés
• Compare le rapport mysqltuner actuel et précédent s’il existe
• Imprime les variables de configuration et les mesures de table
• Fournit des suggestions matérielles, de configuration, d’indexation
• Met en forme la sortie du rapport et met en évidence les problèmes

Nettoyage de la connexion

• Ferme la connexion à la base de données
• Quitte proprement

Cela montre le flux de base – se connecter, collecter des données, analyser, imprimer un rapport, se déconnecter. L’essentiel du travail est effectué par les sections de collecte de données et d’analyse. MySQLTuner s’appuie sur les commandes SHOW et les requêtes INFORMATION_SCHEMA pour obtenir les valeurs de configuration et les mesures de performance.

Différentes façons d’utiliser MySQLTuner

En plus du réglage ad hoc, voici d’autres bonnes façons de tirer parti de mysqltuner :

Tests d’application – Exécuter mysqltuner sur les environnements de développement, intermédiaires et de production pour comparer les configurations. Optimiser finement chaque environnement.

Nouveau serveur de base de données – Créer un rapport de référence après avoir mis en service un nouveau serveur de base de données. À utiliser pour le dimensionnement et l’optimisation initiale.

Tendances de performance – Exécuter mysqltuner au fil du temps et enregistrer les rapports. Vérifier l’évolution à mesure que la base de données augmente.

Après la mise à niveau – Quelle est la performance après une mise à niveau MySQL ou du système d’exploitation ? Exécuter mysqltuner pour vérifier.

Migration vers le cloud – Migration du bare metal vers le cloud ? Un rapport mysqltuner aide à dimensionner correctement l’instance.

Planification de capacité – Planification d’une expansion du stockage, de la mémoire ou du calcul ? Mysqltuner indique où les ressources sont nécessaires.

Diagnostics – Si MySQL est lent, exécutez mysqltuner pour aider à découvrir les goulots d’étranglement.

Ces exemples démontrent l’applicabilité étendue de mysqltuner pour divers scénarios de surveillance et d’optimisation des performances.

Outils d’optimisation alternatifs

Bien que mysqltuner soit facile à utiliser et complet, il existe d’autres outils d’optimisation MySQL à envisager :

• Percona Toolkit – Boîte à outils open source avancée avec pt-mysql-summary et pt-query-digest.
• MySQL Enterprise Monitor – Tableau de bord basé sur une interface graphique pour les mesures et les conseillers.
• MySQL Query Analyzer – Suivi des performances des requêtes au fil du temps.
• EXPLAIN ANALYZE – Statistiques d’optimiseur pour choisir de meilleurs plans de requêtes.
• Performance Schema – Données de performance MySQL très détaillées.

Mysqltuner constitue un excellent point de départ grâce à sa facilité d’utilisation et ses résultats clairs. Complétez-le avec d’autres outils offrant des capacités d’approfondissement pour un réglage avancé.

Conclusion

mysqltuner est un outil inestimable pour surveiller et optimiser les performances MySQL. Il diagnostique rapidement les problèmes et propose des suggestions claires et exploitables pour l’amélioration.

Suivre les conseils de mysqltuner peut aider à résoudre ou à éviter de nombreux écueils de performances MySQL courants. C’est un outil essentiel pour tout administrateur de bases de données ou développeur d’applications utilisant MySQL en production.

L’utilisation cohérente de mysqltuner et d’autres outils complémentaires permettra de maximiser l’efficacité de la base de données, de réduire les coûts d’infrastructure et d’offrir la meilleure expérience aux applications et à leurs utilisateurs.