Re: psql leaks memory on query cancellation

On 12.04.2018 13:26, Darafei "Komяpa" Praliaskouski wrote:
> Hi,
>
> psql (PostgreSQL) 10.3
>
> Here are the steps to reproduce a leak:
>
> 1. connect to 10.3 server, perform the query similar to:
>
> select 'message' || generate_series(1,1000000000);
>
> 2. monitoring psql memory usage in htop or similar tool, press ctrl+c 
> at some point where you can clearly distinguish a psql with a big 
> allocated buffer from psql without it.
>
> 3. see the query cancelled, but psql memory usage stays the same.
>
> This is especially painful when query you're debugging has a runaway 
> join condition, and you understand it only after it doesn't return in 
> seconds as you've expected.
>
> Is it expected behavior (so I can have a look at something server 
> returned somehow and it's kept there for me), or a plain leak?
>
> Darafei Praliaskouski,
> GIS Engineer / Juno Minsk

It seems to be effect of glibc malloc which doesn't return deallocated 
memory to OS.
I attach gdb to psql and print memory usage before/after query interruption:

Arena 0:
system bytes     =  989835264
in use bytes     =  989811328
Total (incl. mmap):
system bytes     = 1258274816
in use bytes     = 1258250880
max mmap regions =          1
max mmap bytes   =  268439552

^CCancel request sent
ERROR:  canceling statement due to user request
postgres=# Arena 0:
system bytes     = 1284907008
in use bytes     =     278032
Total (incl. mmap):
system bytes     = 1284907008
in use bytes     =     278032
max mmap regions =          1
max mmap bytes   =  536875008

As you can seen there are 1.2 Gb of mapped memory, but only 270kb of it 
is used.
Unfortunately it is more or less expected behavior of malloc: it is 
assumed that if application consume a lot of memory at some moment of 
time and then release it,
then most likely it will reuse it again in future. So there is not so 
much sense to return it to OS. And it is really not easy to do so 
because of fragmentation.
You can not easily unmap this 1.2 Gb of mapped space if there is live 
objects may be just just few bytes  length allocated somewhere in this area.

The only solution is to use some memory contexts like in Postgres 
backends, but it requires complete rewriting of libpq. I am not sure 
that somebody will want to do it.


-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company