Comment optimiser MySQL ? optimisation liée à MySQL

1 Optimisation liée à la couche matérielle

1.1, liée au CPU

Dans les paramètres du BIOS du serveur, vous pouvez ajuster les configurations suivantes afin de maximiser les performances du CPU , ou Évitez les problèmes NUMA classiques :

1. Sélectionnez le mode Performance Per Watt Optimized (DAPC) pour maximiser les performances du processeur. N'envisagez pas d'économiser lors de l'exécution de services comme DB qui nécessitent généralement. une grande quantité de calculs. Éteignez

2. Désactivez les options telles que C1E et C States, également pour améliorer l'efficacité du processeur ; > 3. Fréquence de fréquence de la mémoire) sélectionnez Performances maximales (meilleures performances)

4. Dans le menu des paramètres de mémoire, activez l'entrelacement de nœuds pour éviter les problèmes NUMA ; 🎜> 1.2. Liés aux E/S disque

Voici quelques mesures qui peuvent être optimisées pour les E/S disque dans l'ordre d'amélioration des performances IOPS :

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

2. Achetez une carte réseau équipée de CACHE et Modules BBU, qui peuvent améliorer considérablement les IOPS (se réfère principalement aux disques mécaniques, à l'exclusion des SSD ou SSD PCIe. Dans le même temps, l'état de santé des modules CACHE et BBU doit être vérifié régulièrement pour garantir que les données ne seront pas perdues dans un 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 une double protection d'alimentation ou si les exigences de sécurité des données sont remplies). pas particulièrement élevé), 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 d'en choisir un à haute vitesse, par exemple, choisissez un disque de 15 KRPM au lieu d'un disque de 7,2 KRPM, ce qui ne vaut pas quelques dollars

2 Optimisations liées à la couche système

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 IOPS :

1. Utilisez les deux planificateurs d'E/S date limite/noop. N'utilisez pas cfq (il ne convient pas à l'exécution de services DB)

2. . Lorsque vous utilisez le système de fichiers xfs, n'utilisez pas ext3 ; ext4 est à peine disponible, mais si le volume d'activité est important, vous devez utiliser xfs ; paramètre ajouté : options noatime, nodiratime, nobarrier (nobarrier est unique au système de fichiers xfs)


　2.2, autres optimisations des paramètres du noyau

Définissez des valeurs appropriées pour les paramètres clés du noyau afin 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 . Réglez vm.swappiness sur environ 5-10, voire 0 (veillez à le définir sur 0 pour RHEL 7 ou 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 définissez vm.dirty_ratio sur environ le double pour garantir que les données sales peuvent être continuellement vidées sur le disque et éviter les écritures d'E/S instantanées. similaire à innodb_max_dirty_pages_pct dans MySQL);

3. Définissez net.ipv4.tcp_tw_recycle et net.ipv4.tcp_tw_reuse 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 Internet, après tests, j'ai constaté qu'ils ont peu d'impact sur l'environnement OLTP où la lecture et l'écriture sont principalement mélangées (cela devrait être sur la lecture et l'écriture). Les scènes sensibles sont plus efficaces), mais peut-être qu'il y a quelque chose qui ne va pas avec ma méthode de test, vous pouvez l'ajuster à votre propre discrétion

;

3 optimisations liées aux couches MySQL

3.1. À propos de la sélection de version

La version officielle s'appelle ORACLE MySQL Il n'y a rien à dire à ce sujet je crois. la plupart des gens le sélectionneront.

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 d'amélioration des 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 génère un 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, qui peut améliorer les performances en cas de concurrence élevée. conditions. Il n’y aura pas de grosse baisse. 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 ; Le moteur InnoDB est utilisé par défaut. Il est fortement déconseillé d'utiliser le moteur MyISAM. Le moteur InnoDB peut certainement répondre à plus de 99% des scénarios métiers

3. Ajustez le innodb_buffer_pool_size ; size S'il s'agit d'une seule instance et absolument. Si la plupart d'entre elles sont des tables du moteur InnoDB, vous pouvez envisager de les définir à 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 légère 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, utilisez un espace table indépendant, je ne vois vraiment aucun avantage à utiliser l'espace table partagé. N'utilisez pas les 10 M par défaut, sinon ce sera grandement ; affecté lorsqu'il y a un nombre élevé de transactions simultanées ;

7. Définissez innodb_log_file_size=256M, définissez innodb_log_files_in_group=2, ce qui peut essentiellement satisfaire 90 % ou plus de scénarios
8. Définissez long_query_time = 1. Dans la version 5.5 et supérieure, il peut être défini à moins de 1. Il est recommandé de le définir sur 0,05 (50 millisecondes) pour enregistrer ces exécutions SQL plus lent, utilisé pour l'analyse ultérieure et dépannage ;

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 sur 10 Plus de dix mille, et les paramètres open_files_limit, innodb_open_files, table_open_cache et table_definition_cache peuvent être définis à environ 10 fois la taille de max_connection

10. Un malentendu courant est de définir tmp_table_size et max_heap_table_size est relativement grand, et je l'ai vu défini sur 1G. Ces deux options sont allouées pour chaque session de connexion, donc ne les définissez pas trop grandes, sinon cela provoquera facilement d'autres options au niveau de la session de connexion telles que : sort_buffer_size, join_buffer_size, vous également ; il faut faire attention à ne pas définir read_buffer_size, read_rnd_buffer_size, etc. trop grand ;

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

　3.3. À propos des spécifications de conception de schéma et des suggestions d'utilisation de SQL

Ce qui suit répertorie plusieurs spécifications de conception de schéma courantes et des suggestions d'utilisation de SQL qui peuvent aider à améliorer l'efficacité de MySQL :

1. Toutes les tables InnoDB sont conçues avec une colonne à incrémentation automatique qui n'a aucun objectif commercial comme clé primaire. Cela est vrai pour la plupart des scénarios. Peu de tables InnoDB sont véritablement en lecture seule. . Si c'est le cas, il est préférable d'utiliser TokuDB

2. Tant 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 si nécessaire ; Il est recommandé de diviser le tableau en sous-types, ne le rassemblez pas avec le tableau principal pour éviter de mauvaises performances de lecture lors de SELECT *.

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, notamment lors de la lecture de certaines colonnes TEXTE/BLOB ;

5. Lors de la création d'un index sur une colonne VARCHAR(N), il suffit généralement de prendre environ 50 % (ou même moins) de sa longueur pour créer un index de préfixe pour atteindre plus de 80 % des besoins de requête. , il n'est pas nécessaire de créer un index complet pour la colonne entière

6. Dans des circonstances normales, les performances des sous-requêtes sont relativement médiocres, et c'est le cas. Il est recommandé de le remplacer par l'écriture JOIN ;

　7. Lors d'une requête avec des jointures multi-tables, les types de champs associés doivent être aussi cohérents que possible et tous doivent avoir des index ; 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) en tant que table pilote

9. Lorsque plusieurs tables sont jointes et triées, le champ de tri ; Il 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 moins d'index indépendants multiples, surtout si une cardinalité est trop petite ; ( Par exemple, si le nombre total de valeurs uniques de la colonne est inférieur à 255, ne créez pas d'index indépendant ; Ensemble de résultats, l'efficacité sera bien supérieure ;

　3.4. Autres suggestions

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

1. Habituellement, une seule table Le la taille physique 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. Cette suggestion consiste principalement à considérer que le DDL EN LIGNE est plus cher

2. Ne vous inquiétez pas trop du fait que le processus mysqld prend 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 le passé, le but d'exécuter plusieurs instances sur une seule machine était de 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 les index en double. 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 utilisez l'outil pt-query-digest pour l'analyse ; , qui peut être combiné avec Le système Anemometer effectue une gestion des requêtes lentes pour analyser les requêtes lentes et effectuer un travail d'optimisation ultérieur

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

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

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

Écrit à la fin : Cette référence d'optimisation, grande Dans certains cas, j'ai introduit des scénarios applicables. Si votre scénario d'application est différent de ce qui est décrit dans cet article, il est recommandé de l'ajuster en fonction de la situation réelle au lieu de le copier. cela mécaniquement. Les questions 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 le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!