obdiag DDL时报磁盘不足，官网给出的sql无法执行

建议与反馈 产品缺陷反馈
#1

image
image1259×127 67.1 KB

使用的config配置了sys租户密码，想查询下某个oracle租户下面ddl需要使用多少磁盘，根据官网的执行命令报错，麻烦帮忙看下

1 个赞
#2

额外的参数是–env不是-env

1 个赞
#3

文档中给出的示例存在问题，

  1. 文档中的–env 写成了-env
  2. database_name 为额外新加的参数，为了规避不同库下同名表新加，示例中未加

应该为如下

 obdiag rca run --scene=ddl_disk_full --env tenant_name=test1 --env table_name=t555 --env action_type=add_index --env index_name=k1 --env database_name=xxxx
2 个赞
#4

5fdb40ddea57927d9197462e44f8cdf
5fdb40ddea57927d9197462e44f8cdf897×76 34 KB

想查询的是oracle租户hxdb下V7CORE用户下面表的索引，还是报错

1 个赞
#5

报错是在对应的库里没有找到对应的表，要不这次我直接帮你拼接吧。

可以把 租户名、库名、表名、索引名 发出来么，图片里看不太清

obdiag rca run --scene=ddl_disk_full \
--env tenant_name={租户名} \
--env database_name={库名} \
--env table_name={表名} \
--env index_name={索引名}
1 个赞
#7

[root@ob1 ~]# obdiag rca run --scene=ddl_disk_full --env tenant_name=hxdb --env table_name=KDPL_ZHMINX --env action_type=add_index --env index_name=KDPL_ZHMINX_IDX7 --env database_name=V7CORE
[ERROR] rca run Exception: rca_scene.init err: can not find database id by database name: V7CORE. Please check the table name.
Trace ID: b45e64f4-edc8-11ef-bbc2-9cc2c43bc151
If you want to view detailed obdiag logs, please run: obdiag display-trace b45e64f4-edc8-11ef-bbc2-9cc2c43bc151 还是报错

1 个赞
#9

[root@ob1 ~]# obdiag display scene run --scene=observer.table_datasize --env tenant_id=1002 --env database_name=V7CORE --env table_name=KDPL_ZHMINX
display_scenes_run start …
execute tasks: observer.table_datasize
±----------±------------±--------------±------------±---------±---------------+
| TENANT_ID | tenant_name | DATABASE_NAME | TABLE_NAME | TABLE_ID | data_size_in_B |
±----------±------------±--------------±------------±---------±---------------+
| 1002 | hxdb | V7CORE | KDPL_ZHMINX | 943165 | 73037746440 |
±----------±------------±--------------±------------±---------±---------------+
Trace ID: 791c8b5e-edc9-11ef-a39f-9cc2c43bc151
If you want to view detailed obdiag logs, please run: obdiag display-trace 791c8b5e-edc9-11ef-a39f-9cc2c43bc151

1 个赞
#10

查询这张表大小可以，但是查询ddl不行

1 个赞
#11

obdiag设计时主要面向了开源版本mysq租户，可能是这块的兼容存在问题，可以在rca指令后加上 -v 来打印详细的日志，看下是哪里存在问题么？

1 个赞
#12

[root@ob1 ~]# obdiag rca run --scene=ddl_disk_full --env tenant_name=hxdb --env table_name=KDPL_ZHMINX --env action_type=add_index --env index_name=KDPL_ZHMINX_IDX7 --env database_name=V7CORE -v

  • cmd: obdiag rca run
  • opts: {‘inner_config’: None, ‘scene’: ‘ddl_disk_full’, ‘store_dir’: ‘./obdiag_rca/’, ‘env’: None, ‘report_type’: ‘table’, ‘c’: ‘/root/.obdiag/config.yml’, ‘config’: None}
  • mkdir /usr/local/oceanbase-diagnostic-tool/conf/inner_config.yml
  • mkdir /root/.obdiag/config.yml
  • connect databse …
  • RCAHandler.init store dir: ./obdiag_rca/
  • rca result save_path is :./obdiag_rca/
  • get observer version, by sql
  • start get_observer_version_by_sql . input: 10.25.129.75:2881
  • connect databse …
  • get_observer_version_by_sql ob_version_info is (‘5.7.25-OceanBase-v4.2.1.7’,)
  • RCAHandler.init get observer version: 4.2.1.7
  • RCAHandler.init get observer version: 4.2.1.7
  • Execute Shell command on server 10.25.129.226:/opt/taobao/install/obproxy-4.2.1.0/bin/obproxy --version
  • get obproxy version, run cmd = [/opt/taobao/install/obproxy-4.2.1.0/bin/obproxy --version]
  • RCAHandler.init get obproxy version: 4.2.1.0
  • RCAHandler init.cluster:obcluster, init.nodes:[{‘ip’: ‘10.25.129.226’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/home/admin/oceanbase’, ‘data_dir’: ‘/home/admin/oceanbase/store’, ‘redo_dir’: ‘/home/admin/oceanbase/store’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: ‘’, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBSERVER’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8e8f2340>}, {‘ip’: ‘10.25.129.75’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/home/admin/oceanbase’, ‘data_dir’: ‘/home/admin/oceanbase/store’, ‘redo_dir’: ‘/home/admin/oceanbase/store’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: ‘’, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBSERVER’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8e8e83a0>}, {‘ip’: ‘10.25.129.76’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/home/admin/oceanbase’, ‘data_dir’: ‘/home/admin/oceanbase/store’, ‘redo_dir’: ‘/home/admin/oceanbase/store’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: ‘’, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBSERVER’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8e8e8a90>}], init.obproxy_nodes:[{‘ip’: ‘10.25.129.226’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/opt/taobao/install/obproxy-4.2.1.0’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: None, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBPROXY’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8e8e8970>}, {‘ip’: ‘10.25.129.75’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/opt/taobao/install/obproxy-4.2.1.0’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: None, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBPROXY’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8d43f9a0>}, {‘ip’: ‘10.25.129.76’, ‘ssh_username’: ‘root’, ‘ssh_port’: ‘22’, ‘home_path’: ‘/opt/taobao/install/obproxy-4.2.1.0’, ‘ssh_key_file’: ‘’, ‘ssh_type’: ‘remote’, ‘container_name’: None, ‘namespace’: ‘’, ‘pod_name’: ‘’, ‘kubernetes_config_file’: ‘’, ‘host_type’: ‘OBPROXY’, ‘ssher’: <src.common.ssh_client.ssh.SshClient object at 0x7f0c8d43f4f0>}], init.store_dir:./obdiag_rca/
  • ddl_disk_full store_dir:./obdiag_rca/obdiag_ddl_disk_full_20250218153414
  • get observer version, by sql
  • start get_observer_version_by_sql . input: 10.25.129.75:2881
  • connect databse …
  • get_observer_version_by_sql ob_version_info is (‘5.7.25-OceanBase-v4.2.1.7’,)
  • [DDlDiskFullScene] observer version is 4.2.1.7.
  • [DDlDiskFullScene] action type is add_index.
  • [DDlDiskFullScene] index name is KDPL_ZHMINX_IDX7.
  • [DDlDiskFullScene] tenant_id is 1002
    [ERROR] rca run Exception: rca_scene.init err: can not find database id by database name: V7CORE. Please check the table name.
  • Traceback (most recent call last):
    File “src/handler/rca/rca_handler.py”, line 173, in handle
    File “/root/.obdiag/rca/ddl_disk_full.py”, line 94, in init
    raise RCAInitException(“can not find database id by database name: {0}. Please check the table name.”.format(database_name))
    src.handler.rca.rca_exception.RCAInitException: can not find database id by database name: V7CORE. Please check the table name.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “src/common/core.py”, line 518, in rca_run
File “src/handler/rca/rca_handler.py”, line 176, in handle
Exception: rca_scene.init err: can not find database id by database name: V7CORE. Please check the table name.

Trace ID: c03cc57a-edca-11ef-b924-9cc2c43bc151
If you want to view detailed obdiag logs, please run: obdiag display-trace c03cc57a-edca-11ef-b924-9cc2c43bc151

1 个赞
#13

看起来是弄巧成拙了，特意加了一个database的防御，但是没有用上，反而使oracle租户无法实现根因分析

可以先使用 obdiag analyze index_space 指令实现空间所需分析，然后再对照目前节点剩余的空间分析哪台节点需要扩容

$ obdiag analyze index_space --tenant_name=hxdb --table_name=KDPL_ZHMINX --column_names={所涉及到的列名}

使用文档

https://www.oceanbase.com/docs/common-obdiag-cn-1000000002200473

1 个赞
#14

这个查询出来的estimated_index不是膨胀的5.5倍的值把

#15

1、这是通过obdiag查看表的大小
[2025-02-18 15:25:04.830] [DEBUG] - StepSQLHandler execute: SELECT /*+ query_timeout(30000000) */ a.TENANT_ID, c.tenant_name, a.DATABASE_NAME, a.TABLE_NAME, a.TABLE_ID, SUM(CASE WHEN b.nested_offset = 0 THEN IFNULL(b.data_block_count + b.index_block_count + b.linked_block_count, 0) * 2 * 1024 * 1024 ELSE IFNULL(b.size, 0) END ) AS data_size_in_B FROM oceanbase.CDB_OB_TABLE_LOCATIONS a INNER JOIN oceanbase.__all_virtual_table_mgr b ON a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port AND a.tenant_id = b.tenant_id AND a.LS_ID = b.LS_ID AND a.TABLET_ID = b.TABLET_ID inner join oceanbase.all_tenant c on a.tenant_id=c.tenant_id WHERE a.role = ‘LEADER’ AND c.tenant_id = 1002 AND b.table_type >= 10 AND b.size > 0 AND a.TABLE_NAME NOT REGEXP '^’ and a.database_name=‘V7CORE’ and a.TABLE_NAME=‘KDPL_ZHMINX’ GROUP BY a.TABLE_ID;
[2025-02-18 15:25:25.818] [INFO] ±----------±------------±--------------±------------±---------±---------------+
[2025-02-18 15:25:25.818] [INFO] | TENANT_ID | tenant_name | DATABASE_NAME | TABLE_NAME | TABLE_ID | data_size_in_B |
[2025-02-18 15:25:25.818] [INFO] ±----------±------------±--------------±------------±---------±---------------+
[2025-02-18 15:25:25.818] [INFO] | 1002 | hxdb | V7CORE | KDPL_ZHMINX | 943165 | 73037746440 |
[2025-02-18 15:25:25.818] [INFO] ±----------±------------±--------------±------------±---------±---------------+

2、这是通过你之前博客里面发的sql查询的表大小
obclient [oceanbase]> select svr_ip, svr_port, sum(original_size) as estimated_data_size from __all_virtual_tablet_sstable_macro_info where tablet_id in (select tablet_id from __all_virtual_tablet_to_table_history where table_id = 943165) and (svr_ip, svr_port) in (select svr_ip, svr_port from __all_virtual_ls_meta_table where role = 1) group by svr_ip, svr_port;
±--------------±---------±--------------------+
| svr_ip | svr_port | estimated_data_size |
±--------------±---------±--------------------+
| 10.25.129.75 | 2882 | 573357105915 |
| 10.25.129.226 | 2882 | 573357107798 |
| 10.25.129.76 | 2882 | 573357099981 |
±--------------±---------±--------------------+
3 rows in set (35.013 sec)

想问下这两种查询结果有什么区别吗

#16

从上述执行时获取到的日志，分析获得你的集群版本是4.2.1.7，在这个版本上是5.5倍的膨胀

#17
  • 第一个 SQL:
    • 目标是统计表的数据大小（以字节为单位），基于 CDB_OB_TABLE_LOCATIONS 和 __all_virtual_table_mgr 表。
    • 主要关注的是表的存储块计数（如 data_block_count、index_block_count 等）或直接使用 size 字段来计算数据大小。
  • 第二个 SQL:
    • 目标是估算索引创建过程中使用的临时空间大小，基于 __all_virtual_tablet_sstable_macro_info 和 __all_virtual_ls_meta_table 表。
    • 主要关注的是主表的原始数据量（original_size），并结合 Leader 分布信息进行汇总。
    • 另外，由于依赖__all_virtual_tablet_sstable_macro_info 来进行查询，所以只确定sstable里的数据，这和上次合并存在关联，若上次合并后数据量不大，但后续存在大量变更且未及时合并，可能存在计算上的误差，建议创建索引前进行一次合并（但是实际生产中根据经验影响大不），使计算数据更加准确。

关于oracle租户database_name字段的兼容方式，obdiag3.2.0将会进行对应优化，商业版用户也可以联系运维sa获取支持帮助

1 个赞
#18

那如果要自己手动计算创建索引膨胀的大小，就按照第二个sql计算是吧，主要是这两个值查的有点多，想确认下

#19

是的，建索引的时候查出来的数据占用量是未压缩的数据（原数据），而使用observer.table_datasize这个查出来的值是压缩后的，两者存在区别是正常的。

1 个赞