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
