mysql运维问题解决：MySQL主从延迟（无主键表）

小亦智能运维
60 阅读3分钟

小亦平台会持续给大家科普一些运维过程中常见的问题解决案例，运维朋友们可以在常见问题及解决方案专栏查看更多案例

问题概述

  • 巡检发现客户mysql数据库发现如下环境存在主从复制延迟****

image.png

  • 延迟表现： ...42 和 ...43：SQL线程回放效率低下；...96 和 ...98：SQL线程位点长期停滞****

问题分析

1. 根本原理

  • MySQL主从复制是逻辑复制，从库需重新执行主库的SQL语句
  • 无主键/唯一键的表会导致从库SQL线程全表扫描，大幅降低回放效率

2. 具体诊断

...96 和 ...98 延迟分析：

  • 通过登录mysql 多次执行show slave status \G命令发现从库sql线程执行的主节点的binlog位点一直不变化 如下图:

image.png

  • 通过如下语句获取sql 线程正在执行的相关中继日志内容如下图:
mysqlbinlog -vv --base64-output=decode-rows --start-position=4043790 lchod11098-relay-bin.000436|more

image.png

  • 通过发现正在对unirecvpaydb.task_logs_item表做一个批量的delete 操作。通过查看表结构(如下图)发现该表上无任何索引。

image.png

...42 和 ...43 延迟分析：

  • 同样存在批量DELETE操作（表 dm.DM_llMEDICALFEEDETAIL_G）
  • 该表无任何索引：

image.png

  • 12月28号上午10点操作为：| INSERT INTO ods_gx_llclaimpolicy (CLMNO, CASENO, RGTNO, GRPCONTNO, GRPPOLNO, CONTNO, POLNO, GETDUTYKIND, KINDCODE, RISKCODE, RISKVER, POLMNGCOM, SALECHNL, AGENTCODE, AGENTGROUP, INSUREDNO, INSUREDNAME, APPNTNO, APPNTNAME, CVALIDATE, POLSTATE, CLMSTATE, STANDPAY, REALPAY, PAYTYPE, PREGIVEAMNT, SELFGIVEAMNT, REFUSEAMNT, APPROVEAMNT, AGREEAMNT, GIVETYPE, GIVETYPEDESC, GIVEREASON, GIVEREASONDESC, CLMUWER, ENDCASEDATE, REMARK, CASEPOLTYPE, POLTYPE, NBPOLNO, CASEPAYTYPE, CURRENCY, MNGCOM, OPERATOR, MAKEDATE, MAKETIME, MODIFYDATE, MODIFYTIME) VALUES ( '86282300000410', '86282300000410', '86282300000410', '00000000000000000000', '00000000000000000000', '280051512888', '210280000873599', '200', 'H', 'HMT049', '2008', '862804001', '01', '2804001683', '000000020194', '000229410688', '鏉ㄦ亽杈, '000229410688', '鏉ㄦ亽杈, '2022-11-15 00:00:00', '1', '50', 0, 0, null, 0, 0, 0, 0, 0, '01', null, null, null, 'SC00515', '2023-07-17 00:00:00', null, null, null, null, '5', '01', '8628', 'SC00515', '2023-07-17 00:00:00', '17:44:19', '2023-07-17 00:00:00', '17:44:19')****

解决方案

  1. 紧急修复延迟节点
  • 重建从库：立即重建 ...42、...43、...96、...98 节点
  1. 表结构优化
  • 添加主键：为所有无主键表添加主键:
select table_schema, table_name

from information_schema.tables

where table_type = 'BASE TABLE'

and (table_schema, table_name) not in

(select /*+ subquery(materialization) */

a.TABLE_SCHEMA, a.TABLE_NAME

from information_schema.TABLE_CONSTRAINTS a

where a.CONSTRAINT_TYPE in ('PRIMARY KEY')

and table_schema not in

('mysql', 'information_schema', 'sys', 'performance_schema'))

AND table_schema not in

('mysql', 'information_schema', 'sys', 'performance_schema');

3. 大事务优化

  • 拆分批量操作： 建议在做大表更新的时候、将修改语句拆分为单次修改的行数2W行左右。

立即查看更多mysql的相关内容：www.ces-xiaoyi.com.cn/#/workOrder…

运维工作中遇到难题？立即提交工单：www.ces-xiaoyi.com.cn/#/welcome/k… 小亦平台工程师火速响应，助您快速修复故障!

avatar
文章
阅读
粉丝