Hi,
On one of our production servers postgres backend processes are killed by
linux OOM killer. It runs 9.1.13 version now.
Using the method described here
http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ I was
able to find out that processes which are killed use a lot of private
memory ~3Gb. It turns out that memory consumption grows when a particular
query is executed namely delete with filtering when sequential scan is
used.
The simplified test which demonstrates this problem.
$ psql -U postgres postgres
psql (9.1.11, server 9.3.5)
postgres=# create table t (i integer, t text);
CREATE TABLE
postgres=# insert into t select v,
'01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
|| text(v) from generate_series(1,4000000) v;
INSERT 0 4000000
postgres=# \q
restart postgres
$ psql -U postgres postgres
execute 500 times the following command in the same connection
postgres=# delete from t where t = '';
On my setup of 9.3.5 this causes postgres backend process to consume 130Mb
of private memory which seems is reclaimed only when connection is closed.
On 9.1.13 this consumes about 250Mb.
I was able to find that this problem happens on 9.1.13, 9.2.9 and 9.3.5.
Note that this doesn't happen on latest 9.1.14 - memory usage is about 26
Mb after the above scenario.
On 9.4beta2 the memory consumption grows very much like on 9.3.5 but after
about a minute of connection being IDLE it decreases.
If I add index on the field which is used for filtering private memory
consumption is much smaller (2-10 Mb).
This problem can be reliably reproducible only after restart of postgres.
After some time when some statements are executed on the database the
problem sometimes gone.
Using of a connection pool makes this problem more painful.
My initial intention was to upgrade to 9.3 but it also has this problem.
This looks very much like a bug for me which affects latest stable version
(9.3.5) but I'm not sure that my method to diagnose the problem is correct.
Should I fine a bug regarding this?
Regards,
Roman Konoval
