Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g3CUQ-00087K-Dw for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Sep 2018 03:52:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g3CUO-00052Z-Ke for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Sep 2018 03:52:20 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g3CUN-00052N-Ut for pgsql-hackers@lists.postgresql.org; Fri, 21 Sep 2018 03:52:20 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g3CUJ-0003WD-Fy for pgsql-hackers@postgresql.org; Fri, 21 Sep 2018 03:52:18 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id B0CFA21C2B0C for ; Fri, 21 Sep 2018 06:52:12 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.0.103] (unknown [5.79.139.210]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 5554121C2B01 for ; Fri, 21 Sep 2018 06:52:12 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1537501932; bh=oFIqEhpZuyT8JYqPuQDyAEc3bt6H8fEcXYA+3HcVACs=; h=Subject:To:References:From:Date:In-Reply-To; b=n3Zwh9fDmtHbqPtqu54yPhhteoPdcvS9cW/hJcLxx/gpNC5sADwDjixYjEqTpXo/F ciEZPuF8h9Rsx43lWufX2HH+0C1uhQue032d6XtM8hDqaFAJ/vK5NxE0iN99HnPooq 3GpO+yrqUzirLWBoZJwHVhLYSFaLN2DLujkt5BKU= Subject: Re: [WIP] [B-Tree] Retail IndexTuple deletion To: PostgreSQL-Dev References: <425db134-8bba-005c-b59d-56e50de3b41e@postgrespro.ru> <9a008ab3-f591-e5e9-8e03-c0dd1dc13579@postgrespro.ru> <2ada5790-dffa-a41a-ef84-10f60a525ba6@postgrespro.ru> <7598602c-f329-a6aa-00e6-e3335ed06048@postgrespro.ru> From: Andrey Lepikhov Organization: Postgres Professional Message-ID: Date: Fri, 21 Sep 2018 08:52:10 +0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------013AA4D3A6299DE33B7E7C84" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------013AA4D3A6299DE33B7E7C84 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit The v6 version of quick vacuum, which utilizes the amtargetdelete() interface for retail indextuple deletion. Now it is more simple and laconic. It must be applied after Retail-IndexTuple-Deletion-Access-Method.patch. BENCHMARKS: ----------- Initial script: pgbench -i -s $scale # initial DB generation "CREATE INDEX pgbench_accounts_ext ON public.pgbench_accounts USING btree (abalance);" # additional index Comparison with lazy vacuum: script: "DELETE FROM pgbench_accounts WHERE (random() < $factor);" # delete a part of tuples for cleaning strategies comparison "VACUUM pgbench_accounts;" # check time of vacuum process by bash 'date +%s%N | cut -b1-13' command Results: | $scale=10 | $scale=100 | $factor| QVAC | LVAC | QVAC | LVAC | 1E-6 | - | - | 284 | 979 | 1E-5 | 78 | 144 | 288 | 1423 | 1E-4 | 72 | 280 | 388 | 3304 | 1E-3 | 189 | 609 | 2294 | 6029 | 1E-2 | 443 | 783 | 54232| 67884| 1E-1 | 1593 | 1237 | 83092| 86104| where QVAC - forced use of quick vacuum; LVAC - use lazy vacuum for index cleanup. $factor corresponds a number of vacuumed tuples. For example, $scale=10, $factor=1E-1 -> 100000 tuples vacuumed. Time measured in ms. So, quick strategy can be used in a vacuum process effectively up to 1-2% of DEAD tuples in a relation. -- Andrey Lepikhov Postgres Professional https://postgrespro.com The Russian Postgres Company --------------013AA4D3A6299DE33B7E7C84 Content-Type: text/x-patch; name="v6-0001-Quick-Vacuum-Strategy.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="v6-0001-Quick-Vacuum-Strategy.patch" From a5664f6fa42f615d1fb46250ac61aa9ac8a9d32d Mon Sep 17 00:00:00 2001 From: "Andrey V. Lepikhov" Date: Thu, 20 Sep 2018 13:04:26 +0500 Subject: [PATCH] Quick Vacuum Strategy --- src/backend/access/heap/heapam.c | 31 +++++++ src/backend/access/heap/pruneheap.c | 41 ++++++++-- src/backend/catalog/index.c | 122 ++++++++++++++++++++++++++++ src/backend/commands/vacuumlazy.c | 32 ++++++-- src/include/access/heapam.h | 2 + src/include/catalog/index.h | 4 + 6 files changed, 220 insertions(+), 12 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 56f1d82f96..0f0949bef3 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -9412,6 +9412,36 @@ heap_sync(Relation rel) } } +/* + * Mask DEAD tuples at a HEAP page + * + * We introduce this function for wal_consistency_checking satisfaction at + * Hot Standby node. + * + * DEAD tuple on a master node has t_cid and t_infomask, which can not be + * obtained by WAL records applying on a Hot Standby node. + */ +static void +mask_dead_tuples(Page page) +{ + OffsetNumber offnum; + + for (offnum = FirstOffsetNumber; offnum <= PageGetMaxOffsetNumber(page); offnum = OffsetNumberNext(offnum)) + { + ItemId lp = PageGetItemId(page, offnum); + char* htup_begin; + + if (!ItemIdIsDead(lp)) + continue; + + if (!ItemIdHasStorage(lp)) + continue; + + htup_begin = (char *) PageGetItem(page, lp); + memset(htup_begin, MASK_MARKER, ItemIdGetLength(lp)); + } +} + /* * Mask a heap page before performing consistency checks on it. */ @@ -9425,6 +9455,7 @@ heap_mask(char *pagedata, BlockNumber blkno) mask_page_hint_bits(page); mask_unused_space(page); + mask_dead_tuples(page); for (off = 1; off <= PageGetMaxOffsetNumber(page); off++) { diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c index c2f5343dac..f66e5d0260 100644 --- a/src/backend/access/heap/pruneheap.c +++ b/src/backend/access/heap/pruneheap.c @@ -43,6 +43,9 @@ typedef struct bool marked[MaxHeapTuplesPerPage + 1]; } PruneState; +/* Parameter for target deletion strategy in lazy vacuum */ +double target_index_deletion_factor = 0.01; + /* Local functions */ static int heap_prune_chain(Relation relation, Buffer buffer, OffsetNumber rootoffnum, @@ -405,10 +408,9 @@ heap_prune_chain(Relation relation, Buffer buffer, OffsetNumber rootoffnum, if (HeapTupleSatisfiesVacuum(&tup, OldestXmin, buffer) == HEAPTUPLE_DEAD && !HeapTupleHeaderIsHotUpdated(htup)) { - heap_prune_record_unused(prstate, rootoffnum); + heap_prune_record_dead(prstate, rootoffnum); HeapTupleHeaderAdvanceLatestRemovedXid(htup, &prstate->latestRemovedXid); - ndeleted++; } /* Nothing more to do */ @@ -580,8 +582,10 @@ heap_prune_chain(Relation relation, Buffer buffer, OffsetNumber rootoffnum, */ for (i = 1; (i < nchain) && (chainitems[i - 1] != latestdead); i++) { - heap_prune_record_unused(prstate, chainitems[i]); + if (chainitems[i] == latestdead) + continue; ndeleted++; + heap_prune_record_unused(prstate, chainitems[i]); } /* @@ -598,9 +602,28 @@ heap_prune_chain(Relation relation, Buffer buffer, OffsetNumber rootoffnum, * redirect the root to the correct chain member. */ if (i >= nchain) + { + if (rootoffnum != latestdead) + { + if (ItemIdIsNormal(rootlp)) + heap_prune_record_unused(prstate, latestdead); + else + { + /* + * We allow overlapping of redirected and dead items + */ + heap_prune_record_redirect(prstate, rootoffnum, latestdead); + heap_prune_record_dead(prstate, latestdead); + } + } heap_prune_record_dead(prstate, rootoffnum); + } else + { + if (rootoffnum != latestdead) + heap_prune_record_unused(prstate, latestdead); heap_prune_record_redirect(prstate, rootoffnum, chainitems[i]); + } } else if (nchain < 2 && ItemIdIsRedirected(rootlp)) { @@ -653,7 +676,12 @@ heap_prune_record_dead(PruneState *prstate, OffsetNumber offnum) Assert(prstate->ndead < MaxHeapTuplesPerPage); prstate->nowdead[prstate->ndead] = offnum; prstate->ndead++; - Assert(!prstate->marked[offnum]); + + /* + * We suppress checking prstate->marked[offnum]. It is not the best idea, + * but this is most simplistic way to enable Dead Redirecting by + * overlapping Dead and Redirected states. + */ prstate->marked[offnum] = true; } @@ -706,7 +734,10 @@ heap_page_prune_execute(Buffer buffer, OffsetNumber off = *offnum++; ItemId lp = PageGetItemId(page, off); - ItemIdSetDead(lp); + if (target_index_deletion_factor > 0) + ItemIdMarkDead(lp); + else + ItemIdSetDead(lp); } /* Update all now-unused line pointers */ diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 7eb3e35166..15648c49c5 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -4160,3 +4160,125 @@ RestoreReindexState(void *reindexstate) sistate->pendingReindexedIndexes[c]); MemoryContextSwitchTo(oldcontext); } + + +/* + * htid2itup - get datum for index tuple assembly. + * + * Returns false, if Index Datum can't be generated by current TID + */ +bool +htid2IndexDatum(Relation hrel, Relation irel, ItemPointer htid, Datum *values, bool *isnull) +{ + IndexInfo *indexInfo = BuildIndexInfo(irel); + EState *estate = CreateExecutorState(); + ExprContext *econtext = GetPerTupleExprContext(estate); + ExprState *predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate); + TupleTableSlot *slot = MakeSingleTupleTableSlot(RelationGetDescr(hrel)); + HeapTuple tuple; + Buffer buffer; + Page page; + OffsetNumber offnum = ItemPointerGetOffsetNumber(htid); + ItemId lp; + BlockNumber npages; + + npages = RelationGetNumberOfBlocks(hrel); + + if (ItemPointerGetBlockNumber(htid) > npages) + return false; + + buffer = ReadBuffer(hrel, ItemPointerGetBlockNumber(htid)); + page = (Page) BufferGetPage(buffer); + lp = PageGetItemId(page, offnum); + + Assert(ItemIdIsUsed(lp)); + + /* Walk along the chain */ + while (!ItemIdHasStorage(lp)) + { + offnum = ItemIdGetRedirect(lp); + lp = PageGetItemId(page, offnum); + Assert(ItemIdIsUsed(lp)); + } + + /* Form a tuple */ + tuple = palloc(sizeof(HeapTupleData)); + ItemPointerSet(&(tuple->t_self), BufferGetBlockNumber(buffer), offnum); + tuple->t_tableOid = RelationGetRelid(hrel); + tuple->t_data = (HeapTupleHeader) PageGetItem(page, lp); + tuple->t_len = ItemIdGetLength(lp); + ReleaseBuffer(buffer); + + econtext->ecxt_scantuple = slot; + MemoryContextReset(econtext->ecxt_per_tuple_memory); + ExecStoreTuple(tuple, slot, InvalidBuffer, false); + + /* + * In a partial index, ignore tuples that don't satisfy the + * predicate. + */ + if ((predicate != NULL) && (!ExecQual(predicate, econtext))) + { + pfree(tuple); + ExecDropSingleTupleTableSlot(slot); + FreeExecutorState(estate); + return false; + } + + FormIndexDatum(indexInfo, slot, estate, values, isnull); + + pfree(tuple); + ExecDropSingleTupleTableSlot(slot); + FreeExecutorState(estate); + return true; +} + +/* + * quick_vacuum_index() -- quick vacuum one index relation. + * + * Delete all the index entries pointing to tuples listed in + * dead_tuples. + */ +void +quick_vacuum_index(Relation irel, Relation hrel, + ItemPointer dead_tuples, + int num_dead_tuples) +{ + int tnum; + bool *found = palloc0(num_dead_tuples*sizeof(bool)); + IndexTargetDeleteResult stats; + IndexTargetDeleteInfo ivinfo; + + Assert(found != NULL); + + stats.tuples_removed = 0; + ivinfo.indexRelation = irel; + ivinfo.heapRelation = hrel; + + /* Get tuple from heap */ + for (tnum = num_dead_tuples-1; tnum >= 0; tnum--) + { + Datum values[INDEX_MAX_KEYS]; + bool isnull[INDEX_MAX_KEYS]; + + /* Index entry for the TID was deleted early */ + if (found[tnum]) + continue; + + /* Get an index tuple building data by heap tid */ + if (!htid2IndexDatum(hrel, irel, &(dead_tuples[tnum]), values, isnull)) + continue; + + /* + * Make attempt to delete some index entries by one tree descent. + * We use only a part of TID list, which contains not found TID's. + */ + ivinfo.dead_tuples = dead_tuples; + ivinfo.last_dead_tuple = tnum; + ivinfo.found_dead_tuples = found; + + index_target_delete(&ivinfo, &stats, values, isnull); + } + + pfree(found); +} diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index 8996d366e9..f0b0a2ffb9 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -41,9 +41,11 @@ #include "access/heapam_xlog.h" #include "access/htup_details.h" #include "access/multixact.h" +#include "access/nbtree.h" #include "access/transam.h" #include "access/visibilitymap.h" #include "access/xlog.h" +#include "catalog/index.h" #include "catalog/storage.h" #include "commands/dbcommands.h" #include "commands/progress.h" @@ -744,9 +746,18 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats, /* Remove index entries */ for (i = 0; i < nindexes; i++) - lazy_vacuum_index(Irel[i], - &indstats[i], - vacrelstats); + { + bool use_quick_strategy = (vacrelstats->num_dead_tuples/vacrelstats->old_live_tuples < target_index_deletion_factor); + + if (use_quick_strategy && (Irel[i]->rd_amroutine->amtargetdelete != NULL)) + quick_vacuum_index(Irel[i], onerel, + vacrelstats->dead_tuples, + vacrelstats->num_dead_tuples); + else + lazy_vacuum_index(Irel[i], + &indstats[i], + vacrelstats); + } /* * Report that we are now vacuuming the heap. We also increase @@ -1389,10 +1400,18 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats, /* Remove index entries */ for (i = 0; i < nindexes; i++) - lazy_vacuum_index(Irel[i], - &indstats[i], - vacrelstats); + { + bool use_quick_strategy = (vacrelstats->num_dead_tuples/vacrelstats->old_live_tuples < target_index_deletion_factor); + if (use_quick_strategy && (Irel[i]->rd_amroutine->amtargetdelete != NULL)) + quick_vacuum_index(Irel[i], onerel, + vacrelstats->dead_tuples, + vacrelstats->num_dead_tuples); + else + lazy_vacuum_index(Irel[i], + &indstats[i], + vacrelstats); + } /* Report that we are now vacuuming the heap */ hvp_val[0] = PROGRESS_VACUUM_PHASE_VACUUM_HEAP; hvp_val[1] = vacrelstats->num_index_scans + 1; @@ -1681,7 +1700,6 @@ lazy_check_needs_freeze(Buffer buf, bool *hastup) return false; } - /* * lazy_vacuum_index() -- vacuum one index relation. * diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index ca5cad7497..cba8130422 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -100,6 +100,8 @@ extern Relation heap_openrv_extended(const RangeVar *relation, typedef struct HeapScanDescData *HeapScanDesc; typedef struct ParallelHeapScanDescData *ParallelHeapScanDesc; +extern double target_index_deletion_factor; + /* * HeapScanIsValid * True iff the heap scan is valid. diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index f20c5f789b..6b1d726338 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -153,5 +153,9 @@ extern void SerializeReindexState(Size maxsize, char *start_address); extern void RestoreReindexState(void *reindexstate); extern void IndexSetParentIndex(Relation idx, Oid parentOid); +extern bool htid2IndexDatum(Relation hrel, Relation irel, ItemPointer htid, Datum *values, bool *isnull); +extern void quick_vacuum_index(Relation irel, Relation hrel, + ItemPointer dead_tuples, + int num_dead_tuples); #endif /* INDEX_H */ -- 2.17.1 --------------013AA4D3A6299DE33B7E7C84--