MySQL数据库复制延迟

梦莱2年前技术文章725

【问题处理记录】

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 操作要快,且不会产生大量日志。



相关文章

MySQL运维实战(4.9) SQL_MODE之NO_UNSIGNED_SUBTRACTION

在mysql数据库中,unsigned表示不存负数,如果unsigned类型的字段作运算,得到的结果为负数,SQL会报错。mysql> create table t...

SQL隐式转换导致索引失效_函数

SQL隐式转换导致索引失效_函数

一、隐式转换分类1.函数2.数据类型3.字符集4.校验规则二、常见案例本节将会针对第一部分提到的四种隐式转换内容,举例说明。1.索引列使用函数导致索引失效示例 SQL 如下,该 SQL 的 where...

开源大数据集群部署(八)Ranger编译部署

开源大数据集群部署(八)Ranger编译部署

在hd1.dtstack.com主机root权限下操作。1、 编译rangerranger二进制包编译过程在本次过程中不做详细说明。简单说明如下:Ø  在pom.xml中更改对应hadoop集群组件版...

MySQL 8.0 新特性:innodb_dedicated_server

MySQL 8.0 新特性:innodb_dedicated_server

一、前言Innodb Dedicated Server 是 8.0 版本推出的一个参数,开启之后可以根据服务器的配置自适应 innodb 引擎中的一些重要影响性能的参数,默认是关闭的。二、参数测试使用...

Elasticsearch查询慢日志配置

shard级别的查询慢日志会将慢查询(查询和获取阶段)记录到elasticsearch_cluster_index_search_slowlog.log日志中。设置查询慢日志各种级别下的阀值,同时也支...

oracle开启关闭归档

一、开启归档1.开启前准备:备份spfileCreate pfile='d:pfileSID.ora' from spfile;OS上创建归档目录:+DATA_DG/arch设置归档路径:alter...

发表评论    

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