On Sat, Sep 3, 2016 at 8:55 AM, Claudio Freire <klaussfre...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
