Обсуждение: BUG #8318: memory leak during CREATE TEMPORARY TABLE

The following bug has been logged on the website:

Bug reference:      8318
Logged by:          G.Bakalarski
Email address:      g.bakalarski@icm.edu.pl
PostgreSQL version: 9.1.3
Operating system:   Linux Debian 6 (squeezy)
Description:

When doing these kind of statements (this is from log file):


2013-07-21 02:29:26 UTC [16683] composite LOG:  duration: 630631.234 ms
execute <unnamed>: CREATE TEMPORARY TABLE
tmp2_4da9ce5d_bf86_4331_a18d_4c918cdd71de AS SELECT contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain, COUNT(*) AS
_aggregated FROM rel_2_repContributorView AS _main WHERE _tick > $1 AND
_tick <= $2 AND contributorType = $3 GROUP BY contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain UNION ALL SELECT
contributorMD5, contributorFirstName, contributorFirstName_sortkey,
contributorLastName, contributorLastName_sortkey, contributorTitle,
contributorTitle_sortkey, contributorLastFirstName_sortkey,
contributorLastFirstName_plain, contributorFirstLastName,
contributorFirstLastName_plain, -COUNT(*) AS _aggregated FROM
del_2_repContributorView AS _main WHERE _tick > $4 AND _tick <= $5 AND
_origtick <= $6 AND (contributorType = $7) GROUP BY contributorMD5,
contributorFirstName, contributorFirstName_sortkey, contributorLastName,
contributorLastName_sortkey, contributorTitle, contributorTitle_sortkey,
contributorLastFirstName_sortkey, contributorLastFirstName_plain,
contributorFirstLastName, contributorFirstLastName_plain
2013-07-21 02:29:26 UTC [16683] composite DETAIL:  parameters: $1 = '6', $2
= '1160', $3 = 'PERSON', $4 = '6', $5 = '1160', $6 = '6', $7 = 'PERSON'


a commited system memory measured
with sar -r , goes up by 2-20GBytes and never goes down eg:


00:00:01    kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit
%commit
01:55:01     76347732 122133736     61.53         0  29816912 116970332
58.93
02:05:01     75886088 122595380     61.77         0  30289504 116960972
58.93
02:15:01     75317968 123163500     62.05         0  30814996 117007696
58.95
02:25:01     31505704 166975764     84.13         0  73817200 117399024
59.15
02:35:01       439584 198041884     99.78         0  90340732 131819000
66.41
02:45:01       436480 198044988     99.78         0  89510732 131750320
66.38
02:55:01     14986680 183494788     92.45         0  75110884 132450980
66.73
03:05:01     15881024 182600444     92.00         0  74202256 132465840
66.74
03:15:01     45572732 152908736     77.04         0  45209796 132472464
66.74
03:25:01     44059268 154422200     77.80         0  46752888 132480808
66.75
03:35:01     42515568 155965900     78.58         0  48230784 132536984
66.78
03:45:01     40578860 157902608     79.56         0  50224608 132477148
66.75
03:55:01     38876772 159604696     80.41         0  51858264 132495080
66.75
04:05:01     72974428 125507040     63.23         0  18578900 132527424
66.77




This happens (noticeably) when statement last long enough (short lasting
statements don't pull up memory).


After few days (such long lasting Create temp table happens few time a day),
my huge server (192GB of RAM), gets stucked due to no memory available
(usually %commit is around 103-105%), linux OOM killer goes into action - it
usually kills one of postgres processes - postmaster restart all databases)
and after all linux  has again some 100GB of not commited memory (--> sar
-r).


Looking in rel notes of PG 9.1.4+ I did not see any fixes on this ...


GB

"g.bakalarski@icm.edu.pl" <g.bakalarski@icm.edu.pl> wrote:=0A=0A> When doin=
g these kind of statements=0A=0A> execute <unnamed>: CREATE TEMPORARY TABLE=
 [...]=0A=0A> After few days [...] my huge server (192GB of RAM), gets stuc=
ked=0A> due to no memory available (usually %commit is around 103-105%),=0A=
> linux OOM killer goes into action - it usually kills one of=0A> postgres =
processes - postmaster restart all databases) and after=0A> all linux=A0 ha=
s again some 100GB of not commited memory=0A=0AWhat do you have as settings=
 for temp_buffers and max_connections?=0A=0A--=0AKevin Grittner=0AEDB: http=
://www.enterprisedb.com=0AThe Enterprise PostgreSQL Company

"G.Bakalarski@icm.edu.pl" <G.Bakalarski@icm.edu.pl> wrote:=0A> Kevin Grittn=
er <kgrittn@ymail.com> wrote:=0A>> "g.bakalarski@icm.edu.pl" <g.bakalarski@=
icm.edu.pl> wrote:=0A>>=0A>>> When doing these kind of statements=0A>>=0A>>=
> execute <unnamed>: CREATE TEMPORARY TABLE [...]=0A>>=0A>>> After few days=
 [...] my huge server (192GB of RAM), gets stucked=0A>>> due to no memory a=
vailable (usually %commit is around 103-105%),=0A>>> linux OOM killer goes =
into action - it usually kills one of=0A>>> postgres processes - postmaster=
 restart all databases) and after=0A>>> all linux=A0 has again some 100GB o=
f not commited memory=0A>>=0A>> What do you have as settings for temp_buffe=
rs and max_connections?=0A>>=0A> temp_buffers =3D 16GB=0A> max_connections =
=3D 250=0A=0Atemp_buffers is per-connection, and any space allocated for th=
is=0Apurpose by a connection is not released until the connection is=0Aclos=
ed.=A0 So you have configured your server to acquire and hold up=0Ato 3.9 T=
B of RAM for this purpose.=0A=0Ahttp://www.postgresql.org/docs/current/stat=
ic/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY=0A=0A> (with=
 real number of connections about 120 and not going above)=0A=0AOK, so you =
can only get to 1.9 TB of RAM used for temp_buffers with=0A120 connections,=
 but that's still way more than the 192 GB you=0Aactually have.=0A=0A--=0AK=
evin Grittner=0AEDB: http://www.enterprisedb.com=0AThe Enterprise PostgreSQ=
L Company

> "g.bakalarski@icm.edu.pl" <g.bakalarski@icm.edu.pl> wrote:
>
>> When doing these kind of statements
>
>> execute <unnamed>: CREATE TEMPORARY TABLE [...]
>
>> After few days [...] my huge server (192GB of RAM), gets stucked
>> due to no memory available (usually %commit is around 103-105%),
>> linux OOM killer goes into action - it usually kills one of
>> postgres processes - postmaster restart all databases) and after
>> all linux  has again some 100GB of not commited memory
>
> What do you have as settings for temp_buffers and max_connections?
>
temp_buffers = 16GB
max_connections = 250

(with real number of connections about 120 and not going above)

Grzegorz