2、大概20w个分区表,每张表4个hash分区
3、使用oms在界面只配置了一个库(大概64个表),做同步测试
4、预检查的时候很慢,目的端-数据库表结构检查一直是等待中,直到超时,通过ob中show processlist查看,卡在了如下sql上:
select /+query_timeout(1800000000)/ TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,upper(COLUMN_TYPE) ,CHARACTER_MAXIMUM_LENGTH ,NUMERIC_PRECISION,NUMERIC_SCALE,NULL,CASE WHEN GENERATION_EXPRESSION=’’ or GENERATION_EXPRESSION is null THEN ‘NO’ ELSE ‘YES’ END GENERATION,IS_NULLABLE,CHARACTER_SET_NAME from information_schema.columns where (TABLE_SCHEMA,TABLE_NAME) in (…,…,…,…) order by TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION asc
5、2-2-2架构,其中ob unit的配置为:80G、20 CPU、ob_sql_work_area_percentage = 40、_storage_meta_memory_limit_percentage=20
请问大概什么原因导致的,该怎么优化 一下呢?
但是在全量迁移开始的时候,还是会执行
select /+query_timeout(1800000000) / TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME ,upper(COLUMN_TYPE ) ,CHARACTER_MAXIMUM_LENGTH ,NUMERIC_PRECISION ,NUMERIC_SCALE ,NULL,CASE WHEN GENERATION_EXPRESSION=’’ or GENERATION_EXPRESSION is null THEN ‘NO’ ELSE ‘YES’ END GENERATION,IS_NULLABLE ,CHARACTER_SET_NAME from information_schema.columns where (TABLE_SCHEMA,TABLE_NAME) in (…,…,…,…) order by TABLE_SCHEMA ,TABLE_NAME ,ORDINAL_POSITION asc
这个sql,全量数据一条也过不来,卡在这个sql这里
select /+query_timeout(1800000000)/ TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME,SUB_PART from information_schema.STATISTICS where (TABLE_SCHEMA,TABLE_NAME) in (…,…) order by TABLE_SCHEMA,TABLE_NAME,INDEX_NAME, SEQ_IN_INDEX asc
还有这个sql也很慢。全量同步一条数据都没进来呢