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

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
>
>
>