truncate分区耗时问题始终无法解决

OceanBase 技术问题
#62

老师，今天重新获取了一下observer日志，您有空的时候帮忙看一下
election.7z (5.3 MB)
observer.7z (6.5 MB)
rootservice.7z (112.6 KB)

TRACE_ID: YB427F000001-00061FAA94A70FAC-0-0’

#63

老师，今天重新获取了一下observer日志，您有空的时候帮忙看一下
trace_id: YB427F000001-00061FAA94A70FAC-0-0’
election.7z (5.3 MB)
observer.7z (6.5 MB)
rootservice.7z (112.6 KB)

#65

我们分区的建立是按照日期创建的，当然我们delete的时候的条件也是按照日期筛选的。

我们最开始的方案是 delete from THisPosition where FSettleDate = “2024-08-13”;但是这个有一个问题是，delete的数量很大，之前遇到过很多次删除时间超时的情况。

后来增加分区后，直接删除分区

请问，按照小事务的delete怎么书写。通过limit限制删除的数量吗还是啥意思。
！！！我们的业务是通过mysql_c API完成的sql操作

#66

就是通过你们业务层来做比如java、python这样 删除事务时长短，可以通过limit限制数量等方式，这样事务的执行时间不长，不至于影响业务。

#67

rs日志的时间段不对，没覆盖到这条trace，麻烦重新拿一下吧。

#68

还有个分析诊断的方法。你看看 show trace 结果。

image
image1245×875 31.4 KB

show trace 使用方法参考官网文档： OceanBase Show Trace

#69

通过limit的方式删除多次，这个方案有几个问题，我们内部讨论过
1）首先不同的客户，需要删除的量不同，会区别很大
2）通过mysql_c API的方式，会有很多时间浪费在接口的IO里。
3）删除前需要知道需要删除的数量，再按照某一个固定的次数，比如删10次删完的话，就需要在得到总量后 除以 10，得到每一次limit的量。意味着删除前我需要先select数量。

疑问：有的客户可能不到1s都能完成truncate操作，为啥这个客户这么慢

#70

不好意思老师，上传错了刚才
rootservice.log.7z (8.2 MB)

#72

我们之前遇到过在分区表建有全局索引的情况下，无论是drop分区还是truncate 分区都会触发全局索引的重建，在数据量比较大、分区数量多的情况下，DDL的操作耗时较高的问题。

首先可以确认的一个点是，drop/truncate partition操作会触发全局索引重建，这个问题暂时无解。强如oracle，也没有针对这个问题进行过彻底有效的优化。

在这个前提下，我们的做法是禁用全局索引，并且设定定时任务在每个月月初新建当月的空分区，每天早晨通过drop partition清理掉一年前的老分区（分区表按日分区，一天6亿左右的记录数，保留一年的数据， 也就是分区数量在400个以内，数据量特别大），DDL耗时基本在秒级，我们运行环境的硬件比较好，物理机部署（96C756G内存）运行的版本有3.1.4-ce 和4.2.1-ce

“分区6200个。同时，表存在3个索引。GLOBLAL属性的Primary Key(主键)，以及两个LOCAL的索引”
不太理解这里的Global 属性的primary key是什么意思，你可以观察观察在执行分区操作的时候是否触发了索引的重建。也可以测试下减少分区数量的情况下 truncate/drop partition 操作的耗时。
同时也建议查看下集群system_memory的配置大小，我有印象原厂的工程师协助我们做参数调优的时候调整过这个参数。

#73

1）您提到的，减少分区数量，可以一试。等到下午客户上班我试一下。
2）好的，system_memory参数我也会关注下。有效果的话给您回复

#74

那这个卡到这里是为啥呢 :sweat_smile:，时间应该都是浪费到这里了。我在obclient里试了一下truncate，发现需要19分钟。truncate一个分区

#75

只要是全局索引truncate分区都会重建，你这带全局索引并且数据量大的表，慢应该是正常，还有，你这慢是随便建一张分区表，truncate都慢还是，

#77

truncate不带分区但是有主键的表，很快。
只要truncate带分区且带主键的，很慢很慢。十几分钟。
我们的数据库里只有两个表有分区。且这两个表的分区数量都是6200

#78

老师，我们的表结构如下
CREATE TABLE THisPosition (

FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,

FSettleDate date NOT NULL COMMENT ‘结算日’,

FOpenDate date NOT NULL DEFAULT ‘0000-00-00’,

FAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FTradeNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FParentAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FExchangeNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FCommodityType char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’,

FCommodityNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FContractNo varchar(16) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FCallOrPutFlag char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FContractSerialNo varchar(7) NOT NULL DEFAULT ‘’ ,

FStrikePrice int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘执行价’,

FMatchSide char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FHedgeFlag char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FPositionPrice decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionQty int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘持仓量’,

FPositionId bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘持仓编号’,

FCombineType char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FPositionCmbId bigint(20) NOT NULL DEFAULT ‘0’ ,

FAccountMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FExchangeMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionZProfit decimal(38,8) NOT NULL DEFAULT ‘0.00000000’,

FPositionDProfit decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FOptionMarketValue decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMaintenanceMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMarginMode char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘1’ ,

FAccountSettleMarginParam decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMaintenanceMarginParam decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMarginRef decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FExchangeMarginRef decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionCmbIdRef bigint(20) NOT NULL DEFAULT ‘0’ ,

FOperatorNo varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,

FSettlePrice decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘结算价’,

PRIMARY KEY (FSettleDate, FAccountNo, FPositionId, FPositionCmbId, FMatchSide, FExchangeNo, FHedgeFlag),

UNIQUE KEY FSerialId (FSerialId, FSettleDate) BLOCK_SIZE 16384 GLOBAL,

KEY FSettleDate (FSettleDate, FAccountNo, FExchangeNo, FCommodityType, FCommodityNo, FContractNo) BLOCK_SIZE 16384 LOCAL

) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘历史持仓表’

PARTITION BY RANGE COLUMNS (FSettleDate)

(

PARTITION D0 VALUES LESS THAN (‘2023-10-10’),

PARTITION D20231010 VALUES LESS THAN (‘2023-10-11’),

PARTITION D20231011 VALUES LESS THAN (‘2023-10-12’),

PARTITION D20231012 VALUES LESS THAN (‘2023-10-13’),

PARTITION D20231013 VALUES LESS THAN (‘2023-10-14’),

PARTITION D20231014 VALUES LESS THAN (‘2023-10-15’)

…//省略不写了

);

#79

老师，给您提供一下我们的表结构
CREATE TABLE THisPosition (

FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,

FSettleDate date NOT NULL COMMENT ‘结算日’,

FOpenDate date NOT NULL DEFAULT ‘0000-00-00’,

FAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FTradeNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FParentAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FExchangeNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’,

FCommodityType char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’,

FCommodityNo varchar(11) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FContractNo varchar(16) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FCallOrPutFlag char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FContractSerialNo varchar(7) NOT NULL DEFAULT ‘’ ,

FStrikePrice int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘执行价’,

FMatchSide char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FHedgeFlag char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FPositionPrice decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionQty int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘持仓量’,

FPositionId bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘持仓编号’,

FCombineType char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ ,

FPositionCmbId bigint(20) NOT NULL DEFAULT ‘0’ ,

FAccountMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FExchangeMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionZProfit decimal(38,8) NOT NULL DEFAULT ‘0.00000000’,

FPositionDProfit decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FOptionMarketValue decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMaintenanceMargin decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMarginMode char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘1’ ,

FAccountSettleMarginParam decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMaintenanceMarginParam decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FAccountMarginRef decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FExchangeMarginRef decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ ,

FPositionCmbIdRef bigint(20) NOT NULL DEFAULT ‘0’ ,

FOperatorNo varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ ,

FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,

FSettlePrice decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘结算价’,

PRIMARY KEY (FSettleDate, FAccountNo, FPositionId, FPositionCmbId, FMatchSide, FExchangeNo, FHedgeFlag),

UNIQUE KEY FSerialId (FSerialId, FSettleDate) BLOCK_SIZE 16384 GLOBAL,

KEY FSettleDate (FSettleDate, FAccountNo, FExchangeNo, FCommodityType, FCommodityNo, FContractNo) BLOCK_SIZE 16384 LOCAL

) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘历史持仓表’

PARTITION BY RANGE COLUMNS (FSettleDate)

(

PARTITION D0 VALUES LESS THAN (‘2023-10-10’),

PARTITION D20231010 VALUES LESS THAN (‘2023-10-11’),

PARTITION D20231011 VALUES LESS THAN (‘2023-10-12’),

PARTITION D20231012 VALUES LESS THAN (‘2023-10-13’),

PARTITION D20231013 VALUES LESS THAN (‘2023-10-14’),

PARTITION D20231014 VALUES LESS THAN (‘2023-10-15’)

…//省略不写了

);

#80

UNIQUE KEY FSerialId (FSerialId , FSettleDate ) BLOCK_SIZE 16384 GLOBAL,这不就是了，你们选择全局索引的基于啥考虑

1 个赞
#81

可能创建的时候，没考虑到GLOBAL和LOCAL会对效率存在影响。我们下午会测试一下，把这个UNIQUE KEY删除掉，是否会有影响。

#82

你把这个Global 的 UNIQUE KEY 去掉，在进行分区清理操作，耗时应该就降下来了

1 个赞
#83

好的，谢谢老师，我联系客户试试

#84

唯一索引有额外的校验，会受主表未结束事务的影响。