Introduction aux idées d'optimisation MySQL

Le contenu de cet article est une introduction aux idées d'optimisation de MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Choses pour résoudre les problèmes au niveau de la base de données

Idées générales de réglage d'urgence :
En cas de retard soudain dans le traitement des affaires, le traitement commercial normal ne peut pas être effectué ! Un scénario qui doit être résolu immédiatement !

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

Idées générales de réglage :
Pour les décalages commerciaux périodiques, par exemple, l'entreprise est extrêmement lente à 10h-11h tous les jours, mais elle peut toujours être utilisée, et elle le sera bien après cette période.

1. Vérifiez le slowlog, analysez le slowlog et analysez les instructions de requête lentes.

2. Vérifiez toutes les déclarations lentes une par une selon une certaine priorité.

3. Analysez Top SQL, effectuez le débogage expliqué et vérifiez le temps d'exécution de l'instruction.

4. Ajustez l'index ou la déclaration elle-même.

1. Niveau système

cpu :
vmstat, sar top, htop, nmon, mpstat

Mémoire :
gratuite, ps - aux,

Périphériques IO (disque, réseau) :
iostat, ss, netstat, iptraf, iftop, lsof,

description de la commande vmstat :
Procs : r affiche combien les processus attendent le temps CPU. b Affiche le nombre de processus en veille ininterrompue. En attente d'E/S

Mémoire : swpd affiche le nombre de blocs de données échangés sur le disque. Le nombre de blocs de données inutilisés, de blocs de données du tampon utilisateur et de blocs de données utilisés par le système d'exploitation

Échange : le nombre de blocs de données que le système d'exploitation échange du disque à la mémoire et de la mémoire au disque par seconde. s1 et s0 sont de préférence 0

Io : le nombre de blocs de données écrits sur le périphérique b0 qui sont lus à partir du périphérique b1 par seconde. Reflète les E/S du disque

Système : affiche le nombre d'interruptions (in) et de changements de contexte (cs) qui se produisent par seconde

Cpu : affiche le nombre utilisé pour exécuter le code utilisateur, le code système , inactif, temps CPU en attente d'E/S

description de la commande iostat
Exemple de commande : iostat -dk 1 5
iostat -d -k -x 5 (Afficher l'utilisation du périphérique (%util) et temps de réponse (attendre))

tps : Le nombre de transmissions par seconde de l'appareil. « Un transfert » signifie « une demande d'E/S ». Plusieurs requêtes logiques peuvent être combinées en une « demande d'E/S unique ».

iops : Lorsque le matériel quitte l'usine, le fabricant définit un nombre maximum d'IOS par seconde. La taille de la requête "un transfert" est inconnue.

kB_read/s : La quantité de données lues sur l'appareil (lecteur exprimé) par seconde ;

KB_wrtn/s : La quantité de données écrites sur l'appareil (lecteur exprimé) par seconde ;

kB_read : la quantité totale de données lues ;

kB_wrtn : la quantité totale de données écrites ; ces unités sont des kilo-octets.

1. Solutions aux problèmes au niveau du système

Pensez-vous qu'il est préférable d'avoir une charge plus élevée ou une charge plus faible ?
En production réelle, on pense généralement qu'il n'y a pas de problème tant que le CPU ne dépasse pas 90 %.

Bien entendu, les situations particulières suivantes ne sont pas exclues :
Problème 1 : charge CPU élevée, charge d'E/S faible
Mémoire insuffisante

Mauvaises performances du disque

Problème SQL- ----->Accédez à la couche de base de données pour résoudre davantage le problème SQL

Il y a un problème avec les E/S (le disque est critique, la conception du raid n'est pas bonne, le raid est dégradé , verrouillé et le tps par unité de temps est trop élevé)

tps est trop élevé : beaucoup de petites IO de données, beaucoup d'analyses de table complètes

Problème 2 : charge d'IO élevée, faible charge du processeur
Beaucoup de petites opérations d'écriture d'E/S :

autocommit, qui génère un grand nombre de petites E/S

IO/PS est une valeur fixe du disque lorsque le matériel. En sortie d'usine, le constructeur définit un nombre maximum d'E/S par seconde.

Un grand nombre d'opérations d'écriture d'E/S volumineuses

La probabilité de problèmes SQL est relativement élevée

Problème 3 : les charges d'E/S et de CPU sont très élevées
Le matériel est pas assez ou il y a un problème avec SQL

5. Optimisation de base

1. Idées d'optimisation

Emplacement des points problématiques :
Matériel-- > Système--> Application-- > Base de données --> Architecture (haute disponibilité, séparation lecture-écriture, sous-base de données et sous-table)

Direction du traitement :
Objectifs d'optimisation clairs , faites un compromis entre performances et sécurité, et évitez les problèmes avant qu'ils ne surviennent

1. Optimisation du matériel

Aspect de l'hôte :
Selon le type de base de données, la sélection du processeur hôte, la capacité mémoire sélection, sélection de disque

Équilibrer la mémoire et les ressources du disque

E/S aléatoires et E/S séquentielles

BBU (Battery Backup Unit) de la carte RAID hôte est désactivée

Sélection du processeur :
Deux facteurs clés du processeur : Nombre de cœurs et fréquence principale

Choisissez en fonction des différents types d'activité :

CPU gourmand : plus de calculs, OLTP nécessite plus de processeurs et de cœurs avec une fréquence principale élevée

Intensif en IO : la comparaison des requêtes nécessite plus de cœurs OLAP et la fréquence principale n'est pas nécessairement élevée

Sélection de la mémoire :
La base de données de type OLAP nécessite plus de mémoire, ce qui est lié au niveau d'acquisition des données.

La mémoire générale pour les données de type OLTP est 2 à 4 fois supérieure au nombre de cœurs de processeur, et il n'existe pas de bonne pratique.

Stockage :
Choisissez différents périphériques de stockage en fonction des différents types de données stockées

Configurez un niveau RAID raisonnable (raid 5, raid 10, disque de secours)

Pour le système d'exploitation, il n'est pas nécessaire de faire des choix trop particuliers. Il est préférable de faire des redondances (raid1) (ssd, sas, sata)

carte raid : sélection de la carte raid hôte :
implémentation Operating Redondance du disque système (raid1)

Équilibrer les ressources mémoire et disque

E/S aléatoires et E/S séquentielles

BBU (Batterie) de l'unité de sauvegarde de la carte RAID hôte) pour fermer

Équipement réseau :
Utiliser le trafic pour prendre en charge des équipements réseau supérieurs (commutateurs, routeurs, câbles réseau, cartes réseau, cartes HBA)

Remarque : la planification ci-dessus doit être prise en compte lors de la conception initiale du système. .

1. Optimisation du matériel du serveur

1. Voyant d'état physique :

2. Propre appareil de gestion : carte de contrôle à distance (appareil FENCE : ipmi ilo idarc) , mise sous/hors tension, surveillance du matériel.

3. Des logiciels et équipements de surveillance tiers (snmp, agent) surveillent les installations physiques

4. Équipements de stockage : plateforme de surveillance intégrée. EMC2 (acquis par HP), Hitachi (hds), disques durs OEM bas de gamme IBM, le stockage haut de gamme est sa propre technologie, stockage Huawei

1. Optimisation du système

CPU :  Fondamentalement, aucun ajustement n'est requis, concentrez-vous simplement sur la sélection du matériel.

Mémoire :

En principe, aucun réglage n'est nécessaire, concentrez-vous simplement sur la sélection du matériel.

SWAP :

MySQL essaie d'éviter d'utiliser le swap. Le swap par défaut dans le serveur d'Alibaba Cloud est 0

IO :

raid, pas de lvm, ext4 ou xfs, ssd, stratégie de planification IO

Ajustement du swap (ne pas utiliser de partition de swap)

Ce paramètre détermine si Linux a tendance à utiliser le swap ou à libérer le cache du système de fichiers. Dans les situations où la mémoire est limitée, des valeurs inférieures ont tendance à libérer le cache du système de fichiers. Bien entendu, ce paramètre ne peut que réduire la probabilité d'utiliser le swap, mais ne peut pas empêcher Linux d'utiliser le swap.

Modifiez le paramètre de configuration MySQL innodb_flush_method et activez le mode O_DIRECT. Dans ce cas, le pool de tampons d'InnoDB contournera directement le cache du système de fichiers pour accéder au disque, mais le journal redo utilisera toujours le cache du système de fichiers. Il convient de noter que le journal Redo est en mode écrasement. Même si le cache du système de fichiers est utilisé, cela ne prendra pas trop de temps.

Stratégie de planification des E/S :

Ajustement des paramètres système.

Optimisation des paramètres du noyau du système Linux :

Paramètres de restriction utilisateur (MySQL n'a pas besoin de définir la configuration suivante) :

Optimisation des applications

Les applications métiers et les applications de base de données sont indépendantes, pare-feu : iptables, selinux et autres services inutiles (fermés) :

Ne démarrez pas l'interface graphique runlevel 3 lors de l'installation d'un serveur avec une interface graphique. De plus, demandez-vous si notre activité sera réelle à l'avenir. Avez-vous besoin de MySQL ou utilisez-vous un autre type de base de données ? L’état le plus élevé de l’utilisation d’une base de données est de ne pas utiliser de base de données.

6. Optimisation de la base de données

Direction de l'optimisation SQL :
Plan d'exécution, index, réécriture SQL

Direction de l'optimisation de l'architecture :

Architecture haute disponibilité, architecture haute performance, sous- Bibliothèque Sous-table

Optimisation des paramètres de base de données

Ajustement :

Instance globale (optimisation avancée, expansion)

Couche de connexion (optimisation de base)

Configurez des clients de connexion et des méthodes de connexion raisonnables

Cet article est ici Pour un contenu plus passionnant, vous pouvez prêter attention à la colonne

Vidéo du didacticiel MySQL du site Web PHP chinois !

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!