Search Postgresql Archives

Re: postgres vacuum memory limits

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Sun, 1 Aug 2021 at 10:27, Ayub M <hiayub@xxxxxxxxx> 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.
 *

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux