From: | Simon Riggs <simon(at)2ndquadrant(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-04 06:46:26 |
Message-ID: | CANP8+jLMHoMfSbbJqOcgCTA=yfxUHrXy+U-r6eRgrrx7jmUUmA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 3 September 2016 at 04:25, 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).
This part looks fine. I'm inclined to commit the attached patch soon.
> 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.
How long does that part ever take? Is there any substantial gain from this?
Lets discuss that as a potential second patch.
> Tested with pgbench scale 4000 after deleting the whole
> pgbench_accounts table, seemed to work fine.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment | Content-Type | Size |
---|---|---|
allow_vacuum_work_mem_gt_1GB.v2.patch | application/octet-stream | 1.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-09-04 08:06:55 | Re: patch: function xmltable |
Previous Message | Michael Paquier | 2016-09-04 06:38:13 | Re: LSN as a recovery target |