关键字
表膨胀 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.