Re: psql leaks memory on query cancellation

[Craig Ringer]

On 13 April 2018 at 05:10, Tom Lane  wrote:

> Therefore, I propose the attached patch, which simply sees to it that
> we discard any partial query result at the start of error message
> collection not the end.  This makes the behavior very much better,
> at least on Linux.
>
> I think this is a back-patchable bug fix; certainly so at least back
> to 9.6 where \errverbose was added.  Versions before that do not show
> the persistent memory bloat the OP is complaining of, so that what
> we have here is arguably a performance regression.  Comments?

That seems reasonable.

It's all dependent on tons of internal details of how libc decides to
lay out memory, how it gets memory from the kernel, etc. On some
platforms that "island" won't affect anything anyway, and on others I
expect that freeing it probably still won't release memory back to the
OS. But if it helps on even one common platform and has no downsides,
it's well worth it.

BTW, https://stackoverflow.com/q/2215259/398670 is relevant.

This makes me wonder if we could do something cheeky like mmap
MAP_ANONYMOUS'ing /dev/zero ourselves, giving ourselves a separate
memory arena for result sets that we can reset when we're done. We
don't know which result sets will be big though, so it hardly seems
worth it.

>
> I looked into that too, and found that what I was seeing was that
> if I exit my pager with "q", psql continues to run, formatting the
> enormous query result and writing it to nowhere.

Huh. I'd noticed that behaviour, but it never crossed my care
threshold. I think I tend to do just what you describe below
habitually.

>  Since we've got
> SIGPIPE disabled, nothing happens to stop it; although if you have
> the presence of mind to press control-C, you do get control back
> pretty quickly.  I wonder whether fe_utils/print.c ought to be
> adjusted to stop on output errors, along the lines of
>
> -   if (cancel_pressed)
> +   if (cancel_pressed || ferror(fout))
> break;
>
> However, the implications of that aren't entirely clear, so I merely
> suggest that as a topic for research not an immediately proposed patch.

Definitely separate.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: psql leaks memory on query cancellation

[Komяpa]

>
> Therefore, I propose the attached patch, which simply sees to it that
> we discard any partial query result at the start of error message
> collection not the end.  This makes the behavior very much better,
> at least on Linux.
>

I have tested the build of Postgres with attached patch and confirm that I
don't see this problematic behavior anymore even with default allocator.
Thank you!

I think this is a back-patchable bug fix; certainly so at least back
> to 9.6 where \errverbose was added.  Versions before that do not show
> the persistent memory bloat the OP is complaining of, so that what
> we have here is arguably a performance regression.  Comments?
>

This should not bring regression, since the memory is freed anyway, but is
valuable as puts less pressure on client memory requirements for manual
data inspection workflows.

Darafei Praliaskouski,
GIS Engineer / Juno Minsk

Re: psql leaks memory on query cancellation

[Teodor Sigaev]

I imagine that this indicates that control-C processing allocates some
memory it doesn't free, resulting in an "island" up at the end of memory
that prevents glibc from releasing all the free memory it's got.  Whether
that's an actual leak, or just memory we're holding onto in hopes of
reusing it, isn't clear.  (valgrind might be useful.)


malloc could request memory from kernel by two ways: sbrk() and mmap(), 
first one has described problem, mmap hasn't. It's described in 
mallopt(3) in section M_MMAP_THRESHOLD, to test that try to repeat test 
with MALLOC_MMAP_THRESHOLD_ environment  set to 8192.



--
Teodor Sigaev  E-mail: teo...@sigaev.ru
  WWW: http://www.sigaev.ru/

Re: psql leaks memory on query cancellation

[Tom Lane]

Craig Ringer  writes:
> On 12 April 2018 at 18:26, Darafei "Komяpa" Praliaskouski  
> wrote:
>> 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?

> This is totally normal behaviour for any C program.

Well, it depends.  On some platforms, malloc basically never gives back
memory to the OS, but on glibc/Linux it does.  What I see here,
experimenting with a slightly-cut-down version of the example, is
that if psql is allowed to finish the query normally then its memory
usage according to "top" *does* go back down to very little at the
end.  But if one hits control-C partway through, it doesn't.

I imagine that this indicates that control-C processing allocates some
memory it doesn't free, resulting in an "island" up at the end of memory
that prevents glibc from releasing all the free memory it's got.  Whether
that's an actual leak, or just memory we're holding onto in hopes of
reusing it, isn't clear.  (valgrind might be useful.)

In short, there might be something that could be improved here, but
I've not dug into it enough to say for sure.

BTW, I also notice that either psql or libpq seems to take a darn
long time to release a several-GB-sized query result.  That might
be improvable as well.

regards, tom lane

Re: psql leaks memory on query cancellation

[Komяpa]

>
>
> > 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?
>
> This is totally normal behaviour for any C program.
>

Thanks Konstantin and Craig for the help.

To mitigate the issue I've changed the allocator on my laptop to jemalloc.
For single psql run on my Ubuntu system:

sudo apt install libjemalloc1
LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1 psql

A global replacement by putting /usr/lib/x86_64-linux-gnu/libjemalloc.so.1
to /etc/ld.so.preload also had a positive effect fixing this behavior in
all the applications, reducing after-boot memory footprint from 7 GB to 3.

Darafei Praliaskouski,
GIS Engineer / Juno Minsk

Re: psql leaks memory on query cancellation

[Craig Ringer]

On 12 April 2018 at 18: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,10);
>
> 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?

This is totally normal behaviour for any C program.

It's part of why systems should have swap space. But it's generally
fairly harmless, as it's uncommon for apps to make huge allocations
only once then stay running with low memory use thereafter.

It would potentially be a leak if doing the same thing repeatedly led
to repeated growth.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: psql leaks memory on query cancellation

[Konstantin Knizhnik]

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,10);

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