Subject: Re: Lowering the minimum value for maintenance_work_mem

From: Masahiko Sawada <[email-masked]>
Date: Mon, 20 May 2024 13:58:28 +0900

On Fri, May 17, 2024 at 5:55 AM Andres Freund <[email-masked]> wrote:
>
> Hi,
>
> In the subthread at [1] I needed to trigger multiple rounds of index vacuuming
> within one vacuum.
>
> It turns out that with the new dead tuple implementation, that got actually
> somewhat expensive. Particularly if all tuples on all pages get deleted, the
> representation is just "too dense". Normally that's obviously very good, but
> for testing, not so much:
>
> With the minimum setting of maintenance_work_mem=1024kB, a simple table with
> narrow rows, where all rows are deleted, the first cleanup happens after
> 3697812 dead tids. The table for that has to be > ~128MB.
>
> Needing a ~128MB table to be able to test multiple cleanup passes makes it
> much more expensive to test and consequently will lead to worse test coverage.
>
> I think we should consider lowering the minimum setting of
> maintenance_work_mem to the minimum of work_mem.

+1 for lowering the minimum value of maintenance_work_mem. I've faced
the same situation.

Even if a shared tidstore is empty, TidStoreMemoryUsage() returns
256kB because it's the minimum segment size of DSA, i.e.
DSA_MIN_SEGMENT_SIZE. So we can lower the minimum maintenance_work_mem
down to 256kB, from a vacuum perspective.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: John Naylor <[email-masked]>
Date: Mon, 20 May 2024 13:05:32 +0700

> down to 256kB, from a vacuum perspective.

I've verified 256kB works with both local and shared memory with the
below commands, and 200k records are enough to cause a second round of
index cleanup. I don't think we can go much smaller than that without
changing how we size the blocks in the node slab contexts (or when
they're created), which is currently somewhat arbitrary. That'll need
some thought, at least when we get a use case with work_mem as the
limit.

set maintenance_work_mem = '256kB';

drop table if exists test;
create unlogged table test (a int) with (autovacuum_enabled=false);
insert into test (a) select i from generate_series(1,200_000) i;
create index on test (a);
--create index on test (a); -- toggle for parallel vacuum

delete from test;
vacuum (verbose) test;

Side note: I'm confused why shared memory works at all in this case,
since it failed for 1MB init segments until we allowed callers to
specify a smaller init size. The overhead for DSA seems to be
significant for small sizes, as evidenced from the amount of usable
memory:

shared:
INFO:  finished vacuuming "john.public.test": index scans: 56

local:
INFO:  finished vacuuming "john.public.test": index scans: 2