本篇内容介绍了“怎么理解PostgreSQL的后台进程autovacuum”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

AutoVacuumShmem
主要的autovacuum共享内存结构体,存储在shared memory中,同时WorkerInfo也会存储在其中.

/*-------------*Themainautovacuumshmemstruct.Onsharedmemorywestorethismain*structandthearrayofWorkerInfostructs.Thisstructkeeps:*主要的autovacuum共享内存结构体,存储在sharedmemory中,同时WorkerInfo也会存储在其中.*该结构体包括:**av_signalsetbyotherprocessestoindicatevariousconditions*其他进程设置用于提示不同的条件*av_launcherpidthePIDoftheautovacuumlauncher*autovacuumlauncher的PID*av_freeWorkerstheWorkerInfofreelist*WorkerInfo空闲链表*av_runningWorkerstheWorkerInfonon-freequeue*WorkerInfo非空闲队列*av_startingWorkerpointertoWorkerInfocurrentlybeingstarted(clearedby*theworkeritselfassoonasit'supandrunning)*av_startingWorker指向当前正在启动的WorkerInfo*av_workItemsworkitemarray*av_workItems工作条目数组**ThisstructisprotectedbyAutovacuumLock,exceptforav_signalandparts*oftheworkerlist(seeabove).*除了av_signal和workerlist的一部分信息，该数据结构通过AutovacuumLock保护*-------------*/typedefstruct{sig_atomic_tav_signal[AutoVacNumSignals];pid_tav_launcherpid;dlist_headav_freeWorkers;dlist_headav_runningWorkers;WorkerInfoav_startingWorker;AutoVacuumWorkItemav_workItems[NUM_WORKITEMS];}AutoVacuumShmemStruct;staticAutoVacuumShmemStruct*AutoVacuumShmem;

avw_dbase
用于跟踪worker中的数据库的结构体

/*structtokeeptrackofdatabasesinworker*///用于跟踪worker中的数据库的结构体typedefstructavw_dbase{Oidadw_datid;char*adw_name;TransactionIdadw_frozenxid;MultiXactIdadw_minmulti;PgStat_StatDBEntry*adw_entry;}avw_dbase;二、源码解读

rebuild_database_list用于构建出现变化后的DatabaseList，链表中的数据库应出现在pgstats中，在autovacuum_naptime所设定的时间间隔范围内均匀分布。
比如autovacuum_naptime = 60s，有4个数据库db1->db4，那么每隔60s/4就会有启动一个autovacuum worker对相应的DB进行处理。
可能的一个处理时间序列是：db1->XX（时）:XX（分）:18（秒）,db4->XX:XX:33,db4->XX:XX:48,db4->XX:XX:03
后续如需要对db1->db4进行vacuum，那么db1->db4会在下一个18秒、33秒、48秒和03秒触发autovacuum。

/**BuildanupdatedDatabaseList.Itmustonlycontaindatabasesthatappear*inpgstats,andmustbesortedbynext_workerfromhighesttolowest,*distributedregularlyacrossthenextautovacuum_naptimeinterval.*构建出现变化后的DatabaseList，链表中的数据库应出现在pgstats中，通过next_worker从最高到最低排列，*在autovacuum_naptime所设定的间隔范围内均匀分布。*比如autovacuum_naptime=60s，有4个数据库db1->db4，那么每隔60s/4就会有启动一个autovacuumworker对相应的DB进行处理。*可能的一个处理时间序列是：db1->XX:XX:18,db4->XX:XX:33,db4->XX:XX:48,db4->XX:XX:03**ReceivestheOidofthedatabasethatmadethislistbegenerated(wecall*thisthe"new"database,becausewhenthedatabasewasalreadypresenton*thelist,weexpectthatthisfunctionisnotcalledatall).The*preexistinglist,ifany,willbeusedtopreservetheorderofthe*databasesintheautovacuum_naptimeperiod.Thenewdatabaseisputatthe*endoftheinterval.Theactualvaluesarenotsaved,whichshouldnotbe*muchofaproblem.*/staticvoidrebuild_database_list(Oidnewdb){List*dblist;ListCell*cell;MemoryContextnewcxt;MemoryContextoldcxt;MemoryContexttmpcxt;HASHCTLhctl;intscore;intnelems;HTAB*dbhash;dlist_iteriter;/*usefreshstats*/autovac_refresh_stats();newcxt=AllocSetContextCreate(AutovacMemCxt,"AVdblist",ALLOCSET_DEFAULT_SIZES);tmpcxt=AllocSetContextCreate(newcxt,"tmpAVdblist",ALLOCSET_DEFAULT_SIZES);oldcxt=MemoryContextSwitchTo(tmpcxt);/**Implementingthisisnotassimpleasitsounds,becauseweneedtoput*thenewdatabaseattheendofthelist;nextthedatabasesthatwere*alreadyonthelist,andfinally(atthetailofthelist)allthe*otherdatabasesthatarenotontheexistinglist.*这里的实现并没有看上去的那么简单，因为需要把新数据库放在链表的末尾;*接下来是处理已经在链表上的数据库，最后(在链表的末尾)是处理不在现有链表上的所有其他数据库。**Todothis,webuildanemptyhashtableofscoreddatabases.Wewill*startwiththelowestscore(zero)forthenewdatabase,then*increasingscoresforthedatabasesintheexistinglist,inorder,and*lastlyincreasingscoresforalldatabasesgottenvia*get_database_list()thatarenotalreadyonthehash.*为了实现这个目的，构建了一个空的哈希表用于存储数据库（已加权重值）。*从最低分值（0）开始，赋予新的数据库，然后为现存在链表中的数据库增加分值，*继续为不在链表中的数据库增加分值。**Thenwewillputallthehashelementsintoanarray,sortthearrayby*score,andfinallyputthearrayelementsintothenewdoublylinked*list.*完成上述工作后，会把所有哈希表中的元素放到数组中，通过分值进行排序，最后把数组元素放到新的双向链接链表中。*/hctl.keysize=sizeof(Oid);hctl.entrysize=sizeof(avl_dbase);hctl.hcxt=tmpcxt;dbhash=hash_create("dbhash",20,&hctl,/*magicnumberhereFIXME*/HASH_ELEM|HASH_BLOBS|HASH_CONTEXT);/*startbyinsertingthenewdatabase*/score=0;//分值从0开始if(OidIsValid(newdb)){avl_dbase*db;PgStat_StatDBEntry*entry;/*onlyconsiderthisdatabaseifithasapgstatentry*///只关注存在pgstat条目的数据库entry=pgstat_fetch_stat_dbentry(newdb);if(entry!=NULL){/*weassumeitisn'tfoundbecausethehashwasjustcreated*/db=hash_search(dbhash,&newdb,HASH_ENTER,NULL);/*hash_searchalreadyfilledinthekey*/db->adl_score=score++;/*next_workerisfilledinlater*/}}/*Nowinsertthedatabasesfromtheexistinglist*///从现存链表中插入到数据库中dlist_foreach(iter,&DatabaseList){avl_dbase*avdb=dlist_container(avl_dbase,adl_node,iter.cur);avl_dbase*db;boolfound;PgStat_StatDBEntry*entry;/**skipdatabaseswithnostatentries--inparticular,thisgetsrid*ofdroppeddatabases*跳过没有统计信息的数据库*/entry=pgstat_fetch_stat_dbentry(avdb->adl_datid);if(entry==NULL)continue;db=hash_search(dbhash,&(avdb->adl_datid),HASH_ENTER,&found);if(!found){/*hash_searchalreadyfilledinthekey*/db->adl_score=score++;/*next_workerisfilledinlater*/}}/*finally,insertallqualifyingdatabasesnotpreviouslyinserted*///插入先前没有处理过的数据库dblist=get_database_list();foreach(cell,dblist){avw_dbase*avdb=lfirst(cell);avl_dbase*db;boolfound;PgStat_StatDBEntry*entry;/*onlyconsiderdatabaseswithapgstatentry*///只考虑存在pgstat的数据库entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);if(entry==NULL)continue;db=hash_search(dbhash,&(avdb->adw_datid),HASH_ENTER,&found);/*onlyupdatethescoreifthedatabasewasnotalreadyonthehash*/if(!found){/*hash_searchalreadyfilledinthekey*/db->adl_score=score++;/*next_workerisfilledinlater*/}}nelems=score;/*fromhereon,theallocatedmemorybelongstothenewlist*/MemoryContextSwitchTo(newcxt);dlist_init(&DatabaseList);if(nelems>0){TimestampTzcurrent_time;intmillis_increment;avl_dbase*dbary;avl_dbase*db;HASH_SEQ_STATUSseq;inti;/*putallthehashelementsintoanarray*///放到数组中dbary=palloc(nelems*sizeof(avl_dbase));i=0;hash_seq_init(&seq,dbhash);while((db=hash_seq_search(&seq))!=NULL)memcpy(&(dbary[i++]),db,sizeof(avl_dbase));/*sortthearray*///排序qsort(dbary,nelems,sizeof(avl_dbase),db_comparator);/**Determinethetimeintervalbetweendatabasesintheschedule.If*weseethattheconfigurednaptimewouldtakeustosleeptimes*lowerthanourminsleeptime(whichlauncher_determine_sleepis*codednottoallow),silentlyusealargernaptime(butdon'ttouch*theGUCvariable).*///确定数据库之间的调度间隔：autovacuum_naptime/数据库个数millis_increment=1000.0*autovacuum_naptime/nelems;if(millis_increment<=MIN_AUTOVAC_SLEEPTIME)millis_increment=MIN_AUTOVAC_SLEEPTIME*1.1;current_time=GetCurrentTimestamp();/**movetheelementsfromthearrayintothedllist,settingthe*next_workerwhilewalkingthearray*把数组中的元素移到dllist中，在遍历数组时设置next_worker*/for(i=0;i<nelems;i++){avl_dbase*db=&(dbary[i]);current_time=TimestampTzPlusMilliseconds(current_time,millis_increment);db->adl_next_worker=current_time;/*laterelementsshouldgoclosertotheheadofthelist*/dlist_push_head(&DatabaseList,&db->adl_node);}}/*alldone,cleanupmemory*/if(DatabaseListCxt!=NULL)MemoryContextDelete(DatabaseListCxt);MemoryContextDelete(tmpcxt);DatabaseListCxt=newcxt;MemoryContextSwitchTo(oldcxt);}三、跟踪分析

启动gdb,设置信号处理,设置断点

(gdb)brebuild_database_listBreakpoint1at0x82eb2a:fileautovacuum.c,line931.(gdb)handleSIGINTprintnostoppassSIGINTisusedbythedebugger.Areyousureyouwanttochangeit?(yorn)ySignalStopPrintPasstoprogramDescriptionSIGINTNoYesYesInterrupt(gdb)cContinuing.^CProgramreceivedsignalSIGINT,Interrupt.

进入断点

Breakpoint1,rebuild_database_list(newdb=0)atautovacuum.c:931931autovac_refresh_stats();(gdb)n933newcxt=AllocSetContextCreate(AutovacMemCxt,(gdb)936tmpcxt=AllocSetContextCreate(newcxt,(gdb)939oldcxt=MemoryContextSwitchTo(tmpcxt);(gdb)957hctl.keysize=sizeof(Oid);(gdb)958hctl.entrysize=sizeof(avl_dbase);(gdb)959hctl.hcxt=tmpcxt;

查看统计信息文件：pg_stat_tmp/global.stat

(gdb)p*pgstat_stat_filename$1=112'p'(gdb)ppgstat_stat_filename$2=0x203d7e0"pg_stat_tmp/global.stat"(gdb)n960dbhash=hash_create("dbhash",20,&hctl,/*magicnumberhereFIXME*/(gdb)###[pg12@localhostpg_stat_tmp]$pwd/data/pgsql/pg121db/pg_stat_tmp[pg12@localhostpg_stat_tmp]$lltotal4-rw-------1pg12pg12237Dec1116:40global.stat[pg12@localhostpg_stat_tmp]$

构建需处理的数据库链表

964score=0;(gdb)965if(OidIsValid(newdb))(gdb)p*hctlStructurehasnocomponentnamedoperator*.(gdb)phctl$3={num_partitions=140725872814104,ssize=34131296,dsize=32,max_dsize=0,ffactor=257,keysize=4,entrysize=40,hash=0xc6afd3,match=0x208cd60,keycopy=0x0,alloc=0x1,hcxt=0x2090d80,hctl=0xfe3a00<ConfigureNamesString+4864>}(gdb)n984dlist_foreach(iter,&DatabaseList)(gdb)p*DatabaseListStructurehasnocomponentnamedoperator*.(gdb)pDatabaseList$4={head={prev=0xfd9880<DatabaseList>,next=0xfd9880<DatabaseList>}}(gdb)n1010dblist=get_database_list();(gdb)1011foreach(cell,dblist)(gdb)p*dblist$5={type=T_List,length=7,head=0x2090ef8,tail=0x2091240}(gdb)p*dblist->head$6={data={ptr_value=0x2090e98,int_value=34147992,oid_value=34147992},next=0x2090fb0}(gdb)p*(Node*)dblist->head->data.ptr_value$7={type=13591}(gdb)p*dblist->head->data.ptr_valueAttempttodereferenceagenericpointer.(gdb)n1013avw_dbase*avdb=lfirst(cell);(gdb)

如没有统计信息，则不予处理

1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)p*avdb$8={adw_datid=13591,adw_name=0x2090ed0"postgres",adw_frozenxid=479,adw_minmulti=1,adw_entry=0x0}(gdb)n1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)p*avdb$9={adw_datid=16384,adw_name=0x2090f90"testdb",adw_frozenxid=2921,adw_minmulti=1,adw_entry=0x0}(gdb)steppgstat_fetch_stat_dbentry(dbid=16384)atpgstat.c:24382438backend_read_statsfile();(gdb)stepbackend_read_statsfile()atpgstat.c:56445644TimestampTzmin_ts=0;(gdb)n5645TimestampTzref_ts=0;(gdb)5650if(pgStatDBHash)(gdb)5651return;(gdb)5766}(gdb)pgstat_fetch_stat_dbentry(dbid=16384)atpgstat.c:24432443return(PgStat_StatDBEntry*)hash_search(pgStatDBHash,(gdb)2446}(gdb)rebuild_database_list(newdb=0)atautovacuum.c:10201020if(entry==NULL)(gdb)n1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1013avw_dbase*avdb=lfirst(cell);(gdb)1019entry=pgstat_fetch_stat_dbentry(avdb->adw_datid);(gdb)1020if(entry==NULL)(gdb)1011foreach(cell,dblist)(gdb)1032nelems=score;(gdb)1035MemoryContextSwitchTo(newcxt);(gdb)n1036dlist_init(&DatabaseList);(gdb)

所有数据库都不需要处理，返回

1038if(nelems>0)(gdb)pnelems$10=0(gdb)n1089if(DatabaseListCxt!=NULL)(gdb)1091MemoryContextDelete(tmpcxt);(gdb)1092DatabaseListCxt=newcxt;(gdb)1093MemoryContextSwitchTo(oldcxt);(gdb)1094}(gdb)AutoVacLauncherMain(argc=0,argv=0x0)atautovacuum.c:625625while(!got_SIGTERM)(gdb)

“怎么理解PostgreSQL的后台进程autovacuum”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！