Optimisation des performances MySQL

Pour le full stack, des compétences en bases de données sont indispensables, base de données relationnelle ou nosql, base de données mémoire ou base de données de stockage sur disque partiel, objet Il existe de nombreux types de bases de données stockées ou bases de données graphiques, mais la première compétence essentielle doit être MySQL. De l’essor de LAMP à l’émergence de Mariadb, en passant par l’arrivée de PG, les compétences MySQL sont d’une grande utilité.

Il existe de nombreux aspects de la technologie des bases de données MySQL. Nous couvrons ici uniquement le réglage des performances nécessaire. Nous recommandons un réglage ascendant des performances, qui comprend principalement l'environnement d'exploitation, les paramètres de configuration, les performances SQL et le réglage de la conception de l'architecture système. .

Réglage de l'environnement d'exploitation

C'est le monde de Linux, et le réglage de l'environnement d'exécution MySQL est souvent complété avec le réglage du noyau Linux. Bien entendu, il a également un certain rôle de référence pour le service cloud RDS.

Ajustez l'algorithme de planification des E/S par défaut de Linux

L'objectif global du planificateur d'E/S est de faire en sorte que la tête bouge toujours dans une direction, puis dans la même direction. Dans la direction opposée, c'est exactement le modèle d'ascenseur dans la vie réelle, donc le planificateur d'E/S est également appelé ascenseur, et l'algorithme correspondant est également appelé algorithme d'ascenseur. Il existe plusieurs algorithmes d'ascenseur pour la planification d'E/S sous Linux, l'un d'eux s'appelle. comme (Anticipatoire), l'un est appelé cfq (Complete Fairness Queueing), l'autre est appelé date limite et l'autre est appelé noop (No Operation).

IO a un plus grand impact sur la base de données. de Linux est cfq doit être modifié jusqu'à la date limite. S'il s'agit d'un périphérique SSD ou PCIe-SSD, il doit être modifié en noop. Vous pouvez utiliser les deux méthodes de modification suivantes.

1. La modification dynamique en ligne échouera après le redémarrage.

echo “deadline” > /sys/block/sda/queue/scheduler

2. Modifiez /etc/grub.conf et cela prendra effet définitivement.

Modifiez le fichier de configuration /etc/grub.conf et ajoutez une configuration dans la ligne du noyau, par exemple :

elevator=deadline

Concentrez-vous principalement sur le paramètre d'ascenseur. vous devez redémarrer le système pour prendre effet.

Désactiver la fonctionnalité numa

Le NUMA de l'architecture de nouvelle génération n'est pas adapté à l'exécution de bases de données. NUMA est destiné à améliorer l'utilisation de la mémoire, mais cela peut entraîner un processeur. mémoire insuffisante. La mémoire restante n'est pas suffisante et un problème d'échange se produit. Par conséquent, il est généralement recommandé de désactiver ou de modifier la planification NUMA.

numa=off

2. Modifiez le script /etc/init.d/mysql ou mysqld_safe pour définir le mécanisme de planification NUMA lors du démarrage du processus mysqld, tel que numactl –interleave=all.

Modifier les paramètres de swappiness

swappiness est un paramètre du noyau de Linux qui est utilisé pour contrôler la stratégie d'échange de mémoire physique. Il autorise une valeur en pourcentage, avec le minimum. étant 0 , le maximum est 100 et la valeur par défaut est 60. Quel est l'impact de cette valeur de paramètre ?

Régler vm.swappiness sur 0 signifie utiliser le swap le moins possible, et 100 signifie essayer d'échanger les pages de mémoire inactives vers un cache d'échange ou de libération. La mémoire inactive signifie la mémoire mappée par le programme mais non utilisée pendant une « longue période ». Nous pouvons utiliser vmstat pour voir la quantité de mémoire inactive dans le système.

# vmstat -a 1

Il est recommandé de définir cette valeur sur 1. La méthode de paramétrage est la suivante. Ajoutez une ligne au fichier /etc/sysctl.conf.

vm.swappiness = 1

Développer le descripteur de fichier

Il s'agit d'un paramètre fréquemment modifié, et les programmes à haute concurrence le modifieront.

ulimit -n 51200

2 . Modifiez le fichier de configuration et il prendra effet définitivement.

在/etc/security/limits.conf配置文件中增加

* hardnofile 51200
 
* softnofile 51200

面向session的进程文件描述符的修改稍有不同，在云上的修改也略有差异，可以参见一样的“open too many files”

优化文件系统挂载参数。

对于文件系统，如无特殊要求，最好采用ext4.

文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。

noatime表示不记录访问时间,nodiratime不记录目录的访问时间。

barrier=0,表示关闭barrier功能.

barrier的主要目的是为了保证磁盘写数据的安全性,但是会降低性能。如果有BBU之类的电池备份电源保证控制卡不瞬间掉电,那么这个功能就可以放心大胆的关闭。

配置参数调优

my.cnf中的配置参数调优取决于业务，负载或硬件，在慢内存和快磁盘、高并发和写密集型负载情况下，都需要特殊的调整。

基本配置

query_cache_size

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

max_connections

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

back_log

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

Innodb配置

innodb_file_per_table

Ce paramètre indique à InnoDB s'il doit stocker les données et les index de toutes les tables dans un espace table partagé (innodb_file_per_table = OFF) ou placer les données de chaque table 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. Dans MySQL 5.6, la valeur par défaut de cette propriété est ON.

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 la préoccupation concerne 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. Une valeur de 0 est plus rapide, mais certaines données peuvent être perdues en cas de panne du système, donc un seul passage ne s'applique qu'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 ê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 tampon alloué pour les transactions qui n'ont pas encore été exécutées. Mais si la transaction contient des objets binaires volumineux ou des champs de texte volumineux, regardez la variable d'état Innodb_log_waits. Si elle n'est pas 0, augmentez innodb_log_buffer_size.

Autres configurations

log_bin

Si le serveur de base de données agit comme un nœud de sauvegarde pour le nœud maître, alors l'activation du journal binaire est nécessaire. Même si vous n'avez qu'un seul serveur, cela est utile si vous souhaitez effectuer une récupération de données à un moment donné. Une fois créé, le journal binaire est enregistré définitivement. 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.

interactive_timeout

Le nombre de secondes pendant lesquelles le serveur attend une action sur une connexion interactive avant de la fermer. Un client interactif est défini comme celui qui utilise l'option client_interactive de mysql_real_connect(). La valeur par défaut est 28 800 et il est recommandé de la remplacer par 7 200.

table_open_cache

Chaque fois que MySQL ouvre une table, il lira certaines données dans le cache table_open_cache. Lorsque MySQL ne trouve pas les informations correspondantes dans ce cache, il les lira à partir du disque. En supposant que le système dispose de 200 connexions simultanées, vous devez définir ce paramètre sur 200*N (N est le nombre de descripteurs de fichiers requis pour chaque connexion) ; lorsque table_open_cache est défini sur une valeur élevée, si le système ne peut pas gérer autant de fichiers. symbole de descripteurs, le client échouera et la connexion ne pourra pas être établie.

max_allowed_packet

Taille de paquet acceptée ; il est prudent d'augmenter la valeur de cette variable car de la mémoire supplémentaire ne sera allouée qu'en cas de besoin. Par exemple, MySQLd allouera plus de mémoire uniquement si vous émettez une requête longue ou si MySQLd doit renvoyer de grandes lignes de résultats. La petite valeur par défaut de cette variable est une mesure de précaution pour capturer les paquets d'erreur entre le client et le serveur et pour garantir qu'un débordement de mémoire n'est pas causé par l'utilisation accidentelle de paquets volumineux

skip_name_resolve

Lorsque le client se connecte au serveur de base de données, 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.

Réglage des instructions SQL

Au niveau de la couche application, grâce à la combinaison de l'outil pt et du journal de requêtes lent, vous pouvez facilement identifier les instructions de l'analyse complète de la table.

Principes de base

• Éviter les analyses de tables complètes

• Construire des index

• Essayez d'éviter de renvoyer de grandes quantités de données au client. Si la quantité de données est trop importante, vous devez vous demander si les exigences correspondantes sont raisonnables

• Essayez de évitez les opérations de transactions volumineuses et améliorez les capacités de concurrence du système

• Avant d'utiliser la méthode basée sur le curseur ou la méthode de table temporaire, vous devez d'abord rechercher une solution basée sur un ensemble pour résoudre le problème. La méthode basée sur les ensembles est généralement plus efficace. Essayez d'éviter d'utiliser des curseurs car ils sont moins efficaces.

Conseils

À propos des conditions après où

• doit être évité dans la clause Where car autant que possible Utilisez l'opérateur != ou <>, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table.

• Vous devriez essayer d'éviter d'utiliser ou dans la clause Where pour connecter les conditions. Vous pouvez envisager d'utiliser union au lieu de

• in et not in. . Soyez également prudent. Utilisez, pour les valeurs continues, si vous pouvez utiliser between, n'utilisez pas in, existe au lieu de in

• Essayez d'éviter les opérations d'expression et les opérations de fonction sur les champs dans le. clause Where

À propos des types de données

• Essayez d'utiliser des champs numériques Si les champs ne contiennent que des informations numériques, essayez de ne pas les concevoir comme des types de caractères. . Cela réduira l’efficacité des requêtes et des connexions et augmentera la surcharge de stockage.

• Utilisez autant que possible varchar/nvarchar au lieu de char/nchar, car l'espace de stockage des champs de longueur variable est petit et pour les requêtes, l'efficacité de la recherche dans un champ relativement petit est évidemment plus élevé.

• Il est préférable de ne pas laisser NULL dans la base de données. Utilisez NOT NULL pour remplir autant que possible la base de données. Les notes, descriptions, commentaires, etc. peuvent être définis sur NULL. pour d'autres, il est préférable de ne pas utiliser NULL .

• N'utilisez select * from t nulle part, remplacez "*" par une liste de champs spécifique et ne renvoyez aucun champ inutilisé.

À propos des tables temporaires

• Évitez la création et la suppression fréquentes de tables temporaires pour réduire la consommation des ressources des tables système. Pour les événements ponctuels, il est préférable d'utiliser des tables d'exportation.

• Lors de la création d'une table temporaire, si la quantité de données insérées en même temps est importante, vous pouvez utiliser select into au lieu de create table pour éviter de provoquer un grand nombre de journaux afin d'améliorer la vitesse. ; si la quantité de données n'est pas importante, afin d'alléger les ressources de la table système, vous devez d'abord créer la table puis l'insérer.

• Si des tables temporaires sont utilisées, lorsque toutes les tables temporaires sont explicitement supprimées à la fin, tronquez d'abord la table, puis supprimez-la. Cela peut éviter le verrouillage à long terme des tables système.

À propos des index

• Vous devez d'abord envisager de créer des index sur les colonnes impliquées dans où et trier par.

• Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index composite, le premier champ de l'index doit être utilisé comme condition pour garantir que le système utilise l'index, sinon, l'index ne sera pas utilisé et l'ordre des champs doit être cohérent avec l'ordre de l'index dans la mesure du possible.

• Plus il y a d'index, mieux c'est. Bien que l'index puisse améliorer l'efficacité de la sélection correspondante, il réduit également l'efficacité de l'insertion et de la mise à jour, car il peut être reconstruit lors de l'insertion ou de la mise à jour. .index, donc cela dépend du cas. Il est préférable de ne pas avoir plus de 7 index sur une table. S'il y en a trop, vous devez vous demander s'il est nécessaire de créer des index sur certaines colonnes qui ne sont pas couramment utilisées.

Réglage de l'architecture de base de données

Du bas à la couche application, et enfin à la couche architecture, cependant, parler d'architecture sans logique métier doit être un voyou. L'architecture de la base de données dépend également du système d'entreprise, et la clé est de servir le système d'entreprise de manière stable et flexible. Les directions de réglage de l'architecture sont :

• Partition et table

• Sous-bibliothèque métier

• Séparation principale de la synchronisation esclave et de la lecture et de l'écriture

• Cache de données

• Veille chaude maître-esclave et HA actif-actif

• …..

Ce qui précède est le contenu du réglage des performances de MySQL. Pour plus de contenu connexe, veuillez faire attention au chinois PHP. site Web (www.php.cn) !