Re: postgres vacuum memory limits

On Sun, 1 Aug 2021 at 10:27, Ayub M <hiayub(at)gmail(dot)com> wrote:
>
> Hello, when maintenance_work_mem and autovacuum_work_mem are set, my
understanding is that the vacuum and autovacuum sessions should be limited
to use the memory limits set by these parameters. But I am seeing more
memory being used than these limits by autovacuum sessions, any reason why
this would happen?
>
> Please see below examples, where maintenance_work_mem is set to 20mb and
shared_buffers is 128mb. When I see the memory for this session in top, it
shows 162mb. But when default_statistics_target is increased to 3000, the
session usage is 463mb, which is way more than 20mb maintenance_work_mem
and 128mb shared_buffer. Shouldn't the process memory be capped to 20+128mb?
>
>
> postgres=# show maintenance_work_mem ;
> maintenance_work_mem
> ----------------------
> 20MB
> (1 row)
> postgres=# vacuum analyze mdm_context;
> VACUUM
> postgres=# show shared_buffers;
> shared_buffers
> ----------------
> 128MB
> (1 row)
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND

> 62246 postgres 20 0 422892 165996 139068 R 57.1 15.7 25:06.34
postgres: postgres postgres [local] VACUUM
>

your assumption may be right, but i am not sure of the interpretation from
top.
I have to admit I am not great at understanding top output (RES vs VIRT) in
general when it comes to limits.

I did a demo cgroup setup with limit max memory to 5MB, started psql using
cgexec and ran vacuum with maintenance_work_mem = 1024 (1MB)
it ran fine. I am not sharing the results, it may divert the convo.

The vacuum process seems to get dead tuples as a function of
maintenance_work_mem, and if it is small, it will loop many times (the run
may take longer)
but respect that limit (+- tolerance). also, vacuum verbose only prints CPU
usage per iteration of removing dead tupes from pages, but no mem usage. so
idk.

DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: vacuuming "public.t"
DEBUG: launched 1 parallel vacuum worker for index vacuuming (planned: 1)
DEBUG: scanned index "t_col1_idx" to remove 174518 row versions
DETAIL: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: starting parallel vacuum worker for bulk delete
DEBUG: scanned index "t_col1_idx1" to remove 174518 row versions
DETAIL: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
DEBUG: CommitTransaction(1) name: unnamed; blockState:
PARALLEL_INPROGRESS; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: "t": removed 174518 dead item identifiers in 1424 pages

i can be corrected, as i could not really get values from the source to
profile mem usage per function call.
from the source,
i have a feeling, shared_mem has nothing to do with vacuum, but i may be
wrong.

I think someone who is more aware of the process/code can throw more light.
but thanks for asking. will learn something figuring this out.

FROM THE CODE COMMENTS:
* The major space usage for LAZY VACUUM is storage for the array of dead
tuple
* TIDs. We want to ensure we can vacuum even the very largest relations
with
* finite memory space usage. To do that, we set upper bounds on the
number of
* tuples we will keep track of at once.
*
* We are willing to use at most maintenance_work_mem (or perhaps
* autovacuum_work_mem) memory space to keep track of dead tuples. We
* initially allocate an array of TIDs of that size, with an upper limit
that
* depends on table size (this limit ensures we don't allocate a huge area
* uselessly for vacuuming small tables). If the array threatens to
overflow,
* we suspend the heap scan phase and perform a pass of index cleanup and
page
* compaction, then resume the heap scan with an empty TID array.
*
* If we're processing a table with no indexes, we can just vacuum each page
* as we go; there's no need to save up multiple tuples to minimize the
number
* of index scans performed. So we don't use maintenance_work_mem memory
for
* the TID array, just enough to hold as many heap tuples as fit on one
page.
*
* Lazy vacuum supports parallel execution with parallel worker processes.
In
* a parallel vacuum, we perform both index vacuum and index cleanup with
* parallel worker processes. Individual indexes are processed by one
vacuum
* process. At the beginning of a lazy vacuum (at lazy_scan_heap) we
prepare
* the parallel context and initialize the DSM segment that contains shared
* information as well as the memory space for storing dead tuples. When
* starting either index vacuum or index cleanup, we launch parallel worker
* processes. Once all indexes are processed the parallel worker processes
* exit. After that, the leader process re-initializes the parallel context
* so that it can use the same DSM for multiple passes of index vacuum and
* for performing index cleanup. For updating the index statistics, we need
* to update the system table and since updates are not allowed during
* parallel mode we update the index statistics after exiting from the
* parallel mode.
*

vacuumlazy.c
<https://github.com/postgres/postgres/blob/70685385d70f8da73ab189a72f46311091ff09be/src/backend/access/heap/vacuumlazy.c#L12>
compute_max_dead_tuples
<https://github.com/postgres/postgres/blob/70685385d70f8da73ab189a72f46311091ff09be/src/backend/access/heap/vacuumlazy.c#L3457>
parallel_workers
<https://github.com/postgres/postgres/blob/70685385d70f8da73ab189a72f46311091ff09be/src/backend/access/heap/vacuumlazy.c#L3802>