×
技术社区 >  技术博客 >  深度解析:OceanBase 建索引进度可视化监控指南(附实战命令)

深度解析:OceanBase 建索引进度可视化监控指南(附实战命令)

在 OceanBase 数据库运维中,创建索引是高频操作之一,尤其面对海量数据时,索引构建可能持续数小时甚至更久。实时掌握建索引进度、判断当前所处阶段,不仅能避免运维人员盲目等待,更能及时发现异常并介入处理。

本文基于 OceanBase 2.2.77 版本,从索引创建的核心阶段出发,结合系统表查询实战,带大家全方位监控建索引全流程。

一、OceanBase 建索引的四大核心阶段

在监控进度前,需明确索引创建的完整生命周期,每个阶段对应特定的系统状态特征:

1、准备阶段:生成索引表的元数据信息,索引表设置成只写状态;等待之前未往索引表插入过数据的事务结束,获取构建快照点。

2、构建阶段:基于获取的快照点扫描主表数据,并写入到索引表基线SSTable中,而增量事务产生的数据写入到Memtable中,最后完成基线补全也就是将快照点后的dml同步到索引中。

3、拷贝阶段:索引构建是单副本上完成的,构建完成后会通过一致性算法同步到索引表多个副本上。

4、收尾阶段:进行数据校验,对于唯一索引还需要进行唯一性校验,一切完成后索引表设置成可读写状态,否则设置成不可用状态。

以下案例版本为2.2.77

二、观察全局索引创建过程

全局索引主要依靠观察__all_index_build_stat信息表定位创建到哪个阶段了:

该表的status列值对应如下:

  • 0 = invalid,创建本地索引时观察该值为0

  • 1 = 单副本构建

  • 2 = 多副本拷贝

  • 3 = 校验唯一性

  • 4 = 校验和比对

  • 5 = 设置索引生效

  • 6 = 构建索引失败

  • 7 = 构建索引完成

三、按阶段查询操作指南

1、准备阶段:

  • a. 查看建表语句:
mysql> show create table t1;
--------------------+------------------
| Table | Create Table  |
+-------+-----------------------------
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`, `k`)
) AUTO_INCREMENT = 12000001 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(k)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4) |
  • b. 执行建索引语句:
mysql> create index idx_pad on t1(pad) global;
  • c. 在等待事务结束时,查询__all_index_build_statstatus列为1,快照点snapshot为0:
mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+----------
| gmt_create                 | gmt_modified               | tenant_id | data_table_id    | index_table_id   | status | snapshot | schema_version   |
+----------------------------+----------------------------+---------
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:19:25.503550 |      1014 | 1114904790615059 | 1114904790615064 |      1 |        0 | 1649157565414240 |

2、构建阶段:

  • a. 观察 __all_index_build_statstatus为1,已获取快照点snapshot

    观察__all_virtual_table_mgr,已完成memtable创建(table_type=0)

    观察__all_virtual_sys_task_status,每个分区都有一个create index任务

    观察__all_virtual_rebalance_task_stat,每个分区都有一个SQL_BACKGROUND_DIST_TASK任务

    观察__all_index_checksum,暂无checksum信息

mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------
| gmt_create                 | gmt_modified               | tenant_id | data_table_id    | index_table_id   | status | snapshot         | schema_version   |
+----------------------------+----------------------------+---------
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:19:47.551521 |      1014 | 1114904790615059 | 1114904790615064 |      1 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+-----------------
| svr_ip      | svr_port | tenant_id | table_type | table_id         | partition_id | index_id         | base_version     | multi_version_start | snapshot_version    | version | logical_data_version | size | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+-------------------+-----------+-----+-----------+-----------+
| 10.10.10.10 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |    0 |         1 |   2 |         0 |         0 |
| 10.10.10.11 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |    0 |         1 |   2 |         0 |         0 |
| 10.10.10.12 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |    0 |         1 |   2 |         0 |         0 |
+-------------+----------+-----------+------------+-----------------
mysql> select * from __all_virtual_sys_task_status;
+----------------------------+--------------+-----------------------
| start_time                 | task_type    | task_id                       | svr_ip      | svr_port | tenant_id | comment                                                | is_cancel |
+----------------------------+--------------+-----------------------
| 2023-04-05 19:19:48.632648 | create index | YB420B973EA3-0005D1DBCF659889 | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:0, task_cnt:5} |         0 |
| 2023-04-05 19:19:48.633941 | create index | YB420B973EA3-0005D1DBCF65988A | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:1, task_cnt:5} |         0 |
| 2023-04-05 19:19:48.635211 | create index | YB420B973EA3-0005D1DBCF65988B | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:2, task_cnt:5} |         0 |
| 2023-04-05 19:19:48.636490 | create index | YB420B973EA3-0005D1DBCF65988C | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:3, task_cnt:5} |         0 |
| 2023-04-05 19:19:48.637767 | create index | YB420B973EA3-0005D1DBCF65988D | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:4, task_cnt:5} |         0 |
+----------------------------+--------------+-----------------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id         | partition_id | partition_count | source    | data_source | destination      | offline   | is_replicate | task_type                | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
|      1014 | 1114904790615059 |            0 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |           56 |         326548 |
|      1014 | 1114904790615059 |            1 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |         1374 |         325151 |
|      1014 | 1114904790615059 |            2 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |         2550 |         323916 |
|      1014 | 1114904790615059 |            3 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |         3793 |         322623 |
|      1014 | 1114904790615059 |            4 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |         5025 |         321342 |
+-----------+------------------+--------------+-----------------+----+----------------+
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
  • b. 观察 __all_virtual_sys_task_status ,有一个 create index任务

    观察__all_virtual_rebalance_task_stat,有一个SQL_BACKGROUND_DIST_TASK任务

    观察__all_index_checksum,发现有5个分区的索引列(pad)、主键列(id/k)信息,5x3=15

mysql> select * from __all_virtual_sys_task_status;
+----------------------------+--------------+-----------------------------------------------------+-----------+
| start_time                 | task_type    | task_id                       | svr_ip      | svr_port | tenant_id | comment                                                                                                                                                                                    | is_cancel |
+----------------------------+--------------+-----------------------------------------------------+-----------+
| 2023-04-05 19:19:50.958727 | create index | YB420B973EA3-0005D1DBCF65988E | 10.10.10.10 |     2882 |      1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:3}, task_id:0, task_cnt:1} |         0 |
+----------------------------+--------------+-----------------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id         | partition_id | partition_count | source    | data_source | destination      | offline   | is_replicate | task_type                | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
|      1014 | 1114904790615064 |            0 |               0 | 0.0.0.0:0 | 0.0.0.0:0   | 10.10.10.10:2882 | 0.0.0.0:0 | No           | SQL_BACKGROUND_DIST_TASK | Yes          | No        |           41 |        3646335 |
+-----------+------------------+--------------+-----------------+----+----------------+
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
  • c. 观察__all_virtual_sys_task_status,暂无任务信息

    观察__all_virtual_rebalance_task_stat,暂无任务信息

    观察__all_index_checksum,发现多出了全局索引表的checksum信息

mysql> select * from __all_virtual_sys_task_status;
Empty set (0.01 sec)
mysql> select * from __all_virtual_rebalance_task_stat;
Empty set (0.01 sec)
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
|       18 |
+----------+
  • d. 观察__all_index_checksum内容(以下内容为重建索引获取,会存在索引表id不一致情况)
mysql> select * from __all_index_checksum where gmt_create>'2023-04-05 20:10:00' and tenant_id=1014 order by column_id;
+----------------------------+----------------------------+---------
| gmt_create                 | gmt_modified               | execution_id        | tenant_id | table_id         | partition_id | column_id | task_id | checksum          | checksum_method |
+----------------------------+----------------------------+---------
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 |      1014 | 1114904790615059 |            0 |        16 |       0 |  4294189217994805 |               2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 |      1014 | 1114904790615059 |            1 |        16 |       1 |  4293460459368680 |               2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 |      1014 | 1114904790615068 |            0 |        16 |       0 | 21474837339484641 |               2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 |      1014 | 1114904790615059 |            2 |        16 |       2 |  4294471659607893 |               2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 |      1014 | 1114904790615059 |            4 |        16 |       4 |  4291068240165421 |               2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 |      1014 | 1114904790615059 |            3 |        16 |       3 |  4301647762347842 |               2 |
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 |      1014 | 1114904790615059 |            0 |        17 |       0 |  4294529994534407 |               2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 |      1014 | 1114904790615068 |            0 |        17 |       0 | 21478992387389366 |               2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 |      1014 | 1114904790615059 |            4 |        17 |       4 |  4295367552394642 |               2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 |      1014 | 1114904790615059 |            3 |        17 |       3 |  4296764324236270 |               2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 |      1014 | 1114904790615059 |            2 |        17 |       2 |  4299921221083111 |               2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 |      1014 | 1114904790615059 |            1 |        17 |       1 |  4292409295140936 |               2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 |      1014 | 1114904790615059 |            2 |        19 |       2 |  4296268846119605 |               2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 |      1014 | 1114904790615059 |            3 |        19 |       3 |  4300722872371463 |               2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 |      1014 | 1114904790615059 |            1 |        19 |       1 |  4289982707443986 |               2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 |      1014 | 1114904790615059 |            4 |        19 |       4 |  4293685226392460 |               2 |
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 |      1014 | 1114904790615059 |            0 |        19 |       0 |  4292840718037296 |               2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 |      1014 | 1114904790615068 |            0 |        19 |       0 | 21473500370364810 |               2 |
+----------------------------+----------------------------+---------
mysql> select 4294189217994805+4293460459368680+4294471659607893+4291068240165421+4301647762347842;
+-------------------------------------------------------------------
| 4294189217994805+4293460459368680+4294471659607893+4291068240165421+4301647762347842 |
+--------------------------------------------------------------------------------------+
|                                                                    21474837339484641 |
+-------------------------------------------------------------------

3、拷贝阶段:

观察__all_index_build_stat,status为2,已进入多副本拷贝阶段

观察__all_virtual_table_mgr,发现正在拷贝基线sstable(table_type=1)

观察__all_virtual_sys_task_status,发现migration任务

观察__all_virtual_rebalance_task_stat,发现COPY_SSTABLE任务

mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------+------------------+
| gmt_create                 | gmt_modified               | tenant_id | data_table_id    | index_table_id   | status | snapshot         | schema_version   |
+----------------------------+----------------------------+---------+------------------+
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:20:53.331745 |      1014 | 1114904790615059 | 1114904790615064 |      2 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------+------------------+
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
| svr_ip      | svr_port | tenant_id | table_type | table_id         | partition_id | index_id         | base_version     | multi_version_start | snapshot_version    | version | logical_data_version | size      | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
| 10.10.10.11 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.10 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.11 |     2882 |      1014 |          1 | 1114904790615064 |            0 | 1114904790615064 |                0 |    1649157587510214 |    1649157587510214 |     298 |                  298 | 293289439 |         0 |   2 |         0 |         0 |
| 10.10.10.12 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.10 |     2882 |      1014 |          1 | 1114904790615064 |            0 | 1114904790615064 |                0 |    1649157587510214 |    1649157587510214 |     298 |                  298 | 293289439 |         0 |   3 |         0 |         0 |
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
mysql> select * from __all_virtual_sys_task_status;
+----------------------------+----------------------------+----------------------------------------------+-----------+
| start_time                 | task_type                  | task_id                       | svr_ip      | svr_port | tenant_id | comment                                                                                                                                                                                                                                                 | is_cancel |
+----------------------------+----------------------------+----------------------------------------------+-----------+
| 2023-04-05 19:20:56.134953 | group partition migration  | YB420B277B81-0005C4F9E6B7E579 | 10.10.10.12 |     2882 |      1014 | group partition migration executor: group_task_id=YB420B277B81-0005C4F9E6B7E579 partition_count=1 partition_id=0 key={tid:1114904790615064, partition_id:0, part_cnt:0}, op_type=COPY_GLOBAL_INDEX_OP, src="10.10.10.10:2882", dest="10.10.10.12:2882", |         0 |
| 2023-04-05 19:20:56.137597 | single partition migration | YB420B277B81-0005C4F9C3F84711 | 10.10.10.12 |     2882 |      1014 | single partition migration: group_task_id=YB420B277B81-0005C4F9E6B7E579 key={tid:1114904790615064, partition_id:0, part_cnt:0}, op_type=COPY_GLOBAL_INDEX_OP, src="10.10.10.10:2882", dest="10.10.10.12:2882",dest_memstore_percent=100                 |         0 |
+----------------------------+----------------------------+----------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id         | partition_id | partition_count | source           | data_source      | destination      | offline   | is_replicate | task_type    | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
|      1014 | 1114904790615064 |            0 |               0 | 10.10.10.10:2882 | 10.10.10.10:2882 | 10.10.10.12:2882 | 0.0.0.0:0 | No           | COPY_SSTABLE | Yes          | No        |           57 |         452611 |
+-----------+------------------+--------------+-----------------+---

4、收尾阶段:

观察__all_index_build_stat,status为5,设置索引表生效

观察__all_virtual_table_mgr,发现基线sstable(table_type=1)已补齐

补充:查询select index_status from__all_virtual_table where table_id= 索引表id,为2说明索引表构建成功

mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------+------------------+
| gmt_create                 | gmt_modified               | tenant_id | data_table_id    | index_table_id   | status | snapshot         | schema_version   |
+----------------------------+----------------------------+---------+------------------+
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:20:58.114574 |      1014 | 1114904790615059 | 1114904790615064 |      5 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------+------------------+
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+------------------+-----------------+-----------+
| svr_ip      | svr_port | tenant_id | table_type | table_id         | partition_id | index_id         | base_version     | multi_version_start | snapshot_version    | version | logical_data_version | size      | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+------------------+-----------------+-----------+
| 10.10.10.11 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.10 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.11 |     2882 |      1014 |          1 | 1114904790615064 |            0 | 1114904790615064 |                0 |    1649157587510214 |    1649157587510214 |     298 |                  298 | 293289439 |         0 |   2 |         0 |         0 |
| 10.10.10.12 |     2882 |      1014 |          0 | 1114904790615064 |            0 | 1114904790615064 | 1649095210272570 |    1649095210272570 | 9223372036854775807 |       0 |                    0 |         0 |         1 |   2 |         0 |         0 |
| 10.10.10.10 |     2882 |      1014 |          1 | 1114904790615064 |            0 | 1114904790615064 |                0 |    1649157587510214 |    1649157587510214 |     298 |                  298 | 293289439 |         0 |   2 |         0 |         0 |
| 10.10.10.12 |     2882 |      1014 |          1 | 1114904790615064 |            0 | 1114904790615064 |                0 |    1649157587510214 |    1649157587510214 |     298 |                  298 | 293289439 |         0 |   2 |         0 |         0 |
+-------------+----------+-----------+------------+-----------------

通过以上方法,可实现 OceanBase 建索引全流程的可视化监控,精准掌握每个阶段的进度与状态,为运维决策提供数据支撑。如果在实操中遇到异常场景,欢迎在评论区留言交流,下次见!

精选推荐