锐客网

  1. 首页 > 睿知 > it技术 > >

俺怎么诊断mysql 如何检测mysql数据库变化( 二 )

IT技术查询


1.2 外部资源观测
外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了 。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检 。一共十条命令，这是前五条，我们一条一条来看 。
1.uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少 。
2.dmesg -T | tail ， 告诉我们系统日志里边有没有什么报错 。
3.vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题 ， swap有没有使用 。
4. mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的 。
5.pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子 。
我们来看一下后五条：
首先是iostat-xz 1，查看IO的问题 ， 然后是free-m内存使用率 ， 之后两个sar ， 按设备网卡设备的维度，看一下网络的消耗状态 ， 以及总体看TCP的使用率和错误率是多少 。最后一条命令top，看一下大概的进程和线程的问题 。
这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源 。
1.3 外部需求改造
第三个诊断思路是外部的需求改造，我在这里引用了一篇文档 ， 这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries ， 文档中介绍了常规的SQL怎么写, 给出了一些例子 。文章的链接二维码在slide上 。
我们来看一下它其中提到的一个例子 。
它做的事情是从一个表里边去选?。庹疟碛腥校琣rticle、dealer、price ， 选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询 。
关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的 。
第三步 ， 会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样 ， 把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了 ， 只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式 。
为什么它能够把一个关联子查询拆掉呢？
这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉 。
上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系 。然后一步步推导怎么去简化这句SQL 。
第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题 。
第二，从服务器的角度，我们从巡检的脚本角度入手 ， 服务器的资源就这几种 ， 观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了 。
第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL ， 这个成本会下降的非常快 ， 这个是常规的MySQL慢的诊断思路 。
如何检查MySQL数据库的主从延时？MySQL数据库主从延时如何去判断呢？本文我们介绍了两种判断方法：1. Seconds_Behind_Master vs 2. mk-heartbeat，接下来我们就分别介绍这些内容 。日常工作中，对于MySQL主从复制检查，一方面我们要保证复制的整体结构是否正常，另一方面需要检查主从数据是否保持一致 。对于前者我们可以通过监控复制线程是否工作正常以及主从延时是否在容忍范围内，对于后者则可以通过分别校验主从表中数据的md5码是否一致，来保证数据一致，可以使用Maatkit工具包中的mk-table- checksum工具去检查 。方法1： 通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时 。其值有这么几种： NULL — 表示io_thread或是sql_thread有任何一个发生故障 ， 也就是该线程的Running状态是No，而非Yes 。0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在 。正值— 表示主从已经出现延时，数字越大表示从库落后主库越多 。负值— 几乎很少见，我只是听一些资深的DBA说见过 ， 其实，这是一个BUG值，该参数是不支持负值的 ， 也就是不应该出现 。show slave status\G ， 该命令的输出结果非常丰厚，给我们的监控提供了很多有意义的参数，比如：Slave_IO_Running该参数可作为 io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；Slave_SQL_Running该参数代表sql_thread是否正常，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在 。下面就说到今天的重点Seconds_Behind_Master ， 该值作为判断主从延时的指标，那么它又是怎么得到这个值的呢，同时，它为什么又受到很多人的质疑？ Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和 io_thread复制好的event的timestamp（简写为ts）进行比较，而得到的这么一个差值 。我们都知道的relay-log和主库的 bin-log里面的内容完全一样，在记录sql语句的同时会被记录上当时的ts，所以比较参考的值来自于binlog ， 其实主从没有必要与NTP进行同步，也就是说无需保证主从时钟的一致 。你也会发现，其实比较真正是发生在io_thread与sql_thread之间，而io_thread才真正与主库有关联，于是，问题就出来了，当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而 sql_thread一直都能跟上io_thread的脚本 ， 这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是 ， 你懂得 。这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当io_thread与 master网络很好的情况下，那么该值也是很有价值的 。之前，提到Seconds_Behind_Master这个参数会有负值出现，我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到的ts差值 ， 前者始终是大于后者的，唯一的肯能就是某个event的ts发生了错误 ， 比之前的小了，那么当这种情况发生时，负值出现就成为可能 。方法2： mk-heartbeat，Maatkit万能工具包中的一个工具 ， 被认为可以准确判断复制延时的方法 。mk-heartbeat的实现也是借助timestmp的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个NTP server同步时钟 。它需要在主库上创建一个heartbeat的表，里面至少有id与ts两个字段 ， id为server_id，ts就是当前的时间戳 now() ， 该结构也会被复制到从库上 。表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为1 秒 ， 同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时 ， 差值越大表示延时的秒数越多 。我们都知道复制是异步的ts不肯完全一致 ， 所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时 。这个工具就是通过实打实的复制，巧妙的借用timestamp来检查延时，非常好用！ 关于检查MySQL数据库的主从延时的两种方法就介绍到这里了 ， 希望本次的介绍能够对您有所收获！

推荐阅读


上一篇：微信公众号关注怎么买，购买关注公众号

下一篇：phpcms怎么修改用户名，phpcms使用教程