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@xxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
