MYSQL数据库延迟问题的定位及解决

一 、状态查看与定位 

MYSQL主主同步有时候会出现莫名的延迟，为了排查这一问题，首先要知道主主复制是否还在进行？

1.    

（1）. 在从数据库上：

>show  slave  status\G

spacer.gif 

先看从数据库的状态，看到IO线程和SQL线程都为yes（正常开启），

spacer.gif 

在看下面有一条 seconds_behind_master : （通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp而得出的差值）

     注：一般情况下若后面为0，表示主从复制良好，不存在延迟；为NULL表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes；若为正数，表明传输存在延迟，正数越大，延迟越多；负数是个bug。

spacer.gif 

在show processlist进程信息，查看相关联的主数据库的command若为Binlog Dump，表示此时主数据库正在向从数据库同步。

通过以上几条参数，我们可以判断出主主结构数据库目前正在进行同步复制。下面，就该查找其他问题。

2 . 

监控目前主从数据库同步的状态信息（同步到哪一行语句，哪张表，在什么时间，甚至已同步和没同步的数据）：

(1) . 依然是在从数据库上：

spacer.gif 

在状态中注意红框中的部分，

分别是：目前所同步的主数据库的binlog日志名，目前所同步的主数据库的binlog日志的偏移位置和目前所执行的relay_log日志偏移位置。

spacer.gif 

我们到主数据库上查看主的状态，比对两库所同步的binlog名字和偏移位置信息是否一致；一致说明IO同步进程没有问题。

 

spacer.gif 

 

找到系统中对应的relay_log文件，然后将它导出：

 

spacer.gif 

 

（2）. 这样就可以通过vi ，cat ，more等命令去查看relay_log文件里面的内容了

 

spacer.gif 

通过定位，很快就可以找到它当前正在同步的位置，

下面分别是它的日期和时间，数服的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       //可以查看到帮助信息

spacer.gif 

图中红线是几个常用的参数；

-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）