MYSQL从节点延迟问题原因及处理方法
mysql 因为异步同步,只能达到最终一致性,而无法达到实时一致性,所以理论是有延迟在所难免。
在mysql 5.7 版本实现了多线程同步,缓解了延迟问题,但不可能完全实现实时同步。
一、延迟原因大概有以下几点:
1.硬件
问题主要体现在服务器性能问题上,服务器性能包括主节点和从节点。
MYSQL 同步如果配置成 binlog_format=row,从节点一般会从节点性能优于主节点。
如果是多源复制,那么从节点的性能高于主节点就尤为重要。
主要是以下几个方面:
1. CPU,MEM
2. 网络
3. 磁盘
2.参数配置
(这里不讨论MYSQL 性能相关参数,只列出以主从同步相关的一些参数。)
影响主从同步参数罗列如下:
#关闭binlog 日志,从节点不写日志, 减少IO。
#log-bin = /data/mysql/mysql-bin
#binlog_format=row
#如果不需要紧急备份还原从节点的话,可以关掉slave的二进制文件记录功能
----------------------
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
参数解析如下:
sync_binlog = N
N=0 不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定;
innodb_flush_log_at_trx_commit = N:
N=0 每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上;
log buffer 会每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响,
也就是 log buffer 的刷写操作和事务提交操作没有关系。
在这种情况下,MySQL性能最好,但如果 mysqld 进程崩溃,通常会导致最后 1s 的日志丢失
N=0,0 磁盘IO写能力有限,无复制或允许复制延迟稍微长点能接受,例如:日志性登记业务;
------------------------
sync_master_info = 1
sync_relay_log=0
sync_relay_log_info = 0
sync_master_info:每间隔多少事务刷新master.info,如果是table(innodb)设置无效,每个事务都会更新
sync_relay_log:默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制
sync_relay_log_info:每间隔多少事务刷新relay-log.info,如果是table(innodb)设置无效,每个事务都会更新
----------------------
log_slave_updates=0 #关闭log_slave_updates,从库【binlog】不再记录主库同步的操作日志到binlog
---------------------------------
#同步线程设置
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=18 #配置自己需要的线程数
-------------------------
long_query_time = 2
slow_query_log = 1
log_slow_slave_statements = 1
slow_query_log_file = /data/mysql/slow.log
#打开慢查询,慢查询时间限制是2秒。
3.主节点中运行的大事务
大批量的插入,修改,删除数据。因为日志格式为binlog_format=row,会产生大量的binlog 日志。
大事务处理,也是批量提交更新,也是有时间延迟的。
4.主节点、从节点的慢查询影响
慢查询会影响MYSQL性能,有锁等待。以至于数据的更新存在时间差。在从节点体现为:io等待,锁等待。
5.主节点数据锁问题
比如表修改: alter table,create index 这类表级锁,直接产生等待。
就是一般的 update 如果修改数据量大,也会影响到从节点的同步。
6.主节点中表无主键
如果一个表没有主键,在同步到从节点后,所有的修改,每次的查询都是全表搜索,性能差问题被广大。
(比如更新100条记录,每条记录更新都是一个binlog事务,每次更新都是全表搜索)
前面说了出现延迟的原因,现在来看看怎样解决:
二.定位同步延迟
在主从节点中,使用以下命令,可以看到以下一些信息:
主节点:show master status\G
Master file:mysql-bin.000010 Master Position:144033539
从节点: show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.1.161
Master_User: bak
Master_Port: 3301
Connect_Retry: 60
Master_Log_File: mysql-bin.000010 #主从节点同步的文件是一致的
Read_Master_Log_Pos: 144033539 #读到的日志位置点也是一致的,说明主从IO同步日志没有问题,也就是说问题不在IO_thread
Relay_Log_File: test-mysql02-relay-bin-master1.000042
Relay_Log_Pos: 77469816
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys,batch
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: 144032167 #重新解析日志的位置点严重滞后,说明问题在 sql_thread
Seconds_Behind_Master: 1 #延迟时间(最小单位1秒,但这个并不是很准确的)
1.从上面的分析可以定位到问题在 sql_thread,也就是说问题在从节点上。
有可能是性能问题,比如:MEM 不足,IO性能差。
计划先修改参数值,进行调整。
2.如果 Read_Master_Log_Pos 189460063 值与主节点 Position 1030784024 相关太大。问题应该是出在网络上,主从同步日志文件太慢。
3.如果 Relay_Log_Pos: 171735369 值 Exec_Master_Log_Pos: 171735164 相关太大,问题应该是在磁盘IO,或者服务器本身性能问题,重解析日志时,读取日志到 执行SQL时间太长 。
2.如果前面的定位发现是IO_thread 问题(日志不同步,读到的日志位置不一致)。我们可以从网络方面解决。
2.1.查看网络带宽,
2.2.使用压缩传输:
主从日志传输进行压缩传输:slave_compressed_protocol=1
mysql> show variables like 'slave_compressed_protocol';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| slave_compressed_protocol | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)
我得到上面的预警信息是使用了以下脚本:
#!/bin/sh
cmd=/usr/local/mysql/bin/mysql
mysqluser=test
mysqlpwd=testpsd
log=/opt/shell/slave_monitor.log
hosts='10.10.1.101'
master_host='10.10.1.10'
m_port='3306'
behind=`$cmd -u$mysqluser -p$mysqlpwd -e "show slave status\G"|grep -iE "Seconds_Behind_Master"|awk '{print $2}'`
m_file=`$cmd -h${master_host} -utest -ptest --port ${m_port} -e "show master status\G"|grep -iE "File"|awk '{print $2}'`
m_position=`$cmd -h${master_host} -utest -ptest --port ${m_port} -e "show master status\G"|grep -iE "Position"|awk '{print $2}'`
s_status=`$cmd -u$mysqluser -p$mysqlpwd -e "show slave status for channel 'master1'\G"|sed -n 1,23p `
if [ "$behind1" -gt 0 ]
then
DingTalk.py "$master_host:Seconds_Behind_Master:$behind. Master file:$m_file Master Position:$m_position $s_status"
fi
3.打开慢查询跟踪,查看具体是什么SQL 很慢,逐个SQL 进行优化。
mysql> show variables like 'log_slow_slave_statements';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_slave_statements | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql>
4.某些表缺少主键或者唯一键则所有的SQL_THREAD会扫描全表并造成同步延迟。
所以需要确保表有主键或者唯一键。以下SQL 可查询是否有表没有主键索引:
mysql> select TABLE_SCHEMA,TABLE_NAME
from `information_schema`.`columns` c where TABLE_SCHEMA='hyjf_config'
GROUP BY TABLE_SCHEMA,TABLE_NAME
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;