案例说明：

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




[转帖]金仓数据库KingbaseES V8R6索引坏块故障处理_Access


适用版本：

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)

二、模拟数据文件故障

1、查看索引文件存储路径

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.

三、重建索引解决故障

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"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)

四、总结

对于KingbaseES数据库，索引块的损坏会影响到表数据的正常访问，对于索引块故障处理比较简单，直接重建索引一般都可以解决此类问题。