Une référence d'optimisation MySQL plus complète

Cet article a compilé quelques méthodes générales d'optimisation pour MySQL et a fait un résumé simple à partager, dans le but d'aider les entreprises qui ne disposent pas d'un administrateur de base de données MySQL à plein temps à effectuer le travail d'optimisation de base. Quant à l'optimisation SQL spécifique, la plupart des choses. ils sont réalisés en ajoutant des index appropriés. L'effet peut être obtenu, et les plus complexes nécessitent une analyse détaillée.

1. Optimisation liée à la couche matérielle

1.1. Liée au processeur

Dans les paramètres du BIOS du serveur, vous pouvez ajuster le Plusieurs configurations suivantes sont conçues pour maximiser les performances du processeur ou éviter les problèmes NUMA classiques :

1. Sélectionnez le mode Performance par watt optimisé (DAPC) pour maximiser les performances du processeur. L'exécution de la base de données nécessite généralement un calcul élevé. Si vous utilisez un grand nombre de services, n'envisagez pas d'économie d'énergie ;

2. Désactivez les options telles que les états C1E et C pour améliorer l'efficacité du processeur

3. Sélectionnez Performances maximales pour ; Fréquence de la mémoire (meilleures performances) ;

4. Dans le menu des paramètres de mémoire, activez l'entrelacement des nœuds pour éviter les problèmes NUMA

1.2 liés aux E/S du disque

Ce qui suit : sont triés en fonction de l'ampleur de l'amélioration des performances IOPS, certaines mesures qui peuvent être optimisées pour les E/S disque :

1. Utilisez des périphériques SSD ou PCIe SSD pour obtenir au moins des centaines, voire des dizaines de milliers de fois l'amélioration des IOPS ;

2. L'achat d'une carte réseau équipée à la fois de modules CACHE et BBU peut augmenter considérablement les IOPS (se réfère principalement aux disques mécaniques, à l'exception des SSD ou SSD PCIe. Dans le même temps, il est nécessaire de vérifier régulièrement l'état de santé état des modules CACHE et BBU pour garantir que les données ne seront pas perdues en cas d'accident );

3. Lorsqu'il y a une carte réseau, définissez la stratégie d'écriture du tableau sur WB, ou même FORCE WB (s'il y a s'il s'agit d'une double protection d'alimentation, ou si les exigences en matière de sécurité des données ne sont pas particulièrement élevées), il est strictement interdit d'utiliser la stratégie WT. Et la stratégie de lecture anticipée de matrice fermée est fondamentalement inutile et de peu d'utilité

4. Utilisez autant que possible RAID-10 au lieu de RAID-5

5. Si vous utilisez un ; disque mécanique, essayez autant que possible de choisir un disque à grande vitesse, par exemple, choisissez un disque 15KRPM au lieu d'un disque 7,2KRPM, ce qui ne vaut pas quelques dollars

2. Lié à la couche système ; optimisation

2.1. Optimisation de la couche du système de fichiers

Au niveau de la couche du système de fichiers, les mesures suivantes peuvent améliorer considérablement les performances des IOPS :

1. Utiliser le délai des deux planificateurs d'E/S /noop, et n'utilisez jamais cfq (cela ne convient pas pour exécuter des services de base de données)

2. Utilisez le système de fichiers xfs, n'utilisez jamais ext3 est à peine disponible, mais si le volume d'activité est important, vous devez utiliser xfs;

3. Ajout de plusieurs options aux paramètres de montage du système de fichiers : noatime, nodiratime, nobarrier (nobarrier est unique au système de fichiers xfs)

　2.2. 🎜>

Définir les paramètres clés du noyau appropriés. Le but de cette valeur est de réduire la tendance au swap et d'éviter de grandes fluctuations de la mémoire et des E/S du disque, entraînant des pics de charge instantanés :

1. Définir la machine virtuelle .swappiness à environ 5-10, ou même réglé sur 0 (veillez à le régler sur 0 pour RHEL 7 et supérieur, à moins que vous n'autorisiez la suppression du MOO) pour réduire les risques d'utilisation de SWAP

　2 ; . Définissez vm.dirty_background_ratio sur 5-10, et vm .dirty_ratio est défini sur environ le double pour garantir que les données sales peuvent être continuellement vidées sur le disque afin d'éviter les écritures d'E/S instantanées et les attentes importantes (similaire à innodb_max_dirty_pages_pct dans MySQL) ;

3. Définissez net .ipv4.tcp_tw_recycle et net.ipv4.tcp_tw_reuse sont tous deux définis sur 1 pour réduire TIME_WAIT et améliorer l'efficacité de TCP

4. Quant aux deux paramètres de read_ahead_kb et nr_requests ; transmis sur le réseau, après test, j'ai trouvé que l'impact de lecture de l'environnement OLTP mixte en écriture n'est pas grand (il devrait être plus efficace dans les scénarios sensibles en lecture), mais il peut y avoir un problème avec ma méthode de test, vous peut décider de l'ajuster vous-même ;

3. Optimisation liée à la couche MySQL

3.1 À propos de la sélection de version

Nous appelons la version officielle ORACLE MySQL Il n'y a rien à dire. à ce sujet. Je crois que la plupart des gens le choisiront.

Personnellement, je recommande fortement de choisir la version de la branche Percona. Il s'agit d'une version de branche MySQL relativement mature et excellente qui a apporté de nombreuses améliorations en termes de performances, de fiabilité et de gestion. Il est fondamentalement entièrement compatible avec la version officielle d'ORACLE MySQL et ses performances ont été améliorées d'environ 20 % ou plus, je le recommande donc en premier et je l'utilise depuis 2008.

Une autre version de branche importante est MariaDB. Il est en fait inapproprié de dire que MariaDB est une version de branche, car son objectif est de remplacer ORACLE MySQL. Il apporte principalement de nombreuses améliorations au niveau du code source sur la couche originale du serveur MySQL, et constitue également une version de branche très fiable et excellente. Cependant, cela a également entraîné de nouvelles fonctionnalités représentées par GTID qui sont incompatibles avec la version officielle (à partir de MySQL 5.7, le mode GTID est également pris en charge pour être activé ou désactivé dynamiquement en ligne, étant donné que la grande majorité des gens suivront toujours). la version officielle, par conséquent, MariaDB n'est pas recommandée en premier.

3.2. Suggestions pour ajuster les options des paramètres les plus importants

Il est recommandé d'ajuster les paramètres clés suivants pour obtenir de meilleures performances (vous pouvez utiliser le générateur my.cnf fourni par ce site pour générer le modèle de fichier de configuration) :

1. Si vous choisissez la version Percona ou MariaDB, il est fortement recommandé d'activer la fonctionnalité de pool de threads afin que les performances ne chutent pas de manière significative dans des conditions de concurrence élevée. De plus, il existe la fonction extra_port, très pratique et qui peut sauver des vies dans les moments critiques. Une autre fonctionnalité importante est la fonction QUERY_RESPONSE_TIME, qui nous permet également d'avoir une idée intuitive de la distribution globale du temps de réponse SQL

　2. Définissez default-storage-engine=InnoDB, ce qui signifie que le moteur InnoDB est utilisé par par défaut, ce qui est fortement recommandé de ne plus utiliser le moteur MyISAM. Le moteur InnoDB peut certainement répondre à plus de 99% des scénarios métier

3. Ajustez la taille de innodb_buffer_pool_size ; la plupart d'entre elles sont des tables du moteur InnoDB, pensez à le définir sur environ 50 % à 70 % de la mémoire physique

4. Définissez les valeurs de innodb_flush_log_at_trx_commit et sync_binlog en fonction des besoins réels. Si les données ne peuvent pas être perdues, définissez les deux sur 1. Si une petite perte de données est autorisée, ils peuvent être définis respectivement sur 2 et 10. Et s'il n'est pas nécessaire de se soucier de la perte des données (par exemple, sur l'esclave, elles seront refaites de toute façon), alors tout peut être mis à 0. Le degré dans lequel les performances de la base de données sont affectées par ces trois valeurs de paramètre est : élevé, moyen et faible, c'est-à-dire que la première rendra la base de données la plus lente et la dernière sera l'inverse

5. Définissez innodb_file_per_table = 1, En utilisant des espaces de table indépendants, je ne vois vraiment aucun avantage à utiliser des espaces de table partagés

6. Définissez innodb_data_file_path = ibdata1:1G:autoextend ; n'utilisez pas la valeur par défaut de 10 M, sinon la concurrence sera élevée.

7. Définissez innodb_log_file_size=256M et innodb_log_files_in_group=2, ce qui peut essentiellement répondre à plus de 90 % des scénarios ;

8. Définissez long_query_time = 1, dans la version 5.5 et supérieure, il peut être défini sur moins de 1. Il est recommandé de le définir sur 0,05 (50 millisecondes) pour enregistrer les SQL qui s'exécutent lentement pour une analyse et un dépannage ultérieurs ;

9. En fonction des besoins réels de l'entreprise, ajustez de manière appropriée max_connection (nombre maximum de connexions) et max_connection_error (nombre maximum d'erreurs). Il est recommandé de le définir à plus de 100 000, tandis que les paramètres open_files_limit, innodb_open_files, table_open_cache et table_definition_cache peuvent être définis sur environ 10 fois la taille de max_connection ;

10. Un malentendu courant est de définir tmp_table_size et max_heap_table_size relativement grands. Je les ai vus définis sur 1G. chaque session de connexion, donc ne les définissez pas trop grandes, sinon cela mènera facilement à MOO ; Certaines autres options au niveau de la session de connexion telles que : sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, etc., doivent également faire attention à ne pas définir. trop grand;

11. Puisqu'il a été recommandé de ne plus utiliser le moteur MyISAM, vous pouvez définir key_buffer_size Il est d'environ 32 Mo, et il est fortement recommandé de désactiver la fonction de cache de requêtes

3.3. Concernant les spécifications de conception de schéma et les suggestions d'utilisation de SQL

Vous trouverez ci-dessous plusieurs spécifications de conception de schéma courantes qui peuvent aider à améliorer l'efficacité de MySQL et les suggestions d'utilisation de SQL :

1. Toutes les tables InnoDB. sont conçus avec une colonne à incrémentation automatique qui n'a aucun objectif commercial comme clé primaire. Cela est vrai pour la plupart des scénarios. Il n'existe pas beaucoup de tables InnoDB qui sont véritablement en lecture seule, si tel est le cas, il est préférable de le faire. utilisez TokuDB ;

2. En partant du principe que la longueur du champ répond aux besoins, choisissez la plus petite longueur possible. De plus, essayez d'ajouter des contraintes NOT NULL aux attributs de champ, ce qui peut améliorer les performances dans une certaine mesure

3. Essayez de ne pas utiliser les types TEXT/BLOB autant que possible. Si nécessaire, il est recommandé de le faire. divisez-les en sous-tableaux et ne les fusionnez pas avec la table principale. Rassemblez les tableaux pour éviter de mauvaises performances de lecture lorsque SELECT * est exécuté.

4. Lors de la lecture des données, sélectionnez uniquement les colonnes dont vous avez besoin. Ne sélectionnez pas * à chaque fois pour éviter de graves problèmes de lecture aléatoire, en particulier lors de la lecture de certaines colonnes TEXTE/BLOB

6. Dans des circonstances normales, les performances de la sous-requête sont relativement médiocres et il est recommandé de la remplacer par la méthode d'écriture JOIN

8. Lors d'une requête avec une connexion multi-table, utilisez la table avec un petit ; ensemble de résultats (notez que cela fait référence à l'ensemble de résultats filtré, pas nécessairement à la petite quantité de données dans la table entière) comme table motrice

9. Lorsque plusieurs tables sont jointes et triées, le champ de tri doit ; être dans la table motrice, sinon la colonne de tri ne peut pas utiliser l'index

10. Utilisez plus d'index composés et utilisez moins de plusieurs index indépendants, en particulier Ne créez pas d'index indépendants pour certaines colonnes dont la cardinalité est trop petite ( par exemple, le nombre total de valeurs uniques de la colonne est inférieur à 255);

11. Pour SQL avec une fonction de pagination similaire, il est recommandé d'utiliser d'abord l'association de clé primaire, puis de renvoyer l'ensemble de résultats. , l'efficacité sera beaucoup plus élevée ;

3.4 Autres suggestions

D'autres suggestions concernant la gestion et la maintenance de MySQL sont :

1. Habituellement, la taille physique de la table ne dépasse pas 10 Go, le nombre de lignes dans une seule table ne dépasse pas 100 millions et la longueur moyenne des lignes ne dépasse pas 8 Ko. Si les performances de la machine sont suffisantes, MySQL peut gérer complètement cette quantité de données. vous devez vous soucier des problèmes de performances. Les principales suggestions sont les suivantes : Considérez le coût plus élevé du DDL EN LIGNE ;

2. Ne vous inquiétez pas trop du fait que le processus mysqld occupe trop de mémoire. Tant que la suppression du MOO ne se produit pas et que beaucoup de SWAP sont utilisés, tout ira bien

3. Dans Dans le passé, le but de l'exécution de plusieurs instances sur une seule machine était de pouvoir maximiser l'utilisation des ressources informatiques. Si une seule instance peut déjà consommer la plupart des ressources informatiques, il n'est pas nécessaire d'exécuter plusieurs instances

 ;

4. Utilisez régulièrement pt-duplicate-key-checker pour vérifier et supprimer l'index des doublons. Utilisez régulièrement l'outil pt-index-usage pour vérifier et supprimer les index avec une très faible fréquence d'utilisation

5. Collectez régulièrement le journal des requêtes lentes et analysez-le avec l'outil pt-query-digest ; le système Anemometer pour la gestion des requêtes lentes. Afin d'analyser les requêtes lentes et d'effectuer des travaux d'optimisation ultérieurs

6. Vous pouvez utiliser pt-kill pour tuer les requêtes SQL à long terme. Il existe une option innodb_kill_idle_transaction dans. la version Percona qui peut également réaliser cette fonction ;

7. Utilisez pt-online-schema-change pour compléter les exigences DDL EN LIGNE des grandes tables

8. Utilisez régulièrement pt-table- ; checksum et pt-table-sync pour vérifier et réparer la réplication maître-esclave MySQL Différences de données ;

Écrit à la fin : Pour cette référence d'optimisation, dans la plupart des cas, j'ai introduit des scénarios applicables si votre scénario d'application est différent. à partir de ce qui est décrit dans cet article, il est alors recommandé de procéder en fonction de la situation réelle. S'adapter au lieu de s'adapter mécaniquement. Les remises en question et les suggestions sont les bienvenues, mais les résistances habituelles sans passer par le cerveau sont rejetées.

Ce qui précède est une référence d'optimisation MySQL relativement complète. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !