postgres vacuum memory limits

On Saturday, July 31, 2021, Ayub M <hiayub@gmail.com> wrote:

But when default_statistics_target is increased to 3000, the session usage
is 463mb

IIUC, the analyze process doesn’t consult maintenance_work_mem. It simply
creates an array, in memory, to hold the random sample of rows needed for
computing the requested statistics.

I skimmed the docs but didn’t get a firm answer beyond the fact that vacuum
is an example command that consult maintenance_work_mem and analyze is not
mentioned in the same list. I did find:

‘The largest statistics target among the columns being analyzed determines
the number of table rows sampled to prepare the statistics. Increasing the
target causes a proportional increase in the time and space needed to do
ANALYZE.”
David J.

On Sun, 1 Aug 2021 at 10:27, Ayub M <hiayub@gmail.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&gt;
compute_max_dead_tuples
<https://github.com/postgres/postgres/blob/70685385d70f8da73ab189a72f46311091ff09be/src/backend/access/heap/vacuumlazy.c#L3457&gt;
parallel_workers
<https://github.com/postgres/postgres/blob/70685385d70f8da73ab189a72f46311091ff09be/src/backend/access/heap/vacuumlazy.c#L3802&gt;

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Saturday, July 31, 2021, Ayub M <hiayub@gmail.com> wrote:

But when default_statistics_target is increased to 3000, the session usage
is 463mb

IIUC, the analyze process doesn’t consult maintenance_work_mem. It simply
creates an array, in memory, to hold the random sample of rows needed for
computing the requested statistics.

Yeah. A sample of N rows of the table is going to take X amount of
memory; playing with [maintenance_]work_mem isn't going to affect that.
If you're not happy with the memory consumption, the statistics target
is exactly the knob that's provided to adjust that.

In an ideal world maybe ANALYZE could work within a memory budget that's
smaller than the sample size, but I think that'd inevitably involve a
lot more I/O and significantly worse performance than what we do now.
In any case it'd require a massive rewrite that breaks a lot of
extensions, since the per-datatype APIs for ANALYZE presume in-memory
data.

Keep in mind also that large statistics targets translate to bloat
everywhere else too, since that implies larger pg_statistic entries
for the planner to consult. So I'm not sure that focusing on ANALYZE's
usage in isolation is a helpful way to think about this. If you can't
afford the amount of memory needed to run ANALYZE, you won't like the
downstream behavior either.

regards, tom lane

On Sun, 1 Aug 2021 at 20:04, Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:

On Sun, 1 Aug 2021 at 10:27, Ayub M <hiayub@gmail.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 just tried a couple of more things, maybe it helps.

every connection ( like psql in interactive mode ) has an overhead of
around 10MB.

postgres=# set maintenance_work_mem TO 1024;
SET
postgres=# -- 11284 this was showing in my RES mem on a fresh connection
postgres=# do $$
begin
for i in 1..20 loop
update t set col1 = col1 || i::text;
commit;
end loop;
end; $$;
DO
postgres=# -- 394924 this was showing in my RES mem on a connection that
did large updates, adding to connection cache ?
postgres=# vacuum t;
VACUUM
postgres=# -- 395852 this was showing in my RES mem on a connection that
did vacuum, although the value is around 395M,
-- but vacuum only took around ~ 1M when
maintenance_work_mem was set to 1024 (1MB)

PostgreSQL connections are process based, and a lot goes into what is held
into the memories right since init, i did a pmap and lsof on the process id,
it touches a lot of files in datadir/base and datadir/global, basically the
pages touched during the session activities.

also there is a huge chunk allocated to
00007f233b839000 2164816K rw-s- /dev/zero (deleted)
which I think is mmap to /dev/zero that contents have been deleted, but the
connection has to be closed to reclaim that space. idk

https://rhaas.blogspot.com/2012/01/linux-memory-reporting.html?m=1

I think this awesome blog will clear a lot of 'understanding of top' output
in postgresql context of memory growth.