mysql %util 100% 导致主从同步延迟
查看当前%util
# iostat -k -d -x 1 2Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util
loop0 0.00 0.02 0.00 0.00 0.79 42.17 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.29 5.94 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop10 0.00 0.00 0.00 0.00 0.05 6.02 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop11 0.00 0.00 0.00 0.00 0.30 3.90 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.41 5.14 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.02 0.00 0.00 0.59 36.18 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.28 7.54 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop5 0.00 0.00 0.00 0.00 0.40 12.45 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop6 0.00 0.00 0.00 0.00 0.60 10.97 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop7 0.00 0.03 0.00 0.00 0.52 36.61 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop8 0.00 0.04 0.00 0.00 0.15 34.66 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop9 0.00 0.00 0.00 0.00 0.29 4.74 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
nvme0n1 0.16 8.12 0.02 13.29 0.82 51.70 0.40 5.36 0.14 25.85 1.11 13.28 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.04
nvme2n1 169.77 3037.78 0.00 0.00 0.66 17.89 1087.56 19548.10 0.13 0.01 0.26 17.97 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.39 79.66
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util
loop0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
loop9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
nvme0n1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
nvme2n1 756.00 13064.00 0.00 0.00 0.58 17.28 2553.00 64388.00 0.00 0.00 0.95 25.22 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2.86 99.20 # 将近100%查看io 进程
# iotopTotal DISK READ: 7.81 M/s | Total DISK WRITE: 35.89 M/s
Current DISK READ: 7.80 M/s | Current DISK WRITE: 37.33 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
1600 be/4 mysql 0.00 B/s 32.95 M/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
1605 be/4 mysql 1268.70 K/s 0.00 B/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
1606 be/4 mysql 44.78 K/s 0.00 B/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
1607 be/4 mysql 18.66 K/s 0.00 B/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
1608 be/4 mysql 14.93 K/s 0.00 B/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
1609 be/4 mysql 14.93 K/s 0.00 B/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4169591 be/4 mysql 29.85 K/s 41.05 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4172532 be/4 mysql 3.73 K/s 44.78 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4173721 be/4 mysql 0.00 B/s 3.73 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4174245 be/4 mysql 44.78 K/s 44.78 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4175543 be/4 mysql 0.00 B/s 11.19 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4175786 be/4 mysql 0.00 B/s 11.19 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4177387 be/4 mysql 59.70 K/s 7.46 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4177538 be/4 mysql 29.85 K/s 3.73 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4177875 be/4 mysql 14.93 K/s 7.46 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4178105 be/4 mysql 29.85 K/s 44.78 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4178164 be/4 mysql 14.93 K/s 18.66 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4178425 be/4 mysql 0.00 B/s 7.46 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4179273 be/4 mysql 100.75 K/s 26.12 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4179434 be/4 mysql 97.02 K/s 55.97 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4180650 be/4 mysql 111.94 K/s 63.44 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4180891 be/4 mysql 115.68 K/s 29.85 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4181049 be/4 mysql 59.70 K/s 26.12 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4181088 be/4 mysql 44.78 K/s 7.46 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4181256 be/4 mysql 44.78 K/s 37.31 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4182101 be/4 mysql 59.70 K/s 11.19 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4183463 be/4 mysql 29.85 K/s 82.09 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4183827 be/4 mysql 134.33 K/s 55.97 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4184553 be/4 mysql 14.93 K/s 33.58 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4185280 be/4 mysql 0.00 B/s 22.39 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4185741 be/4 mysql 29.85 K/s 18.66 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4185871 be/4 mysql 152.99 K/s 37.31 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4185878 be/4 mysql 59.70 K/s 18.66 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4186239 be/4 mysql 59.70 K/s 14.93 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4186668 be/4 mysql 29.85 K/s 37.31 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4186683 be/4 mysql 149.26 K/s 14.93 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4186817 be/4 mysql 104.48 K/s 37.31 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pid
4187035 be/4 mysql 0.00 B/s 33.58 K/s ?unavailable? mysqld --defaults-file=/data/mysql/my.cnf --daemonize --pid-file=/data/mysql/data/mysqld.pidslave 状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host:
Master_User:
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001082
Read_Master_Log_Pos: 829601827
Relay_Log_File: mysql-02-relay-bin.003009
Relay_Log_Pos: 900147090
Relay_Master_Log_File: mysql-bin.001008
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: 900146877
Relay_Log_Space: 80286738658
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: 173433 # 主从延迟很高
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: 5
Master_UUID: 850952b9-a15a-11ed-a2e1-0a09325a0881
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)修改主从配置
sync_binlog
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.05 sec)mysql> set global sync_binlog=1000;mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1000 |
+---------------+-------+
1 row in set (0.00 sec)innodb_flush_log_at_trx_commit
mysql> show variables like '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
2 rows in set (0.00 sec)mysql> set global innodb_flush_log_at_trx_commit=2;mysql> show variables like '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
+--------------------------------+-------+
2 rows in set (0.00 sec)查看主从延迟状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host:
Master_User:
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001083
Read_Master_Log_Pos: 463492023
Relay_Log_File: mysql-02-relay-bin.003015
Relay_Log_Pos: 303719958
Relay_Master_Log_File: mysql-bin.001010
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: 303719745
Relay_Log_Space: 78846880932
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: 172406 # 此值在下降
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: 5
Master_UUID: 850952b9-a15a-11ed-a2e1-0a09325a0881
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)还原主从配置
等主从同步完成后还原配置
mysql> set global sync_binlog=1;
mysql> set global innodb_flush_log_at_trx_commit=1;
