Explication détaillée de l'optimisation du fichier de configuration MySQL my.cnf

MySQL 5.5.13
Description du paramètre :
[client]
jeu de caractères- serveur = utf8
port = 3306
socket = /data/mysql/3306/mysql.sock
[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = / data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
# Le paramètre table_cache définit le nombre de caches de tables. Chaque fois qu'une connexion est établie, au moins un cache de table sera ouvert. #Par conséquent, la taille de table_cache doit être liée au paramètre max_connections. Par exemple, pour 200 # connexions exécutées en parallèle, vous devez disposer d'un cache de tables d'au moins 200 × N, où N est le nombre maximum de tables dans une jointure pour lesquelles l'application peut exécuter des requêtes #. De plus, certains descripteurs de fichiers supplémentaires doivent être réservés aux tables et fichiers temporaires.
# Lorsque Mysql accède à une table, si la table a été ouverte dans le cache, le cache est accessible directement si # il n'a pas été mis en cache, mais il y a encore de l'espace dans la table Mysql ; buffer , puis la table est ouverte et placée dans le tampon de table ; si le cache de table est plein, la table actuellement inutilisée sera libérée selon certaines règles, ou le cache de table sera temporairement étendu pour le stockage. Les avantages de l'utilisation de la table. cache are Fournit un accès plus rapide au contenu de la table. L'exécution des tables de vidage #effacera le contenu du cache. De manière générale, vous pouvez juger si vous devez augmenter la valeur de table_cache en examinant les valeurs d'état​​de Open_tables # et Opened_tables pendant la période de pointe d'exécution de la base de données (où open_tables est le nombre de tables actuellement ouvertes, et Opened_tables est le nombre de tables qui ont été ouvertes). Autrement dit, si open_tables est proche de table_cache et que la valeur de Opened_tables augmente progressivement, vous devriez alors envisager d'augmenter la taille de cette # valeur. De plus, lorsque Table_locks_waited est relativement élevé, table_cache doit également être augmenté.
open_files_limit = 10240
table_cache = 512
#Variables non dynamiques, le service doit être redémarré
# Précisez le nombre de connexions possibles à MySQL. Lorsque le thread principal MySQL reçoit de nombreuses demandes de connexion sur une courte période, ce paramètre prend effet et le thread principal passe un court moment à vérifier la connexion et à démarrer un nouveau thread. La valeur du paramètre back_log indique combien de requêtes peuvent être stockées dans la pile sur une courte période avant que MySQL ne cesse temporairement de répondre aux nouvelles requêtes. Si le système dispose de nombreuses connexions sur une courte période, vous devez augmenter la valeur de ce paramètre, qui spécifie la taille de la file d'attente d'écoute pour les connexions TCP/IP entrantes. Différents systèmes d'exploitation ont leurs propres limites sur cette taille de file d'attente. Tenter de définir back_log une valeur supérieure à la limite de votre système d'exploitation n'aura aucun effet. La valeur par défaut est 50. Pour les systèmes Linux, il est recommandé de le définir sur un nombre entier inférieur à 512.
back_log = 600
#MySQL autorise le nombre maximum de connexions
max_connections = 5000
#Oui Combien de connexions d'erreur sont autorisées
max_connect_errors = 6000
#Utilisez l'option MySQL --skip-external-locking pour éviter le verrouillage externe. Cette option est activée par défaut
external-locking = FALSE
# Définir la taille maximale des paquets, limiter la taille des paquets de données acceptés par le serveur et éviter les problèmes avec l'exécution de SQL trop long. La valeur par défaut est 16M. Lorsque le client MySQL ou le serveur mysqld reçoit un paquet supérieur à max_allowed_packet octets, une erreur "paquet trop volumineux" sera émise et la connexion sera fermée. Pour certains clients, vous pouvez rencontrer une erreur « Connexion perdue au serveur MySQL » lors de l'exécution de la requête si les paquets de communication sont trop volumineux. La valeur par défaut est 16 M.
#dev-doc : //m.sbmmt.com/
max_allowed_packet = 32M
# Sort_Buffer_Size est une connexion Paramètre de niveau, lorsque chaque connexion (session) doit utiliser ce tampon pour la première fois, la mémoire définie est allouée une fois.
#Sort_Buffer_Size n'est pas plus grand, mieux c'est. Puisqu'il s'agit d'un paramètre au niveau de la connexion, un paramètre trop grand et une concurrence élevée peuvent épuiser les ressources mémoire du système. Par exemple : 500 connexions consommeront 500*sort_buffer_size(8M)=4G de mémoire
#Sort_Buffer_Size Lorsqu'elle dépasse 2 Ko, mmap() sera utilisé à la place de malloc() pour l'allocation de mémoire, ce qui entraînera L'efficacité est réduite.
#Introduction technique//m.sbmmt.com/
#dev-doc : //m.sbmmt.com/
#explain select*from tableau où la limite de commande ; le tri des fichiers apparaît
#Paramètre d'optimisation clé
sort_buffer_size = 8M
#La taille utilisée pour le cache d'association inter-tables
join_buffer_size = 1M
# Cache des threads du serveur Cette valeur indique le nombre de threads enregistrés dans le cache qui peuvent être réutilisés. S'il y a encore de l'espace dans le cache lorsque la connexion est déconnectée, le thread du client sera placé dans le cache. est à nouveau demandé, la requête sera alors lue à partir du cache. Si le cache est vide ou s'il s'agit d'une nouvelle requête, le thread sera recréé. S'il y a beaucoup de nouveaux threads, augmenter cette valeur peut améliorer les performances du système. En comparant la variable d'état Connections et Threads_created, vous pouvez voir le rôle de cette variable
thread_cache_size = 300
# Que la valeur de thread_concurrency soit définie correctement ou non un impact important sur les performances de MySQL. Dans le cas de plusieurs processeurs (ou plusieurs cœurs), une définition incorrecte de la valeur de thread_concurrency empêchera MySQL d'utiliser pleinement plusieurs processeurs (ou plusieurs cœurs) et un seul processeur (ou noyau) peut fonctionner en même temps. thread_concurrency doit être défini sur 2 fois le nombre de cœurs de processeur. Par exemple, s'il existe un processeur double cœur, alors le thread_concurrency doit être 4 ; pour 2 processeurs double cœur, la valeur de thread_concurrency doit être 8
# Il s'agit d'un paramètre d'optimisation clé
thread_concurrency = 8
# Pour les utilisateurs qui utilisent MySQL, tout le monde sera familier avec cette variable. Dans l'optimisation du moteur MyISAM des années précédentes, ce paramètre était également un paramètre d'optimisation important. Mais avec le développement, ce paramètre a également révélé certains problèmes. La mémoire des machines est de plus en plus grande et les gens sont habitués à attribuer des valeurs de plus en plus grandes à des paramètres auparavant utiles. L'augmentation de ce paramètre a également provoqué une série de problèmes. Analysons d'abord le principe de fonctionnement de query_cache_size : après qu'une requête SELECT ait été exécutée dans DB, DB mettra l'instruction en cache. Lorsque le même SQL reviendra à DB et sera appelé, DB le mettra en cache sans modifier la table. le Client du cache. Un point clé ici est que lorsque DB utilise Query_cache pour fonctionner, cela nécessite que la table impliquée dans l'instruction n'ait pas changé pendant cette période. Donc, si la table change, comment les données dans Query_cache seront-elles traitées ? Tout d’abord, invalidez tous les Query_cache et les instructions liées à la table, puis écrivez les mises à jour. Ensuite, si le Query_cache est très volumineux, que la table comporte de nombreuses structures de requête et que l'instruction de requête n'est pas valide lentement, une mise à jour ou une insertion sera très lente, vous voyez donc pourquoi la mise à jour ou l'insertion est si lente. Par conséquent, dans les systèmes où la quantité d’écritures ou de mises à jour de base de données est relativement importante, ce paramètre ne convient pas à une allocation trop importante. De plus, dans les systèmes à forte concurrence et avec un volume d'écriture important, il est recommandé de désactiver cette fonction.
#Paramètres d'optimisation clés (ajouts, suppressions et modifications de la base de données principale-MyISAM)
query_cache_size = 512M
#Spécifiez le tampon qui peut être utilisé par une seule requête Taille de la zone, la valeur par défaut est 1M
query_cache_limit = 2M
#La valeur par défaut est 4 Ko, définir une valeur élevée est bon pour le Big Data requêtes, mais si vos requêtes sont de petites requêtes de données, elles peuvent facilement provoquer une fragmentation et un gaspillage de mémoire
#Taux de fragmentation du cache des requêtes = Qcache_free_blocks / Qcache_total_blocks * 100%
#Si le Le taux de fragmentation du cache de requêtes dépasse 20 %, vous pouvez utiliser FLUSH QUERY CACHE pour défragmenter le cache, ou essayer de réduire query_cache_min_res_unit, si vos requêtes concernent toutes de petits volumes de données.
#Utilisation du cache de requête = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#Si l'utilisation du cache de requêtes est inférieure à 25 %, cela signifie que query_cache_size est trop grand et peut être réduit de manière appropriée ; si l'utilisation du cache de requêtes est supérieure à 80 % et Qcache_lowmem_prunes > 50, cela signifie que query_cache_size peut être un peu petit, ou il est trop fragmenté.
# Taux de réussite du cache de requête = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit = 2k
stockage par défaut- engine = MyISAM
#Limiter la taille de la pile utilisée pour chaque thread de base de données. Les paramètres par défaut sont suffisants pour la plupart des applications
thread_stack = 192K
# Définissez le niveau d'isolement des transactions par défaut :
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-lecture non validée 2.READ COMMITTE-lecture validée 3.REPEATABLE READ-répétable lecture 4 . SERIALIZABLE - SERIAL
transaction_isolation = READ-COMMITTED
# La taille par défaut de tmp_table_size est de 32 M. Si une table temporaire dépasse cette taille, MySQL génère une erreur du type La table tbl_name est pleine. Si vous effectuez beaucoup de requêtes GROUP BY avancées, augmentez la valeur tmp_table_size.
tmp_table_size = 246M
max_heap_table_size = 246M
#Taille du cache d'index : Il détermine la vitesse de traitement de l'index de la base de données, en particulier l'index Vitesse de lecture
key_buffer_size = 512M
# Taille du tampon de lecture MySql. Une demande d'analyse séquentielle de la table allouera un tampon de lecture et MySql lui allouera un tampon mémoire. La variable read_buffer_size contrôle la taille de ce tampon. Si les requêtes d'analyse séquentielle d'une table sont très fréquentes et que vous estimez que les analyses fréquentes s'exécutent trop lentement, vous pouvez améliorer ses performances en augmentant la valeur de cette variable et la taille de la mémoire tampon.
read_buffer_size = 4M
# Taille du tampon de lecture aléatoire (opération de requête) de MySql. Lorsque les lignes sont lues dans n'importe quel ordre (par exemple, dans l'ordre trié), un tampon de lecture aléatoire est alloué. Lors de l'exécution d'une requête de tri, MySql analysera d'abord le tampon pour éviter les recherches sur le disque et améliorer la vitesse de requête. Si une grande quantité de données doit être triée, cette valeur peut être augmentée de manière appropriée. Cependant, MySql allouera cet espace tampon pour chaque connexion client, vous devez donc essayer de définir cette valeur de manière appropriée pour éviter une surcharge de mémoire excessive.
read_rnd_buffer_size = 16M
#Taille du cache de données d'insertion par lots, ce qui peut améliorer efficacement l'efficacité de l'insertion, la valeur par défaut est 8M
bulk_insert_buffer_size = 64M
# Mise en mémoire tampon requise pour la réorganisation lorsque les tables MyISAM changent
myisam_sort_buffer_size = 128M
# La taille maximale du fichier temporaire autorisée lorsque MySQL reconstruit l'index (lors de REPAIR, ALTER TABLE ou LOAD DATA INFILE).
# If la taille du fichier est supérieure à cette valeur, l'index sera créé via la mise en mémoire tampon clé-valeur (plus lente)
myisam_max_sort_file_size = 10G
# Si une table a plus de un index, MyISAM peut les corriger en utilisant plusieurs threads via un tri parallèle.
# C'est un bon choix pour les utilisateurs disposant de plusieurs processeurs et de grandes quantités de mémoire.
myisam_repair_threads = 1
# Vérifier et réparer automatiquement les tables MyISAM qui n'ont pas été fermées correctement
myisam_recover
interactive_timeout = 120
wait_timeout = 120
innodb_data_home_dir = /data/mysql/3306/data
#Données importantes du fichier d'espace table
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#Ce paramètre est utilisé pour définir la taille du pool de mémoire des informations du répertoire de données et d'autres structures de données internes stockées dans InnoDB, similaire à Cache de bibliothèque d'Oracle. Ce n'est pas un paramètre obligatoire et peut être dépassé.
innodb_additional_mem_pool_size = 16M
# Ceci est très important pour les tables Innodb. Les tables Innodb sont plus sensibles à la mise en mémoire tampon que les tables MyISAM. MyISAM peut fonctionner correctement avec le paramètre par défaut key_buffer_size, mais Innodb s'exécute à un rythme d'escargot avec le paramètre par défaut innodb_buffer_pool_size. Étant donné qu'Innodb met en cache les données et les index, il n'est pas nécessaire de laisser trop de mémoire au système d'exploitation, donc si vous avez uniquement besoin d'utiliser Innodb, vous pouvez le configurer jusqu'à 70 à 80 % de la mémoire disponible. Certaines règles qui s'appliquent à key_buffer sont : Si votre volume de données n'est pas important et n'augmentera pas de manière explosive, il n'est pas nécessaire de définir innodb_buffer_pool_size trop grand
innodb_buffer_pool_size = 512M
#Le nombre de threads d'E/S de fichier est généralement de 4, mais sous Windows, il peut être défini plus grand.
innodb_file_io_threads = 4
# Le nombre de threads autorisés dans le noyau InnoDb
# La valeur optimale dépend de l'application, matériel Et la méthode de planification du système d'exploitation.
# Une valeur trop élevée peut provoquer une exclusion mutuelle des threads.
innodb_thread_concurrency = 8
# Si ce paramètre est défini sur 1, le journal sera écrit sur le disque après la validation de chaque transaction. Pour offrir des performances, il peut être réglé à 0 ou 2, mais au risque de perdre des données en cas de panne. La valeur 0 signifie que le journal des transactions est écrit dans le fichier journal et que le fichier journal est vidé sur le disque une fois par seconde. La valeur 2 signifie que le journal des transactions sera écrit dans le journal lors de la validation, mais que le fichier journal sera vidé sur le disque une fois à la fois.
innodb_flush_log_at_trx_commit = 2
#Ce paramètre détermine la taille de la mémoire utilisée par certains fichiers journaux, en M. Un tampon plus grand peut améliorer les performances, mais des échecs inattendus entraîneront une perte de données. Les développeurs MySQL recommandent de le définir entre 1 et 8 M
innodb_log_buffer_size = 16M
# Ce paramètre. détermine la taille du fichier journal de données, en M, des paramètres plus grands peuvent améliorer les performances, mais augmenteront également le temps nécessaire pour récupérer une base de données défaillante
innodb_log_file_size = 128M
#Pour améliorer les performances, MySQL peut écrire des fichiers journaux dans plusieurs fichiers en boucle. Le paramètre recommandé est 3M
innodb_log_files_in_group = 3
# Lecture recommandée //m.sbmmt.com/
# Buffer_Pool Le nombre de Dirty_Pages affecte directement le temps d'arrêt d'InnoDB. Le paramètre innodb_max_dirty_pages_pct peut contrôler directement le ratio de Dirty_Page dans Buffer_Pool, et heureusement innodb_max_dirty_pages_pct peut être modifié dynamiquement. Par conséquent, avant de fermer InnoDB, réduisez innodb_max_dirty_pages_pct et forcez le vidage du bloc de données pendant un certain temps, ce qui peut considérablement raccourcir le temps d'arrêt de MySQL.
innodb_max_dirty_pages_pct = 90
# InnoDB a son mécanisme de détection de blocage intégré, qui peut entraîner l'annulation des transactions inachevées. Cependant, si vous utilisez l'instruction lock tables de MyISAM ou un moteur de transaction tiers en conjonction avec InnoDB, InnoDB ne peut pas reconnaître les blocages. Pour éliminer cette possibilité, innodb_lock_wait_timeout peut être défini sur une valeur entière qui indique à MySQL combien de temps (en secondes) attendre avant d'autoriser d'autres transactions à modifier les données qui sont finalement soumises à l'annulation de la transaction
innodb_lock_wait_timeout = 120
#Espace table exclusif (fermé)
innodb_file_per_table = 0
#démarrez mysqld avec –slow-query-log-file= /data/mysql/3306/slow.log
slow_query_log
long_query_time = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
#Configurer les mises à jour à partir de la base de données S'il faut écrire un binaire pour l'opération. Si cette bibliothèque esclave doit être la bibliothèque maître d'autres bibliothèques esclaves, alors ce paramètre doit être défini pour que la bibliothèque esclave puisse effectuer la synchronisation des journaux. Ce paramètre doit être utilisé avec -logs-binlog-slave-updates
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
#STATEMENT,ROW,MIXED
# Réplication basée sur les instructions (SBR), réplication basée sur les lignes (RBR), réplication en mode mixte (réplication basée sur des mélanges, MBR). En conséquence, il existe trois formats de binlog : STATEMENT, ROW et MIXED.
binlog_format = MIXED
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
skip-name-resolve
#master-connect -retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
server-id = 1
[mysqldump]
rapide
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[ mysqlhotcopy]
interactive-timeout

Ce qui précède est l'explication détaillée de l'optimisation du fichier de configuration MySQL my.cnf Pour plus d'informations. contenu, veuillez faire attention à PHP Chinese Net (m.sbmmt.com) !