MYSQL数据库延迟问题的定位及解决
一 、状态查看与定位
MYSQL主主同步有时候会出现莫名的延迟,为了排查这一问题,首先要知道主主复制是否还在进行?
1.
(1). 在从数据库上:
>show slave status\G
先看从数据库的状态,看到IO线程和SQL线程都为yes(正常开启),
在看下面有一条 seconds_behind_master : (通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp而得出的差值)
注:一般情况下若后面为0,表示主从复制良好,不存在延迟;为NULL表示io_thread或是sql_thread有任何一个发生故障,也就是该线程的Running状态是No,而非Yes;若为正数,表明传输存在延迟,正数越大,延迟越多;负数是个bug。
在show processlist进程信息,查看相关联的主数据库的command若为Binlog Dump,表示此时主数据库正在向从数据库同步。
通过以上几条参数,我们可以判断出主主结构数据库目前正在进行同步复制。下面,就该查找其他问题。
2 .
监控目前主从数据库同步的状态信息(同步到哪一行语句,哪张表,在什么时间,甚至已同步和没同步的数据):
(1) . 依然是在从数据库上:
在状态中注意红框中的部分,
分别是:目前所同步的主数据库的binlog日志名,目前所同步的主数据库的binlog日志的偏移位置和目前所执行的relay_log日志偏移位置。
我们到主数据库上查看主的状态,比对两库所同步的binlog名字和偏移位置信息是否一致;一致说明IO同步进程没有问题。
找到系统中对应的relay_log文件,然后将它导出:
(2). 这样就可以通过vi ,cat ,more等命令去查看relay_log文件里面的内容了
通过定位,很快就可以找到它当前正在同步的位置,
下面分别是它的日期和时间,数服的id号码,以及对哪一个表,哪条数据做了哪些操作;
由于我们目前的库较少,大部分所操作的表都是在一个库里,可以通过/^use 来查找到是哪一个数据库。
二 、问题处理
MYSQL主主复制的基本运行问题已经检查完毕,主从库的结构、状态等都属正常,那么是哪里出现的延迟呢,我们还要从以下几个方面去考虑一下?
(一般容易出现的数据库延迟问题有:网络延时、SQL语句延迟、单线程延迟、参数调整)
依次来看
1 . 网络延时
在 MySQL 的复制里,由 Slave 发送一个请求命令后,就完全由 Master 来推送数据。如果 Master 没有更新,也就不会有数据流,Slave 就不会收到任何数据包。但是如果由于网络故障或其他原因导致Master 无法把数据发送到 Slave,就没法知道收不到数据是因为 Master没有更新还是出了故障。但是MySQL 5.5以后有个‘复制心跳’的功能可以解决这一问题。
配置方法如下:
>stop slave;
>change master to master_heartbeat_period = 10;
>set global slave_net_timeout = 25;
>start slave;
中间两条配置的意思是:Master 在没有数据的时候,每10秒发送一个心跳包,能够知道 Master是不是还正常。slave_net_timeout(默认3600)是设置在25秒没收到数据后认为网络超时,之后Slave的IO线程会重新连接Master。结合这两个设置就可以避免由于网络问题导致的复制延误。
2 . SQL语句延迟
如果网站在运营过程中遇到突然变慢的情况,一般和MySQL 慢有关系,可以通过开启慢查询,找到影响效率的 SQL ,然后采取相应的措施。
(1) 开启慢查询方法:
1)在数据库中操作:
mysql>show variables like ‘%slow%’; //查看慢日志查询是否开启
mysql>set global long_query_time = 2; //SQL 执行时间超过2秒即记录
mysql>set global slow_query_log = ON; //开启
2)添加到配置文件my.cnf 中(需要重启):
#long_query_time=2#log-slow-queries= /usr/local/mysql/mysql-slow.log //自定义的日志记录文件位置
(2) 在mysql-slow.log中会记录下sql语句的执行情况、锁定时间等等,如果数据过多的话,可以使用慢查询分析工具。(简介一下官方自带的分析工具mysqldumpslow的使用)
#mysqldumpslow --help //可以查看到帮助信息
图中红线是几个常用的参数;
-s,表示按照何种方式排序,
c、t、l、r分别是按照记录次数、查询时间、锁定时间、返回的记录数来排序(从大到小),ac、at、al、ar代表相应的平均值;
-r,是反向排序;
-t,表示返回前面多少条数据;
-g,后边可以写一个正则匹配模式(忽略大小写)。
例如:
# mysqldumpslow -s t -t 10 -g left join host-slow.log
(按照时间返回前10条里面含有左连接的sql语句)
使用mysqldumpslow工具可以明确的得到各种我们需要的查询语句,对MySQL语句的监控、分析、优化起到非常大的帮助。
3 . 单线程延迟
(1). mysql的数据库主从复制原理 :
mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高。slave的Slave_IO_Running线程到主库取日志,然后,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施;DML和DDL的IO操作是随即的,不是顺序的,效率要低很多,还可能与slave上的其他查询产生lock争用,由于Slave_SQL_Running是单线程的,所以一个DDL卡住了10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。
那为什么只有slave会延时?如果主库上那个相同的DDL也需要执行10分,是不是有一种状态能达到相对无延时的呢? 答案是“NO”,因为master可以并发,但是Slave_SQL_Running线程却不可以。
这样,当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,或者与slave的大型query语句产生了锁等待,那么延时就产生了。
(2). 处理办法:
1)首先一个MYSQL实例不要创建太多database,否则一旦其中一个库压力大经常被锁,会导致所有库同步都延迟【目前我们的状况在这一点上还是比较符合的】
2)压力较大的情况下使用几个从数据库值得考量,如果使用多个从数据库也是可以适当缓解上面lock的情况发生【主-从-从结构在大并发运行环境中可行,作为备用方案】
3)在MySQL5.6版本中新引入了GTID和多线程复制, 在新版中支持基于库的多线程(队列)复制,但是库里的表不能多线程,只能是基于不同库里的表进行多线程。 【从我们目前的延迟情况看暂不涉及到,略过】
4 . 参数调整
1、介绍两个重要的参数是: sync_binlog 和 innodb_flush_log_at_trx_commit
(1)、sync_binlog = N
(This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction)
N = 0,
默认值,不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定;
N = 1,
值1是最安全的,当系统或服务器崩溃时,最多丢掉二进制日志中的一个语句或事务, 但也是最慢的(除非硬盘有电池备份缓存,从而使同步工作较快);
N > 0,
每向二进制日志文件写入N条SQL或N个事务后,MySQL服务器把二进制日志文件的数据刷新到磁盘上。
如果对响应速度要求比较高的设成0,对安全要求性比较高的就设成1。
(2)、innodb_flush_log_at_trx_commit = N
N = 0 ,
每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上;
( log buffer 会每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响,也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下,MySQL性能最好,但如果 mysqld 进程崩溃,会导致上一秒钟所有事务数据的丢失)
N=1 ,默认值,
每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上;
(当取值为 1 时,每次事务提交时,log buffer 会被写入到日志文件并刷写到磁盘。这是最安全的配置,但由于每次事务都需要进行磁盘I/O,所以也最慢 )
N=2 ,
每事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,刷新一次日志文 件,但不一定刷新到磁盘上,而是取决于操作系统的调度;
(当取值为 2 时,每次事务提交会写入日志文件,但并不会立即刷写到磁盘,日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃,由于日志已经写入到系统缓存,所以并不会丢失数据;只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失)
上文的「最后 1s」并不是绝对的,有的时候会丢失 更多数据。有时候由于调度的问题,每秒刷写(once-per-second flushing)并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用,配置为 2 就足够了;如果为了最高性能,可以设置为 0。有些应用,如支付服务,对一致性和完整性要求很高,所以即使最慢,也最好设置为1。
备注:
当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得,双11 会导致频繁的io操作,因此该模式也是最慢的一种方式。 双1适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时,推荐的做法是 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。
2 、一些其他的参数:
#skip-locking
避免MySQL的外部锁定,减少出错,增强稳定性。
#key_buffer_size=256M
指定用于索引缓冲区大小,适当增加可以得到更好的索引处理性能。(内存4G建议为256M或384M,不宜过大)
#max_connections=1000
指定MySQL允许的最大连接进程数。如果访问出现TOO MANY CONNECTION错误,则应增大该值。
#wait_timeout=10
指定一个请求最大连接时间。(内存4G可设为5-10)
#max_connect_errors=30
每个客户端允许连接的最大错误数量。
#query_cache_size=128M
查询缓冲区的大小,常被用来缓冲SELECT 的结果并且在下一次同样查询时直接返回结果。(查询缓冲可以提高服务器速度,如果有大量的相同查询而不是修改表的话)
#transaction_isolation = REPEATABLE-READ
设定默认事务隔离级别。(可设为READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE)
#server_id=1
唯一服务器标识号(主、从服务器均要设置)
#binlog_format=MIXED
二进制日志的复制格式。(可选为混合,防止出错;特殊情况下可选为row,默认是statement)
转载于:https://blog.51cto.com/01wang/1635952