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 20:04, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:

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

 

[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