案例说明:
在执行表数据查询时,出现下图所示错误,索引故障导致表无法访问,后重建索引问题解决。本案例复现了此类故障解决过程。
适用版本: KingbaseES V8R3/R6
一、创建测试环境
# 表结构信息
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" PRIMARY KEY, btree (id)
"test1_name_ind" btree (v_name)
Access method: heap
# 插入测试数据
prod=# insert into test1 values (generate_series(1,10000),'usr'||generate_series(1,10000));
INSERT 0 10000
prod=# select count(*) from test1;
count
-------
10000
(1 row)
# 查看索引应用
prod=# explain analyze select * from test1 where v_name='usr2';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test1_name_ind on test1 (cost=0.29..8.30 rows=1 width=11) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: ((v_name)::text = 'usr2'::text)
Planning Time: 0.055 ms
Execution Time: 0.038 ms
(4 rows)
二、模拟数据文件故障
1、查看索引文件存储路径
prod=# select pg_relation_filepath('test1_name_ind');
pg_relation_filepath
----------------------
base/16385/26800
(1 row)
2、模拟数据文件被破坏
[kingbase@node102 data]$ ls -lh base/16385/26800
-rw------- 1 kingbase kingbase 240K Nov 17 15:01 base/16385/26800
[kingbase@node102 data]$ dd if=/dev/zero of=/data/kingbase/v8r6_c6/data/base/16385/26800 bs=8k count=2
2+0 records in
2+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 17 15:04 base/16385/26800
# 如下所示索引故障导致表访问错误
prod=# select * from test1;
ERROR: index "test1_name_ind" contains unexpected zero page at block 0
HINT: Please REINDEX it.
三、重建索引解决故障
1、查看表索引信息
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" PRIMARY KEY, 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
(3 rows)
prod=# select * from test1 where v_name='usr2';
id | v_name
----+--------
2 | usr2
(1 row)
prod=# explain analyze select * from test1 where v_name='usr2';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test1_name_ind on test1 (cost=0.29..8.30 rows=1 width=11) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: ((v_name)::text = 'usr2'::text)
Planning Time: 0.055 ms
Execution Time: 0.038 ms
(4 rows)
四、总结
对于KingbaseES数据库,索引块的损坏会影响到表数据的正常访问,对于索引块故障处理比较简单,直接重建索引一般都可以解决此类问题。