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

[AntTech_5N9HRW]

【 使用环境 】生产环境
【 OB or 其他组件 】OceabBase_CE 4.2.1.2; Obdiag 2.3.0
【 使用版本 】OceabBase_CE 4.2.1.2; Obdiag 2.3.0
【问题描述】客户机器：32核心 32G内存；分配给Observer租户用26C（max_cpu的值为26C，最低为3C。内存分了24G。我们有一个表THisPosition，建立有分区6200个。同时，表存在3个索引。GLOBLAL属性的Primary Key(主键)，以及两个LOCAL的索引。当初建立分区的目的是，直接delete某一天的数据非常耗时，有时候会超时报错。为了减少删除数据的时间，我们在表中增加了分区，按照日期建立分区。现在发现客户的机器上，truncate分区非常耗时，一张表15min左右。为了检查问题，我们做了如下的努力
1）加大给OceanBase的cpu核数和内存数量，发现分配到8C数量之后得不到时间四的优化。优化后的结果为一张表15min
2）对客户的机器做巡检，通过obdiag检查。检查结果见附件。我们尝试根据检查结果做系统内核参数的调整，首先调整了ulimit -s -u -h三个参数。确认修改到之后，重新运行sql，发现时间还是很久。
诉求：希望得到OB的重视，这个问题困扰了我们很久得不到解决。
疑点：有的客户很快，有的客户很慢。truncate分区耗时目前测出来跟数据量还没有关系，空表也会很耗时。
3）truncate带上UPDATE GLOBAL INDEXES无效，不支持这个命令。这个命令只支持到3版本的，我们是4版本的ob
4）确认执行我们的任务时，没有其他的DML任务
5）如果问题无法得到解决，是否可以提出另外一种方案，能让我们解决“delete数据”耗时的问题得到解决。
6）之前已经问了一个帖子，可以参考之前的结果再看看。Obdiag
obdiag_check_report_observer_2024-08-14-19-31-04.zip (1.7 KB)

【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

[论坛小助手]

再向您确认几个问题:

1. 在执行truncate操作时是否有其他类型的SQL操作正在运行
2. 能否提供更多关于THisPosition表的数据量信息例如每张表大概有多少条记录
3. 您提到有的客户很快有的客户很慢这里的客户是指不同的租户还是指同一租户下不同的实例
4. 您能否提供执行truncate操作的具体SQL语句

[AntTech_5N9HRW]

@论坛小助手

• 在执行truncate操作时是否有其他类型的SQL操作正在运行
• 能否提供更多关于THisPosition表的数据量信息例如每张表大概有多少条记录
• 您提到有的客户很快有的客户很慢这里的客户是指不同的租户还是指同一租户下不同的实例
• 您能否提供执行truncate操作的具体SQL语句

1）无其它类型的sql在操纵，因为所有sql任务都是顺序执行的
2）当前测试的分区，D20240813。分区内一共有87847条数据
3）不同的租户，也就是说不同的机器
4）ALTER TABLET HisPosition Truncate PARTITION D20240813

[论坛小助手]

再向您确认几个问题:

1. 在执行truncate操作时系统的CPU和内存利用率情况如何
2. 您是否观察到了网络延迟或者磁盘I/O方面的问题
3. 对于那些处理速度较快的租户他们的配置或者操作习惯与处理速度较慢的租户有何不同
4. 您能否提供在执行truncate操作期间的OBServer的日志信息

[AntTech_MO9IXW]

针对你这个truncate分区慢的问题：

需要弄清楚这15min都花在哪些地方了？可以尝试用show trace找找答案，SQL Trace能够交互式的提供上一次执行的SQL请求执行过程信息及各阶段的耗时。 找到问题的原因，才好解决问题。

步骤：
1.开启SQL Trace功能：
obclient> SET ob_enable_show_trace = 1;

2. 故障重现，也即执行truncate partition操作。

3. 运行show trace; 命令

[AntTech_5N9HRW]

1）CPU占用高达 150%左右 mem占用在15%左右
2）没有异常
3）操作上没有区别
4）如何提供日志

[论坛小助手]

根据您提供的信息，我们可以尝试分析一下问题的原因：

1. CPU占用率高：您提到在执行TRUNCATE操作时，CPU占用率高达150%左右，这表明在执行TRUNCATE操作时，系统确实处于较高的负载状态。需要注意的是，OceanBase的多核并行处理能力可能会导致CPU占用率超过100%，但这并不一定意味着存在问题。不过，如此高的CPU占用率可能会影响TRUNCATE操作的性能。

2. 内存占用低：您提到内存占用仅为15%左右，这表明内存资源相对充足，并不太可能是导致TRUNCATE操作耗时的主要原因。

3. 网络和磁盘I/O正常：您提到没有观察到网络延迟或磁盘I/O方面的问题，这意味着网络和磁盘I/O不太可能是导致问题的原因。

4. 不同租户间的差异：您提到不同租户间在操作上没有区别，但处理速度却有明显差异。这可能意味着某些硬件配置或系统设置上的差异影响了TRUNCATE操作的性能。

5. 日志信息：您询问如何提供日志。通常可以通过obdiag工具来收集日志信息。您可以使用obdiag collect命令来收集相关日志，然后使用obdiag upload命令将这些日志上传至OceanBase的支持团队进行进一步分析。

建议

1. 检查系统配置：确保您的系统配置（如resource_hard_limit等）适合您的工作负载。例如，您提到的CPU配置为26C，最低为3C，这表明您已经预留了一定的资源余量。但是，如果resource_hard_limit设置不当，可能会影响到资源的分配。

2. 查看日志：通过obdiag工具收集并上传日志，以便进一步分析。特别是关注TRUNCATE操作期间的系统日志和错误日志，看是否存在异常信息。

3. 性能监控：持续监控系统性能指标，包括CPU、内存、磁盘I/O等，以确定是否有瓶颈存在。

4. 联系支持团队：如果问题仍然无法解决，建议联系OceanBase的技术支持团队，他们可以提供更深入的帮助和技术支持。

综上所述，尽管目前的信息不足以完全解决问题，但上述建议可以帮助您进一步排查问题。如果您能够提供更多的日志信息，我们将能够更好地帮助您定位问题。

具体信息可参考以下文档:

• 通过调整资源规格实现租户扩缩容

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

[淇铭]

你按照五楼说的 提供一下 trace信息

[Giant]

@AntTech_5N9HRW

Truncate

这么用能满足您的需求不

您的有两个疑问

1. 6000多个分区，说明你们的 空分区特别多。 可能有10年了吧， 一年按照日才365个
2. 数据都truncate了 说明分区可以drop
3. 感觉疑点较大的是 空分区特别多，可以尝试删除超过一年的空分区看看，
   4,。 分区数据量仅仅 8W多， 即使是单表的mysql 也应该特别快。

[obpilot]

发一下 sys租户的资源规格。
业务租户的 DDL 本质上是 sys租户的 事务（包含多个DML）。 sys租户的规格不要太小，另外，sys租户的primary_zone 设置为一个具体的 zone（不要选所有zone或random）。

看看有没有改善？

[Giant]

再说一个关于truncate 表较慢的案例

环境：polardb-x 1.0
即使是单表也比较慢，原因是：poalrdb-x 的处理逻辑问题， 即使是单表 也会再各库创建一个空表，目的是为了兼容 广播表的特性，我们这truncate一个单表 相当于执行 400次truncate 空表操作 ，

我还是怀疑您的空分区表太多导致

truncate分区 后，还有创建一个新的空分区，还需要维护分区表的逻辑，可以尝试drop操作看看

[zmix]

可以看下这个

Truncate Table 的性能问题-OceanBase知识库

[AntTech_5N9HRW]

1）我们的历史数据都是需要保存的，都是客户的明细，不可以删除
2）truncate的是业务当天的数据，之前的都是不可以删除的
3）按照我们现在的业务逻辑，分区数量只会越来越多
4）单表删除的很慢，按照之前测试的场景，delete数据会超时，所以才采取了当前的方案

疑问：
1）如果分区数量越来越大，是不是会越来越慢

[AntTech_5N9HRW]

将尝试帖子的解决方式“通过调小以下参数来控制 schema 历史信息的回收”。观测是否有效果

• schema_history_expire_time
• schema_history_recycle_interval

[AntTech_5N9HRW]

老师，图片是客户方提供的，不是很清楚。客户本身是32C 32G内存的机器，分给observer的是26C了。24G内存。您可以看一下

sys租户3个C
dstarv10租户26个C，平常客户用dstarv10多一些。
按照您说的意思，是不是sys的C少导致的？但是dstarv10是客户主要用的。这样的情况下您这边建议怎么分配C

2）"CREATE TENANT IF NOT EXISTS dstar PRIMARY_ZONE=‘zone1’,RESOURCE_POOL_LIST=(‘user_pool’) SET OB_TCP_INVITED_NODES=’%’,LOWER_CASE_TABLE_NAMES = 0;
老师我们创建分区的语句如上，您帮忙看下有没有问题

[AntTech_5N9HRW]

老师，有以下几点向您补充说明
1）我们的分区设计逻辑是按照日期创建的分区，一天一个分区
2）我们的分区会越来越多，因为客户的历史数据是不能删除的
3）我刚才尝试drop分区之后，再add回来，发现add不回来报错
add语句：ALTER TABLE THisPosition ADD PARTITION D20231011 VALUES LESS THAN (“2023-10-11”);

drop语句执行成功，分区D20231011原来是已经创建好的，他的后边原来就是D20231012

drop语句：ALTER TABLE THisPosition DROPPARTITION D20231011

[AntTech_5N9HRW]

好的老师，下午客户运维才上班，下午提供给您。

[淇铭]

我看你的另一个帖子说的 是有一个全局索引和两个本地索引
1、分区表包含全局索引，那么删除分区后，OceanBase 数据库的 MySQL 模式下会自动重建该全局索引。即使你删除了一个没有数据的分区，如果整个表数据量很大，重建全局索引是非常耗时的，整个 DDL 的耗时可能会超过预期，甚至超时。

2、你可以尝试一下 调整这两个参数

• schema_history_expire_time
• schema_history_recycle_interval

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000514283?back=kb

3、你可以用obdiag 收集执行的信息 贴一下 分析分析
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
obdiag文档
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001102504

[AntTech_5N9HRW]

老师，我在数据库里查schema_history_expire_time和schema_history_recycle_interval，这两个变量，查不到都

[淇铭]

这两个参数都是集群级配置项 查询的方式show parameters like ‘schema_history_expire_time’;