The attached patch allows setting maintainance_work_mem or
autovacuum_work_mem higher than 1GB (and be effective), by turning the
allocation of the dead_tuples into a huge allocation.
This results in fewer index scans for heavily bloated tables, and
could be a lifesaver in many situations (in particular, the situation
I'm living right now in production, where we don't have enough room
for a vacuum full, and have just deleted 75% of a table to make room
but have to rely on regular lazy vacuum to free the space).
The patch also makes vacuum free the dead_tuples before starting
truncation. It didn't seem necessary to hold onto it beyond that
point, and it might help give the OS more cache, especially if work
mem is configured very high to avoid multiple index scans.
Tested with pgbench scale 4000 after deleting the whole
pgbench_accounts table, seemed to work fine.
From feddd4f58c30ca6a446ce0eed343f2562030f0e5 Mon Sep 17 00:00:00 2001
From: Claudio Freire <klaussfre...@gmail.com>
Date: Fri, 2 Sep 2016 23:21:01 -0300
Subject: [PATCH 1/2] Vacuum: allow using more than 1GB work mem
Turns the palloc for dead_tuples into a huge allocation to allow
using more than 1GB worth of dead_tuples, saving index scans on
heavily bloated tables
---
src/backend/commands/vacuumlazy.c | 19 +++++++++++++++++--
1 file changed, 17 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 231e92d..dbe2040 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -155,6 +155,7 @@ static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats);
static BlockNumber count_nondeletable_pages(Relation onerel,
LVRelStats *vacrelstats);
static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks);
+static void lazy_space_dealloc(LVRelStats *vacrelstats);
static void lazy_record_dead_tuple(LVRelStats *vacrelstats,
ItemPointer itemptr);
static bool lazy_tid_reaped(ItemPointer itemptr, void *state);
@@ -1310,6 +1311,8 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
for (i = 0; i < nindexes; i++)
lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ lazy_space_dealloc(vacrelstats);
+
/* If no indexes, make log report that lazy_vacuum_heap would've made */
if (vacuumed_pages)
ereport(elevel,
@@ -1952,7 +1955,7 @@ lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
{
maxtuples = (vac_work_mem * 1024L) / sizeof(ItemPointerData);
maxtuples = Min(maxtuples, INT_MAX);
- maxtuples = Min(maxtuples, MaxAllocSize / sizeof(ItemPointerData));
+ maxtuples = Min(maxtuples, MaxAllocHugeSize / sizeof(ItemPointerData));
/* curious coding here to ensure the multiplication can't overflow */
if ((BlockNumber) (maxtuples / LAZY_ALLOC_TUPLES) > relblocks)
@@ -1969,7 +1972,19 @@ lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
vacrelstats->num_dead_tuples = 0;
vacrelstats->max_dead_tuples = (int) maxtuples;
vacrelstats->dead_tuples = (ItemPointer)
- palloc(maxtuples * sizeof(ItemPointerData));
+ MemoryContextAllocHuge(CurrentMemoryContext, maxtuples * sizeof(ItemPointerData));
+}
+
+/*
+ * lazy_space_dealloc - free lazy vacuum work mem
+ */
+static void
+lazy_space_dealloc(LVRelStats *vacrelstats)
+{
+ if (vacrelstats->dead_tuples != NULL) {
+ pfree(vacrelstats->dead_tuples);
+ vacrelstats->dead_tuples = NULL;
+ }
}
/*
--
2.6.6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
