Thread: postgres vacuum memory limits
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
postgres=# show default_statistics_target; default_statistics_target
--------------------------- 100
(1 row)
postgres=# set default_statistics_target=3000;
SET
postgres=# vacuum analyze mdm_context;
VACUUM
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 62246 postgres 20 0 876132 474384 2976 R 62.9 47.6 25:11.41 postgres: postgres postgres [local] VACUUM
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.
*
"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.