mysql 查询延时_mysql主从延时问题排查全过程

最新推荐文章于 2024-08-11 00:15:00 发布

哦日常

最新推荐文章于 2024-08-11 00:15:00 发布

阅读量758

点赞数

文章标签： mysql 查询延时

本文链接：https://blog.csdn.net/weixin_35870524/article/details/113209576

版权

如果一开始的方向错了，会导致你多走不少弯路，可这并不一定是坏事儿，你可以学到很多相关别的知识。开发同事反应测试环境主从有问题，就让我看一下，问题始于这里，本来测试环境有挂主从监控脚本，看了下邮件没有报错，就没有在意，以为直接再做一次主从或者重新全备一下要做主从的数据库，就可以了。下面附上主从监控脚本，不会的同学可以仿着用，大相径庭：

#!/bin/bash

export

PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin

export mail_list='接收告警邮件地址'

export mail_fm="报警邮箱地址"

export mail_smtp="发信地址"#例QQ的发件地址：smtp.exmail.qq.com

export mail_user="邮箱登陆帐号"

export mail_pwd="邮箱密码"

export

mail_type="邮件内容的格式"#例message-content-type=html表示它是html格式

Datatime=`date +%F_%R_%S`

masterhost="x.x.x.x"#主库ip地址

masteruser="root"

masterdbpassword='123456'

slavehost="x.x.x.x"#从库ip地址

slaveuser="root"

slavedbpassword="123456"

mstool="/usr/local/mysql/bin/mysql -h

$masterhost -u$masteruser -p$masterdbpassword " #

请根据自己实际情况填写楼主mysql源码安装

sltool="/usr/local/mysql/bin/mysql -u$slaveuser -p$slavedbpassword"

text="$Datatime------slavex.x.x.x---------Warning..."

declare -a slave_stat #变量slave_stat -a 会被当作数组,方便后面的切片,其它参数请自行百度

slave_stat=($($sltool -e "show slave status\G" | grep Running

|awk '{print $2}'))

if [ "${slave_stat[0]}" = "Yes" -a

"${slave_stat[1]}" = "Yes" ]

then

exit 0

else

file=`$mstool -e "show master status\G" | grep

"File" | awk '{print $2}'`

pos=`$mstool -e "show master status\G"|

grep "Pos" | awk '{print $2}'`

$sltool -e "stop slave ;change master to

master_host='$masterhost',master_user='mysync',master_password='123456',master_log_file='$file',master_log_pos=$pos;

start slave ;"#mysync自行授权从库权限帐号百度大把

echo $Datatime >>

/var/log/check_slave.log

/usr/bin/sendEmail -u

Mysql_slave -f $mail_fm -t

$mail_list,$mail_list1 -s $mail_smtp -xu

$mail_user -xp $mail_pwd -o $mail_type

message-charset=utf-8 -m $text > /dev/null#-u

邮件的标题 -f 发件人邮箱 -t

接收人邮件地址可以是多个 -s 发件人邮箱的smtp服务器 -xu 发件人邮箱的用户名 -xp 发件人邮箱密码 -o 邮件内容的格式,html表示它是html格式 utf-8 邮件内容编码 -m 邮件的具体内容

1、机器反应问题的直接排查：

刚开始查看发现SLAVE机器IO很高，然后用命令iostat 发现 %until 一直是99%左右，然后用命令iotop 发现在占用IO最多的进程是 jbd2

这个进程，百度发现：mysql启动slave, jbd2进程占用大量的磁盘IO ，然后一步一步排查。楼主系统：Centos 6.5 64bit

如下三个原因一步一步排查： 1)、yum升级kernel

，重启查看是否有效。(在此之前先要做好备机使用的准备工作)

2)、重装系统分区，完成后重新挂载数据分区。

3)、验证临时补丁的可用性。并在现网修复。

原文链接地址：http://www.361way.com/ext4-jbd2-io-bug/2963.html

楼主升组了kernel 并重新挂载了根分区，问题依然没有任何改变。第3步的补丁，一直没有找到所以放弃第三步。

2.会不会是硬件问题

然后开始测试磁盘读写相关，关掉slave,会引发很高的IO导致测试结果完全不准确。发现测试完和别的机器相差不多。

测试过程如下：

hdparm -t --direct

/dev/sda3 简单测读速度

dd bs=1M

count=20000 if=/dev/zero of=test.dd

conv=fdatasync 简单测试写速度

这个原因排除

原文参考地址：https://blog.csdn.net/qq_35298894/article/details/79022859

3.mysql本身相关日志的分析，慢查询日志、错误日志、中继日志、binlog日志

对比主库从库binlog日志节点发现主从做好后，刚开始5分钟是正常的，然后就开始频繁的写，用dstat

或者 atop都会看到磁盘的实时写入读取情况

会一直写入速度大概是500K左右。对比主从两个日志节点发现日志同步刚开台是正常的和主库一致，在主库进行了一个不算

特别大的批量update以后，从库开始慢慢执行。至些问题应该是这些语句了吧，然后关掉相关计划任务还有语句update

重新查看发现还是同样的情况。然后彻底感到疑惑了。

这里有两个my.cnf 参数需要了解一下：

innodb_flush_log_at_trx_commit =

N： N=0 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；

log buffer 会

每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响，也就是 log buffer

的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s

的日志丢失。

N=1 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；当取值为 1 时，每次事务提交时，log buffer

会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。

N=2 每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；当取值为 2

时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld

进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s

的日志丢失。上面说到的「最后 1s」并不是绝对的，有的时候会丢失更多数据。有时候由于调度的问题，每秒刷写(once-per-second

flushing)并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用，配置为 2

就足够了；如果为了最高性能，可以设置为 0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为

1. 当我们设置为2

的时候，Log Thread

会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log

Thread

的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log

Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据的丢失，但是OS Crash

或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样，大家可以自行参阅相关的手册。 sync_binlog = N： N>0 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上； N=0 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；

原文连接地址：https://blog.csdn.net/thundermeng/article/details/50448614

4.查看配置文件、查看配置文件

最后没有办法就开始看SQL的配置文件，查看mysql启动程序

的时候看到了my.cnf主配置文件，然后赶紧检查了一下这台机器的my.cnf.发现/etc/my.cnf 还有/usr/local/mysql/etc/my.cnf 至此原因应该是这个了。重命名掉一个my.cnf

,再把MYSQL跑起来，再查看主从状态，一切正常，至此原因找到。my_print_defaults |

grep my.cnf 这条命令可以查看mysql读取配置文件的顺序。