联系我们
18591797788
hubin@rlctech.com
北京市海淀区中关村南大街乙12号院天作国际B座1708室
18681942657
lvyuan@rlctech.com
上海市浦东新区商城路660号乐凯大厦26c-1
18049488781
xieyi@rlctech.com
广州市越秀区东风东路华宫大厦808号1608房
029-81109312
service@rlctech.com
西安市高新区天谷七路996号西安国家数字出版基地C座501
在 OceanBase 数据库运维中,创建索引是高频操作之一,尤其面对海量数据时,索引构建可能持续数小时甚至更久。实时掌握建索引进度、判断当前所处阶段,不仅能避免运维人员盲目等待,更能及时发现异常并介入处理。
本文基于 OceanBase 2.2.77 版本,从索引创建的核心阶段出发,结合系统表查询实战,带大家全方位监控建索引全流程。
在监控进度前,需明确索引创建的完整生命周期,每个阶段对应特定的系统状态特征:
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 = 构建索引完成
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) |
mysql> create index idx_pad on t1(pad) global;
__all_index_build_stat表status列为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 |
a. 观察 __all_index_build_stat ,status为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 |
+----------+
__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 |
+-------------------------------------------------------------------
观察__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 |
+-----------+------------------+--------------+-----------------+---
观察__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 建索引全流程的可视化监控,精准掌握每个阶段的进度与状态,为运维决策提供数据支撑。如果在实操中遇到异常场景,欢迎在评论区留言交流,下次见!