MIME-Version: 1.0
References: <20240516205458.ohvlzis5b5tvejru@awork3.anarazel.de> <CAD21AoAkdZN1+kbtrV3vjE2kxRzL_2mYziQWQX0vk-kC-9vOiA@mail.gmail.com>
In-Reply-To: <CAD21AoAkdZN1+kbtrV3vjE2kxRzL_2mYziQWQX0vk-kC-9vOiA@mail.gmail.com>
From: John Naylor <johncnaylorls@gmail.com>
Date: Mon, 20 May 2024 13:05:32 +0700
Message-ID: <CANWCAZZUTvZ3LsYpauYQVzcEZXZ7Qe+9ntnHgYZDTWxPuL++zA@mail.gmail.com>
Subject: Re: Lowering the minimum value for maintenance_work_mem
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, pgsql-hackers@postgresql.org, 
	John Naylor <jcnaylor@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CANWCAZZUTvZ3LsYpauYQVzcEZXZ7Qe%2B9ntnHgYZDTWxPuL%2B%2BzA%40mail.gmail.com>
Precedence: bulk

On Mon, May 20, 2024 at 11:59=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
>
> On Fri, May 17, 2024 at 5:55=E2=80=AFAM Andres Freund <andres@anarazel.de=
> wrote:

> > 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.

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 =3D '256kB';

drop table if exists test;
create unlogged table test (a int) with (autovacuum_enabled=3Dfalse);
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