MySQL - Une introduction détaillée aux 10 configurations qui doivent être ajustées pour MySQL nouvellement installé

Êtes-vous toujours inquiet du service mysql nouvellement installé et ne savez pas quelles configurations par défaut modifier ? Mysql a plus de 100 paramètres réglables. Lequel des paramètres les plus importants doit être ajusté immédiatement ! >
Cet article présente principalement 10 configurations qui doivent être ajustées pour l'optimisation MySQL. L'utilisation de ces méthodes peut vous permettre d'obtenir rapidement une configuration MySQL robuste. Les amis dans le besoin peuvent se référer à ce qui suit :

Quand nous y sommes. Lorsqu'ils sont embauchés pour surveiller les performances de MySQL, les gens s'attendent à ce que nous examinions la configuration de MySQL et fassions des suggestions d'améliorations. De nombreuses personnes sont surprises lorsque nous leur suggérons de modifier seulement quelques paramètres, même s'il existe des centaines d'options de configuration. Le but de cet article est de vous donner une liste très importante d’éléments de configuration.

Nous avons donné ce conseil sur le blog il y a quelques années, mais le monde de MySQL évolue si vite

Écrit avant de commencer...

Même si Même ! les personnes expérimentées peuvent commettre des erreurs, ce qui peut causer beaucoup de problèmes. Alors avant d'appliquer aveuglément ces recommandations, rappelez-vous ce qui suit :

Ne modifiez qu'un seul paramètre à la fois ! C'est le seul moyen de tester si un changement est bénéfique.

La plupart des configurations peuvent être modifiées au moment de l'exécution à l'aide de SET GLOBAL. C'est un moyen très pratique d'annuler rapidement les modifications en cas de problème. Cependant, pour le rendre permanent, vous devez apporter des modifications au fichier de configuration.

Une modification ne prend pas effet même après le redémarrage de MySQL

Veuillez vous assurer que vous utilisez le bon fichier de configuration ? Veuillez vous assurer de placer la configuration dans la bonne zone (toutes les configurations mentionnées dans cet article appartiennent à [mysqld])

Le serveur ne peut pas démarrer après avoir modifié une configuration : Veuillez vous assurer d'utiliser la bonne unité de configuration.

Par exemple, l'unité de innodb_buffer_pool_size est Mo et max_connection n'a pas d'unité.

Ne pas avoir d'éléments de configuration en double dans un fichier de configuration. Si vous souhaitez suivre les modifications, utilisez le contrôle de version.

N'utilisez pas de calculs naïfs, tels que "Maintenant, mon serveur a deux fois plus de mémoire, je dois donc modifier toutes les valeurs pour doubler la valeur précédente."

1. Configuration de base

Vous devez vérifier fréquemment les 3 éléments de configuration suivants. Sinon, des problèmes pourraient survenir rapidement.

innodb_buffer_pool_size :

C'est la première option que vous devez définir après l'installation d'InnoDB.

Le pool de mémoire tampon est l'endroit où les données et les index sont mis en cache : plus la valeur est grande, mieux c'est, garantissant que vous utilisez la mémoire au lieu du disque dur pour la plupart des opérations de lecture. Les valeurs typiques sont 5 à 6 Go (mémoire de 8 Go), 20 à 25 Go (mémoire de 32 Go), 100 à 120 Go (mémoire de 128 Go).

innodb_log_file_size :

C'est la taille du journal de rétablissement. La journalisation redo est utilisée pour garantir que les opérations d'écriture sont rapides et fiables et pour récupérer après un crash.

Jusqu'à MySQL 5.1, il était difficile à régler car d'une part vous vouliez qu'il soit plus grand pour améliorer les performances, et d'autre part vous vouliez le rendre plus petit pour récupérer plus rapidement après un crash. Heureusement, depuis MySQL 5.5, les performances de récupération après incident ont été considérablement améliorées, de sorte que vous puissiez bénéficier en même temps de performances d'écriture et de récupération après incident élevées. Jusqu'à MySQL 5.5, la taille totale du journal redo était limitée à 4 Go (il peut y avoir 2 fichiers journaux par défaut). Cela a été amélioré dans MySQL 5.6.

Définissez innodb_log_file_size sur 512 Mo depuis le début (il y a donc 1 Go de journal de rétablissement), ce qui vous donnera suffisamment d'espace pour les opérations d'écriture. Si vous savez que votre application a besoin d'écrire fréquemment des données et que vous utilisez MySQL 5.6, vous pouvez la définir sur 4G dès le début.

max_connections :

Si vous voyez souvent l'erreur « Trop de connexions », c'est parce que la valeur de max_connections est trop faible. Ceci est très courant car l'application ne ferme pas correctement les connexions à la base de données et vous avez besoin d'une valeur supérieure aux 151 connexions par défaut. Un inconvénient majeur lorsque la valeur max_connection est définie sur une valeur élevée (par exemple 1 000 ou plus) est que le serveur ne répond plus lors de l'exécution de 1 000 transactions actives ou plus. L’utilisation du pooling de connexions dans votre application ou du pooling de processus dans MySQL peut aider à résoudre ce problème.

2. Configuration d'InnoDB

À partir de la version 5.5 de MySQL, InnoDB est le moteur de stockage par défaut et il est bien plus utilisé que tout autre moteur de stockage. C'est pourquoi il doit être configuré avec soin.

innodb_file_per_table :

Ce paramètre indique à InnoDB s'il doit stocker les données et les index pour toutes les tables dans un espace table partagé (innodb_file_per_table = OFF) ou pour chaque table. les données sont placées dans un fichier .ibd séparé (innodb_file_per_table = ON). Un fichier par table vous permet de récupérer de l'espace disque lors de la suppression, de la troncature ou de la reconstruction de tables. Cela est également nécessaire pour certaines fonctionnalités avancées, telles que la compression des données. Mais cela n’apporte aucun gain de performances. Le scénario principal dans lequel vous ne voulez pas d'un fichier par table est si vous avez un très grand nombre de tables (comme 10 000).

Dans MySQL 5.6, la valeur par défaut de cette propriété est ON, donc dans la plupart des cas, vous n'avez rien à faire. Avec les versions précédentes, vous deviez définir cette propriété sur ON avant de charger les données, car elle n'affectait que les tables nouvellement créées.

innodb_flush_log_at_trx_commit :

La valeur par défaut est 1, indiquant qu'InnoDB prend entièrement en charge les fonctionnalités ACID. Cette valeur est la plus appropriée lorsque votre principale préoccupation est la sécurité des données, comme sur un nœud maître. Mais pour les systèmes avec des vitesses de disque (lecture et écriture) lentes, cela entraînera une surcharge énorme, car chaque fois que le vidage des modifications dans le journal de rétablissement nécessite des fsync supplémentaires. Définir sa valeur sur 2 entraînera une valeur moins fiable car la transaction soumise n'est vidée dans le journal de rétablissement qu'une fois par seconde, mais cela est acceptable pour certains scénarios, tels que le nœud de sauvegarde du nœud principal. Cette valeur est acceptable. . de. Une valeur de 0 est plus rapide, mais peut entraîner une certaine perte de données en cas de panne du système : s'applique uniquement aux nœuds de sauvegarde.

innodb_flush_method :

Cette configuration détermine la manière dont les données et les journaux sont écrits sur le disque dur. De manière générale, si vous disposez d'un contrôleur RAID matériel et que son cache indépendant utilise un mécanisme de réécriture et dispose d'une protection contre les pannes de batterie, il doit alors être défini sur O_DIRECT ; sinon, dans la plupart des cas, il doit être défini sur fdatasync (valeur par défaut) ; ). sysbench est un excellent outil pour vous aider à choisir cette option.

innodb_log_buffer_size :

Cette configuration détermine le cache alloué pour les transactions qui n'ont pas encore été exécutées. La valeur par défaut (1 Mo) est généralement suffisante, mais si votre transaction contient des objets binaires volumineux ou des champs de texte volumineux, ce cache se remplira rapidement et déclenchera des opérations d'E/S supplémentaires. Regardez la variable d'état Innodb_log_waits, si elle n'est pas 0, augmentez innodb_log_buffer_size.

3. Autres paramètres

query_cache_size :

le cache de requêtes (cache de requêtes) est un goulot d'étranglement bien connu, même Cela est également vrai lorsqu'il n'y a pas beaucoup de concurrence.

La meilleure option est de le désactiver depuis le début, de définir query_cache_size = 0 (maintenant la valeur par défaut dans MySQL 5.6) et d'utiliser d'autres méthodes pour accélérer les requêtes : optimiser les index, ajouter des copies pour répartir la charge ou activer cache supplémentaire (tel que Memcache ou Redis). Si vous avez activé le cache de requêtes pour votre application et que vous n'avez remarqué aucun problème, le cache de requêtes peut vous être utile. C'est une chose à laquelle il faut faire attention si vous souhaitez le désactiver.

log_bin :

Si vous souhaitez que le serveur de base de données agisse comme un nœud de sauvegarde pour le nœud maître, il est alors nécessaire d'activer le journal binaire. Si vous faites cela, n'oubliez pas de définir server_id sur une valeur unique. Même avec un seul serveur, cette opération (activer la journalisation binaire) est utile si vous souhaitez effectuer une récupération de données à un moment donné : restaurez à partir de votre sauvegarde la plus récente (sauvegarde complète) et appliquez les modifications dans le journal binaire (sauvegarde incrémentielle). . Une fois créé, le journal binaire est enregistré définitivement. Ainsi, si vous ne souhaitez pas manquer d'espace disque, vous pouvez utiliser PURGE BINARY LOGS pour purger les anciens fichiers, ou définir expire_logs_days pour spécifier combien de jours après lesquels les journaux seront automatiquement purgés.

La journalisation des journaux binaires n'est pas sans surcharge, il est donc recommandé de désactiver cette option si vous n'en avez pas besoin sur un nœud réplica qui n'est pas le nœud principal.

skip_name_resolve :

Lorsque le client se connecte au serveur de base de données, le serveur effectue la résolution du nom d'hôte, et lorsque le DNS est lent, l'établissement d'une connexion sera également lent. Il est donc recommandé de désactiver l'option skip_name_resolve lors du démarrage du serveur sans effectuer de recherche DNS. La seule limitation est que seules les adresses IP peuvent être utilisées ultérieurement dans les instructions GRANT. Des précautions doivent donc être prises lors de l'ajout de ce paramètre à un système existant.

Résumé

Bien sûr, il existe d'autres paramètres qui peuvent fonctionner, en fonction de votre charge ou de votre matériel : mémoire lente et disque rapide, simultanéité élevée et écriture intensive sous charge, vous aurez besoin d'ajustements spéciaux. Cependant, l'objectif ici est que vous puissiez obtenir rapidement une configuration MySQL robuste sans passer trop de temps à ajuster certains paramètres MySQL insignifiants ou à lire la documentation pour savoir quels paramètres sont importants pour vous.

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!