Memory leak during delete with sequential scan
От | Roman Konoval |
---|---|
Тема | Memory leak during delete with sequential scan |
Дата | |
Msg-id | CABcZEEA22KAD4CpfC0qFdMi0EPRH08WTJmkhGUkG8Jq2+EBNWQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Memory leak during delete with sequential scan
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: