The following bug has been logged on the website:
Bug reference: 8318
Logged by: G.Bakalarski
Email address: g.bakalar...@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 : 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:01kbmemfree 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
