Shulou(Shulou.com)05/31 Report--

This article focuses on "vacuum process Analysis in PostgreSQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "vacuum process Analysis in PostgreSQL".

I. data structure

Macro definition

Vacuum and Analyze command options

/ *-- * Vacuum and Analyze Statements * Vacuum and Analyze command options * * Even though these are nominally two statements, it's convenient to use * just one node type for both. Note that at least one of VACOPT_VACUUM * and VACOPT_ANALYZE must be set in options. Although there are two different statements here, you only need to use a uniform Node type. * Note that at least VACOPT_VACUUM/VACOPT_ANALYZE is set in the options. *-- * / typedef enum VacuumOption {VACOPT_VACUUM = 11) use_own_xacts = true; else use_own_xacts = false;} / * * vacuum_rel expects to be entered with no transaction active; it will * start and commit its own transaction. But we are called by an SQL * command, and so we are executing inside a transaction already. We * commit the transaction started in PostgresMain () here, and start * another one before exiting to match the commit waiting for us back in * PostgresMain (). * you do not want to have transactional activities before entering vacuum_rel. This function starts and commits its own transaction. * but since we are called through the SQL command, we are already executing in a transaction. Here we commit the transaction started in PostgresMain () and start another one before exiting to match our commit in PostgresMain (). * / if (use_own_xacts) {Assert (! in_outer_xact); / * ActiveSnapshot is not set by autovacuum * / / autovacuum does not set ActiveSnapshot if (ActiveSnapshotSet ()) PopActiveSnapshot (); / * matches the StartTransaction in PostgresMain () * / / matches StartTransaction CommitTransactionCommand () in PostgresMain () } / * Turn vacuum cost accounting on or off, and set/clear in_vacuum * / / set vacuum cost count on/off and set/clear in_vacuum parameter PG_TRY (); {ListCell * cur; in_vacuum = true; VacuumCostActive = (VacuumCostDelay > 0); VacuumCostBalance = 0; VacuumPageHit = 0; VacuumPageMiss = 0; VacuumPageDirty = 0 / * Loop to process each selected relation. * Loop each selected relation. * / foreach (cur, relations) {VacuumRelation * vrel = lfirst_node (VacuumRelation, cur); if (options & VACOPT_VACUUM) {/ / execute vacuum processing if (! vacuum_rel (vrel- > oid, vrel- > relation, options, params) continue } if (options & VACOPT_ANALYZE) {/ * * If using separate xacts, start one for analyze. Otherwise, * we can use the outer transaction. * if you use a separate xacts, start a transaction for analyze. * otherwise, we can use outer transactions. * / if (use_own_xacts) {/ / use your own transaction StartTransactionCommand (); / * functions in indexes may want a snapshot set * / / Snapshot stack PushActiveSnapshot (GetTransactionSnapshot ()) } / / analyze relation analyze_rel (vrel- > oid, vrel- > relation, options, params, vrel- > va_cols, in_outer_xact, vac_strategy) If (use_own_xacts) {/ / use your own transaction, unstack PopActiveSnapshot (); / / commit transaction CommitTransactionCommand ();}} PG_CATCH () {in_vacuum = false; VacuumCostActive = false; PG_RE_THROW ();} PG_END_TRY (); in_vacuum = false; VacuumCostActive = false; / * * Finish up processing. * complete the process * / if (use_own_xacts) {/ * here, we are not in a transaction * / / here, there is no transaction / * * This matches the CommitTransaction waiting for us in * PostgresMain (). * match the CommitTransaction waiting for us in the PostgresMain () function. * / StartTransactionCommand ();} if ((options & VACOPT_VACUUM) & &! IsAutoVacuumWorkerProcess ()) {/ * * Update pg_database.datfrozenxid, and truncate pg_xact if possible. * (autovacuum.c does this for itself.) * update pg_database.datfrozenxid and truncate pg_xact if possible. * (autovacuum.c won't handle this) * / vac_update_datfrozenxid ();} / * Clean up working storage-note we must do this after * StartTransactionCommand, else we might be trying to delete the active * context! * clear the working storage-Note that the cleanup process must be performed after the StartTransactionCommand command, * otherwise we may try to delete the context of the activity. * / MemoryContextDelete (vac_context); vac_context = NULL;} 3. Tracking analysis

Test script

17:19:28 (xdb@ [local]: 5432) testdb=# vacuum T1

Start gdb and set breakpoint

(gdb) b vacuumBreakpoint 1 at 0x6b9b8c: file vacuum.c, line 175. (gdb) cContinuing.Breakpoint 1, vacuum (options=1, relations=0x2294988, params=0x7fff403d8880, bstrategy=0x0, isTopLevel=true) at vacuum.c:175175 Assert (params! = NULL); (gdb)

Input parameters

Options=1-> VACOPT_VACUUM

Relations=0x2294988,relation linked list with only one item, T1

Params=0x7fff403d8880, default parameter

Bstrategy=NULL

IsTopLevel=T, a top-level transaction

(gdb) p * params$2 = {freeze_min_age =-1, freeze_table_age =-1, multixact_freeze_min_age =-1, multixact_freeze_table_age =-1, is_wraparound = false, log_min_duration =-1} (gdb)

Assign variables and execute relevant judgments

(gdb) n177 stmttype = (options & VACOPT_VACUUM)? "VACUUM": "ANALYZE"; (gdb) 187if (options & VACOPT_VACUUM) (gdb) 189PreventInTransactionBlock (isTopLevel, stmttype); (gdb) 190in_outer_xact = false; (gdb) 200if (in_vacuum) (gdb) 209if ((options & VACOPT_FULL)! = 0 & & (gdb)

Statistical information

219 if ((options & VACOPT_VACUUM) & &! IsAutoVacuumWorkerProcess ()) (gdb) 220 pgstat_vacuum_stat (); (gdb)

Create and set the memory context

(gdb) n228 vac_context = AllocSetContextCreate (PortalContext, (gdb) 236 if (bstrategy = = NULL) (gdb) 238 MemoryContext old_context = MemoryContextSwitchTo (vac_context); (gdb) 240bstrategy = GetAccessStrategy (BAS_VACUUM); (gdb) 241MemoryContextSwitchTo (old_context); (gdb) 243vac_strategy = bstrategy; (gdb) 249if (relations! = NIL) (gdb)

Construct VacuumRelation linked list

(gdb) 251 List * newrels = NIL; (gdb) 254foreach (lc, relations) (gdb) 256VacuumRelation * vrel = lfirst_node (VacuumRelation, lc); (gdb) 260sublist = expand_vacuum_rel (vrel) (gdb) p * vrel$3 = {type = T_VacuumRelation, relation = 0x22948d0, oid = 0, va_cols = 0x0} (gdb) p * vrel- > relation$4 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0, relname = 0x22948b0 "T1", inh = true, relpersistence = 112 'packs, alias = 0x0, location = 7} (gdb) (gdb) n261 old_context = MemoryContextSwitchTo (vac_context); (gdb) 262 newrels = list_concat (newrels, sublist) (gdb) 263 MemoryContextSwitchTo (old_context); (gdb) 254 foreach (lc, relations) (gdb) 265 relations = newrels; (gdb)

Use autonomous transactions

If (options & VACOPT_VACUUM) (gdb) 285 use_own_xacts = true; (gdb) 307 if (use_own_xacts) (gdb) 307 if (use_own_xacts) (gdb) 309 Assert (! in_outer_xact); (gdb) 312 if (ActiveSnapshotSet ()) (gdb) 313 PopActiveSnapshot (); (gdb) 316 CommitTransactionCommand (); (gdb) 320 PG_TRY (); (gdb)

Start execution, set vacuum cost count on/off, and set/clear in_vacuum parameter

(gdb) 324 in_vacuum = true; (gdb) 325 VacuumCostActive = (VacuumCostDelay > 0); (gdb) 326 VacuumCostBalance = 0; (gdb) 327 VacuumPageHit = 0; (gdb) 328 VacuumPageMiss = 0; (gdb) 329 VacuumPageDirty = 0; (gdb)

Loop relation, call vacuum_rel

334 foreach (cur, relations) (gdb) 336 VacuumRelation * vrel = lfirst_node (VacuumRelation, cur); (gdb) 338 if (options & VACOPT_VACUUM) (gdb) 340 if (! vacuum_rel (vrel- > oid, vrel- > relation, options, params)) (gdb) 344 if (options & VACOPT_ANALYZE) (gdb) 334 foreach (cur, relations) (gdb) 374 PG_END_TRY () (gdb)

Carry out the finishing touches

(gdb) 376 in_vacuum = false; (gdb) 377 VacuumCostActive = false; (gdb) 382 if (use_own_xacts) (gdb) 390 StartTransactionCommand (); (gdb) 393 if ((options & VACOPT_VACUUM) & &! IsAutoVacuumWorkerProcess ()) (gdb) 399 vac_update_datfrozenxid (); (gdb) 407 MemoryContextDelete (vac_context); (gdb) 408 vac_context = NULL; (gdb)

Complete the call

(gdb) ExecVacuum (vacstmt=0x22949c0, isTopLevel=true) at vacuum.c:142142} (gdb) so far, I believe you have a deeper understanding of "vacuum process analysis in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.