Référence des paramètres d'optimisation MySQL !

Avant-propos

Lorsqu'il s'agit de régler le fonctionnement et la maintenance quotidiens de MySQL, le fichier de configuration MySQL my.cnf ne peut être ignoré. Les paramètres par défaut de MySQL ne peuvent pas répondre aux besoins de notre activité quotidienne en ligne, l'optimisation des paramètres est donc également un lien indispensable. Je ne veux pas lister le nombre d'éléments qu'il y a dans la configuration my.cnf et la signification de chaque élément. Ceux-ci peuvent être trouvés dans la documentation officielle. Ce qui suit décrit uniquement certains paramètres auxquels il convient de prêter attention dans le travail quotidien.

Certains paramètres sont expliqués ci-dessous. Il existe bien sûr d'autres paramètres qui peuvent entrer en jeu, en fonction de votre charge ou de votre matériel : vous aurez besoin de réglages spéciaux dans des situations de mémoire lente et de disque rapide, de concurrence élevée et de charges de travail intensives en écriture. 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.

Configuration 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

Les données de table et les index sont stockés dans des espaces de table partagés ou des espaces de table séparés. Notre installation de scénario de travail définit innodb_file_per_table = ON par défaut, ce qui facilite également la migration d'espaces table séparés pendant le travail. Dans MySQL 5.6, la valeur par défaut de cette propriété est ON.

_flush_log_at_trx_commit

La valeur par défaut est 1, ce qui signifie 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 un comportement peu fiable. Étant donné que les transactions validées ne sont transmises au journal redo qu'une fois par seconde, cette valeur est acceptable dans certains scénarios. Par exemple, cette valeur est acceptable pour le nœud de sauvegarde du nœud principal. 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. Lorsqu'on parle de ce paramètre, un autre sync_binlog viendra certainement à l'esprit.

innodb_flush_method

Cette configuration détermine la manière dont les données et les journaux sont écrits sur le disque dur. Il existe trois méthodes au total. Nous utilisons O_DIRECT par défaut. Mode O_DIRECT : l'opération d'écriture du fichier de données s'effectue directement du tampon mysql innodb vers le disque, sans passer par le tampon du système d'exploitation. La véritable achèvement se fait dans l'étape de vidage, et le journal doit encore passer par le tampon du système d'exploitation.

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.

innodb_buffer_pool_size

Ce paramètre doit être pris en compte lors du fonctionnement et de la maintenance. Le pool de mémoire tampon est l'endroit où les données et les index sont mis en cache. Il s'agit d'un paramètre principal de MySQL. La valeur par défaut est de 128 Mo. Dans des circonstances normales, ce paramètre est défini sur 60 % à 70 % de la mémoire physique. (Cependant, nos instances sont essentiellement des déploiements mixtes de plusieurs instances, cette valeur doit donc être analysée en fonction de l'échelle de l'entreprise.)

innodb_log_file_size

C'est la taille du journal redo. La journalisation redo est utilisée pour garantir que les opérations d'écriture sont rapides et fiables et peuvent être récupérées en cas de panne. Si vous savez que votre application doit é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. (La taille spécifique doit être ajustée de manière appropriée en fonction de votre propre entreprise)

innodb_support_xa

innodb_support_xa peut changer la soumission de transaction XA en deux étapes d'InnoDB. Par défaut, innodb_support_xa=true prend en charge la soumission de transaction XA en deux étapes. Étant donné que la soumission de transactions XA en deux étapes entraîne un vidage redondant et d'autres opérations, l'impact sur les performances atteindra 10 %. Par conséquent, afin d'améliorer les performances, certains administrateurs de base de données définiront innodb_support_xa=false. Dans ce cas, redolog et binlog ne seront pas synchronisés et il peut y avoir des situations où les transactions sont soumises dans la base de données principale mais ne sont pas enregistrées dans le binlog. Cela peut également entraîner la perte de données de transaction.

innodb_additional_mem_pool_size

Ce paramètre est utilisé pour stocker les informations sur les champs de données et d'autres structures de données internes. Plus il y a de tables, plus il faut allouer de mémoire ici. Si InnoDB manque de mémoire dans ce pool, InnoDB commence à allouer de la mémoire du système d'exploitation et écrit des messages d'avertissement dans le journal des erreurs MySQL. La valeur par défaut est de 8 Mo. Le paramètre général est de 16 Mo.

max_connections

Le nombre par défaut de connexions sur le serveur MySQL est relativement faible, généralement autour de 100. Il est préférable de définir une valeur maximale plus grande. Généralement, si vous le réglez sur 500~1000, chaque lien occupera une certaine quantité de mémoire, donc plus le paramètre est grand, mieux c'est. Certaines personnes augmenteront la taille de ce paramètre lorsqu'elles rencontreront trop de connexions. Cependant, en fait, s'il y a un problème avec le volume d'activité ou la logique du programme ou si le SQL n'est pas bien écrit, même augmenter ce paramètre n'aidera pas. ce n'est qu'une question de temps avant qu'une erreur ne se reproduise. L’utilisation du pooling de connexions dans votre application ou du pooling de processus dans MySQL peut aider à résoudre ce problème.

• Allocation de mémoire au niveau de la session

max_threads(当前活跃连接数)* (

read_buffer_size– 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size– 随机读缓冲，提高随机读效率

+sort_buffer_size– 排序缓冲，提高排序效率

+join_buffer_size– 表连接缓冲，提高表连接效率

+binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß

+tmp_table_size– 内存临时表，提高临时表存储效率

+thread_stack– 线程堆栈，暂时寄存SQL语句/存储过程

+thread_cache_size– 线程缓存，降低多次反复打开线程开销

+net_buffer_length– 线程持连接缓冲以及读取结果缓冲

+bulk_insert_buffer_size– MyISAM表批量写入数据缓冲

)

• Allocation de mémoire au niveau global

global buffer(全局内存分配总和) =

innodb_buffer_pool_size

— InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+ innodb_additional_mem_pool_size

— InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size

— InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size

— MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size

–查询高速缓存，缓存查询结果，提高反复查询返回效率+table_cahce — 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache

–表定义文件描述符缓存，提高数据表打开效率

Le but ultime de l'optimisation des paramètres est de permettre à MySQL de mieux utiliser les ressources en contrôlant raisonnablement l'allocation de mémoire. Une utilisation raisonnable du processeur est recommandée pour réduire l'allocation de mémoire de session.

identifiant du serveur

Assurez-vous que l'ID du serveur est différent lors de la copie du schéma, généralement l'ID maître est plus petit que l'ID esclave.

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 indispensable 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). .

Les journaux binaires seront enregistrés de manière permanente une fois créés. 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 binaire 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 de réplique 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.

sync_binlog

La valeur par défaut de sync_binlog est 0. Comme le mécanisme du système d'exploitation pour actualiser d'autres fichiers, MySQL ne se synchronisera pas sur le disque mais s'appuie sur le système d'exploitation pour actualiser le journal binaire.

Lorsque sync_binlog = N (N>0), MySQL utilisera la fonction fdatasync() pour synchroniser son journal binaire écrit sur le disque à chaque fois qu'il écrira le journal binaire N fois. Il est plus sûr lorsque innodb_flush_log_at_trx_commit et sync_binlog sont tous deux égaux à 1. En cas de crash du service mysqld ou de crash de l'hôte du serveur, le journal binaire peut perdre au plus une instruction ou une transaction. Cependant, vous ne pouvez pas avoir le gâteau et le manger aussi. Double 1 entraînera des opérations d'E/S fréquentes, ce mode est donc également le moyen le plus lent. Pour nos considérations commerciales et lorsque la pression commerciale le permet, la configuration par défaut est double 1.

log_slave_update

Lorsque l'architecture en cascade doit être utilisée dans l'entreprise, le paramètre log_slave_update = 1 doit être activé, sinon le troisième niveau risque de ne pas pouvoir recevoir le binlog généré par le premier niveau, et donc la synchronisation des données ne pourra pas être effectuée.

tmpdir

Si la table temporaire de mémoire dépasse la limite, MySQL la convertira automatiquement en une table MyISAM sur disque et la stockera dans le répertoire tmpdir spécifié. Par conséquent, configurez tmpdir sur un périphérique de stockage avec de bonnes performances et une bonne vitesse autant que possible.

Lié au journal lent

slow_query_log = 1 #Ouvrir le journal lent

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5 #Définissez combien de secondes une requête sera saisie dans le journal lent

Autres questions

L'impact du SSD sur les paramètres

Avec le développement de la science et de la technologie, de plus en plus de dispositifs de stockage commencent à passer des composants mécaniques traditionnels au stockage permanent composé de composants électroniques, et les prix deviennent de plus en plus acceptables pour les entreprises. Une fois la vitesse des composants de stockage améliorée, il semble inutile d'utiliser la configuration de base de données de composants mécaniques traditionnels. Par conséquent, la configuration de MySQL doit être ajustée en fonction des différentes technologies de stockage, par exemple, innodb_io_capacity doit être augmentée, les fichiers journaux et les refaire doivent être placés. sur le disque dur mécanique, et l'annulation doit être placée sur le disque dur mécanique. Sur SSD, l'écriture atomique ne nécessite pas de double tampon d'écriture, de compression InnoDB, d'instances multiples sur une seule machine + groupe de contrôle, etc. Analysez la situation des E/S et ajustez dynamiquement innodb_io_capacity et innodb_max_dirty_pages_pct ; essayez d'ajuster innodb_adaptive_flushing pour voir l'effet.

Paramètres du pool de threads

Nous n'avons pas encore effectué de réglage pour innodb_write_io_threads et innodb_read_io_threads, mais je pense qu'en ajustant à 8 ou 16, les performances d'E/S du système seront meilleures. En outre, vous devez prêter attention aux points suivants : tout ajustement doit être basé sur des données et une analyse rigoureuse, sinon ce sera un discours vide de sens ; ce type de réglage est très significatif et peut vraiment apporter de la valeur. Nous devons donc travailler plus dur. et comprendre autant que possible pourquoi nous devons nous ajuster de cette façon.

Lié au processeur

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• table_definition_cache=2000

lié à l'IO

• Innodb_flush_method Il est recommandé d'utiliser O_DIRECT
• Innodb_io_capacity est défini sur l'IOPS maximum pris en charge par le disque
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• En termes de pré-lecture d'Innodb, si celui-ci repose sur un système d'index principal ou unique, il est recommandé de désactiver la pré-lecture
• Innodb_random_read_ahead = désactivé

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!