Comment optimiser le serveur de base de données MYSQL avec un iowait élevé

Un cas d'optimisation iowait élevé d'un serveur de base de données

1. Le retour d'information du développement indique que SQL dans un certain environnement de test s'exécute lentement, tandis que dans d'autres environnements de test, SQL s'exécute très rapidement. Les deux environnements ont la même configuration, et seul le serveur mysql est déployé.

2. Exécutez la commande top et constatez que l'iowait du disque sur la machine avec SQL lent est beaucoup plus élevé que celui de la machine avec SQL rapide. On suppose que c'est la principale raison du lent fonctionnement de SQL, car SQL doit lire une table, la table est grande et le nombre de lignes à analyser est grand.

3. Qu'est-ce qui fait que l'iowait de la machine est élevé ? Lors de l'exécution de iotop, il s'avère que le processus qui consomme io est principalement MySQL, et qu'il s'agit principalement de l'opération de lecture sur MySQL.

4. Il doit y avoir d'autres instructions de requête à haute fréquence qui interrogent constamment les données d'une grande table, et l'index ne peut pas être utilisé pendant la requête. Le nombre de lignes de la table à analyser est important, ce qui entraîne. opérations io à haute fréquence, ce qui entraîne une exécution lente des autres SQL nécessitant des opérations io.

5. Quel SQL en est la cause ? J'ai activé le journal général et j'ai constaté qu'il y avait trop d'instructions collectées et que le SQL à surcharge élevée ne pouvait pas être facilement localisé.

6. Activez le journal lent, définissez long_query_time sur 1 pour capturer les requêtes lentes et utilisez pt-ioprofile pour suivre quels fichiers du fichier de données MySQL consomment plus d'E/S.

7. Sur la base des résultats du journal lent (qui peut être agrégé à l'aide de pt-query-digest) et de pt-ioprofile, il s'avère qu'il existe effectivement deux instructions SQL typiques qui doivent analyser la table entière. et la table correspondante est très volumineuse et est fréquemment exécutée, ce qui conduit à des E/S disque élevées.

8. Ensuite, le problème restant est d'optimiser la table ou la requête. Le moyen le plus simple et le plus direct consiste à améliorer les performances des requêtes en créant des index appropriés et en réduisant le nombre de lignes analysées dans la table. Si vous devez continuer à optimiser les performances, vous pouvez résoudre le problème en optimisant la méthode d'écriture de SQL et en l'ajustant. la structure de la table et l'ajustement de la configuration des paramètres.

9. Commencez par la méthode la plus rentable, évaluez d'abord l'instruction SQL, vérifiez la distribution des données de chaque champ en fonction des conditions de l'instruction et évaluez le caractère raisonnable de la création d'un index ou d'une multi-colonne. index commun sur le terrain en expliquant les propriétés, etc. et en créant des index appropriés.

10. Enfin, il a été constaté qu'après la construction de l'index, les instructions d'origine qui devaient analyser la table entière pouvaient réduire efficacement le nombre de lignes analysées dans l'index, puis les opérations d'E/S étaient réduites. Le sujet iowait du serveur, le retour d'origine du SQL plus lent. La vitesse d'exécution a été améliorée, mais elle n'est toujours pas idéale.

11. Enfin, en construisant un index sur la table correspondant à l'instruction lente et en corrigeant le mauvais type de valeur utilisé dans la condition Where, la vitesse d'exécution de l'instruction SQL a été grandement améliorée et la consommation globale des E/S. du serveur a été considérablement réduit.

12. Vous pouvez utiliser pt-query-digest pour agréger le journal lent généré par le serveur mysql optimisé et utiliser pt-ioprofile pour analyser l'occupation io du fichier de données mysql optimisé afin de comprendre la différence avant et après. optimisation.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!