Maîtrisez complètement la solution au délai maître-esclave MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui organise principalement les problèmes liés à la solution du délai maître-esclave, y compris ce qu'est le délai maître-esclave, la source du délai maître-esclave et la solution du délai maître-esclave Jetons un coup d'œil au contenu ci-dessous, j'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Dans les projets précédents, la séparation lecture-écriture a été implémentée sur la base de la réplication maître-esclave MySQL et de l'AOP, et j'ai également écrit un blog pour enregistrer ce processus de mise en œuvre. Puisque la réplication maître-esclave MySQL est configurée, il y aura naturellement un retard maître-esclave. Comment minimiser l'impact du retard maître-esclave sur le système d'application est un point de réflexion nécessaire. Je pense personnellement que la solution au retard maître-esclave est. pour implémenter la séparation lecture-écriture, l'essence de la réplication maître-esclave MySQL.

Concernant ce sujet, j'avais effectivement pensé à écrire un blog pour le partager auparavant, mais je ne l'ai jamais mis à l'ordre du jour. Récemment, un lecteur a laissé un message posant cette question dans « SpringBoot Implements MySQL Read-Write Separation », ce qui m'a également inspiré pour écrire cet article. Concernant cette problématique, j'ai lu beaucoup d'informations et de blogs, et grâce à ma propre pratique, j'ai résumé ce blog en me tenant sur les épaules du patron.

Qu'est-ce que le délai maître-esclave

Avant de discuter de la façon de résoudre le délai maître-esclave, comprenons d'abord ce qu'est le délai maître-esclave.

Afin de terminer la réplication maître-esclave, la bibliothèque esclave doit obtenir le contenu du binlog lu par le thread de dump dans la bibliothèque maître via le thread d'E/S et l'écrire dans son propre journal de relais, puis dans le thread SQL. de la bibliothèque esclave lit le journal du relais, refaire le journal dans le journal du relais équivaut à exécuter à nouveau SQL et à mettre à jour votre propre base de données pour obtenir la cohérence des données.

Les points temporels liés à la synchronisation des données comprennent principalement les trois suivants :

1. La bibliothèque principale termine l'exécution d'une transaction, l'écrit dans le binlog et enregistre ce moment comme T1
2. Ensuite, il est transmis à la bibliothèque esclave ; , et la bibliothèque esclave reçoit ceci. L'heure dans binlog est enregistrée comme T2
3. La bibliothèque esclave exécute cette transaction et enregistre cette heure comme T3 ;

Le délai dit maître-esclave est la différence entre le moment où l'exécution de la bibliothèque esclave est terminée et le moment où l'exécution de la bibliothèque principale est terminée pour la même transaction, qui est T3 - T1. T3 - T1。

可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。
seconds_behind_master 的计算方法是这样的：

1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

在网络正常的时候，日志从主库传给从库所需的时间是很短的，即 T2 - T1 的值是非常小的。也就是说，网络正常情况下，主从延迟的主要来源是从库接收完 binlog 和执行完这个事务之间的时间差。

由于主从延迟的存在，我们可能会发现，数据刚写入主库，结果却查不到，因为可能还未同步到从库。主从延迟越严重，该问题也愈加明显。

主从延迟的来源

主库和从库在执行同一个事务的时候出现时间差的问题，主要原因包括但不限于以下几种情况：

• 有些部署条件下，从库所在机器的性能要比主库性能差。
• 从库的压力较大，即从库承受了大量的请求。
• 执行大事务。因为主库上必须等事务执行完成才会写入 binlog，再传给备库。如果一个主库上语句执行 10 分钟，那么这个事务可能会导致从库延迟 10 分钟。
• 从库的并行复制能力。

主从延迟的解决方案

解决主从延迟主要有以下方案：

1. 配合 semi-sync 半同步复制；
2. 一主多从，分摊从库压力；
3. 强制走主库方案（强一致性）；
4. sleep 方案：主库更新后，读从库之前先 sleep 一下；
5. 判断主备无延迟方案（例如判断 seconds_behind_master
Vous pouvez exécuter la commande show slave status sur la base de données de secours, et son résultat de retour affichera seconds_behind_master, qui est utilisé pour indiquer combien de secondes dure la base de données de secours actuelle. retardé.
seconds_behind_master est calculé comme suit :
6. Il y a un champ d'heure dans le journal binaire de chaque transaction, qui est utilisé pour enregistrer l'heure écrite dans la base de données principale

Récupérer dans la base de données principale ; base de données de secours La valeur du champ d'heure de la transaction en cours d'exécution est calculée, et la différence entre elle et l'heure actuelle du système est calculée pour obtenir seconds_behind_master.

Lorsque le réseau est normal, le temps nécessaire pour transférer les journaux de la base de données maître vers la base de données esclave est très court, c'est-à-dire que la valeur de T2 - T1 est très petite. En d’autres termes, dans des conditions de réseau normales, la principale source de retard maître-esclave est le décalage horaire entre la bibliothèque esclave recevant le binlog et l’exécution de la transaction.

En raison de l'existence d'un délai maître-esclave, nous pouvons constater que les données viennent d'être écrites dans la base de données maître, mais le résultat ne peut pas être trouvé car il n'a peut-être pas encore été synchronisé avec la base de données esclave. Plus le retard maître-esclave est important, plus ce problème devient évident. La source du retard maître-esclave

Il existe un problème de décalage horaire entre la bibliothèque maître et la bibliothèque esclave lors de l'exécution de la même transaction. Les principales raisons incluent, sans s'y limiter, les situations suivantes :

🎜Sous. certaines conditions de déploiement, 🎜La bibliothèque esclave Les performances de la machine sont moins bonnes que les performances de la base de données principale🎜. 🎜🎜🎜La base de données esclaves est soumise à une plus grande pression🎜, c'est-à-dire que la base de données esclaves est soumise à un grand nombre de requêtes. 🎜🎜🎜Exécuter de grandes choses🎜. Parce que la base de données principale doit attendre que l'exécution de la transaction soit terminée avant d'être écrite dans le binlog puis transmise à la base de données de secours. Si l'exécution d'une instruction sur une base de données maître prend 10 minutes, cette transaction peut entraîner un retard de 10 minutes dans la base de données esclave. 🎜🎜🎜Capacité de copie parallèle depuis la bibliothèque🎜. 🎜

🎜Solutions pour le délai maître-esclave🎜🎜Pour résoudre le délai maître-esclave, il existe principalement les solutions suivantes : 🎜🎜🎜🎜Coopérer avec la réplication semi-synchrone semi-synchrone🎜 ; plusieurs esclaves🎜 pour partager la pression sur la base de données esclave ; 🎜🎜🎜Forcer la solution de la bibliothèque principale🎜 (forte cohérence) ; 🎜🎜solution de mise en veille : une fois la bibliothèque principale mise à jour, mettre en veille avant de lire à partir de la bibliothèque esclave ; solution sans délai maître-esclave (par exemple, le paramètre juge seconds_behind_master code> est déjà égal à 0, site de comparaison 🎜🎜🎜Réplication parallèle🎜 - résout le problème du délai de copie depuis la bibliothèque ); 🎜Ici, nous présentons principalement plusieurs solutions que j'utilise dans le projet, à savoir 🎜Réplication semi-synchrone, le fonctionnement en temps réel force l'utilisation de la bibliothèque principale et la réplication parallèle🎜. 🎜🎜🎜Réplication semi-synchrone semi-synchrone🎜🎜🎜MySQL dispose de trois modes de synchronisation, qui sont : 🎜<p><strong>"Réplication asynchrone"</strong> : la réplication par défaut de MySQL est asynchrone. La base de données principale renverra immédiatement les résultats au client après avoir exécuté la transaction soumise par le client, et ne se soucie pas de savoir si la base de données esclave l'a reçue et traitée. Il y aura un problème. Une fois la base de données principale en panne, les transactions qui ont été soumises sur la base de données principale risquent de ne pas être transmises à la base de données esclave pour des raisons de réseau si un basculement est effectué à ce moment-là et que l'esclave est promu de force. au maître, cela peut causer Les données sur le nouveau maître sont incomplètes. </p> <p><strong>"Réplication entièrement synchrone"</strong> : Cela signifie que lorsque la bibliothèque principale a terminé une transaction et que toutes les bibliothèques esclaves ont exécuté la transaction, la bibliothèque principale soumettra la transaction et renverra les résultats au client. Étant donné que vous devez attendre que toutes les bibliothèques esclaves terminent la transaction avant de revenir, les performances de la réplication entièrement synchrone seront inévitablement sérieusement affectées. </p> <p><strong>"Réplication semi-synchrone"</strong> : C'est un type entre la réplication entièrement synchrone et la réplication entièrement asynchrone. La bibliothèque principale n'a besoin que d'attendre qu'au moins une bibliothèque esclave reçoive et écrive dans le fichier journal du relais. n'a pas besoin d'attendre que toutes les bibliothèques esclaves renvoient ACK à la bibliothèque maître. Ce n'est qu'après que la bibliothèque principale a reçu cet ACK qu'elle peut renvoyer une confirmation de « transaction terminée » au client. </p> <p><strong>La réplication par défaut de MySQL est asynchrone, il y aura donc un certain délai dans les données entre la base de données maître et la base de données esclave. Plus important encore, la réplication asynchrone peut entraîner une perte de données</strong>. Cependant, une réplication entièrement synchrone allongera le temps nécessaire pour terminer une transaction et réduira les performances. J'ai donc tourné mon attention vers la réplication semi-synchrone. <strong>À partir de MySQL 5.5, MySQL prend en charge la réplication semi-synchronisée sous la forme d'un plug-in</strong>. </p> <p>Par rapport à la réplication asynchrone, la réplication semi-synchrone améliore la sécurité des données et réduit le délai maître-esclave. Bien entendu, elle présente toujours un certain degré de retard. Ce délai est d'au moins un temps d'aller-retour TCP/IP. Par conséquent, la <strong>la réplication semi-synchrone est mieux utilisée dans les réseaux à faible latence</strong>. </p> <blockquote> <p>Il convient de noter que : </p> <ul> <li>La bibliothèque maître et la bibliothèque esclave doivent activer la réplication semi-synchrone pour effectuer une réplication semi-synchrone. Sinon, la bibliothèque maître reviendra à la réplication asynchrone par défaut. </li> <li>Si pendant le processus d'attente, le temps d'attente a dépassé le délai d'attente configuré et qu'aucun ACK n'est reçu d'une bibliothèque esclave, alors la bibliothèque principale sera automatiquement convertie en réplication asynchrone à ce moment-là. Lorsqu'au moins un nœud esclave semi-synchrone rattrape son retard, la base de données maître se convertit automatiquement en réplication semi-synchrone. </li> </ul> </blockquote> <h4>Problèmes potentiels avec la réplication semi-synchrone</h4> <p>Dans la réplication semi-synchrone traditionnelle (introduite dans MySQL 5.5), la base de données principale écrit les données dans binlog, et après avoir exécuté commit pour valider la transaction, elle attendra toujours un ACK à partir de la base de données esclave, c'est-à-dire la base de données esclave. Une fois que la bibliothèque a écrit le journal de relais, écrit les données sur le disque, puis renvoie un ACK à la bibliothèque principale, ce n'est qu'après que la bibliothèque principale a reçu cet ACK qu'elle peut renvoyer une "transaction terminée". confirmation au client. </p> <p><img src="https://img.php.cn/upload/article/000/000/067/cbef40562254d59aabda3ee32efe155e-0.jpg" alt="Maîtrisez complètement la solution au délai maître-esclave MySQL"></p> <p>Un problème surviendra, c'est-à-dire que la bibliothèque principale a effectivement validé la transaction dans la couche du moteur de stockage, et l'application peut déjà voir que les données ont changé et n'attend que le retour. Si <strong>la base de données principale est en panne à ce moment-là</strong>, la base de données esclave n'a peut-être pas écrit le journal de relais et <strong>une incohérence des données entre les bases de données maître et esclave se produira</strong>. </p> <p>Afin de résoudre les problèmes ci-dessus, <strong>MySQL 5.7 introduit la réplication semi-synchrone améliorée</strong>. Pour l'image ci-dessus, "Waiting Slave dump" est ajusté avant "Storage Commit", c'est-à-dire qu'après que la bibliothèque principale a écrit des données dans le binlog, elle commence à attendre la réponse ACK de la bibliothèque esclave jusqu'à ce qu'au moins une bibliothèque esclave écrit dans le journal de relais, puis les données sont écrites sur le disque, puis ACK est renvoyé à la bibliothèque principale, informant la bibliothèque principale qu'elle peut effectuer l'opération de validation, puis la bibliothèque principale soumet la transaction au moteur de transaction Ce n’est qu’alors que l’application peut voir les modifications des données. <strong></strong></p> <p><img src="https://img.php.cn/upload/article/000/000/067/cbef40562254d59aabda3ee32efe155e-1.png" alt="Maîtrisez complètement la solution au délai maître-esclave MySQL"></p> Bien entendu, la solution de semi-synchronisation précédente est également prise en charge. MySQL 5.7.2 introduit un nouveau paramètre <blockquote> pour le contrôle. Ce paramètre a deux valeurs : <p><code>rpl_semi_sync_master_wait_point

AFTER_SYNC : Il s'agit d'un nouveau schéma de semi-synchronisation, Waiting Slave dump before Storage Commit.

1. AFTER_COMMIT : Il s'agit de l'ancienne solution semi-synchrone.

Dans MySQL 5.5 - 5.6 utilisant le mode after_commit, une fois la transaction client soumise au niveau de la couche moteur de stockage, pendant que la bibliothèque principale attend la confirmation de la bibliothèque esclave, la bibliothèque principale est en panne. À ce stade, même si le résultat n'est pas renvoyé au client actuel, la transaction a été soumise et les autres clients liront la transaction soumise. Si la base de données esclave ne reçoit pas la transaction ou ne l'écrit pas dans le journal de relais et que la base de données maître est en panne, puis passe à la base de données de secours, les transactions lues précédemment disparaîtront et des lectures fantômes se produiront, ce qui signifie que les données seront perdues.

La valeur par défaut de MySQL 5.7 est after_sync. La base de données maître écrit chaque transaction dans le binlog, la transmet à la base de données esclave et la vide sur le disque (journal de relais). La bibliothèque principale attend que la bibliothèque esclave renvoie un accusé de réception, puis valide la transaction et renvoie le résultat de validation OK au client. Même si la bibliothèque principale tombe en panne, toutes les transactions qui ont été soumises sur la bibliothèque principale sont garanties d'être synchronisées avec le journal de relais de la bibliothèque esclave, ce qui résout le problème de lecture fantôme et de perte de données causée par le mode after_commit Cohérence des données. sera amélioré lors du basculement Améliorer . Parce que si la base de données esclave n'écrit pas correctement, la base de données maître ne validera pas la transaction. De plus, attendre l'ACK de la base de données avant de valider peut également accumuler des transactions, ce qui est bénéfique pour la soumission du groupe de validation de groupe et améliore les performances.

Mais cela posera également un problème. En supposant que la bibliothèque principale se bloque avant que le moteur de stockage ne soit validé, il est alors évident que la transaction échoue. Cependant, puisque le Binlog correspondant a déjà effectué une opération de synchronisation, la bibliothèque esclave. a reçu ces Binlogs et l'exécution est réussie, cela équivaut à avoir des données supplémentaires sur la base de données esclave (la base de données esclave a ces données mais pas la base de données principale), ce qui est également un problème, mais les données supplémentaires le sont généralement. pas un problème sérieux. Ce qu'il peut garantir, c'est qu'aucune donnée n'est perdue. Il vaut mieux avoir plus de données que perdre des données.

Un maître, plusieurs esclaves

Si la base de données esclave entreprend un grand nombre de requêtes de requête, les opérations de requête sur la base de données esclave consommeront beaucoup de ressources CPU, affectant ainsi la vitesse de synchronisation et provoquant un retard maître-esclave. Ensuite, nous pouvons connecter plusieurs bibliothèques esclaves supplémentaires et laisser ces bibliothèques esclaves partager la pression de lecture.

En bref, il s'agit d'ajouter des machines. La méthode est simple et grossière, mais elle entraînera aussi un certain coût.

Forcer la solution de base de données principale

Si certaines opérations ont des exigences strictes en matière de données en temps réel et doivent refléter les dernières données en temps réel, telles que les systèmes financiers impliquant de l'argent, les systèmes en temps réel en ligne ou après avoir écrit If l'affaire doit être relue immédiatement, nous devons alors abandonner la séparation de la lecture et de l'écriture, et laisser ces demandes de lecture aller également à la bibliothèque principale, il n'y a donc pas de problème de retard.

Bien sûr, cela perd également l'amélioration des performances que nous apporte la séparation de la lecture et de l'écriture, des compromis appropriés sont donc nécessaires.

Réplication parallèle

Généralement, la réplication maître-esclave MySQL implique trois threads, qui sont tous des threads uniques : le thread Binlog Dump, le thread IO et le thread SQL. Les retards de réplication se produisent généralement à deux endroits :

• Les threads SQL sont trop occupés (la raison principale) ;
• La gigue du réseau provoque des retards de réplication des threads IO (raisons secondaires).

L'exécution du journal sur la base de données de secours est la logique du thread SQL sur la base de données de secours exécutant le journal de relais pour mettre à jour les données.

Avant la version 5.6 de MySQL, MySQL ne prenait en charge que la réplication monothread. Par conséquent, de graves problèmes de délai maître-esclave survenaient lorsque la concurrence de la base de données principale et le TPS étaient élevés. À partir de MySQL 5.6, il existe le concept de plusieurs threads SQL, qui peuvent restaurer les données simultanément, c'est-à-dire la technologie de réplication parallèle. Cela peut très bien résoudre le problème de délai maître-esclave MySQL.

De la réplication monothread à la dernière version de la réplication multithread, l'évolution est passée par plusieurs versions. En fait, en dernière analyse, tous les mécanismes de réplication multithread doivent diviser le sql_thread avec un seul thread en plusieurs threads, ce qui signifie qu'ils sont tous conformes au modèle multithread suivant :

coordinator est le sql_thread d'origine, mais désormais, il ne met plus directement à jour les données, mais est uniquement responsable de la lecture du journal de transit et de la répartition des transactions. Ce qui met réellement à jour le journal devient le fil de travail. Le nombre de threads de travail est déterminé par le paramètre slave_parallel_workers.

Étant donné que les threads de travail s'exécutent simultanément, afin d'assurer l'isolement des transactions et d'éviter les problèmes de couverture des mises à jour, le coordinateur doit répondre aux deux exigences de base suivantes lors de la distribution :

1. Mettre à jour deux transactions dans la même ligne, doit être distribué au même travailleur (pour éviter la couverture des mises à jour) .
2. La même transaction ne peut pas être fractionnée et doit être placée dans le même travailleur (pour garantir l'isolement des transactions).

Toutes les versions de réplication multithread suivent ces deux principes de base.

Voici la stratégie de distribution table par table et la stratégie de distribution ligne par ligne, qui peuvent aider à comprendre l'itération de la version officielle MySQL de la stratégie de réplication parallèle :

• Stratégie de distribution par table : Deux transactions peuvent s'exécuter en parallèle si elles mettent à jour des tables différentes. Étant donné que les données sont stockées dans la table, la répartition par table garantit que deux nœuds de calcul ne mettront pas à jour la même ligne.
  • Le schéma de distribution basé sur les tables fonctionne bien dans les scénarios où plusieurs tables ont des charges égales, mais l'inconvénient est le suivant : si une table de point chaud est rencontrée, par exemple, lorsque toutes les transactions de mise à jour impliquent une certaine table, toutes les transactions seront si elles sont affectées à le même travailleur, cela devient une réplication monothread.
• Stratégie de distribution ligne par ligne : Si deux transactions ne mettent pas à jour les mêmes lignes, elles peuvent être parallélisées sur la base de données de secours. Évidemment, ce mode nécessite que le format binlog soit en ligne.
  • La solution de réplication parallèle ligne par ligne résout le problème des tables de points chauds et présente un degré de parallélisme plus élevé. Cependant, l'inconvénient est le suivant : par rapport à la stratégie de distribution parallèle table par table, le parallèle ligne par ligne. La stratégie nécessite plus de calculs lors de la détermination de la distribution des ressources des threads.

Stratégie de réplication parallèle de la version MySQL 5.6

La version MySQL 5.6 prend en charge la réplication parallèle, mais la granularité prise en charge est le parallélisme par base (basé sur le schéma).

L'idée principale est  : lorsque des tables sous différents schémas sont soumises simultanément, les données ne s'affecteront pas les unes les autres, c'est-à-dire que la bibliothèque esclave peut allouer un thread similaire à la fonction de thread SQL à différents schémas dans le journal de relais pour relire le journal de relais Les transactions qui ont été validées par la base de données principale restent cohérentes avec les données de la base de données principale.

S'il existe plusieurs bases de données sur la base de données principale, l'utilisation de cette stratégie peut considérablement améliorer la vitesse de réplication à partir de la base de données esclave. Mais généralement, il y a plusieurs tables dans une seule base de données, donc la concurrence basée sur la base de données n'a aucun effet et la relecture parallèle ne peut pas être effectuée du tout, cette stratégie n'est donc pas beaucoup utilisée.

Stratégie de réplication parallèle de MySQL 5.7

MySQL 5.7 introduit la réplication parallèle basée sur la soumission de groupe, et le paramètre slave_parallel_workers 设置并行线程数，由参数 slave-parallel-type est utilisé pour contrôler la stratégie de réplication parallèle :

• est configuré comme DATABASE, ce qui signifie utiliser la base de données par -stratégie parallèle de base de données de la version MySQL 5.6 ;
• Configuré comme LOGICAL_CLOCK, indiquant l'utilisation d'une stratégie de réplication parallèle basée sur la soumission de groupe

En utilisant le mécanisme de validation de groupe de binlog, on peut conclure que les transactions soumises par un groupe peuvent être exécuté en parallèle, car : peut être exécuté en parallèle Les transactions soumises dans le même groupe ne modifieront certainement pas la même ligne (en raison du mécanisme de verrouillage de MySQL), car la transaction a réussi le test de conflit de verrouillage.

Le processus spécifique de réplication parallèle basé sur la soumission de groupe est le suivant :

1. Les transactions soumises ensemble dans un groupe ont le même commit_id, et le groupe suivant est commit_id+1 et est écrit directement dans le binlog ;
Transmission Lorsqu'elles atteignent l'application de base de données de secours, les transactions avec le même commit_id sont distribuées à plusieurs travailleurs pour exécution
2. Une fois toutes les exécutions de ce groupe terminées, le coordinateur supprimera un lot d'exécutions ;

Toutes les transactions en statut de préparation et de validation peuvent être exécutées en parallèle sur la base de données en veille.

Deux paramètres associés soumis par le groupe binlog :

paramètre binlog_group_commit_sync_delay, qui indique le nombre de microsecondes à attendre avant d'appeler fsync pour vider le disque ;
• paramètre binlog_group_commit_sync_no_delay_count, qui indique combien de fois il est accumulé avant d'appeler fsync.

Ces deux paramètres sont utilisés pour allonger délibérément le temps entre l'écriture du binlog et fsync, réduisant ainsi le nombre d'écritures du binlog sur le disque. Dans la stratégie de réplication parallèle de MySQL 5.7, ils peuvent être utilisés pour créer plusieurs « transactions en phase de préparation en même temps ». Vous pouvez envisager d'ajuster les valeurs de ces deux paramètres pour atteindre l'objectif d'améliorer la simultanéité de la réplication de la base de données de secours.

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!