MySQL数据库复制延迟

梦莱2年前技术文章735

【问题处理记录】

1.查看资源情况,通过查找慢 SQL 发现,从 3:00 开始,存在较多 update 和 delete 操作。怀疑是表结构无主键,由于 RDS 日志模式默认采取的 row 模式,导致后续日志放到备库后,每更新或者删除一条记录,对应到备库都需要进行全表扫描,以此造成主备延迟现象。

2.通过查看表结构发现,表无任何索引,且存在较多 text 字段,具体表结构信息如下:

CREATE TABLE `XXX` (
  ..........
  `parent_label` text COLLATE utf8mb4_general_ci,
  `parent_data_version` int DEFAULT NULL,
  `parent_sn_path` text COLLATE utf8mb4_general_ci,
  `category_sn` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `category_label` text COLLATE utf8mb4_general_ci,
  `category_data_version` int DEFAULT NULL,
  `category_sn_path` text COLLATE utf8mb4_general_ci,
  `column1` text COLLATE utf8mb4_general_ci,
  `column2` text COLLATE utf8mb4_general_ci,
  `column3` text COLLATE utf8mb4_general_ci,
  `column4` text COLLATE utf8mb4_general_ci,
  `column5` text COLLATE utf8mb4_general_ci,
  `column6` text COLLATE utf8mb4_general_ci,
  `column7` text COLLATE utf8mb4_general_ci,
  `column8` text COLLATE utf8mb4_general_ci,
  `column9` text COLLATE utf8mb4_general_ci,
  `column10` text COLLATE utf8mb4_general_ci,
  `column11` text COLLATE utf8mb4_general_ci,
  `column12` text COLLATE utf8mb4_general_ci,
  `column13` text COLLATE utf8mb4_general_ci,
  `column14` text COLLATE utf8mb4_general_ci,
  `column15` text COLLATE utf8mb4_general_ci,
  `column16` text COLLATE utf8mb4_general_ci,
  `column17` text COLLATE utf8mb4_general_ci,
  `column18` text COLLATE utf8mb4_general_ci,
  `column19` text COLLATE utf8mb4_general_ci,
  `column20` text COLLATE utf8mb4_general_ci,
............
  `building` text COLLATE utf8mb4_general_ci,
  `pushId` text COLLATE utf8mb4_general_ci,
  `alias` text COLLATE utf8mb4_general_ci,
  `saleId` text COLLATE utf8mb4_general_ci,
  `saleName` text COLLATE utf8mb4_general_ci,
  `nature` text COLLATE utf8mb4_general_ci,
  `status` text COLLATE utf8mb4_general_ci,
  `series` text COLLATE utf8mb4_general_ci,
  `structure` text COLLATE utf8mb4_general_ci,
  `land` text COLLATE utf8mb4_general_ci,
  `stageId` text COLLATE utf8mb4_general_ci,
  `stageName` text COLLATE utf8mb4_general_ci,
  `businessId` text COLLATE utf8mb4_general_ci,
  `businessType` text COLLATE utf8mb4_general_ci,
  `remark` text COLLATE utf8mb4_general_ci,
  `jzzdmj` text COLLATE utf8mb4_general_ci,
  `zjzmj` text COLLATE utf8mb4_general_ci,
  `dszjzmj` text COLLATE utf8mb4_general_ci,
  `dxzjzmj` text COLLATE utf8mb4_general_ci,
  `zjrmj` text COLLATE utf8mb4_general_ci,
  `dsjrmj` text COLLATE utf8mb4_general_ci,
  `dxjrmj` text COLLATE utf8mb4_general_ci,
  `dsfjrmj` text COLLATE utf8mb4_general_ci,
  `dxfjrmj` text COLLATE utf8mb4_general_ci,
  `cdxqckjzmj` text COLLATE utf8mb4_general_ci,
  `dxzxckjzmj` text COLLATE utf8mb4_general_ci,
  `dxsbyfjzmj` text COLLATE utf8mb4_general_ci,
  `dxqtjzmj` text COLLATE utf8mb4_general_ci,
  `fjrjkczjzmj` text COLLATE utf8mb4_general_ci,
  `zksmj` text COLLATE utf8mb4_general_ci,
  `dsksmj` text COLLATE utf8mb4_general_ci,
  `dxksmj` text COLLATE utf8mb4_general_ci,
  `zzsmj` text COLLATE utf8mb4_general_ci,
  `dszsmj` text COLLATE utf8mb4_general_ci,
  `dxzsmj` text COLLATE utf8mb4_general_ci,
  `sjdj` text COLLATE utf8mb4_general_ci,
  `dsksb` text COLLATE utf8mb4_general_ci,
  `dscs` text COLLATE utf8mb4_general_ci,
  `dxcs` text COLLATE utf8mb4_general_ci,
  `dys` text COLLATE utf8mb4_general_ci,
  `lddts` text COLLATE utf8mb4_general_ci,
  `dxjzmj` text COLLATE utf8mb4_general_ci,
  `buildingId` text COLLATE utf8mb4_general_ci,
  `completeDate` text COLLATE utf8mb4_general_ci,
  `isHanding` text COLLATE utf8mb4_general_ci,
  `handingDate` text COLLATE utf8mb4_general_ci,
  `rfjzmj` text COLLATE utf8mb4_general_ci,
  `projectId` text COLLATE utf8mb4_general_ci,
  `projectName` text COLLATE utf8mb4_general_ci,
  `natureCode` text COLLATE utf8mb4_general_ci,
  `statusCode` text COLLATE utf8mb4_general_ci,
  `seriesCode` text COLLATE utf8mb4_general_ci,
  `structureCode` text COLLATE utf8mb4_general_ci,
  `landCode` text COLLATE utf8mb4_general_ci,
  `sfyx` text COLLATE utf8mb4_general_ci,
  `protypeCode` text COLLATE utf8mb4_general_ci,
  `protype` text COLLATE utf8mb4_general_ci,
  `timestamp` text COLLATE utf8mb4_general_ci,
  `planBulidCode` text COLLATE utf8mb4_general_ci,
  `xxrq` text COLLATE utf8mb4_general_ci,
  `fangkuantj` text COLLATE utf8mb4_general_ci,
  `jhxxdata` text COLLATE utf8mb4_general_ci,
  `jhjgdata` text COLLATE utf8mb4_general_ci,
  `jhjfdata` text COLLATE utf8mb4_general_ci,
  `jhjdfktj` text COLLATE utf8mb4_general_ci,
  `htydjfdate` text COLLATE utf8mb4_general_ci,
  `houseAmount` text COLLATE utf8mb4_general_ci,
  `cppx` text COLLATE utf8mb4_general_ci,
  `ksmj` text COLLATE utf8mb4_general_ci,
  `fwyckdsbyfmj` text COLLATE utf8mb4_general_ci,
  `kswymj` text COLLATE utf8mb4_general_ci,
  `ccsmj` text COLLATE utf8mb4_general_ci,
  `wcpjmj` text COLLATE utf8mb4_general_ci,
  `zcwymj` text COLLATE utf8mb4_general_ci,
  `gcldmc` text COLLATE utf8mb4_general_ci,
  `ccsgs` text COLLATE utf8mb4_general_ci,
  `ychgmj` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

3.核实表结构信息,并反馈如下内容

核实表结构,表是没有主键的。目前给到如下意见:
1.表最好都添加上主键索引,避免删除的时候造成大的延迟。
2.这边可以核实一下业务层面为什么每天要进行 delete 操作。是否可以使用truncate操作代替。但是使用 truncate 以后,如果操作失误,不能通过 binlog 进行数据恢复,只能通过时间节点进行备份恢复。truncate操作会比delete 操作要快,且不会产生大量日志。



相关文章

Kubernetes源码解读(三)-- Indexer和ThreadSafeStore源码分析

Kubernetes源码解读(三)-- Indexer和ThreadSafeStore源码分析

Indexer主要提供一个对象根据一定条件检索的能力,典型的实现是通过nameapce/name来构造key,通过ThreadSafeStore 来存储对象。换而言之,Indexer主要依赖于Thre...

MySQL运维实战(5.1) 字符和编码的基本概念

MySQL运维实战(5.1) 字符和编码的基本概念

字符和编码字符字符是符号,是人们用于交流的各类符号,如26个英文字母、汉字、标点符号、数学运算符、其他语言的字母和符号。编码编码是计算机中以二进制方式存储字符的方式。字符集字符集是字符和编码的映射表。...

ES运维(二)字段类型与内存管理

ES运维(二)字段类型与内存管理

一、ES常见字段类型1、 概述字段是数据存储的最小微粒,根据数据的性质不同将数据分成不同的字段类型,熟悉不同字段类型的特性,对索引的Mapping设计、查询调优都极其重要。2、 关键参数In...

kafka部署建议

1       集群部署规范1.1      Cpu规格与挂盘数量的关系 &nb...

greenplum扩容

一、纵向扩展1、执行命令,生成参数文件[gpadmin@gw_mdw1 ~]$ gpexpand -f seg_hosts -D test 20190327:23:18:01:007122 gpex...

kafka高可靠性相关配置

kafka高可靠性相关配置

为保证高可靠可以通过以下方面进行设置:1) 物理机器场景配置项配置说明高可靠高性能不间断电源配置,防止服务器异常断电RAID卡电池配置,防止服务器异常断电RAID卡写缓存开启,提高性能RAID 1配置...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。