Re: Memory Leak executing small queries without closing the connection - FreeBSD - Mailing list pgsql-general

Hello Bill,

Thank you for your response, comments inline:

On 13.12.2015 16:05, Bill Moran wrote:
> On Sun, 13 Dec 2015 09:57:21 +0100
> Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> some further details from the original FreeBSD 10.1 machine:
>>
>> Mem: 7814M Active, 20G Inact, 2982M Wired, 232M Cache, 1661M Buf, 30M Free
>> Swap: 512M Total, 506M Used, 6620K Free, 98% Inuse
>>
>>     PID USERNAME    THR PRI NICE   SIZE    RES STATE   C   TIME WCPU COMMAND
>> 77941 pgsql         5  20    0  7925M  7296M usem    2 352:34 6.98%
>> postgres: username dbnamee 127.0.0.1(43367)  (postgres)
> <snip>
>
> I see no evidence of an actual leak here. Each process is basically using
> the 7G of shared_buffers you have allocated in the config (which is only
> 7G _total_ for all processes, since it's shared memory)

OK, but why do we then get: kernel: swap_pager_getswapspace(4): failed?

>> Out of memory:
>> kernel: swap_pager_getswapspace(4): failed
>> kernel: swap_pager_getswapspace(8): failed
>> kernel: swap_pager_getswapspace(3): failed
>>
>> Main issue is IHMO (as far as I understood the FreeBSD Memory system)
>> that 20G are INACTIVE. When I subtract the shared memory, even ~13GB
>> should be available, but they are still allocated but inactive
>> (INACTIVE). INACTIVE memory might be clean or dirty. As we get into out
>> of memory situations it is likely that the memory is dirty (otherwise it
>> would have been reused).
> Not quite correct. Inactive memory is _always_ available for re-use.

Are you sure that's true?

Monitoring inactive memory:
cat vm_stat.sh
#!/usr/bin/env bash

while [ 1 ]; do
   date +%Y.%m.%d.%H.%M.%S
   sysctl -a | grep vm.stats.vm.
   sleep 1
done

And even we get out of memory with swap_pager_getswapspace Inactive
Memory (from the log file) is around 20GB (doesn't go down or up)
vm.stats.vm.v_inactive_count: 5193530 (*4k pages is around 20GB)

Then we have 20GB inactive memory, but we still get out of memory with
kernel: swap_pager_getswapspace(4): failed. Any ideas why?

>
>> Config:
>> Memory: 32GB, Swap: 512MB
> Probably not the cause of this problem, but this is a non-optimal layout.
> If you're going to use swap at all, it should generally be 2x the size of
> your RAM for optimal performance under memory pressure.

Yes, we will try to disable it totally. Nevertheless why do we get out
of memory/Swap?

>
>> maintenance_work_mem = 512MB
>> effective_cache_size = 10GB
>> work_mem = 892MB
> I expect that this value is the cause of the problem. The scenario you
> describe below is sorting a large table on an unindexed column, meaning
> it will have to use all that work_mem. I'd be interested to see the
> output of:
>
> EXPLAIN ANALYZE SELECT * FROM t_random ORDER BY md5 LIMIT 100000;

That was only a test query, has nothing to do with production based
query. They are mostly SELECT/INSERTS/UPDATES on primary keys.

> But even without that information, I'd recommend you reduce work_mem
> to about 16M or so.

Why so low? E.g. sorting on reporting or some long running queries are
then done on disk and not in memory.

>> wal_buffers = 8MB
>> checkpoint_segments = 16
>> shared_buffers = 7080MB
>> max_connections = 80
>> autovacuum_max_workers = 3
> [snip]
>
>>> We are running PostgreSQL 9.4.5 on FreeBSD 10.1 and have multiple
>>> worker processes connected via persistent connections to PostgreSQL,
>>> they perform just simple queries with SELECT on primary keys and
>>> simple INSERTS/UPDATES.
> That's not at all the test scenario you show below. The scenario below
> is a large sort operation on a non-indexed column, which is vastly
> different than a single-row fetch based on an index.

Yes, that non indexed select was just for testing purporeses.

>
>>> Normally nearly all the workers are idle but
>>> they still consume the maximum configured work mem on the PostgreSQL
>>> server and the memory is also resident.
> I see no evidence of that in your top output. Each process has a
> reference to the 7G of shared_buffers you allocated, which is memory
> shared by all processes, and is expected. I'm not as familiar with
> Linux top, but the results appear to be the same.

Yes, might be the case, but if it is n times 7G shared memory then we
have ~20GB Inactive Memory available, so plenty of memory. And why are
we getting: kernel: swap_pager_getswapspace(4): failed?

Thnx.

Ciao,
Gerhard