OB 针对大表DDL操作如何提升性能

Livedba · 2024 年12 月 6 日 10:16

【使用环境】生产环境 or 测试环境
【 OB or 其他组件】OcenBase 4.2.1.8，集群规模1-1-1
【使用版本】社区版
【问题描述】有一张大表存在很多的分区，进行truncate操作时，CPU使用率会大幅上升并且耗时也很久。OB针对这类大表的truncate操作有没有好的优化方法
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

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

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

论坛小助手 · 2024 年12 月 6 日 10:17

根据文档库中的信息，对于大表的 DDL 操作如 TRUNCATE，OceanBase 数据库确实存在一些优化建议和方法，可以帮助提高性能并减少对系统的影响。以下是具体的优化措施：

调整租户资源单元的 max_cpu：
- 通过增加租户的最大 CPU 使用量，可以为 DDL 操作提供更多资源，从而加快操作速度。
- 示例 SQL 语句：
```
alter resource unit <unit_name> set max_cpu=<new_value>;
```
调大租户 CPU 并发度：
- 通过增加 CPU 并发度，可以提高 DDL 操作的并行处理能力，从而减少总耗时。
- 示例 SQL 语句：
```
alter system set cpu_quota_concurrency=<new_value> tenant = <tenant_name>;
```

减少转储线程数量或降低线程优先级：

转储操作会占用大量 CPU 资源，减少转储线程数量或降低其优先级可以减少对 DDL 操作的影响。

示例 SQL 语句：

alter system set merge_thread_count=<new_value> tenant = <tenant_name>;
alter system set compaction_high_thread_score=<new_value> tenant = <tenant_name>;
alter system set compaction_mid_thread_score=<new_value> tenant = <tenant_name>;
alter system set compaction_low_thread_score=<new_value> tenant = <tenant_name>;

优化表结构和分区策略：
- 如果表的分区过多，可以考虑优化分区策略，减少分区数量，从而减少 DDL 操作的复杂度。
- 例如，可以将细粒度的分区合并为粗粒度的分区。
使用 ALTER TABLE ... TRUNCATE PARTITION 逐个分区进行操作：
- 如果表的分区较多，可以考虑逐个分区进行 TRUNCATE 操作，而不是一次性对整个表进行操作。
- 示例 SQL 语句：
```
ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
```
监控和分析性能瓶颈：
- 使用 GV$OB_SQL_AUDIT 视图监控 DDL 操作的性能，分析是否存在其他性能瓶颈。
- 示例 SQL 语句：
```
SELECT * FROM GV$OB_SQL_AUDIT WHERE SQL_ID = '<sql_id>';
```

通过以上方法，可以有效提升大表 DDL 操作的性能，减少对系统资源的消耗。如果问题仍然存在，建议使用 OceanBase 敏捷诊断工具 obdiag 收集诊断信息，并联系 OceanBase 的技术支持团队获取进一步的帮助。

更多信息请查看:

靖顺 · 2024 年12 月 6 日 11:56

知识库有个case可以看看：https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000514283?back=kb

Livedba · 2024 年12 月 9 日 15:28

这个适用于4.2.2.1版本吗

论坛小助手 · 2024 年12 月 9 日 15:33

根据文档库中的信息，您提到的优化方法主要适用于 OceanBase 4.2.1.8 版本。然而，这些优化方法在 4.2.2.1 版本中也应该适用，因为这些方法主要涉及的是配置参数和 SQL 语句的调整，而不是特定版本的新特性。

为了确保这些方法在 4.2.2.1 版本中同样有效，您可以参考以下几点：

调整租户资源单元的 max_cpu：
- 通过增加租户的最大 CPU 使用量，可以为 DDL 操作提供更多资源，从而加快操作速度。
- 示例 SQL 语句：
```
alter resource unit <unit_name> set max_cpu=<new_value>;
```
调大租户 CPU 并发度：
- 通过增加 CPU 并发度，可以提高 DDL 操作的并行处理能力，从而减少总耗时。
- 示例 SQL 语句：
```
alter system set cpu_quota_concurrency=<new_value> tenant = <tenant_name>;
```

减少转储线程数量或降低线程优先级：

转储操作会占用大量 CPU 资源，减少转储线程数量或降低其优先级可以减少对 DDL 操作的影响。

示例 SQL 语句：

alter system set merge_thread_count=<new_value> tenant = <tenant_name>;
alter system set compaction_high_thread_score=<new_value> tenant = <tenant_name>;
alter system set compaction_mid_thread_score=<new_value> tenant = <tenant_name>;
alter system set compaction_low_thread_score=<new_value> tenant = <tenant_name>;

优化表结构和分区策略：
- 如果表的分区过多，可以考虑优化分区策略，减少分区数量，从而减少 DDL 操作的复杂度。
- 例如，可以将细粒度的分区合并为粗粒度的分区。
使用 ALTER TABLE ... TRUNCATE PARTITION 逐个分区进行操作：
- 如果表的分区较多，可以考虑逐个分区进行 TRUNCATE 操作，而不是一次性对整个表进行操作。
- 示例 SQL 语句：
```
ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
```
监控和分析性能瓶颈：
- 使用 GV$OB_SQL_AUDIT 视图监控 DDL 操作的性能，分析是否存在其他性能瓶颈。
- 示例 SQL 语句：
```
SELECT * FROM GV$OB_SQL_AUDIT WHERE SQL_ID = '<sql_id>';
```

这些方法在 4.2.2.1 版本中应该同样适用。如果您在实际操作中遇到任何问题，建议使用 obdiag 工具收集诊断信息，并联系 OceanBase 的技术支持团队获取进一步的帮助。

如果您需要更详细的版本特定信息，可以查阅官方文档或发布版本的更新日志，以确保没有遗漏任何重要的变更或新特性。

检索到的文档如下:

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

Livedba · 2024 年12 月 9 日 15:46

我调小 schema_history_expire_time 与 schema_history_recycle_interval 参数后，查看truncate table 的执行效率都差不多。版本4.2.2.1 集群规模4-4-4

淇铭 · 2024 年12 月 9 日 16:53

你这个表是分区表么？有全局索引么？

Livedba · 2024 年12 月 9 日 16:55

range按日分区，目前没有全局索引

淇铭 · 2024 年12 月 9 日 17:03

应该是分区比较大删除的时候增删 300+ tablet比较耗时你目前大概多少个分区

Livedba · 2024 年12 月 9 日 17:16

48个分区，租户primary zone 为randdom ，资源规格 3C6G

淇铭 · 2024 年12 月 9 日 17:30

能把表结构的信息发一下我本地测试一下看着分区不是很多呀

Livedba · 2024 年12 月 9 日 17:33

类似于这个

淇铭 · 2024 年12 月 9 日 17:54

你贴一下吧或者改个名字发一下

Livedba · 2024 年12 月 9 日 21:26

就是这种

CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(PARTITION M201001 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/02/01’))
, PARTITION M201002 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/03/01’))
, PARTITION M201003 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/04/01’))
, PARTITION M201004 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/05/01’))
, PARTITION M201005 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/06/01’))
, PARTITION M201006 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/07/01’))
, PARTITION M201007 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/08/01’))
, PARTITION M201008 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/09/01’))
, PARTITION M201009 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/10/01’))
, PARTITION M201010 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/11/01’))
, PARTITION M201011 VALUES LESS THAN(UNIX_TIMESTAMP(‘2020/12/01’))
, PARTITION M201012 VALUES LESS THAN(UNIX_TIMESTAMP(‘2021/01/01’))

   ,PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2021/02/01'))
   , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2021/03/01'))
   , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01'))
   , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2021/05/01'))
   , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2021/06/01'))
   , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01'))
   , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2021/08/01'))
   , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2021/09/01'))
   , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01'))
   , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2021/11/01'))
   , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2021/12/01'))
   , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
   
   ,PARTITION M203001 VALUES LESS THAN(UNIX_TIMESTAMP('2022/02/01'))
   , PARTITION M203002 VALUES LESS THAN(UNIX_TIMESTAMP('2022/03/01'))
   , PARTITION M203003 VALUES LESS THAN(UNIX_TIMESTAMP('2022/04/01'))
   , PARTITION M203004 VALUES LESS THAN(UNIX_TIMESTAMP('2022/05/01'))
   , PARTITION M203005 VALUES LESS THAN(UNIX_TIMESTAMP('2022/06/01'))
   , PARTITION M203006 VALUES LESS THAN(UNIX_TIMESTAMP('2022/07/01'))
   , PARTITION M203007 VALUES LESS THAN(UNIX_TIMESTAMP('2022/08/01'))
   , PARTITION M203008 VALUES LESS THAN(UNIX_TIMESTAMP('2022/09/01'))
   , PARTITION M203009 VALUES LESS THAN(UNIX_TIMESTAMP('2022/10/01'))
   , PARTITION M203010 VALUES LESS THAN(UNIX_TIMESTAMP('2022/11/01'))
   , PARTITION M203011 VALUES LESS THAN(UNIX_TIMESTAMP('2022/12/01'))
   , PARTITION M203012 VALUES LESS THAN(UNIX_TIMESTAMP('2023/01/01'))
   
   ,PARTITION M204001 VALUES LESS THAN(UNIX_TIMESTAMP('2023/02/01'))
   , PARTITION M204002 VALUES LESS THAN(UNIX_TIMESTAMP('2023/03/01'))
   , PARTITION M204003 VALUES LESS THAN(UNIX_TIMESTAMP('2023/04/01'))
   , PARTITION M204004 VALUES LESS THAN(UNIX_TIMESTAMP('2023/05/01'))
   , PARTITION M204005 VALUES LESS THAN(UNIX_TIMESTAMP('2023/06/01'))
   , PARTITION M204006 VALUES LESS THAN(UNIX_TIMESTAMP('2023/07/01'))
   , PARTITION M204007 VALUES LESS THAN(UNIX_TIMESTAMP('2023/08/01'))
   , PARTITION M204008 VALUES LESS THAN(UNIX_TIMESTAMP('2023/09/01'))
   , PARTITION M204009 VALUES LESS THAN(UNIX_TIMESTAMP('2023/10/01'))
   , PARTITION M204010 VALUES LESS THAN(UNIX_TIMESTAMP('2023/11/01'))
   , PARTITION M204011 VALUES LESS THAN(UNIX_TIMESTAMP('2023/12/01'))
   , PARTITION M204012 VALUES LESS THAN(UNIX_TIMESTAMP('2024/01/01'))
   
    );

淇铭 · 2024 年12 月 10 日 17:43

好的我们测试一下

淇铭 · 2024 年12 月 10 日 17:49

我测试执行的很快

淇铭 · 2024 年12 月 10 日 18:03

用show trace查看一下时间在哪里消耗的
步骤：
1.开启SQL Trace功能：
obclient> SET ob_enable_show_trace = 1;

故障重现，也即执行truncate partition操作。
运行show trace; 命令

OB 针对大表DDL操作 如何提升性能

OB 针对大表DDL操作如何提升性能