mysql又遇备库延迟 -m6米乐安卓版下载

今天mysql又遇备库延迟案例，顺便总结一下

mysql>show slave status\g

slave_io_state: waiting for master to send event
master_host: 10.10.13.20
master_user: rpl_user
master_port: 3310
connect_retry: 60
master_log_file: mysql-bin.000013
read_master_log_pos: 48207531
relay_log_file: relay-bin-rpl1.000023
relay_log_pos: 1153
relay_master_log_file: mysql-bin.000013
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: 940
relay_log_space: 48208449
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: 65708
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: 2155887676
master_uuid: 000c1822-5be5-11ec-be70-a8ca7b94d69c
master_info_file: mysql.slave_master_info
sql_delay: 0
sql_remaining_delay: null
slave_sql_running_state: waiting for dependent transaction to commit
master_retry_count: 86400
master_bind:
last_io_error_timestamp:
last_sql_error_timestamp:
master_ssl_crl:
master_ssl_crlpath:
retrieved_gtid_set: 000c1822-5be5-11ec-be70-a8ca7b94d69c:20-2141
executed_gtid_set: 000c1822-5be5-11ec-be70-a8ca7b94d69c:1-2136,
6feab22c-5be4-11ec-b641-1047800760df:1-16

1.查看目前执行的事务

seconds_behind_master的数值不断在变大，首先看看目前正在跑的是什么事务，
查看relay_master_log_file: mysql-bin.000013和exec_master_log_pos: 940
在主库：

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000013  | grep -a '100'  940

发现目前卡在执行gtid:000c1822-5be5-11ec-be70-a8ca7b94d69c:2137上，这是一个大事务，批量删除delete from procu1 limit 6000000。因为binlog设置的row格式，主库在执行了一条delete sql;在binlog会生成六百万条delete记录，到了备库要执行六百万次。

2.查看事务表

在备库：
mysql>select * from information_schema.innodb_trx\g

trx_id:25389
trx_state:running
start:2020-12-11 10:33:41
requested_lock_id:null
waite_started:null
weight:107178
mysql_thread_id:94
tables_in_use:1
tables_locked:1
lock_structs:692
lock_memory_bytes:90320
rows_locked:212972
rows_modified:106486
concurrency_tickets:0
isolation_leve:read commited
unique_checks:1
foreign_key_checks:1
foreign_key_error:null

没有具体的sql语句trx_query,可以看到rows_locked和rows_modified在增加。rows_modified这个值要一直增加到六百万才结束，而且因为autocommit=off，数据目前都是在undo上

3.查看innodb引擎信息

在备库：
mysql>show engine innodb status\g
摘要:

---transaction **25389**, active 58424 sec
mysql tables in use 1, locked 1
18740 lock struct(s), heap size 3137744, 11885860 row lock(s), undo log entries 5942930
mysql thread id 65, os thread handle 139944933123840, query id 415 executing event

4.查看会话信息

在备库：
mysql>show processlist

| 64 | system user | | null | connect | 58362 | waiting for dependent transaction to commit | null |
| 65 | system user | | null | connect | 66073 | executing event | null |

备库复制线程是在后台执行，若是kill掉线程，会暂停slave同步，无法解决。
可以尝试让备库资源宽裕，加快执行，然后耐心等待大事务完成，追上最新数据。
例如一些优化操作：

set global innodb_buffer_pool_size=增大值;
set global slave_pending_jobs_size_max=增大值;
set global foreign_key_checks=0;
set global unique_checks=0;
set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=0;

若是考虑需要等候时间太久，人工干预方案：

• 跳过事务

在备库手动执行跑批操作，不做binlog记录，再跳过gtid

stop slave;
set sql_log_bin=0;
set global super_read_only=0;
delete from procu1 limit 6000000;
commit;
set global super_read_only=1;
set sql_log_bin=1;
set @@session.gtid_next= '000c1822-5be5-11ec-be70-a8ca7b94d69c:1-2137';
begin; commit;
set session gtid_next = automatic;
start slave;

• 重做备库

在主库全量备份，恢复到备库

一、常见主备延迟场景

• 备库所在机器的性能要比主库所在的机器性能差
• 备库的查询压力大
• 大事务
• 大表ddl
• 备库的并行复制能力
• 随机重放
• 主库并发高
• 锁等待

二、常见主备延迟优化

1.主库优化

• dml大事务，分拆小事务，小量分批多次执行
• ddl大表操作，第三方工具pt-online-schema-change，业务低谷执行

2.备库优化

• 增大从库参数innodb_buffer_pool_size的值，可以缓存更多数据，减少由于转换导致的io压力。
• 增大参数innodb_log_file_size、innodb_log_files_in_group的值，减少buffer pool的刷盘io,提升写入性能。
• 修改参数innodb_flush_method为o_direct,提升写入性能。
• 关掉从库binlog日志或者关掉参数log_slave_updates.
• 修改参数innodb_flush_log_at_tr_commit为0或2.
• 如果binlog没有关掉（gtid开启，需要binlog日志打开），修改sync_binlog参数为0或者一个很大的值，减少磁盘io压力。
• 如果binlog_format为row模式并且被修改表没有主键，则需要加上主键
• 如果binlog_format为row模式，则可以在备库总删除一些不必要的索引(同步完成之后再加上)。
• 了解清楚写库上操作内容，适当地在备库中预热一些数据，可以减少在复制时等待的时间。
• 修改参数master_info_repository、relay_log_info_repository为table，减少直接io导致的磁盘压力。
• 可以打开多线程复制。slave_parallel_type=‘logical_clock’ slave_parallel_workers=32
• 升级mysql 版本
• 升级硬件