From: | Gerhard Wiesinger <lists(at)wiesinger(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Memory Leak executing small queries without closing the connection - FreeBSD |
Date: | 2015-12-13 15:35:08 |
Message-ID: | 566D902C.3000504@wiesinger.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)wiesinger(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2015-12-13 15:59:18 | Re: Memory Leak executing small queries without closing the connection - FreeBSD |
Previous Message | Bill Moran | 2015-12-13 15:05:15 | Re: Memory Leak executing small queries without closing the connection - FreeBSD |