关键字
表膨胀 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 COMPANYDEBUG: No workers to disconnect.INFO: repacking table "COMPANY"DEBUG: ---- repack_one_table ----DEBUG: target_name : COMPANYDEBUG: target_oid : 31816DEBUG: target_toast : 49297DEBUG: target_tidx : 49299DEBUG: pkid : 31823DEBUG: ckid : 0DEBUG: 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 DATADEBUG: copy_data : INSERT INTO repack.table_31816 SELECT ID,"NAME",AGE,ADDRESS,SALARY,C1 FROM ONLY COMPANYDEBUG: alter_col_storage : (skipped)DEBUG: drop_columns : (skipped)DEBUG: delete_log : DELETE FROM repack.log_31816DEBUG: lock_table : LOCK TABLE COMPANY IN ACCESS EXCLUSIVE MODEDEBUG: sql_peek: SELECT * FROM repack.log_31816 ORDER BY id LIMIT $1DEBUG: 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 : 49465DEBUG: index[0].create_index : CREATE INDEX index_49465 ON repack.table_31816 USING "HASH" ("NAME")DEBUG: index[1].target_oid : 31823DEBUG: index[1].create_index : CREATE UNIQUE INDEX index_31823 ON repack.table_31816 USING BTREE (ID)DEBUG: LOCK TABLE COMPANY IN ACCESS SHARE MODEDEBUG: 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=0DEBUG: set up index_jobs [0]DEBUG: target_oid : 49465DEBUG: create_index : CREATE INDEX index_49465 ON repack.table_31816 USING "HASH" ("NAME")DEBUG: set up index_jobs [1]DEBUG: target_oid : 31823DEBUG: 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.
