集群诊断 - 安装部署工具 OBD V4.0.0

本文介绍如何使用 obd 命令对 OceanBase 集群进行信息收集和诊断。

obd 集成了 OceanBase Diagnostic Tool（简称: obdiag）工具，obdiag 现有功能包含了对 OceanBase 数据库日志、SQL Audit 以及 OceanBase 数据库进程堆栈等信息的扫描收集，支持通过 obd 实现诊断信息的一键采集。详细 obdiag 信息可参考 obdiag GitHub 仓库。

说明

使用 obd 命令进行信息收集和诊断时，进行信息收集和诊断的 OceanBase 集群需是被 obd 管理的集群，否则 obd 无法获取集群的信息。obd 部署的集群默认由 obd 管理，非 obd 部署的集群可执行接管操作进行接管。接管的详细操作可参见使用 obd 接管集群。
本文操作中所用命令的详细介绍可参见官网 OceanBase 敏捷诊断工具（obdiag）文档。

前提条件

参考本文使用 obdiag 命令前，您需先执行 obd tool install obdiag 命令安装 obdiag。

说明

不同 obd obdiag 命令对 OceanBase 数据库和 ODP 版本要求不同，若版本不支持，使用过程中会输出日志提示。

使用示例

收集诊断信息

您可通过 obd obdiag gather 命令收集 OceanBase 集群的相关信息，命令的详细介绍可参见官网 OceanBase 敏捷诊断工具（obdiag）文档 一键信息收集 章节。

此处以收集 OceanBase 集群的日志为例：

obd obdiag gather log obtest --scope observer --from "2024-08-13 16:25:00" --to "2024-08-13 18:30:00" --grep STORAGE

输出如下：

ZipFileInfo:
+--------+-----------+
| Node   | LogSize   |
+========+===========+
| local  | 47.374M   |
+--------+-----------+
Gather Ob Log Summary:
+------------+-----------+---------+--------+------------------------------------------------------------------------------------+
| Node       | Status    | Size    | Time   | PackPath                                                                           |
+============+===========+=========+========+====================================================================================+
| 10.10.10.1 | Completed | 47.374M | 6 s    | ./obdiag_gather_pack_20240814141437/ob_log_local_20240813162500_20240813183000.zip |
+------------+-----------+---------+--------+------------------------------------------------------------------------------------+
Trace ID: 7b9417f2-5a04-11ef-823c-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace 7b9417f2-5a04-11ef-823c-00163e0808cc

Trace ID: 79ad87c0-5a04-11ef-b74a-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace 79ad87c0-5a04-11ef-b74a-00163e0808cc

日志分析

您可参考本节内容使用 obdiag 分析 OceanBase 数据库日志或 trace 日志。

分析 OceanBase 数据库日志

分析全链路诊断日志

在线模式下可执行如下命令：

obd obdiag analyze log obtest --from "2024-08-13 16:25:00" --to "2024-08-13 18:30:00"

离线模式下可执行如下命令，通过 --files 指定 OceanBase 数据库日志路径：

obd obdiag analyze log obtest --files observer/log/

输出如下，您可复制执行输出中的 cat 命令或 obdiag display-trace 命令查看详细记录。

+----------------+-----------+------------------------------------------------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+---------+
| Node           | Status    | FileName                                                                     |   ErrorCode | Message                                                                                                                       |   Count |
+================+===========+==============================================================================+=============+===============================================================================================================================+=========+
| xx.xx.xx.xx    | Completed | obdiag_analyze_pack_20240814143145/xx_xx_xx_xx/observer.log.20240814104204260       |       -5006 | You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use |       2 |
+----------------+-----------+------------------------------------------------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+---------+
| xx.xx.xx.xx    | Completed | obdiag_analyze_pack_20240814143145/xx_xx_xx_xx/observer.log.20240814111305072       |       -5006 | You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use |       8 |
+----------------+-----------+------------------------------------------------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+---------+
| xx.xx.xx.xx    | Completed | obdiag_analyze_pack_20240814143145/xx_xx_xx_xx/observer.log.20240814114410668       |       -5006 | You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use |      10 |
+----------------+-----------+------------------------------------------------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+---------+
| xx.xx.xx.xx    | Completed | obdiag_analyze_pack_20240814143145/xx_xx_xx_xx/observer.log.20240814114410668       |       -4009 | IO error                                                                                                                      |      20 |
+----------------+-----------+------------------------------------------------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+---------+
For more details, please run cmd ' cat /home/admin/obdiag_analyze_pack_20240814143145/result_details.txt '
Trace ID: e0809166-5a06-11ef-84bb-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace e0809166-5a06-11ef-84bb-00163e0808cc

Trace ID: de972874-5a06-11ef-8502-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace de972874-5a06-11ef-8502-00163e0808cc

获取 FLT_TRACE_ID

如果有明确的 SQL 语句可以通过 QUERY_SQL 查到疑似慢 SQL 的 FLT_TRACE_ID，登录 OceanBase 数据库后执行如下命令查看 FLT_TRACE_ID。

select QUERY_SQL,FLT_TRACE_ID from oceanbase.GV$OB_SQL_AUDIT where QUERY_SQL like 'select @@version_comment limit 1';

输出如下，FLT_TRACE_ID 为 00060aa3-d607-f5f2-328b-388e17f687cb。

+----------------------------------+--------------------------------------+
| QUERY_SQL                        | FLT_TRACE_ID                         |
+----------------------------------+--------------------------------------+
| select @@version_comment limit 1 | 00060aa3-d607-f5f2-328b-388e17f687cb |
+----------------------------------+--------------------------------------+

你也可从 ODP、OceanBase 数据库的 trace.log 日志中找到 flt_trace_id，示例如下，其中 00060bec-275e-9832-e730-7c129f2182ac 就是其 flt_trace_id。

head trace.log

[2023-12-07 22:20:07.242229] [489640][T1_L0_G0][T1][YF2A0BA2DA7E-00060BEC28627BEF-0-0] {"trace_id":"00060bec-275e-9832-e730-7c129f2182ac","name":"close_das_task","id":"00060bec-2a20-bf9e-56c9-724cb467f859","start_ts":1701958807240606,"end_ts":1701958807240607,"parent_id":"00060bec-2a20-bb5f-e03a-5da01aa3308b","is_follow":false}

执行诊断

obd obdiag analyze flt_trace obtest --flt_trace_id 000605b1-28bb-c15f-8ba0-1206bcc08aa3

输出如下，您可复制执行输出中的 cat 命令或 obdiag display-trace 命令查看详细记录。

TOP time-consuming leaf span:
+---+----------------------------------+-------------+---------------------+
| ID| Span Name                        | Elapsed Time|      NODE           |
+---+----------------------------------+-------------+---------------------+
| 18| px_task                          | 2.758 ms    | OBSERVER(xx.xx.xx.1)|
| 5 | pc_get_plan                      | 52 μs       | OBSERVER(xx.xx.xx.1)|
| 16| do_local_das_task                | 45 μs       | OBSERVER(xx.xx.xx.1)|     
| 10| do_local_das_task                | 17 μs       | OBSERVER(xx.xx.xx.1)|
| 17| close_das_task                   | 14 μs       | OBSERVER(xx.xx.xx.1)|     
+---+----------------------------------+-------------+---------------------+
Tags & Logs:
-------------------------------------
18 - px_task  Elapsed: 2.758 ms 
     NODE:OBSERVER(xx.xx.xx.1)
     tags: [{'group_id': 0}, {'qc_id': 1}, {'sqc_id': 0}, {'dfo_id': 1}, {'task_id': 1}]
5 - pc_get_plan  Elapsed: 52 μs 
    NODE:OBSERVER(xx.xx.xx.1)
16 - do_local_das_task  Elapsed: 45 μs 
     NODE:OBSERVER(xx.xx.xx.3)
10 - do_local_das_task  Elapsed: 17 μs 
     NODE:OBSERVER(xx.xx.xx.1)
17 - close_das_task  Elapsed: 14 μs 
     NODE:OBSERVER(xx.xx.xx.3)

Details:
+---+----------------------------------+-------------+---------------------+
| ID| Span Name                        | Elapsed Time|  NODE               |
+---+----------------------------------+-------------+---------------------+
| 1 | TRACE                            | -           | -                   |
| 2 | └─com_query_process              | 5.351 ms    | OBPROXY(xx.xx.xx.1) |
| 3 |   └─mpquery_single_stmt          | 5.333 ms    | OBSERVER(xx.xx.xx.1)|
| 4 |     ├─sql_compile                | 107 μs      | OBSERVER(xx.xx.xx.1)|
| 5 |     │ └─pc_get_plan              | 52 μs       | OBSERVER(xx.xx.xx.1)|
| 6 |     └─sql_execute                | 5.147 ms    | OBSERVER(xx.xx.xx.1)|
| 7 |       ├─open                     | 87 μs       | OBSERVER(xx.xx.xx.1)|
| 8 |       ├─response_result          | 4.945 ms    | OBSERVER(xx.xx.xx.1)|
| 9 |       │ ├─px_schedule            | 2.465 ms    | OBSERVER(xx.xx.xx.1)|
| 10|       │ │ ├─do_local_das_task    | 17 μs       | OBSERVER(xx.xx.xx.1)|
| 11|       │ │ ├─px_task              | 2.339 ms    | OBSERVER(xx.xx.xx.2)|
| 12|       │ │ │ ├─do_local_das_task  | 54 μs       | OBSERVER(xx.xx.xx.2)|
| 13|       │ │ │ └─close_das_task     | 22 μs       | OBSERVER(xx.xx.xx.2)|
| 14|       │ │ ├─do_local_das_task    | 11 μs       | OBSERVER(xx.xx.xx.1)|
| 15|       │ │ ├─px_task              | 2.834 ms    | OBSERVER(xx.xx.xx.3)|
| 16|       │ │ │ ├─do_local_das_task  | 45 μs       | OBSERVER(xx.xx.xx.3)|
| 17|       │ │ │ └─close_das_task     | 14 μs       | OBSERVER(xx.xx.xx.3)|
| 18|       │ │ └─px_task              | 2.758 ms    | OBSERVER(xx.xx.xx.1)|
| 19|       │ ├─px_schedule            | 1 μs        | OBSERVER(xx.xx.xx.1)|
| 20|       │ └─px_schedule            | 1 μs        | OBSERVER(xx.xx.xx.1)|
| ..|       ......                     | ...         |  ......             |
+---+----------------------------------+-------------+---------------------+

For more details, please run cmd ' cat /home/admin/obdiag_analyze_flt_result/000605b1-28bb-c15f-8ba0-1206bcc08aa3.txt '

Trace ID: c7534902-5a0d-11ef-95db-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace c7534902-5a0d-11ef-95db-00163e0808cc

Trace ID: c56997a4-5a0d-11ef-84d6-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace c56997a4-5a0d-11ef-84d6-00163e0808cc

集群巡检

您可使用 obd obdiag check 命令进行 OceanBase 数据库集群相关状态的巡检，目前支持从系统内核参数、内部表等方式对 OceanBase 集群进行分析，发现已存在或可能会导致集群出现异常问题的原因分析并提供运维建议。

您可在命令中通过 --cases 指定 tasks，可通过 obd obdiag check list 命令查看支持配置的 tasks。

说明

支持手动编写 task，详细介绍可参见官网 OceanBase 敏捷诊断工具（obdiag）文档 一键集群巡检 中 task 编写教程 部分内容。

obd obdiag check list obtest

obdiag V2.3.0 中支持配置的 tasks 如下：

[check cases about obproxy]:
-----------------------------------------------------------------------------------------------
command                              info_en                                 info_cn
-----------------------------------------------------------------------------------------------
obdiag check                         default check all task without filter   默认执行除filter组里的所有巡检项
obdiag check --obproxy_cases=proxy   obproxy version check                   obproxy 版本检查
-----------------------------------------------------------------------------------------------

[check cases about observer]:
-----------------------------------------------------------------------------------------------------------------------
command                                   info_en                                                    info_cn
-----------------------------------------------------------------------------------------------------------------------
obdiag check                              default check all task without filter                      默认执行除filter组里的所有巡检项
obdiag check --cases=ad                   Test and inspection tasks                                  测试巡检任务
obdiag check --cases=column_storage_poc   column storage poc                                         列存POC检查
obdiag check --cases=build_before         Deployment environment check                               部署环境检查
obdiag check --cases=sysbench_run         Collection of inspection tasks when executing sysbench     执行sysbench时的巡检任务集合
obdiag check --cases=sysbench_free        Collection of inspection tasks before executing sysbench   执行sysbench前的巡检任务集合
-----------------------------------------------------------------------------------------------------------------------
Trace ID: ab2c078e-5aab-11ef-81b3-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace ab2c078e-5aab-11ef-81b3-00163e0808cc

未指定 --cases 时默认执行全部 tasks，此处以执行全部 tasks 为例：

obd obdiag check run obtest

输出如下：

Check obproxy finished. For more details, please run cmd ' cat ./check_report//obdiag_check_report_obproxy_2024-08-14-15-43-59.table '
Check observer finished. For more details, please run cmd' cat ./check_report//obdiag_check_report_observer_2024-08-14-15-43-59.table '
Trace ID: f795bcd2-5a10-11ef-90e6-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace f795bcd2-5a10-11ef-90e6-00163e0808cc

Trace ID: f59531d8-5a10-11ef-89e9-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace f59531d8-5a10-11ef-89e9-00163e0808cc

您可复制执行输出中的 cat 命令或 obdiag display-trace 命令查看详细记录。

cat ./check_report//obdiag_check_report_observer_2024-08-14-15-43-59.table

输出如下：

+--------------------------------------------------------------------------------------------------------------------+
|                                               critical-tasks-report                                                |
+----------------------------+---------------------------------------------------------------------------------------+
|            task            | task_report                                                                           |
+----------------------------+---------------------------------------------------------------------------------------+
| cluster.data_path_settings | [critical] [local] ip:xx.xxx.xxx.xx ,data_dir and log_dir_disk are on the same disk.  |
+----------------------------+---------------------------------------------------------------------------------------+
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                         warning-tasks-report                                                                        |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
|                        task                       | task_report                                                                                                     |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
|          system.dependent_software_swapon         | [warning] [local] Do not warning. swapon is exist. We will check the swap                                       |
|                  system.parameter                 | [warning] [local] fs.pipe-user-pages-soft : 16384. recommended: 0.                                              |
|                                                   | [warning] [local] net.ipv4.tcp_syncookies: 0. recommended: 1.                                                   |
|              system.ulimit_parameter              | [warning] [local] On ip : xx.xxx.xxx.xx, ulimit -s is 10240 . recommended: unlimited.                           |
|                                                   | [warning] [local] On ip : xx.xxx.xxx.xx, ulimit -n is 655360 . recommended: unlimited.                          |
|                 system.getenforce                 | [warning] [local] Do not warning. getenforce is exist. We will check SELinux by getenforce                      |
|                   disk.disk_hole                  | [warning] [cluster:obagent] not warning ,the DATA_SIZE is not 0 . need check sum(REQUIRED_SIZE)/sum(DATA_SIZE)  |
|      cluster.ob_enable_plan_cache_bad_version     | [warning] Unadapted by version. SKIP                                                                            |
| cluster.optimizer_better_inlist_costing_parmmeter | [warning] Unadapted by version. SKIP                                                                            |
|           cluster.part_trans_action_max           | [warning] Unadapted by version. SKIP                                                                            |
|           cluster.table_history_too_many          | [warning] Unadapted by version. SKIP                                                                            |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                           all-tasks-report                                                                          |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
|                        task                       | task_report                                                                                                     |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
|                     system.aio                    | all pass                                                                                                        |
|             system.dependent_software             | all pass                                                                                                        |
|          system.dependent_software_swapon         | [warning] [local] Do not warning. swapon is exist. We will check the swap                                       |
|                  system.parameter                 | [warning] [local] fs.pipe-user-pages-soft : 16384. recommended: 0.                                              |
|                                                   | [warning] [local] net.ipv4.tcp_syncookies: 0. recommended: 1.                                                   |
|        system.parameter_ip_local_port_range       | all pass                                                                                                        |
|             system.parameter_tcp_rmem             | all pass                                                                                                        |

··· # 省略部分输出

|                    bugs.bug_182                   | all pass                                                                                                        |
|                clog.clog_disk_full                | all pass                                                                                                        |
|      table.information_schema_tables_two_data     | all pass                                                                                                        |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+

根因分析

当 OceanBase 数据库出现一些异常场景时，可通过 obd obdiag rca run 命令进行根因分析，找出可能导致问题的原因。具体支持的异常场景可执行 obd obdiag rca list 命令查看。

obd obdiag rca list obtest

obdiag V2.3.0 中支持的场景如下：

The tool oceanbase-diagnostic-tool is already installed the latest version 2.3.0
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
command                                              info_en                                                                                                 info_cn
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
obdiag rca run --scene=ddl_disk_full                 Insufficient disk space reported during DDL process.                                                    DDL过程中报磁盘空间不足的问题
obdiag rca run --scene=disconnection                 root cause analysis of disconnection                                                                    针对断链接场景的根因分析
obdiag rca run --scene=lock_conflict                 root cause analysis of lock conflict                                                                    针对锁冲突的根因分析
obdiag rca run --scene=major_hold                    root cause analysis of major hold                                                                       针对卡合并场景的根因分析
obdiag rca run --scene=clog_disk_full                Identify the issue of clog disk space being full.                                                       clog日志磁盘空间满的问题
obdiag rca run --scene=ddl_failure                   diagnose ddl failure                                                                                    诊断ddl失败
obdiag rca run --scene=index_ddl_error               Troubleshooting errors in indexing execution.                                                           建索引执行报错问题排查
obdiag rca run --scene=log_error                     Troubleshooting log related issues. Currently supported scenes: no_leader.                              日志相关问题排查。目前支持：无主场景。
obdiag rca run --scene=transaction_disconnection     root cause analysis of transaction disconnection                                                        针对事务断连场景的根因分析
obdiag rca run --scene=transaction_execute_timeout   transaction execute timeout error, error_code like -4012. Need input err_msg                            事务执行超时报错
obdiag rca run --scene=transaction_not_ending        transaction wait timeout error (beta), error_code like -4012                                            事务不结束场景（测试板），目前使用较为复杂
obdiag rca run --scene=transaction_other_error       transaction other error, error_code like -4030，-4121，-4122，-4124，-4019                                  事务其他错误，除了目前已经列出的错误，比如错误码为：-4030，-4121，-4122，-4124，-4019
obdiag rca run --scene=transaction_rollback          transaction rollback error. error_code like -6002                                                       事务回滚报错
obdiag rca run --scene=transaction_wait_timeout      transaction wait timeout error, error_msg like 'Shared lock conflict' or 'Lock wait timeout exceeded'   事务等待超时报错
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Trace ID: b7b86acc-5a12-11ef-9009-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace b7b86acc-5a12-11ef-9009-00163e0808cc

Trace ID: b61482b4-5a12-11ef-b3ee-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace b61482b4-5a12-11ef-b3ee-00163e0808cc

以分析断链场景为例，执行如下命令：

obd obdiag rca run obtest --scene=disconnection

输出如下，您可复制执行输出中的 cat 命令或 obdiag display-trace 命令查看详细记录。

+-----------------------------------------------------------------------------------------------------------+
|                                                   record                                                  |
+------+----------------------------------------------------------------------------------------------------+
| step | info                                                                                               |
+------+----------------------------------------------------------------------------------------------------+
|  1   | node:xxx.xxx.xxx obproxy_diagnosis_log:[2024-08-13 17:48:37.667014] [23173][Y0-00007FAA5183E710] |
|      | [CONNECTION](trace_type="CLIENT_VC_TRACE", connection_diagnosis={cs_id:1065, ss_id:4559,           |
|      | proxy_session_id:837192278409543969, server_session_id:3221810838,                                 |
|      | client_addr:"xxx.xxx.xxx.xxx:xxxx", server_addr:"xxx.xxx.xxx.xxx:2883", cluster_name:"obcluster",     |
|      | tenant_name:"sys", user_name:"root", error_code:-10010, error_msg:"An unexpected connection event  |
|      | received from client while obproxy reading request", request_cmd:"COM_SLEEP", sql_cmd:"COM_END",   |
|      | req_total_time(us):5315316}{vc_event:"VC_EVENT_EOS", user_sql:""})                                 |
|  2   | cs_id:1065, server_session_id:3221810838                                                           |
|  3   | trace_type:CLIENT_VC_TRACE                                                                         |
|  4   | error_code:-10010                                                                                  |
+------+----------------------------------------------------------------------------------------------------+
The suggest: Need client cooperation for diagnosis

rca finished. For more details, the result on '/home/admin/rca/obdiag_disconnection_20240814160846'
You can get the suggest by 'cat /home/admin/rca/obdiag_disconnection_20240814160846/record'
Trace ID: 6ded77fa-5a14-11ef-9b92-00163e0808cc
If you want to view detailed obdiag logs, please run: /home/admin/oceanbase-diagnostic-tool/obdiag display-trace 6ded77fa-5a14-11ef-9b92-00163e0808cc

Trace ID: 6c06e2e6-5a14-11ef-825f-00163e0808cc
If you want to view detailed obd logs, please run: obd display-trace 6c06e2e6-5a14-11ef-825f-00163e0808cc