Hello,
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)
75862 pgsql 1 20 0 7321M 7266M select 4 7:04 0.00%
postgres: checkpointer process (postgres)
75863 pgsql 1 20 0 7321M 7260M select 4 3:34 0.00%
postgres: writer process (postgres)
75867 pgsql 1 20 0 7323M 7237M sbwait 0 3:19 0.00%
postgres: username dbnamee ipaddress(58977) (postgres)
76178 pgsql 1 20 0 7323M 7237M sbwait 2 3:18 0.00%
postgres: username dbnamee ipaddress(35807) (postgres)
75868 pgsql 1 20 0 7323M 7237M sbwait 0 3:20 0.00%
postgres: username dbnamee ipaddress(58978) (postgres)
75884 pgsql 1 20 0 7323M 7236M sbwait 3 3:20 0.00%
postgres: username dbnamee ipaddress(59908) (postgres)
75869 pgsql 1 20 0 7323M 7236M sbwait 3 3:20 0.00%
postgres: username dbnamee ipaddress(58982) (postgres)
76181 pgsql 1 20 0 7323M 7236M sbwait 2 3:18 0.00%
postgres: username dbnamee ipaddress(35813) (postgres)
75883 pgsql 1 20 0 7323M 7236M sbwait 0 3:20 0.00%
postgres: username dbnamee ipaddress(59907) (postgres)
76180 pgsql 1 20 0 7323M 7236M sbwait 1 3:19 0.00%
postgres: username dbnamee ipaddress(35811) (postgres)
76177 pgsql 1 20 0 7323M 7236M sbwait 1 3:18 0.00%
postgres: username dbnamee ipaddress(35712) (postgres)
76179 pgsql 1 20 0 7323M 7236M sbwait 5 3:18 0.00%
postgres: username dbnamee ipaddress(35810) (postgres)
64951 pgsql 1 75 0 7375M 662M CPU1 1 0:01 11.47%
postgres: username dbnamee 127.0.0.1(32073) (postgres)
64950 pgsql 1 77 0 7325M 598M CPU6 6 0:02 16.55%
postgres: username dbnamee 127.0.0.1(44251) (postgres)
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).
Config:
Memory: 32GB, Swap: 512MB
maintenance_work_mem = 512MB
effective_cache_size = 10GB
work_mem = 892MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 7080MB
max_connections = 80
autovacuum_max_workers = 3
Thnx.
Ciao,
Gerhard
On 13.12.2015 08:49, Gerhard Wiesinger wrote:
Hello,
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. 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. If some other queries get in
we get into out of memory situations. So it looks like PostgreSQL has
memory leaks.
I found a test scenario to reproduce it also on a newer FreeBSD 10.2
VM as well as in a Fedora 23 VM (both with PostgreSQL 9.4.5):
Executions in psql with one persisent connection:
-- Create the table
CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM
generate_Series(1,100000000) s;
-- Create the index
CREATE INDEX ON t_random(s);
-- Restart psql with a new connection:
-- Memory goes slighty up after each execution even after canceling:
-- Memory leak on FreeBSD 10.2/Fedora 23 and PostgreSQL 9.4.5 on
cancel the query or multiple execution
SELECT * FROM t_random ORDER BY md5 LIMIT 100000;
-- Therefore I created a function:
CREATE OR REPLACE FUNCTION execmultiplei(IN num int8)
RETURNS void AS $$
BEGIN
-- RAISE NOTICE 'num=%', num;
FOR i IN 1..num LOOP
PERFORM * FROM t_random WHERE s = i;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Test it several times
SELECT execmultiplei(10000000);
-- Linux testing (FreeBSD is similar), relevant part is RES (resident
memory):
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
-- after startup of psql
26851 postgres 20 0 2363276 7432 6292 S 0.0 0.2 0:00.00
postgres: postgres postgres [local] idle
-- Memory goes up, ok so far
26851 postgres 20 0 2365732 255152 253548 R 99.0 6.3 0:10.77
postgres: postgres postgres [local] SELECT
26851 postgres 20 0 2365732 408464 406788 R 100.0 10.1 0:17.81
postgres: postgres postgres [local] SELECT
26851 postgres 20 0 2365732 864472 862576 R 100.0 21.4 0:38.90
postgres: postgres postgres [local] SELECT
-- Function execmultiplei and transaction terminated, but memory still
allocated!!!
26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 0:41.40
postgres: postgres postgres [local] idle
-- Calling it again
26851 postgres 20 0 2365732 920668 918748 R 99.0 22.7 0:46.51
postgres: postgres postgres [local] SELECT
-- idle again, memory still allocated
26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 1:22.54
postgres: postgres postgres [local] idle
Memory will only be released if psql is exited. According to the
PostgreSQL design memory should be freed when the transaction completed.
top commands on FreeBSD: top -SaPz -o res -s 1
top commands on Linux: top -o RES d1
Config: VMs with 4GB of RAM, 2 vCPUs
shared_buffers = 2048MB # min 128kB
effective_cache_size = 2GB
work_mem = 892MB
wal_buffers = 8MB
checkpoint_segments = 16
Any ideas?
Thank you.
Ciao,
Gerhard
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
