oms 4.2.2_CE 版本 数据同步慢

源端:ob-mysql 3.1.4

目标端:ob-mysql 4.2.1.6

预检查和数据表结构同步慢,超级慢;mysql到ob-mysql 4.2.1.6没有此问题

sql root用户加索引失败:ERROR 1044 (42000): Access denied for user ‘root’@’%’ to database ‘information_schema’


异常sql

sql2:

SELECT NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, COLLATION, SUB_PART, INDEX_TYPE, INDEX_COMMENT, IS_VISIBLE FROM information_schema.STATISTICS S WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND NOT EXISTS ( SELECT ? FROM information_schema.TABLE_CONSTRAINTS C WHERE S.TABLE_SCHEMA = C .TABLE_SCHEMA AND S.TABLE_NAME = C .TABLE_NAME AND S.INDEX_NAME = C .CONSTRAINT_NAME ) ORDER BY INDEX_NAME ASC, SEQ_IN_INDEX ASC

sql1:

SELECT C .CONSTRAINT_NAME AS CONSTRAINT_NAME, S.COLUMN_NAME AS COLUMN_NAME, K.ORDINAL_POSITION AS ORDINAL_POSITION, S.COLLATION AS COLLATION, S.SUB_PART AS SUB_PART, S.INDEX_COMMENT AS INDEX_COMMENT FROM information_schema.TABLE_CONSTRAINTS C, information_schema.KEY_COLUMN_USAGE K, information_schema.STATISTICS S WHERE C .TABLE_SCHEMA = K.TABLE_SCHEMA AND C .TABLE_NAME = K.TABLE_NAME AND C .TABLE_SCHEMA = S.TABLE_SCHEMA AND C .TABLE_NAME = S.TABLE_NAME AND C .CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND C .CONSTRAINT_NAME = K.CONSTRAINT_NAME AND C .CONSTRAINT_NAME = S.INDEX_NAME AND K.ORDINAL_POSITION = S.SEQ_IN_INDEX AND C .TABLE_SCHEMA = ? AND C .TABLE_NAME = ? AND C .CONSTRAINT_TYPE = ? ORDER BY K.ORDINAL_POSITION ASC

ob 3.x元数据查询确实是很慢,这是已知问题,如果不能忍受可以升级到oms最新版4.2.6 CE,

V3.x 元数据本地缓存及创建任务时直接查询本地缓存功能,解决 OceanBase 数据库 V3.x 大量表无法创建数据迁移任务的问题。
https://www.oceanbase.com/docs/community-oms-cn-1000000001301999

具体操作:
1、在数据源管理点击3.x数据源的【更多操作】-【缓存系统视图】,


2、重建链路

缓存系统视图比较耗时,具体看数据源中的表的数量,需要等待换成工单成功结束之后才可能进行第二步重新建数据源

好的,谢谢老师 :pray:

4.2.6 今天正在发布中,下午应该可以下载

image
有4.2.6版本的安装包嘛老师,官网看只有4.2.5的

好的好的

哪个版本的ob server 对information_schema.TABLE_CONSTRAINTS这个表性能做了优化老师?

3.x 性能都不好,结构迁移的时候不止访问这一张系统视图,OMS 4.2.6 社区版已经可以下载了