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

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)

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

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

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

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

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

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

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

--
Bill Moran