HGDB索引膨胀的检查与处理思路

文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本4.5.8文档用途本文档主要介绍HGDB索引膨胀的定义、产生的原因、如何检查以及遇到索引膨胀如何处理包括预防和解决详细信息索引膨胀的定义假设对一个索引进行顺序的数据插入那么索引分裂应该只会发生在最右边的叶子结点若对索引进行无序的插入那么中间的叶子结点会进行了分裂多出了很多空闲空间索引扫描的时候需要扫描更多的页造成了io和存储空间上的浪费产生膨胀索引的原因1、表中的每个行版本“元组”都有一个未死亡的索引条目。当 VACUUM 删除死元组时它还必须删除相应的索引条目这会在索引页中创建空白空间。此类空间可以重复使用但如果没有新条目添加到页面则该空间仍为空2、频繁更新相同的行在VACUUM清理老元组前表和索引会维护相同行的很多版本。若索引页填满HGDB会将索引页分裂成2个在VACUUM执行完清理之后最终会得到2个臃肿的页面而不是1个索引膨胀的检查提前安装好pgstattuple插件用于返回一个关系的物理长度、死亡元组的百分比以及其他信息create extension pgstattuple;如下查询用于查看free_space占用前5的索引空闲空间越大说明索引使用率越低select oid::regclass,(pgstattuple(oid)).* from pg_class where relkind‘i’ order by free_space desc limit 5 offset 0;如下查询查看具体表中的各个索引大小selectrelname,pg_relation_size(oid)/1024||KASsizefrompg_classwhererelkindiandrelnamestu_dex;除了扩展也可以通过如下的sql查看索引膨胀的相关信息膨胀大小、膨胀率等便于对膨胀的索引做优化SELECTcurrent_database(),nspnameASschemaname,tblname,idxname,bs*(relpages)::bigintASreal_size,bs*(relpages-est_pages)::bigintASextra_size,100*(relpages-est_pages)::float/relpagesASextra_pct,fillfactor,CASEWHENrelpagesest_pages_ffTHENbs*(relpages-est_pages_ff)ELSE0ENDASbloat_size,100*(relpages-est_pages_ff)::float/relpagesASbloat_pct,FROM(SELECTcoalesce(1ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4nulldatahdrwidth)::float)),0-- ItemIdData size computed avg size of a tuple (nulldatahdrwidth))ASest_pages,coalesce(1ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4nulldatahdrwidth)::float))),0)ASest_pages_ff,bs,nspname,tblname,idxname,relpages,fillfactor,is_na-- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)FROM(SELECTmaxalign,bs,nspname,tblname,idxname,reltuples,relpages,idxoid,fillfactor,(index_tuple_hdr_bmmaxalign-CASE-- Add padding to the index tuple header to align on MAXALIGNWHENindex_tuple_hdr_bm%maxalign0THENmaxalignELSEindex_tuple_hdr_bm%maxalignENDnulldatawidthmaxalign-CASE-- Add padding to the data to align on MAXALIGNWHENnulldatawidth0THEN0WHENnulldatawidth::integer%maxalign0THENmaxalignELSEnulldatawidth::integer%maxalignEND)::numericASnulldatahdrwidth,pagehdr,pageopqdata,is_na-- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)FROM(SELECTn.nspname,i.tblname,i.idxname,i.reltuples,i.relpages,i.idxoid,i.fillfactor,current_setting(block_size)::numericASbs,CASE-- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)WHENversion()~mingw32ORversion()~64-bit|x86_64|ppc64|ia64|amd64THEN8ELSE4ENDASmaxalign,/* per page header, fixed size: 20 for 7.X, 24 for others */24ASpagehdr,/* per page btree opaque data */16ASpageopqdata,/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */CASEWHENmax(coalesce(s.null_frac,0))0THEN8-- IndexTupleData sizeELSE8((328-1)/8)-- IndexTupleData size IndexAttributeBitMapData size ( max num filed per index 8 - 1 /8)ENDASindex_tuple_hdr_bm,/* data len: we remove null values save space using it fractionnal part from stats */sum((1-coalesce(s.null_frac,0))*coalesce(s.avg_width,1024))ASnulldatawidth,max(CASEWHENi.atttypidpg_catalog.name::regtypeTHEN1ELSE0END)0ASis_naFROM(SELECTct.relnameAStblname,ct.relnamespace,ic.idxname,ic.attpos,ic.indkey,ic.indkey[ic.attpos],ic.reltuples,ic.relpages,ic.tbloid,ic.idxoid,ic.fillfactor,coalesce(a1.attnum,a2.attnum)ASattnum,coalesce(a1.attname,a2.attname)ASattname,coalesce(a1.atttypid,a2.atttypid)ASatttypid,CASEWHENa1.attnumISNULLTHENic.idxnameELSEct.relnameENDASattrelnameFROM(SELECTidxname,reltuples,relpages,tbloid,idxoid,fillfactor,indkey,pg_catalog.generate_series(1,indnatts)ASattposFROM(SELECTci.relnameASidxname,ci.reltuples,ci.relpages,i.indrelidAStbloid,i.indexrelidASidxoid,coalesce(substring(array_to_string(ci.reloptions, )fromfillfactor([0-9]))::smallint,90)ASfillfactor,i.indnatts,pg_catalog.string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(i.indkey)), )::int[]ASindkeyFROMpg_catalog.pg_index iJOINpg_catalog.pg_class ciONci.oidi.indexrelidWHEREci.relam(SELECToidFROMpg_amWHEREamnamebtree)ANDci.relpages0)ASidx_data)ASicJOINpg_catalog.pg_class ctONct.oidic.tbloidLEFTJOINpg_catalog.pg_attribute a1ONic.indkey[ic.attpos]0ANDa1.attrelidic.tbloidANDa1.attnumic.indkey[ic.attpos]LEFTJOINpg_catalog.pg_attribute a2ONic.indkey[ic.attpos]0ANDa2.attrelidic.idxoidANDa2.attnumic.attpos)iJOINpg_catalog.pg_namespace nONn.oidi.relnamespaceJOINpg_catalog.pg_stats sONs.schemanamen.nspnameANDs.tablenamei.attrelnameANDs.attnamei.attnameGROUPBY1,2,3,4,5,6,7,8,9,10,11)ASrows_data_stats)ASrows_hdr_pdg_stats)ASrelation_statsORDERBYnspname,tblname,idxname;预防索引膨胀实例级vacuum命令运行的最小延迟:alter system set autovacuum_naptime15s;在一个表上触发vacuum的被插入、被更新或被删除元组的最小数量:alter system set autovacuum_vacuum_threshold25;在一个表上触发analyze的被插入、被更新或被删除元组的最小数量:alter system set autovacuum_analyze_threshold10;决定是否触发vaccum时作为一个分数将它加到autovacuum_vacuum_threshold上:alter system set autovacuum_vacuum_scale_factor0.01;决定是否触发analyze时作为一个分数将它加到autovacuum_vacuum_threshold上:alter system set autovacuum_analyze_scale_factor0.05;autovacuum触发条件:pg_stat_all_tables.n_dead_tup大于 autovacuum_vacuum_threshold pg_class.reltuples * autovacuum_vacuum_scale_factorautoananlyze触发条件:pg_stat_all_tables.n_mod_since_analyze大于 autovacuum_analyze_threshold pg_class.reltuples * autovacuum_analyze_scale_factor表级1、设置合适的autovacuum_vacuum_scale_factor大表如果频繁的有更新或删除和插入操作, 建议设置较小的autovacuum_vacuum_scale_factor来降低空间的浪费加快对表的vacuum操作频率对更新频繁的表单独调整alter table tablename set (autovacuum_vacuum_scale_factor0.05);2、设置表的fillfactor对频繁更新的表调低fillfactor参数alter table tablename set (fillfactor85);解决索引膨胀1、重建索引创建新索引 create index CONCURRENTLY new_index ;删除旧索引 drop index old_index ;或者重建索引 reindex index 索引名称 CONCURRENTLY ;analyze tablename;2、执行vacuum full完全清理这样可以恢复更多的空间但是花的时间更多并且在表上施加了排它锁