Optimiser les performances et gérer les bases de données dans Azure Database pour MySQL - Serveur flexible à l’aide du sys_schema

S’APPLIQUE À : Azure Database pour MySQL - Serveur unique Azure Database pour MySQL - Serveur flexible

Important

Azure Database pour MySQL serveur unique se trouve sur le chemin de mise hors service. Nous vous recommandons vivement de procéder à la mise à niveau vers Azure Database pour MySQL serveur flexible. Pour plus d’informations sur la migration vers Azure Database pour MySQL serveur flexible, consultez Ce qui se passe pour Azure Database pour MySQL serveur unique ?

Le performance_schema MySQL, qui a été introduit dans MySQL 5.5, fournit l’instrumentation pour bon nombre de ressources serveur vitales, telles que l’allocation de mémoire, les programmes stockés, le verrouillage des métadonnées, etc. Or, le performance_schema contient plus de 80 tables et, pour obtenir les informations nécessaires, il faut souvent joindre les tables du performance_schema et les tables d’information_schema. À la fois sur les performance_schema et les information_schema, le sys_schema fournit une collection puissante de vues conviviales dans une base de données en lecture seule et est entièrement activée dans Azure Database pour MySQL serveur flexible version 5.7.

Il existe 52 vues dans le sys_schema, et chaque vue présente l’un des préfixes suivants :

• Host_summary ou IO : latences liées aux E/S.
• InnoDB : état de la mémoire tampon et verrous InnoDB.
• Mémoire : utilisation de la mémoire par l’hôte et les utilisateurs.
• Schéma : informations relatives au schéma, telles que l’incrémentation automatique, les index, etc.
• Statement : informations sur les instructions SQL ; il peut s’agir d’une instruction qui a entraîné une analyse de table complète ou une durée de requête longue.
• Utilisateur : ressources consommées et regroupées par utilisateur. Il peut s’agir par exemple d’E/S de fichiers, de connexions ou de mémoire.
• Wait : événements d’attente regroupés par hôte ou utilisateur.

À présent, intéressons-nous à quelques modèles d’utilisation courants de sys_schema. Pour commencer, nous allons regrouper les modèles d’utilisation dans deux catégories : réglage des performances et maintenance de base de données.

Réglage des performances

sys.user_summary_by_file_io

Les E/S représentent l’opération la plus coûteuse de la base de données. Nous pouvons déterminer la latence moyenne des E/S en interrogeant la vue sys.user_summary_by_file_io. Avec par défaut 125 Go de stockage provisionné, la latence des E/S est d’environ 15 secondes.

Étant donné que Azure Database pour MySQL serveur flexible met à l’échelle les E/S en ce qui concerne le stockage, après avoir augmenté mon stockage approvisionné à 1 To, ma latence d’E/S est réduite à 571 ms.

sys.schema_tables_with_full_table_scans

En dépit d’une planification minutieuse, de nombreuses requêtes peuvent donner lieu à des analyses de table complète. Pour plus d’informations sur les types d’index et sur la façon de les optimiser, vous pouvez consulter cet article : Comment résoudre les problèmes de performances des requêtes. Les analyses de table complète sont gourmandes en ressources et dégradent les performances de votre base de données. Le moyen le plus rapide de rechercher des tables avec une analyse de table complète est d’interroger la vue sys.schema_tables_with_full_table_scans.

sys.user_summary_by_statement_type

Pour résoudre les problèmes de performances de base de données, il peut être utile d’identifier les événements qui se produisent à l’intérieur de votre base de données, ce que permet la vue sys.user_summary_by_statement_type.

Dans cet exemple, Azure Database pour MySQL serveur flexible a passé 53 minutes à vider le journal des requêtes lentes 44579 fois. Cela représente beaucoup de temps et un grand nombre d’E/S. Vous pouvez réduire cette activité en désactivant votre journal des requêtes lentes ou en diminuant la fréquence de la journalisation des requêtes lentes dans le portail Azure.

Maintenance de base de données

sys.innodb_buffer_stats_by_table

[!IMPORTANT]

L’interrogation de cette vue peut avoir un impact sur les performances. Il est recommandé d’effectuer cette résolution des problèmes pendant les heures creuses.

Le pool de mémoires tampons InnoDB réside en mémoire et constitue le principal mécanisme de cache entre le SGBD et le stockage. La taille du pool de mémoires tampons InnoDB est liée au niveau de performances et ne peut pas être changée, à moins de choisir une autre référence SKU de produit. Comme pour la mémoire de votre système d’exploitation, les pages anciennes sont écartées pour faire place à des données plus récentes. Pour identifier les tables qui consomment la majeure partie de la mémoire du pool de mémoires tampons InnoDB, vous pouvez interroger la vue sys.innodb_buffer_stats_by_table.

Dans le schéma ci-dessus, il apparaît qu’en dehors des tables et vues système, chaque table de la base de données mysqldatabase033, qui héberge l’un de mes sites WordPress, occupe 16 Ko, soit 1 page, de données en mémoire.

Sys.schema_unused_indexes &sys.schema_redundant_indexes

Les index sont des outils efficaces pour améliorer les performances de lecture, mais ils induisent des coûts supplémentaires en termes d’insertions et de stockage. Sys.schema_unused_indexes et sys.schema_redundant_indexes donnent des indications sur les index non utilisés ou en double.

Conclusion

En résumé, sys_schema est un outil efficace à la fois pour le réglage des performances et la maintenance de base de données. Veillez à tirer parti de cette fonctionnalité dans votre instance de serveur flexible Azure Database pour MySQL.

Étapes suivantes

• Pour consulter les réponses d’homologues aux questions qui vous préoccupent le plus ou pour poster une nouvelle question/réponse, visitez Stack Overflow.