Voyons si MySQL provoquera un MOO s'il y a trop de requêtes de données

Cet article vous apporte des connaissances pertinentes sur la question de savoir si MySQL provoquera un MOO s'il y a trop de requêtes de données. J'espère qu'il vous sera utile.

La mémoire de l'hôte n'est que de 100 G. Nous devons maintenant analyser la table entière d'une grande table de 200 G. La mémoire de l'hôte de base de données sera-t-elle épuisée ?

Lors d’une sauvegarde logique, ne s’agit-il pas simplement d’une analyse de l’intégralité de la base de données ? Si cela consommait toute la mémoire, la sauvegarde logique n'échouerait-elle pas depuis longtemps ?

Il semble donc qu'il ne devrait y avoir aucun problème avec une analyse complète de la grande table. Pourquoi est-ce ?

L'impact de l'analyse complète de la table sur la couche serveur

Supposons que nous souhaitions maintenant effectuer une analyse complète de la table sur une table InnoDB 200G db1.t. Bien sûr, si vous souhaitez enregistrer les résultats de l'analyse sur le client, vous utiliserez une commande comme celle-ci :

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

Les données InnoDB sont enregistrées sur l'index de clé primaire, donc l'analyse complète de la table analyse directement l'index de clé primaire de la table. t. Étant donné que cette instruction de requête n'a pas d'autres conditions de jugement, chaque ligne trouvée peut être directement placée dans le jeu de résultats puis renvoyée au client.

Alors, où existe cet « ensemble de résultats » ?

Le serveur n'a pas besoin de sauvegarder un ensemble de résultats complet. Le processus d'obtention et d'envoi de données est le suivant :

Obtenez une ligne et écrivez-la dans "net_buffer". La taille de cette mémoire est définie par le paramètre "net_buffer_length"**, la valeur par défaut est 16k

Obtenez à plusieurs reprises les lignes jusqu'à ce que **"net_buffer"** soit plein, appelez l'interface réseau pour l'envoyer

Si l'envoi est réussi, effacez-le**" net_buffer", puis continuez à prendre la ligne suivante et écrivez-la dans "net_buffer"**

Si la fonction d'envoi renvoie **"EAGAIN" ou "WSAEWOULDBLOCK"**, cela signifie que le local la pile réseau (tampon d'envoi du socket) est pleine, entrez attendre. Jusqu'à ce que la pile réseau soit à nouveau accessible en écriture, continuez l'envoi

Processus d'envoi du résultat de la requête

Visible :

• Pendant le processus d'envoi d'une requête, la mémoire interne maximale occupée par MySQL est de **"net_buffer_length"** comme ce Large, il n'atteindra pas 200G

• socket send buffer et il est impossible d'atteindre 200G (définition par défaut /proc/sys/net/core/wmem_default Si le socket send buffer est plein, le processus de lecture). les données seront suspendues

Donc, MySQL est en fait en train de "lire et envoyer". Cela signifie que si le client reçoit lentement, le serveur MySQL ne pourra pas envoyer les résultats et le temps d'exécution de la transaction deviendra plus long.

Par exemple, l'état suivant est le résultat vu par show processlist sur le serveur lorsque le client ne lit pas le contenu du **"socket contain buffer"**.

L'envoi côté serveur est bloqué

Si vous voyez que l'État est toujours en train d'"envoyer au client", cela signifie que la pile réseau côté serveur est pleine.

Si le client utilise le paramètre –quick, la méthode mysql_use_result sera utilisée : lire une ligne et traiter une ligne. Supposons que la logique d'une certaine entreprise est relativement complexe et que si la logique à traiter après la lecture de chaque ligne de données est très lente, il faudra beaucoup de temps au client pour récupérer la ligne de données suivante, et ce qui précède Le résultat peut apparaître.

Par conséquent, pour les affaires en ligne normales, si une requête ne renvoie pas beaucoup de résultats, il est recommandé d'utiliser l'interface **"mysql_store_result"** pour enregistrer directement les résultats de la requête dans la mémoire locale.

Bien sûr, le principe est que la requête ne renvoie pas beaucoup de résultats. S'il y en a trop, le client occupera près de 20 Go de mémoire car une requête volumineuse est exécutée. Dans ce cas, vous devez plutôt utiliser l'interface "mysql_use_result".

Si vous voyez de nombreux fils de discussion dans « Envoi au client » dans MySQL dont vous êtes responsable de la maintenance, cela signifie que vous souhaitez que vos étudiants en développement commercial optimisent les résultats de la requête et évaluent si autant de résultats renvoyés sont raisonnables.

Si vous souhaitez réduire rapidement le nombre de threads dans cet état, vous pouvez définir **"net_buffer_length"** pour qu'il soit plus grand.

Parfois, l'état de nombreuses instructions de requête sur l'instance est « Envoi de données », mais il n'y a aucun problème lors de la vérification du réseau. Pourquoi l'envoi de données prend-il autant de temps ?

Le changement de statut d'une instruction de requête est comme ceci :

• Une fois que l'instruction de requête MySQL entre dans la phase d'exécution, elle définit d'abord le statut sur "Envoi de données"

• Ensuite, elle envoie les informations relatives à la colonne du résultat de l'exécution (métadonnées) au client

• puis continuez le processus d'exécution de l'instruction

• Une fois l'exécution terminée, définissez le statut sur une chaîne vide.

Autrement dit, « Envoi de données » ne signifie pas nécessairement « envoi de données », mais peut avoir lieu à n'importe quelle étape du processus d'exécution. Par exemple, vous pouvez créer un scénario de verrouillage en attente et voir l'état d'envoi des données.

La lecture de l'intégralité du tableau est verrouillée :

Statut d'envoi des données

On peut voir que la session2 attend le verrouillage et l'état est affiché comme Envoi de données.

• Seulement lorsqu'un fil de discussion est dans l'état "En attente que le client reçoive les résultats", "Envoi au client" sera affiché

• S'il est affiché comme "Envoi de données", cela signifie simplement "exécuter"

Par conséquent, les résultats de la requête sont envoyés au client par segments, donc la table entière est analysée et la requête renvoie une grande quantité de données sans exploser la mémoire.

Ce qui précède est la logique de traitement de la couche serveur. Comment est-elle traitée dans le moteur InnoDB ?

L'impact de l'analyse complète de la table sur InnoDB

L'une des fonctions de la mémoire InnoDB est de sauvegarder les résultats mis à jour et de coopérer avec le journal de rétablissement pour éviter les écritures aléatoires sur le disque.

Les pages de données de la mémoire sont gérées dans Buffer Pool (appelé BP), et BP joue un rôle dans l'accélération des mises à jour dans WAL.

BP peut également accélérer les requêtes.

En raison de WAL, lorsque la transaction est validée, la page de données sur le disque est ancienne. S'il y a une requête pour lire la page de données immédiatement, le journal redo doit-il être appliqué immédiatement à la page de données ?

Pas besoin. Parce qu'à ce moment, le résultat de la page de données mémoire est le dernier, il suffit de lire directement la page mémoire. À l'heure actuelle, la requête n'a pas besoin de lire le disque et les résultats sont récupérés directement de la mémoire, ce qui est très rapide. Par conséquent, Buffer Pool peut accélérer les requêtes.

L'effet d'accélération de BP sur les requêtes dépend d'un indicateur important, à savoir : le taux d'accès à la mémoire.

Vous pouvez vérifier le taux de réussite BP actuel d'un système dans les résultats de l'état show engine innodb. D'une manière générale, pour qu'un système en ligne avec un service stable garantisse que le temps de réponse répond aux exigences, le taux d'accès à la mémoire doit être supérieur à 99 %.

Exécutez show engine innodb status, vous pouvez voir les mots « Taux de réussite du pool de tampons », qui affiche le taux de réussite actuel. Par exemple, le taux de réussite dans l'image ci-dessous est de 100 %.

Si toutes les pages de données requises pour la requête peuvent être obtenues directement à partir de la mémoire, c'est le meilleur, et le taux de réussite correspondant est de 100 %.

La taille du pool de tampons InnoDB est déterminée par le paramètre **"innodb_buffer_pool_size"** Il est généralement recommandé de le définir sur 60 % à 80 % de la mémoire physique disponible.

Il y a environ dix ans, le volume de données d'une seule machine était de plusieurs centaines de gigaoctets et la mémoire physique de plusieurs gigaoctets. Aujourd'hui, bien que de nombreux serveurs puissent avoir 128 Go de mémoire ou plus, le volume de données d'une seule machine a atteint le niveau T. .

Il est donc courant que **"innodb_buffer_pool_size"** soit plus petit que la taille des données du disque. Si un pool de mémoire tampon est plein et qu'une page de données doit être lue à partir du disque, une ancienne page de données doit être éliminée.

Gestion de la mémoire InnoDB

utilise l'algorithme Les moins récemment utilisés (LRU) pour éliminer les données qui n'ont pas été utilisées depuis le plus longtemps.

Algorithme LRU de base

L'algorithme LRU d'InnoDB pour la gestion de BP est implémenté à l'aide d'une liste chaînée :

• state1 La tête de la liste chaînée est P1, ce qui signifie que P1 est la page de données récemment consultée

• At. cette fois, une requête de lecture accède à P3, elle devient donc l'état 2, et P3 est déplacé au premier plan

• L'état 3 signifie que la page de données consultée cette fois n'existe pas dans la liste chaînée, donc une nouvelle page de données Px doit être demandé dans BP et ajouté à l’en-tête de la liste chaînée. Mais comme la mémoire est pleine, aucune nouvelle mémoire ne peut être demandée. Ainsi la mémoire de la page de données Pm en fin de liste chaînée est effacée, le contenu de Px est stocké, et placé en tête de la liste chaînée

Au final, la page de données Pm qui n'a pas été l'accès le plus long est éliminé.

Que se passera-t-il si nous voulons faire une analyse complète du tableau à ce moment-là ? Si vous souhaitez analyser une table 200G, cette table est une table de données historiques et aucune entreprise n'y accède généralement.

Ensuite, l'analyse selon cet algorithme éliminera toutes les données du BP actuel et stockera le contenu de la page de données consultée pendant le processus d'analyse. En d’autres termes, BP stocke principalement les données de ce tableau de données historiques.

Pour une bibliothèque qui fournit des services aux entreprises, cela n'est pas possible. Vous verrez que le taux d'accès à la mémoire BP diminue fortement, la pression du disque augmente et la réponse des instructions SQL ralentit.

Ainsi, InnoDB ne peut pas utiliser directement le LRU brut. InnoDB l'optimise.

Algorithme LRU amélioré

InnoDB divise la liste chaînée en nouvelle zone et ancienne zone dans un rapport de 5:3. Dans la figure, LRU_old pointe vers la première position de l'ancienne zone, qui représente 5/8 de l'ensemble de la liste chaînée. Autrement dit, 5/8 près de la tête de la liste chaînée correspond à la nouvelle zone et 3/8 près de la fin de la liste chaînée correspond à l'ancienne zone.

Processus d'exécution de l'algorithme LRU amélioré :

État 1, vous devez accéder à P3 Puisque P3 est dans la zone Nouveau, c'est la même chose que LRU avant optimisation, déplacez-le en tête de la liste chaînée => Après l'état 2.

, vous devez en accéder à une nouvelle. Pour les pages de données qui n'existent pas dans la liste chaînée actuelle, la page de données Pm est toujours éliminée à ce moment, mais la page de données nouvellement insérée Px est placée à **"LRU_old"* *

La page de données dans l'ancienne zone est supprimée à chaque fois. Lors de l'accès, vous devez porter les jugements suivants :

Si la page de données existe dans la liste chaînée LRU depuis plus d'une seconde, déplacez-la en tête de la liste chaînée.

Si la page de données existe dans la liste chaînée LRU depuis moins de 1 seconde, l'emplacement reste inchangé. 1s est contrôlé par le paramètre "innodb_old_blocks_time", la valeur par défaut est 1000, unité ms.

Cette stratégie est conçue pour gérer des opérations telles que des analyses de table complètes. Ou scannez le tableau de données historiques 200G :

4. Pendant le processus de numérisation, les pages de données qui doivent être nouvellement insérées sont placées dans l'ancienne zone

5. Il y a plusieurs enregistrements dans une page de données, et cette page de données le sera. être consulté plusieurs fois, mais en raison de l'analyse séquentielle, l'intervalle de temps entre le premier accès et le dernier accès à cette page de données ne dépassera pas 1 seconde, elle sera donc toujours conservée dans l'ancienne zone

6. données ultérieures, la précédente Cette page de données ne sera plus accessible à l'avenir, il n'y aura donc jamais de chance de passer en tête de la liste chaînée (Nouvelle zone), et elle sera bientôt éliminée.

On peut voir que le plus grand avantage de cette stratégie est que dans le processus d'analyse de cette grande table, bien que BP soit également utilisé, elle n'a aucun impact sur la zone jeune, garantissant ainsi le taux de réussite des requêtes du Buffer Piscine en réponse aux affaires normales.

Résumé

MySQL utilise la logique de calcul et d'envoi en même temps, donc pour les résultats de requête avec une grande quantité de données, l'ensemble de résultats complet ne sera pas enregistré côté serveur. Par conséquent, si le client ne lit pas les résultats à temps, il bloquera le processus de requête MySQL, mais ne saturera pas la mémoire.

Quant au moteur InnoDB, grâce à la stratégie d'élimination, les requêtes volumineuses ne provoqueront pas d'explosion de mémoire. De plus, comme InnoDB a amélioré l'algorithme LRU, l'impact de l'analyse complète de la table des données froides sur le pool de tampons peut également être contrôlé.

L'analyse complète de la table consomme toujours des ressources IO, il n'est donc toujours pas possible d'effectuer directement une analyse complète de la table en ligne sur la base de données principale pendant les périodes de pointe.

Apprentissage recommandé : Tutoriel vidéo mysql

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!