Explication détaillée de la configuration de base de MySQL5.6

Cet article présente principalement la configuration d'optimisation de base de MySQL5.6, détaille les éléments de configuration qui doivent être optimisés dans MySQL5.6 et donne enfin un cas d'optimisation. Les amis dans le besoin peuvent s'y référer

.

Suivre Avec un grand nombre d'améliorations des options par défaut, MySQL 5.6 nécessite beaucoup moins d'options de réglage que les versions précédentes. Dans cet article, je décrirai les éléments de configuration qui doivent être optimisés.

Paramètres InnoDB

1.innodb_buffer_pool_size - La valeur par défaut est 128 Mo. Il s'agit de l'option d'optimisation la plus importante car elle spécifie la quantité de mémoire utilisée par InnoDB pour charger. données et index (données+index). Pour les serveurs MySQL dédiés, il est recommandé de spécifier la plage de 50 à 80 % de la mémoire physique. Par exemple, pour une machine avec 64 Go de mémoire physique, la. Le pool de cache doit être défini sur environ 50 Go.
Si cette valeur est définie, il peut y avoir des risques plus importants, tels qu'un manque de mémoire libre pour le système d'exploitation et certains sous-systèmes MySQL (sous-systèmes) qui dépendent du système de fichiers. cache, y compris les journaux binaires, les journaux de transactions InnoDB (transaction journaux), etc.

2.innodb_log_file_size - La valeur par défaut est 48M Systèmes avec. un débit d'écriture élevé doit augmenter cette valeur pour permettre à l'activité de point de contrôle en arrière-plan d'améliorer les performances en lissant les écritures sur une période de temps plus longue. Il est sûr de définir cette valeur en dessous de 4G. La pratique passée a montré que l'inconvénient de. les fichiers journaux volumineux sont augmentés. Le temps de réparation requis en cas de crash, mais cela a été considérablement amélioré dans les versions 5.5 et 5.6

3.innodb_flush_method - La valeur par défaut est fdatasync si vous utilisez des disques RAID matériels. Le contrôleur devra peut-être être défini sur O_DIRECT. Cela évite l'effet de "double mise en mémoire tampon" lors de la lecture à partir du pool de tampons InnoDB, qui créerait autrement 2 copies entre le cache du système de fichiers et le cache InnoDB
Si un. Le contrôleur RAID matériel n'est pas utilisé, ou lors de l'utilisation du stockage SAN, O_DIRECT peut entraîner une dégradation des performances. Le manuel d'utilisation de MySQL et le bug n° 54306 expliquent cela en détail.

4.innodb_flush_neighbors - La valeur par défaut est 1. Doit être défini sur 0 (désactivé) sur le stockage SSD car il n'y a aucun gain de performances lié à l'utilisation des E/S séquentielles. Ce paramètre doit également être désactivé sur certains matériels utilisant RAID, car il n'est pas garanti que les blocs logiquement contigus soient contigus sur le disque physique.

5.innodb_io_capacity et innodb_io_capacity_max - Ces paramètres affecteront le nombre d'opérations qu'InnoDB effectue en arrière-plan par seconde si vous avez une compréhension approfondie des performances matérielles (comme le nombre d'opérations d'E/S possibles). être exécuté par seconde), il est hautement souhaitable d'utiliser ces fonctions au lieu de les laisser inactives.

Il existe un bon exemple d'analogie : si un certain vol, aucun billet n'a été vendu - il Cela peut être une bonne stratégie de laisser certaines personnes sur les vols ultérieurs prendre ce vol en cas de mauvais temps plus tard, c'est-à-dire que s'il y a une opportunité, l'opération en arrière-plan sera gérée en passant pour réduire la concurrence avec une éventuelle concurrence réelle. -time opérations plus tard.

Il existe un calcul très simple : si chaque disque peut lire et écrire 200 fois par seconde (IOPS), alors une matrice de disques RAID10 avec 10 disques IOPS = (10/2) * 200 = 1000. Je dis que c'est "très simple" car les contrôleurs RAID sont généralement capables de fournir une fusion supplémentaire et d'augmenter efficacement les capacités d'IOPS pour les disques SSD, les IOPS peuvent facilement atteindre plusieurs milliers.

Il peut y avoir certains risques. en définissant ces deux valeurs trop grandes. Vous ne voulez certainement pas que les opérations en arrière-plan entravent les performances des opérations d'E/S des tâches de premier plan. L'expérience passée montre que la définition de ces deux valeurs est trop élevée, le verrouillage interne. détenu par InnoDB entraînera une dégradation des performances (d'après les informations que j'ai apprises, cela a été grandement amélioré dans MySQL5.6

innodb_lru_scan_degree - La valeur par défaut est 1024. Il s'agit d'une nouvelle valeur). option introduite dans MySQL 5.6. Mark Callaghan a fourni quelques suggestions de configuration. En termes simples, si vous augmentez la valeur innodb_io_capacity, vous devez également augmenter innodb_lru_scan_degree.

Réplication

Si le serveur souhaite prendre en charge la réplication maître-esclave ou la récupération à un moment donné, dans ce cas, nous avons besoin de :

1.log -bin - Activer la journalisation binaire. la journalisation n'est pas sécurisée par défaut, mais comme je l'ai dit dans mon article précédent, je recommande à la plupart des utilisateurs de viser la stabilité. Dans ce cas, vous devez également activer : sync_binlog=1, sync_relay_log=1, relay-log-. info-repository=TABLE et master-info-repository=TABLE.

2.expire-logs-days - Par défaut, les anciens journaux seront conservés pour toujours, je recommande de le définir sur 1-. 10 jours. Il ne sert à rien de le sauvegarder plus longtemps car la restauration à partir d'une sauvegarde sera beaucoup plus rapide

3.server-id - Tous les serveurs dans un système de réplication maître-esclave (. topologie de réplication) doit être définie avec un identifiant de serveur unique.

4.binlog_format=ROW - modifié en réplication basée sur les lignes. J'ai récemment écrit une autre réplication basée sur les lignes, qui décrit pourquoi je l'aime vraiment, car elle peut améliorer les performances en réduisant le verrouillage des ressources. des paramètres supplémentaires doivent également être activés : transaction-isolation=READ-COMMITTED et innodb_autoinc_lock_mode = 2.

Autres configurations (Divers)

1. timezone=GMT Définissez le. Fuseau horaire sur l'heure de Greenwich. De plus en plus d'administrateurs système recommandent de régler tous les serveurs sur l'heure de Greenwich (GMT). Personnellement, j'aime beaucoup cela car presque toutes les entreprises sont désormais mondialisées. Le régler sur votre fuseau horaire local semble un peu arbitraire. 🎜>

2.character-set-server=utf8mb4 et collation-server=utf8mb4_general_ci Comme mentionné dans l'article précédent, l'encodage utf8 est une meilleure option par défaut pour les nouvelles applications. Vous pouvez également définir skip-. Character-set-client-handshake pour ignorer les autres jeux de caractères que l'application souhaite définir 3.sql-mode

- MySQL est très tolérant aux données non standard par défaut et. tronquera les données en silence. Dans l'un de mes articles précédents, j'ai mentionné que la nouvelle application Le programme est mieux réglé sur :

4.skip-name-resolve

STRICT_TRANS_TABLES,ERROR_FOR_pISION_BY_ZERO,
 NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,
 NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,
 NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.

- désactiver. résolution inversée du nom de domaine. La résolution DNS peut être un peu lente/instable sur certains systèmes, donc si l'autorisation basée sur le nom d'hôte n'est pas requise, je recommande d'éviter cette analyse

5.max_connect_errors

- Todd Farmer a écrit : "[Cette fonctionnalité] fournit une protection contre les attaques d'accès par force brute. En fait, lors de la configuration de skip-name-resolve, max_connect_errors ne fonctionne même pas (voir le paragraphe précédent).

Un pare-feu). est une solution plus appropriée, je bloque généralement le port 3306. Qu'il s'agisse d'un port public ou privé, seules des applications spécifiques peuvent accéder et se connecter à MySQL Je définis généralement max_connect_errors=100000 afin d'éviter tout "double". configuration" et assurez-vous qu'elle ne gêne pas.

6.max-connections

- La valeur par défaut est 151. J'ai vu de nombreux utilisateurs la définir sur une valeur plus grande, généralement entre 300 et 1 000.

est généralement inévitable. Cette valeur serait définie plus haut, mais ce qui me rend un peu nerveux, c'est que la machine à 16 cœurs n'a qu'une capacité d'exécution de connexion d'environ 2x ~ 10x en cas de blocage d'E/S <.> Vous pouvez espérer que de nombreuses connexions ouvertes sont toutes inactives et dormantes. Mais si elles sont toutes actives, un grand nombre de nouveaux threads (thread-thrash) peuvent être créés Si les conditions le permettent, vous pouvez configurer et optimiser. le pool de connexions à la base de données (connection-pools) pour l'application Pour résoudre ce problème, au lieu d'ouvrir et de maintenir un grand nombre de connexions Bien entendu, les applications qui n'utilisent pas le pooling de connexions (non poolées), s'ouvrent rapidement, et fermer les connexions le plus rapidement possible après avoir effectué des tâches est également réalisable.
Une autre solution à partir de la version 5.5 (avec quelques différences entre MySQL Community Edition et Enterprise Edition) consiste à utiliser le plugin de pool de threads.


Conclusion

Supposons que la configuration du serveur MySQL est : 1,64 Go de mémoire physique 2. Contrôleur RAID matériel (en supposant que les IO peuvent atteindre 2 000 IOPS par seconde)

3. . Nécessite une réplication maître-esclave (Réplication)

4 . Nouvelles applications (par exemple, systèmes non existants)
5. Protection par pare-feu
6. Aucune autorisation basée sur les noms de domaine (noms d'hôte, noms d'hôte) n'est requise.
7. Les applications globales ne veulent pas être fixées dans un certain emplacement. Un fuseau horaire.
8. Si vous voulez que le programme soit fiable et stable (durable).

La configuration peut être comme suit :

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!