金仓数据库 KingbaseES Sys_repack 解决金仓数据库 KingbaseES 表膨胀的问题

 关键字

表膨胀 vacuum 索引膨胀 无锁处理

问题描述

详细描述问题现象，必要时可添加图片。表或索引发生膨胀后，用户可以使用vacuum full重建表，但是vacuum full需要持有排它锁，会堵塞读操作。

问题分析

Vacuum不能处理索引膨胀，且vacuum过程排他锁影响用户业务；采用停机的方式重建表解决表膨胀用户体验也差。

解决方案

KingbaseES提供了sys_repack的工具来处理表及索引膨胀的问题，repack期间不影响用户的业务。sys_repack的插件使用了增量的方式重组数据，最后通过切换FILENODE完成数据重组。仅仅在切换FILENODE时需要持有排他锁，非常短暂，影响比VACUUM FULL和停机重建表的方式小多了。

命令执行

[v7test@kadb_centos7_33 bin]$ ./sys_repack -h localhost -p 54321 -U KINGBASEES -WMANAGER -d TEST --no-order --elevel=debug --table COMPANY

DEBUG: No workers to disconnect.

INFO: repacking table "COMPANY"

DEBUG: ---- repack_one_table ----

DEBUG: target_name       : COMPANY

DEBUG: target_oid        : 31816

DEBUG: target_toast      : 49297

DEBUG: target_tidx       : 49299

DEBUG: pkid              : 31823

DEBUG: ckid              : 0

DEBUG: create_pktype     : CREATE TYPE repack.pk_31816 AS (ID INTEGER)

DEBUG: create_log        : CREATE TABLE repack.log_31816 (id bigint identity(1,1) PRIMARY KEY, pk repack.pk_31816, row COMPANY)

DEBUG: create_trigger    : CREATE TRIGGER repack_trigger_insert AFTER INSERT ON COMPANY FOR EACH ROW as declare begin  INSERT INTO repack.log_31816(pk, row) VALUES( NULL, new); end;CREATE TRIGGER repack_trigger_delete AFTER delete ON COMPANY FOR EACH ROW as declare begin  INSERT INTO repack.log_31816(pk, row) VALUES( old.ID, NULL); end;CREATE TRIGGER repack_trigger_update AFTER update ON COMPANY FOR EACH ROW as declare begin  INSERT INTO repack.log_31816(pk, row) VALUES( old.ID, new); end;

DEBUG: enable_trigger    : ALTER TABLE COMPANY ENABLE TRIGGER repack_trigger_insert;ALTER TABLE COMPANY ENABLE TRIGGER repack_trigger_delete;ALTER TABLE COMPANY ENABLE TRIGGER repack_trigger_update;

DEBUG: create_table      : CREATE TABLE repack.table_31816 WITH (oids = false) TABLESPACE "SYSTEM" AS SELECT ID,"NAME",AGE,ADDRESS,SALARY,C1 FROM ONLY COMPANY WITH NO DATA

DEBUG: copy_data         : INSERT INTO repack.table_31816 SELECT ID,"NAME",AGE,ADDRESS,SALARY,C1 FROM ONLY COMPANY

DEBUG: alter_col_storage : (skipped)

DEBUG: drop_columns      : (skipped)

DEBUG: delete_log        : DELETE FROM repack.log_31816

DEBUG: lock_table        : LOCK TABLE COMPANY IN ACCESS EXCLUSIVE MODE

DEBUG: sql_peek          : SELECT * FROM repack.log_31816 ORDER BY id LIMIT $1

DEBUG: sql_insert        : INSERT INTO repack.table_31816 VALUES ($1.*)

DEBUG: sql_delete        : DELETE FROM repack.table_31816 WHERE (ID) = ($1.ID)

DEBUG: sql_update        : UPDATE repack.table_31816 SET (ID, "NAME", AGE, ADDRESS, SALARY, C1) = ($2.ID, $2."NAME", $2.AGE, $2.ADDRESS, $2.SALARY, $2.C1) WHERE (ID) = ($1.ID)

DEBUG: sql_pop           : DELETE FROM repack.log_31816 WHERE id IN (

DEBUG: ---- setup ----

DEBUG: index[0].target_oid      : 49465

DEBUG: index[0].create_index    : CREATE  INDEX index_49465 ON repack.table_31816 USING "HASH" ("NAME")

DEBUG: index[1].target_oid      : 31823

DEBUG: index[1].create_index    : CREATE UNIQUE  INDEX index_31823 ON repack.table_31816 USING BTREE (ID)

DEBUG: LOCK TABLE COMPANY IN ACCESS SHARE MODE

DEBUG: No competing DDL to cancel.

DEBUG: Waiting on ACCESS SHARE lock...

DEBUG: ---- copy tuples ----

DEBUG: No competing DDL to cancel.

DEBUG: ---- create indexes ----

DEBUG: Have 2 indexes and num_workers=0

DEBUG: set up index_jobs [0]

DEBUG: target_oid   : 49465

DEBUG: create_index : CREATE  INDEX index_49465 ON repack.table_31816 USING "HASH" ("NAME")

DEBUG: set up index_jobs [1]

DEBUG: target_oid   : 31823

DEBUG: create_index : CREATE UNIQUE  INDEX index_31823 ON repack.table_31816 USING BTREE (ID)

DEBUG: ---- swap ----

DEBUG: ---- drop ----

DEBUG: ---- analyze ----

DEBUG: No workers to disconnect.