主從複製延遲推薦解決方案

晴轉陣雨發表於2020-12-09

主從複製延遲推薦解決方案
- 現象描述
- 2個解決方案
  - 方案一
  - 方案二

主從複製延遲推薦解決方案

如何提高複製的效能

現象描述

MySQL從庫上通過SHOW PROCESSLIST可以看到有且僅有一個SQL執行緒在解析中繼日誌Relay Log並應用，例如：

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 2185
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 2190
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 16
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 17
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

那麼寫入壓力特別大的場景下，考慮到主庫是多執行緒併發在寫入（應用伺服器併發連線寫入），而從庫僅僅只有一個SQL執行緒在應用日誌，就容易出現從庫追不上主庫的情況，可以在從庫上通過SHOW SLAVE STATUS來檢視從庫落後主庫的時間：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.211.136
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1271
               Relay_Log_File: mysqld-relay-bin.000011
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1271
              Relay_Log_Space: 620
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 136
                  Master_UUID: cb248a05-3538-11eb-8893-005056253f37
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

其中 Seconds_Behind_Master 顯示了預估從庫落後主庫的秒數，不是特別精準，只是一個預估值。

從庫的資料落後主庫的問題，當然可以通過提高從庫配置的硬體來解決。但更推薦通過架構設計來解決這個問題，通過減少從庫需要做的寫入操作或者在從庫上實現多執行緒寫入操作都能夠解決。

2個解決方案

方案一

通過拆分減少一個從庫上需要資料同步的表來解決。首先考慮配置一主多從的架構，然後在不同的從庫上，通過設定不同 replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或replicate-wild-do-table引數，使得不同的從庫複製不同的庫/表，減少每個從庫上需要寫入的資料。
例如，假設主庫為M1，從庫為S1、S2、S3，其中設定從庫S1僅需要複製databaseA，而從庫S2僅需要複製databaseB，從庫S3僅需要複製databaseC，那麼每個從庫只需要執行自己需要複製的庫/表相關的SQL就可以了，如下圖所示。

這時，由於主庫M1需要給S1、S2、S3三個從庫（或者更多從庫）都傳送完整的Binlog日誌，I/O 和網路壓力較大，再改進一下架構：配置 MySQL 多級主從架構減輕主庫壓力，如下圖所示。

主庫M1首先給二級主庫M2推送完整的Binlog。
二級主庫M2開啟log-slave-updates配置，保證主庫M1傳送過來的Binlog能夠被記錄在二級主庫M2的RelayLog和Binlog中；二級主庫M2選擇BLACKHOLE引擎作為表引擎，降低二級主庫上I/O的壓力。
為二級主庫M2配置3個從庫S1、S2、S3，三個從庫通過配置不同replicate-do-db等引數，讓S1、S2、S3複製不同的庫/表。

通過多級主從的方式，提高從庫的複製效能，同時儘量降低對主庫的影響。

注意：BLACKHOLE引擎就是一個“黑洞”引擎，在建立表的時候，選擇BLACKHOLE引擎，那麼寫入表的資料不會真實地寫入磁碟，僅僅記錄Binlog日誌，極大降低了磁碟的I/O。

方案一的優點在於能夠自由拆分從庫，方便地把熱點資料分散開來；缺點在於維護起來不夠簡潔，並且由於從庫S1、S2、S3上都沒有主庫完整的資料，在主庫M1出現意外當機的情況，應用處理較為麻煩。需要提前和應用溝通好異常的處理解決方案。

方案二

MySQL 5.6提供了基於 Schema的多執行緒複製，允許從庫並行更新。例如，主庫上存在 2個Schema，即ssm和replication。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication        |
| ssm                |
+--------------------+
5 rows in set (0.00 sec)

MySQL 5.6的從庫在同步主庫時，通過設定引數 slave_parallel_workers為 2，讓MySQL從庫在複製時啟動兩個SQL執行緒。引數設定前：

mysql> show variables like '%slave_parallel_workers%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.50    |
+-----------+
1 row in set (0.00 sec)

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 3480
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 3485
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 16
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1297
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 17
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1297
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
5 rows in set (0.00 sec)

設定引數後如下：注意需要先stop slave,再start slave才有效果

mysql> set global slave_parallel_workers=2;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: 192.168.211.1:63440
     db: ssm
Command: Sleep
   Time: 3603
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: 192.168.211.1:63488
     db: ssm
Command: Sleep
   Time: 3608
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 15
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 4. row ***************************
     Id: 18
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for master to send event
   Info: NULL
*************************** 5. row ***************************
     Id: 19
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 6. row ***************************
     Id: 20
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for an event from Coordinator
   Info: NULL
*************************** 7. row ***************************
     Id: 21
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 2
  State: Waiting for an event from Coordinator
   Info: NULL
7 rows in set (0.00 sec)

通過設定slave_parallel_workers 引數，讓ssm和replication兩個Schema擁有自己獨立的SQL執行緒，這樣也大大提高了從庫的複製速度。

複製是MySQL資料庫中經常使用的一個功能，它可以有效地保證主資料庫的資料安全，並減輕主資料庫的備份壓力，以及分擔主資料庫的一部分查詢壓力。

-------學自《深入淺出MySQL》

MySQL主從複製延遲解決方案
2023-03-16
MySql
mysql主從延遲複製
2019-01-29
MySql
mysql同步(複製)延遲的原因及解決方案
2019-05-13
MySql
Mysql 非同步複製延遲的原因及解決方案
2020-11-10
MySql非同步
MySQL主從複製延遲原因及處理思路
2018-05-22
MySql
mysql的主從複製資料延遲問題
2017-06-24
MySql
記一次 MySQL 主從複製延遲的踩坑
2017-07-16
MySql
在Linux中，mysql 如何減少主從複製延遲？
2024-06-22
LinuxMySql
MySQL之從複製延遲問題排查
2018-09-06
MySql
PostgreSQL 主從複製方案
2024-03-15
SQL
HTTP 請求延遲解決方案
2019-05-09
HTTP
MySQL 8 複製（三）——延遲複製與部分複製
2020-02-12
MySql
PostgreSQL中的複製延遲
2023-04-20
SQL
MySQL 5.7 延遲複製配置
2016-05-04
MySql
MySQL主從延遲解決方法的歸納和總結
2016-11-10
MySql
MySQL的主從複製、半同步複製、主主複製詳解
2016-03-11
MySql
mysql的主從複製延遲問題--看這一篇就夠了
2021-07-02
MySql
主從延遲調優思路
2024-04-15
MYSQL主從複製製作配置方案
2018-06-11
MySql
MySQL主從資料庫同步延遲問題怎麼解決
2021-09-04
MySql資料庫
影響MySQL主從延遲的幾個因素及解決方法
2016-10-06
MySql
高頻面試：如何解決MySQL主從複製延時問題
2023-03-14
面試MySql
MariaDB 10之並行複製–延遲測試結果薦
2014-07-01
並行
mysql主從複製配置與問題解決
2018-06-18
MySql
MySQL主從複製與主主複製
2017-03-01
MySql
故障分析 | MySQL 異地從庫複製延遲案例一則
2022-03-05
MySql
MySQL主從複製、半同步複製和主主複製
2016-02-22
MySql
mysql5.7主從複製，主主複製
2018-08-21
MySql
MySQL主從複製、半同步複製和主主複製概述
2016-03-14
MySql
MySQL主從複製問題解決一例
2021-09-09
MySql
MySQL 8.0能徹底解決困擾運維的複製延遲問題！
2018-08-14
MySql運維
主從複製
2024-06-30
配置mysql5.5主從複製、半同步複製、主主複製
2016-01-18
MySql
mysql複製--主從複製配置
2018-04-12
MySql
mysql ab主從複製出錯及解決過程
2013-09-06
MySql
MySQL的主從複製與MySQL的主主複製
2017-11-16
MySql
MongoDB的複製延遲和網路分割槽
2012-10-02
MongoDB
Redis - 主從複製
2020-06-21
Redis

主從複製延遲推薦解決方案

主從複製延遲推薦解決方案

現象描述

2個解決方案

方案一

方案二

相關文章