人大金仓数据库KingbaseES V8R6索引坏块故障处理

案例说明：

在执行表数据查询时，出现下图所示错误，索引故障导致表无法访问，后重建索引问题解决。本案例复现了此类故障解决过程。

适用版本：KingbaseES V8R3/R6

一、创建测试环境

# 表结构信息
prod=# \d+ test1
Table "public.test1"
Column| Type |Collation| Nullable |Default| Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id |integer||notnull|| plain ||
v_name |varchar|||| extended ||
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
"test1_name_ind" btree (v_name)
Access method: heap

# 插入测试数据
prod=# insertinto test1 values (generate_series(1,10000),'usr'||generate_series(1,10000));
INSERT010000
prod=# selectcount(*) from test1;
count
-------10000
(1row)

# 查看索引应用
prod=# explain analyze select*from test1 where v_name='usr2';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test1_name_ind on test1 (cost=0.29..8.30rows=1 width=11) (actual time=0.023..0.024rows=1 loops=1)
Index Cond: ((v_name)::text ='usr2'::text)
Planning Time: 0.055 ms
Execution Time: 0.038 ms
(4rows)

二、模拟数据文件故障

prod=# select pg_relation_filepath('test1_name_ind');
pg_relation_filepath
----------------------
base/16385/26800
(1row)

2、模拟数据文件被破坏

[kingbase@node102 data]$ ls -lh base/16385/26800
-rw------- 1 kingbase kingbase 240K Nov 1715:01 base/16385/26800

[kingbase@node102 data]$ dd if=/dev/zero of=/data/kingbase/v8r6_c6/data/base/16385/26800 bs=8k count=22+0 records in2+0 records out
16384 bytes (16 kB) copied, 0.000147959 s, 111 MB/s

[kingbase@node102 data]$ ls -lh base/16385/26800
-rw------- 1 kingbase kingbase 16K Nov 1715:04 base/16385/26800# 如下所示索引故障导致表访问错误
prod=# select * from test1;ERROR: index "test1_name_ind" contains unexpected zero page at block 0HINT: Please REINDEX it.

三、重建索引解决故障

prod=# \d+ test1;
Table"public.test1"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
v_name | varchar | | | | extended | |
Indexes:
"test1_pkey"PRIMARYKEY, btree (id)
"test1_name_ind" btree (v_name)
Access method: heap

2、重建索引

# 索引重建
prod=# reindex index test1_name_ind;
REINDEX

#如下所示，重建索引后表数据访问正常
prod=# select*from test1 limit 3;
id | v_name
----+--------1| usr1
2| usr2
3| usr3
(3rows)

prod=# select*from test1 where v_name='usr2';
id | v_name
----+--------2| usr2
(1row)

prod=# explain analyze select*from test1 where v_name='usr2';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test1_name_ind on test1 (cost=0.29..8.30rows=1 width=11) (actual time=0.023..0.024rows=1 loops=1)
Index Cond: ((v_name)::text ='usr2'::text)
Planning Time: 0.055 ms
Execution Time: 0.038 ms
(4rows)

四、总结