MySQL中从库延迟状况排查的一则案例

今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL.
可以说是典的单进程复制把一个cpu占满造成的.知道原因了,就好分析了.
分析一下binlog中写的什么,看看有什么地方可以优化或是加速的.利用工具:pasrebinlog
利用show slave status\G; 查当前同步的到节点,然后对日值进行解析.

git clone https://github.com/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 407992, 477888 row lock(s), undo log entries 42
MySQL thread id 43, OS thread handle 0x7fc1800c4700, 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%左右, 同时后续同步一切正常,观查一天没出现同步延迟的问题.

(0)

相关推荐

  • MySql insert插入操作的3个小技巧分享

    1.插入的数据来源自其他表 表A有id, cola 字段 表B有id, cola, colb...等字段,其中id都为主键,cola为相同名字的列 现想将表B中colb>1000的记录都插入表A中,SQL语句可以这样写: 复制代码 代码如下: insert into A(id,cola) select id,cola from B where colb>1000 2.插入时排除(忽略)重复记录 现表A中有一部分记录了,再做如1中的插入操作可能遇到重复的key导致操作失败 复制代码 代码如下:

  • 利用Python绘制MySQL数据图实现数据可视化

    本教程的所有Python代码可以在网上的IPython notebook中获取. 考虑在公司里使用Plotly?可以看一下Plotly的on-premises企业版.(注:On-premises是指软件运行在工作场所或公司内部,详见维基百科) 注意操作系统:尽管Windows或Mac用户也可以跟随本文操作,但本文假定你使用的是Ubuntu系统(Ubuntu桌面版或Ubuntu服务器版).如果你没有Ubuntu Server,你可以通过Amazon的Web服务建立一个云平台(阅读这份教程的前半部分

  • Linux shell实现每天定时备份mysql数据库

    每天定时备份mysql数据库任务,删除指定天数前的数据,保留指定天的数据: 需求:   1,每天4点备份mysql数据:   2,为节省空间,删除超过3个月的所有备份数据:   3,删除超过7天的备份数据,保留3个月里的 10号 20号 30号的备份数据: #创建shell文件 vim backup_mysql.sh mysqldump -uroot -p123456 --all-databases > /data/dbdata/mysqlbak/`date +%Y%m%d`.sql find

  • MySQL中从库延迟状况排查的一则案例

    今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL. 可以说是典的单进程复制把一个cpu占满造成的.知道原因了,就好分析了. 分析一下binlog中写的什么,看看有什么地方可以优化或是加速的.利用工具:pasrebinlog 利用show slave status\G; 查当前同步的到节点,然后对日值进行解析. git clone https://github.com/wubx/mysql-binlog-statisti

  • Mysql中DATEDIFF函数的基础语法及练习案例

    目录 语法格式 参数说明 示例 练习案例 总结 DATEDIFF函数用于返回两个日期的天数 语法格式 DATEDIFF(date1,date2) 参数说明 date1: 比较日期1 date2: 比较日期2 DATEDIFF函数返回date1 - date2的计算结果,date1和date2两个参数需是有效的日期或日期时间值;如果参数传递的是日期时间值,DATEDIFF函数仅将日期部分用于计算,并忽略时间部分(只有值的日期部分参与计算) 示例 -- 1 SELECT DATEDIFF('2022

  • 解决MySQL中的Slave延迟问题的基本教程

    一.原因分析 一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发.简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master. ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制.不过,它只能支持一个实

  • MySQL中修改库名的操作教程

    MySQL在5.1引入了一个rename database操作,但在MySQL5.1.23后又不支持这个命令.可以说是一个实验性的功能,没有在生产中支持过(mysql-5.1 release在mysql-5.1.30),那么生产中我们有时为了追求完美需要改一下库名.怎么操作呢? 这里提供一个变通的方法. 1. 创建出新库名: mysql>create database db_v2; mysql>create database db_v2; 2.生成rename语句,从olddb里迁移,我这里o

  • MySQL中使用MD5加密的实现

    目录 什么是MD5? MD5初体验 MySQL中其他方式 什么是MD5? MD5信息摘要算法(英语:MD5 Message-Digest Agorithm),一种被广泛使用的密码散列函数,可以产生出一个128位(16字节)的散列值(hash value),用于确保信息传输完整一致.MD5由美国密码学家罗纳德·李维斯特(Ronald LinnRivest))设计,于1992年公开,用以取代MD4算法.这套算法的程序在RFC1321标准中被加以规范.1996年后该算法被证实存在弱点,可以被加以破解,

  • PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法

    本文实例讲述了PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法.分享给大家供大家参考,具体如下: 这是一段简单的代码,可实现统计该数据库中每个表的记录数,并按递减顺序排列的功能 $host = '127.0.0.1'; $port = 3306; $dbname = 'test'; $username = 'root'; $password = ''; function ee($p) { if(PHP_SAPI == 'cli') { echo "\n"; }else{

  • MySQL中show命令方法得到表列及整个库的详细信息(精品珍藏)

    show databases;show tables from db_name; show columns from table_name from db_name;show index from talbe_name [from db_name]; show status;show variables; show [full] processlist;show table status [from db_name]; show grants for user; 除了status,process

  • MySQL中进行跨库查询的方法示例

    前言 在MySQL中跨库查询主要分为两种情况,一种是同服务的跨库查询:另一种是不同服务的跨库查询:它们进行跨库查询是不同的,下面就具体介绍这两种跨库查询. 一.同服务的跨库查询 同服务的跨库查询只需要在关联查询的时候带上数据名,SQL的写法是这样的:SELECT * FROM 数据库1.table1 x JOIN 数据库2.table2 y ON x.field1=y.field2:例如: 二.不同服务的跨库查询 不同服务的跨库查询,直接通过数据名加表明是无法进行关联的,这里需要用到MySQL数

  • MySQL5.6升级5.7时出现主从延迟问题排查过程

    最近在做zabbix的数据库MySQL5.6升级5.7时,出现主从延迟问题,这个问题困扰了很久没有解决,昨天终于解决了,整理了一下整个排查过程,分享给大家. 环境说明: mysql主库为5.6的版本,有四个从库,三个为5.6的版本,一个为5.7的版本,所有主从的库表结构均一致,5.7的从库出现大量延迟,5.6的没问题,业务为zabbix监控,基本全部为insert批量插入操作,每条insert SQL插入数据为400-1000行左右. 问题: MySQL5.7的从库大量延迟,relaylog落盘

  • 通过案例分析MySQL中令人头疼的Aborted告警

    本文主要给大家介绍的是关于MySQL中Aborted告警的相关内容,分享出来供大家参考学习,下面来一起看看详细的介绍: 实战 Part1:写在最前 在MySQL的error log中,我们会经常性看到一些各类的Aborted connection错误,本文中会针对这类错误进行一个初步分析,并了解一个问题产生后的基本排查思路和方法.掌握这种方法是至关重要的,而不是出现问题了,去猜,去试.数据库出现问题的时候需要DBA在短时间内快速解决问题,因此一个好与坏的DBA,区别也在于此. Part2:种类

随机推荐