MySQL中从库延迟状况排查的一则案例
作者：吴炳锡
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了MySQL中从库延迟状况排查的一则案例,针对其从库无业务状态下的CPU大量占用情况,需要的朋友可以参考下
今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL.
可以说是典的单进程复制把一个cpu占满造成的．知道原因了，就好分析了．
分析一下binlog中写的什么，看看有什么地方可以优化或是加速的．利用工具：pasrebinlog
利用show slave status\G; 查当前同步的到节点，然后对日值进行解析．
git clone /wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806
====================================
Table xx_db.xxtable:
Type DELETE opt： 101246
Type INSERT opt： 103265
================================
以最大的数排序看，　定位到:　xx_db.xxtable，对于一个日值中能删除10几万，写入10几万．是不是这个表写入比较慢了呢．
在从库上查看innodb的相关情况：
MySQL& show engine innodb status\G;
---TRANSACTION 1C0C2DFDF, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
3361 lock struct(s), heap size 7888 row lock(s), undo log entries 42
MySQL thread id 43, OS thread handle 0x7fc, query id 1908504 Reading event from the relay log
TABLE LOCK table xx_db.xxtable trx id 1C0C2DFDF lock mode IX
RECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6967 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6973 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6982 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6983 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6987 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6999 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 7000 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
----------------------------
END OF INNODB MONITOR OUTPUT
从Innodb 的monitor output 中也可看到 xx_db.xxtable 这表已经是表级表了，造成并发比较低，而且有大量的：　GEN_CLUST_INDEX　而且属于一个事务．　　GEN_CLUST_INDEX表示没有主建，内部产生一个主建，对于内部产生的主建很很容易造成page拆分的操作．
问题到这里基本上可以得到解决问题的方法了：
给xx_db.xxtable 添加一个主建即可．这里后是给xx_db.xxtable 添加了一个无业务意义的id　int　自增主建．这样立马可以看到mysqld占用的cpu单核降到了3％左右，　同时后续同步一切正常，观查一天没出现同步延迟的问题．
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具博客访问： 1229843
博文数量： 132
博客积分： 2559
博客等级： 少校
技术积分： 4374
注册时间：
作为初学者,要想取得进步,成为高手,首先应该了解自己的不足之处.
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Mysql/postgreSQL
binlog介绍
登录到mysql查看binlog
用mysqlbinlog工具查看
binlog介绍
binlog,即二进制日志,它记录了数据库上的所有改变.
改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
binlog格式
基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;
基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.
登录到mysql查看binlog
只查看第一个binlog文件的内容
查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';
查看当前正在写入的binlog文件
show master status\G
获取binlog文件列表
用mysqlbinlog工具查看
不要查看当前正在写入的binlog文件
不要加--force参数强制访问
如果binlog格式是行模式的,请加 -vv参数
基于开始/结束时间
mysqlbinlog --start-datetime=' 00:00:00' --stop-datetime=' 01:01:01' -d 库名 二进制文件
mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hl-db1. -P3306 \
--read-from-remote-server --start-datetime=' 23:00:00' --stop-datetime=' 23:30:00' mysql-bin.000001 > t.binlog
阅读(51238) | 评论(3) | 转发(4) |
相关热门文章
给主人留下些什么吧！~~
mysqlbinlog查看方式很粗糙&，粒度不够，我比较推荐pt-query-digest
你的MySQL没有装好.依赖的库文件找不到.注:&遇到问题,优先问Google或者Baidu
进入数据库出现错误：error&while&loading&shared&libraries&:&libncursesw.so.5:&cannol&open&shared&object&file&:&no&such&file&or&&directory
请登录后评论。打开binlog修改my.cnf，添加log-bin配置项
log-bin=binlog
使用mysql客户端查看binlog（1）查看所有日志文件：
+—————+———–+
| Log_name
| File_size |
+—————+———–+
| binlog.000001 |
+—————+———–+
1 row in set (0.00 sec)
（2）查看正在写入的日志文件：
+—————+———-+————–+——————+
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| binlog.000001 |
+—————+———-+————–+——————+
1 row in set (0.00 sec)
（3）查看当前binlog文件内容：
+—————+—–+————-+———–+————-+—————————————————+
| Log_name
| Pos | Event_type
| Server_id | End_log_pos | Info
+—————+—–+————-+———–+————-+—————————————————+
| binlog.000001 |
4 | Format_desc |
107 | Server ver: 5.5.34-log, Binlog ver: 4
| binlog.000001 | 107 | Query
173 | BEGIN
| binlog.000001 | 173 | Query
269 | use `im`; insert into user values(3,”yangyg”)
| binlog.000001 | 269 | Xid
296 | COMMIT /* xid=4 */
| binlog.000001 | 296 | Query
362 | BEGIN
| binlog.000001 | 362 | Query
460 | use `im`; insert into user values(4,”lidong01″)
| binlog.000001 | 460 | Xid
487 | COMMIT /* xid=5 */
| binlog.000001 | 487 | Query
553 | BEGIN
| binlog.000001 | 553 | Query
650 | use `im`; insert into user values(5,”sunxuan”)
| binlog.000001 | 650 | Xid
677 | COMMIT /* xid=6 */
| binlog.000001 | 677 | Query
743 | BEGIN
| binlog.000001 | 743 | Query
843 | use `im`; insert into user values(6,”chendong01″) |
| binlog.000001 | 843 | Xid
870 | COMMIT /* xid=34 */
+—————+—–+————-+———–+————-+—————————————————+
13 rows in set (0.00 sec)
相关文章 :MySQL远程获取BinLog日志
(window.slotbydup=window.slotbydup || []).push({
id: '2611110',
container: s,
size: '240,200',
display: 'inlay-fix'
您当前位置： &
[ 所属分类
| 时间 2015 |
作者 红领巾 ]
标题中强调了「远程获取」，是因为很多时候我们会遇到的数据并不是存放在本地，而是跨了机器，无法取到日志文件。
当然了，前提需要先确认远程的机器已经打开了BinLog的记录，先连上去看看： mysql -uroot -p123456 -h192.168.1.100 -P3306
mysql& show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
如果提示开关为OFF，那可以把这个文章关掉了，因为没有开启BinLog已经回天乏力，如果是ON请继续，使用下方命令得到文件名：
/* 等于命令 */
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysql-bin.00 |
| mysql-bin.9 |
+-----------------+-----------+
如果要远程提取的日志是 mysql-bin.000099
，则使用以下命令即可：
mysqlbinlog -uroot -p123456 -h192.168.1.100 -P3306 --read-from-remote-server mysql-bin.000099 & binlog-99.sql
提取BinLog的日志时，可以支持指定某个数据库，但是不支持指定到表，
mysqlbinlog -uroot -p123456 -h192.168.1.100 -P3306 --read-from-remote-server --datebase=jtwo mysql-bin.000099 & binlog-99.sql
如果需要限制一下获取的日志时间段，可以加上开始和结束时间的参数：
mysqlbinlog -uroot -p123456 -h192.168.1.100 -P3306 -R --start-datetime=" 00:00:00" --stop-datetime=" 12:00:00" BINLOG_NAME & BINLOG.sql
/mysql_binlog.html
/Linux/653.htm
本文数据库（mysql）相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具
转载请注明本文标题：本站链接：
分享请点击：
1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
阅读(2216)
CodeSecTeam微信公众号
总有人在你切一盘水果时秒杀一道数学题，总有人在你调整愤怒的小鸟弹射角度时记住一个单词，总有人在你打一盘dota的时间内看完一章教材，总有人在你打一局2K的时间里完成一套阅读题，总有人在你与他人闲聊时听一段VOA，总有人在你熟睡时回想一天的得失，总有人比你跑得快，你还会虚度光阴么？
手机客户端