From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Vacuum: allow usage of more than 1GB of work mem |
Date: | 2016-09-06 16:42:25 |
Message-ID: | CA+TgmobscwsCCBMP6-gKGi39p+yUOj6O6OwWckAZxzShKicQ7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sat, Sep 3, 2016 at 8:55 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> 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.
The problem with this is that we allocate the entire amount of
maintenance_work_mem even when the number of actual dead tuples turns
out to be very small. That's not so bad if the amount of memory we're
potentially wasting is limited to ~1 GB, but it seems pretty dangerous
to remove the 1 GB limit, because somebody might have
maintenance_work_mem set to tens or hundreds of gigabytes to speed
index creation, and allocating that much space for a VACUUM that
encounters 1 dead tuple does not seem like a good plan.
What I think we need to do is make some provision to initially
allocate only a small amount of memory and then grow the allocation
later if needed. For example, instead of having
vacrelstats->dead_tuples be declared as ItemPointer, declare it as
ItemPointer * and allocate the array progressively in segments. I'd
actually argue that the segment size should be substantially smaller
than 1 GB, like say 64MB; there are still some people running systems
which are small enough that allocating 1 GB when we may need only 6
bytes can drive the system into OOM.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2016-09-06 16:47:49 | Re: Optimization for lazy_scan_heap |
Previous Message | Tom Lane | 2016-09-06 16:41:48 | Re: Bug in 9.6 tuplesort batch memory growth logic |