Partagez 101 conseils de débogage et d'optimisation MySQL_Mysql

2020-05-20

MySQL est une puissante base de données open source. Avec de plus en plus d'applications basées sur des bases de données, les gens poussent MySQL à ses limites. Voici 101 conseils pour ajuster et optimiser l'installation de MySQL. Certains conseils sont spécifiques à l'environnement d'installation, mais ces idées sont universelles. Je les ai divisés en plusieurs catégories pour vous aider à maîtriser davantage les compétences de réglage et d'optimisation MySQL.

Ajustement du matériel et du système d'exploitation du serveur MySQL:

1. Avoir suffisamment de mémoire physique pour charger l'intégralité du fichier InnoDB en mémoire - la vitesse d'accès aux fichiers en mémoire est beaucoup plus rapide que celle d'accès sur le disque dur.
2. Évitez d'utiliser les partitions de swap swap à tout prix - le swap est lu depuis le disque dur et il est très lent.
3. Utilisez de la RAM alimentée par batterie (Remarque: la RAM est une mémoire à accès aléatoire).
4. Utilisez un RAID avancé (Remarque: les baies redondantes de disques bon marché, c'est-à-dire les baies de disques), de préférence RAID 10 ou supérieur.
5. Évitez RAID5 (Remarque: une solution de stockage qui équilibre les performances de stockage, la sécurité des données et les coûts de stockage) - La vérification de l'intégrité de la base de données a un prix.
6. Séparez le système d'exploitation et la partition de données, non seulement logiquement, mais également physiquement - les opérations de lecture et d'écriture du système d'exploitation affecteront les performances de la base de données.
7. Placez l'espace temporaire MySQL, copiez le journal et les données dans différentes partitions, lorsque les opérations de lecture et d'écriture en arrière-plan de la base de données à partir du disque affecteront les performances de la base de données.
8. Plus d'espace disque équivaut à une vitesse plus rapide.
9. Disque meilleur et plus rapide.
10. Utilisez SAS (Remarque: Serial Attached SCSI, c'est-à-dire Serial Attached SCSI) au lieu de SATA (Remarque: SATA, c'est-à-dire, disque dur série).
11. Les petits disques durs sont plus rapides que les gros disques durs, en particulier dans le cas des configurations RAID.
12. Utilisez un contrôleur RAID de cache avec batterie de secours.
13. Évitez d'utiliser des baies de disques logiciels.
14. Envisagez d'utiliser des cartes IO à semi-conducteurs (pas des unités de disque) pour les partitions de données - ces cartes peuvent prendre en charge une vitesse d'écriture de 2 Go / s pour presque toutes les quantités de données.
15. Définissez la valeur de swappiness sur 0 sous Linux - il n'y a aucune raison de mettre en cache les fichiers dans le serveur de base de données, ce qui est un avantage d'un serveur ou d'un bureau.
16. Si possible, utilisez noatime et nodirtime pour monter le système de fichiers - il n'y a aucune raison de mettre à jour l'heure de modification de l'accès aux fichiers de la base de données.
17. Utilisez le système de fichiers XFS - un système de fichiers plus rapide et plus petit que ext3, avec de nombreuses options de journalisation, et ext3 s'est avéré avoir des problèmes de double tampon avec MySQL.
18. Ajustez le journal du système de fichiers XFS et les variables de tampon - pour les normes de performance les plus élevées.
19. Sur les systèmes Linux, utilisez le planificateur de synchronisation NOOP ou DEADLINE IO par rapport au planificateur de synchronisation NOOP et DEADLINE, ce planificateur de synchronisation CFQ et ANTICIPATORY est très lent.
20. Utilisez un système d'exploitation 64 bits - Pour MySQL, la prise en charge et l'utilisation de la mémoire seront plus importantes.
21. Supprimez les packages d'installation et les démons inutilisés sur le serveur - moins de consommation de ressources.
22. Mettez l'hôte utilisant MySQL et votre hôte MySQL dans une recherche DNS sans fichier d'hôtes.
23. Ne forcez jamais à tuer un processus MySQL - vous endommagerez la base de données et le programme de sauvegarde en cours d'exécution.
24. Contribuer au serveur aux processus d'arrière-plan MySQL et d'autres services peuvent réduire le temps CPU de la base de données.

Configuration MySQL:

25. Lors de l'écriture, utilisez innodb_flush_method = O_DIRECT pour éviter la double mise en mémoire tampon.
26. Évitez les systèmes de fichiers O_DIRECT et EXT3 - vous sérialiserez tout ce que vous voulez écrire.
27. Allouez suffisamment innodb_buffer_pool_size pour charger l'intégralité du fichier InnoDB en mémoire - moins de lecture à partir du disque.
28. Ne définissez pas le paramètre innodb_log_file_size trop grand, il peut être plus rapide et avoir plus d'espace disque en même temps - il est généralement bon de jeter plus de journaux, ce qui peut réduire le temps de récupération de la base de données après le crash de la base de données.
29. Ne mélangez pas les paramètres innodb_thread_concurrency et thread_concurrency - ces deux valeurs sont incompatibles.
30. Attribuez une très petite quantité au paramètre max_connections - trop de connexions utiliseront de la RAM et verrouillent le service MySQL.
31. Gardez thread_cache à un nombre relativement élevé, environ 16 pour éviter des connexions lentes lors de l'ouverture.
32. Utilisez le paramètre skip-name-resolution - supprimez la recherche DNS.
33. Si vos requêtes sont répétées et que les données ne changent pas souvent, vous pouvez utiliser le cache de requêtes. Mais si vos données changent fréquemment, l'utilisation du cache de requêtes vous décevra.
34. Augmentez la valeur de temp_table_size pour empêcher l'écriture sur le disque
35. Augmentez la valeur max_heap_table_size pour empêcher l'écriture sur le disque
36. Ne définissez pas la valeur sort_buffer_size trop élevée, sinon votre mémoire sera épuisée rapidement
37. Déterminez la taille de key_buffer en fonction de la valeur de key_read_requests et key_reads. En général, key_read_requests doit être supérieur à la valeur de key_reads, sinon vous ne pouvez pas utiliser efficacement key_buffer
38. La définition de innodb_flush_log_at_trx_commit sur 0 améliorera les performances, mais si vous souhaitez conserver la valeur par défaut (1), vous devez garantir l'intégrité des données et vous assurer également que la réplication ne sera pas en retard.
39. Vous devez disposer d'un environnement de test pour tester votre configuration et vous pouvez souvent le redémarrer sans affecter la production normale.

Optimisation du mode MySQL:

40. Gardez votre base de données organisée.
41. Anciennes archives de données - supprimer des lignes supplémentaires pour renvoyer ou rechercher la requête.
42. Indexez vos données.
43. Ne pas abuser de l'index, comparer et interroger.
44. Texte compressé et types de données BLOB - pour économiser de l'espace et réduire le nombre de lectures sur disque.
45. UTF 8 et UTF16 sont inférieurs à l'efficacité d'exécution de latin1.
46. ​​Utilisez les déclencheurs avec parcimonie.
47. Gardez les données redondantes au minimum - ne répétez pas les données inutiles.
48. Utilisez des listes liées au lieu de lignes étendues.
49. Faites attention au type de données. Dans vos données réelles, utilisez le plus petit possible.
50. Si d'autres données sont souvent utilisées pour la requête, mais que les données BLOB / TEXT ne le sont pas, séparez les données BLOB / TEXT des autres données.
51. Vérifiez et optimisez fréquemment la table.
52. Réécrire fréquemment l'optimisation des tables InnoDB.
53. Parfois, lorsque vous ajoutez une colonne pour supprimer l'index, puis rajoutez l'index, cetteSera plus rapide.
54. Utilisez différents moteurs de stockage pour différents besoins.
55. Utilisez des tables de journal du moteur de stockage d'archives ou des tables d'audit - cela est plus efficacement écrit.
56. Les données de session sont stockées dans le cache (memcache) au lieu de MySQL - le cache permet un remplissage automatique et vous empêche de créer des données spatio-temporelles difficiles à lire et à écrire sur MySQL.
57. Utilisez VARCHAR au lieu de CHAR lorsque vous stockez des chaînes de caractères de longueur variable, économisez de l'espace, car CHAR de longueur fixe et la longueur de VARCHAR ne sont pas fixes (UTF8 n'est pas affecté par cela).
58. Changer progressivement le modèle - un petit changement peut avoir un impact énorme.
59. Testez tous les modes dans l'environnement de développement pour refléter les changements de production.
60. Ne modifiez pas la valeur de votre fichier de configuration à volonté, cela peut avoir un effet désastreux.
61. Parfois, moins de configurations dans MySQL sont plus.
62. En cas de doute, utilisez un fichier de configuration MySQL commun.

Optimisation des requêtes:

63. Utilisez des journaux de requêtes lentes pour rechercher des requêtes lentes.
64. Utilisez le plan d'exécution pour déterminer si la requête s'exécute normalement.
65. Testez toujours vos requêtes pour voir si elles fonctionnent au mieux - les performances changeront avec le temps.
66. Évitez d'utiliser count (*) sur toute la table, cela pourrait verrouiller la table entière.
67. Gardez les requêtes cohérentes afin que les requêtes similaires ultérieures puissent utiliser la mise en cache des requêtes.
68. Utilisez GROUP BY au lieu de DISTINCT le cas échéant.
69. Utilisez des colonnes indexées dans les clauses WHERE, GROUP BY et ORDER BY.
70. Gardez l'index simple et n'incluez pas la même colonne dans plusieurs index.
71. Parfois, MySQL utilise le mauvais index, dans ce cas, utilisez USE INDEX.
72. Vérifiez le problème d'utilisation de SQL_MODE = STRICT.
73. Pour les champs d'index avec moins de 5 enregistrements, utilisez LIMIT au lieu de OR lorsque vous utilisez UNION.
74. Afin d'éviter SELECT avant la mise à jour, utilisez INSERT ON DUPLICATE KEY ou INSERT IGNORE au lieu de UPDATE.
75. N'utilisez pas MAX, utilisez les champs d'index et les clauses ORDER BY.
76. Évitez d'utiliser ORDER BY RAND ().
77. LIMIT M, N peut en fait ralentir la requête dans certains cas, utilisée avec parcimonie.
78. Utilisez UNION au lieu de sous-requête dans la clause WHERE.
79. Pour les MISES À JOUR, utilisez le MODE PARTAGE pour empêcher les verrous exclusifs.
80. Après avoir redémarré MySQL, n'oubliez pas de réchauffer votre base de données pour vous assurer que vos données sont en mémoire et que la vitesse de requête est rapide.
81. Utilisez DROP TABLE, CREATE TABLE DELETE FROM pour supprimer toutes les données de la table.
82. Données réduites Lors de l'interrogation des données dont vous avez besoin, l'utilisation de * prend beaucoup de temps.
83. Envisagez des connexions persistantes au lieu de plusieurs connexions pour réduire la surcharge.
84. Les requêtes de référence, y compris l'utilisation de la charge sur le serveur, parfois une simple requête peut affecter d'autres requêtes.
85. Lorsque la charge augmente sur votre serveur, utilisez SHOW PROCESSLIST pour afficher les requêtes lentes et problématiques.
86. Toutes les requêtes suspectes testées dans les données miroir générées dans l'environnement de développement.

Processus de sauvegarde MySQL:

87. Sauvegarde à partir du serveur de réplication secondaire.
88. Arrêtez la copie pendant les sauvegardes pour éviter les incohérences dans les dépendances de données et les contraintes de clé étrangère.
89. Arrêtez complètement MySQL et effectuez une sauvegarde à partir du fichier de base de données.
90. Si vous utilisez le vidage MySQL pour la sauvegarde, veuillez également sauvegarder les fichiers journaux binaires - assurez-vous que la réplication n'est pas interrompue.
91. Ne vous fiez pas aux instantanés LVM - cela est susceptible de provoquer des incohérences de données et de vous causer des problèmes à l'avenir.
92. Pour faciliter la restauration d'une seule table, exportez les données dans les tables - si les données sont isolées des autres tables.
93. Utilisez -opt lorsque vous utilisez mysqldump.
94. Vérifiez et optimisez la table avant la sauvegarde.
95. Pour une importation plus rapide, désactivez temporairement les contraintes de clé étrangère lors de l'importation.
96. Pour une importation plus rapide, la détection d'unicité est temporairement désactivée pendant l'importation.
97. Après chaque sauvegarde, calculez la taille de la base de données, des tables et des index pour mieux surveiller la croissance de la taille des données.
98. Surveillez les erreurs et les retards des instances de réplication à l'aide de scripts de planification automatique.
99. Effectuez une sauvegarde régulièrement.
100. Testez régulièrement votre sauvegarde.
101: effectuer la surveillance MySQL: Monitis dévoile la première surveillance MySQL gratuite à la demande au monde.

www.xd1998.com@2001-2030Partage De Technologie