Re: BUG #6763: Severe memory leak with arrays and hstore

From: luben karavelov <karavelov(at)mail(dot)bg>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6763: Severe memory leak with arrays and hstore
Date: 2012-07-27 11:52:52
Message-ID: E067C89B-B29E-4464-9CE9-5EEC78764610@mail.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


On Jul 27, 2012, at 8:47 AM, Tom Lane wrote:

> Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
>> OK, it's certainly leaking, but not in the same drastic way I was able
>> to reproduce manually a couple of times earlier. Self-contained test
>> case attached.
>
> Using HEAD with stock parameters, I don't see any significant change in
> allocated address space (VSZ): it sits right around 170MB. The reported
> resident set size (RSS) starts from very little and rises to about
> 140MB, but I think that's just an artifact of the process touching more
> and more of the shared-buffers array as it runs. The actual backend
> memory consumption seems to be just a few meg.
>
> I can get it to blow out memory if I set work_mem large enough to
> persuade the planner to use hash aggregation (whereupon it tries to run
> all the array_agg aggregates in parallel). However, that requires
> work_mem set to a couple of GB, and I don't think it's really a bug when
> the backend goes ahead and uses a couple of GB after I told it it could.
>
> It's possible that the OP's problem boiled down to the planner making
> a drastic underestimate of the number of GROUP BY groups, which could
> mislead it into applying hash aggregation when there's not room; or
> if the space used per aggregate was a lot more than the 8K that the
> planner assumes when dealing with array_agg. But neither of those
> errors seems to be happening in this example case.
>
> regards, tom lane

It's good that the bug is not in HEAD. I was testing on 9.1.4. Definitely the size of RSS is not just references to shared buffers because they are 1.8G and the backend RSS got to 4G. My setting for work_mem is 64M, so it's quite conservative - the server was tuned for max concurrency, not for max throughput per single query.

Here is the plan of the insert:

=> explain INSERT INTO new_preferences SELECT user_id,hstore(array_agg(name), array_agg(value)) FROM old_prefs WHERE user_id<200000 GROUP BY user_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Insert on new_preferences (cost=65615.89..65617.73 rows=67 width=36)
-> HashAggregate (cost=65615.89..65617.06 rows=67 width=68)
-> Bitmap Heap Scan on old_prefs (cost=17645.25..56555.65 rows=1208032 width=68)
Recheck Cond: (user_id < 200000)
-> Bitmap Index Scan on old_prefs_user_id_ids (cost=0.00..17343.24 rows=1208032 width=0)
Index Cond: (user_id < 200000)
(6 rows)

So, it is using hash aggregate as you have suggested. I have tried the query with disabled hash aggregate and it consumes a lot less memory - single query to migrate the whole table finishes with 900M RSS.

After "ANALYZE old_prefs" the planner chooses GroupAggregate instead of HashAggregate - you were right about missing statistics of old_prefs.

Thank you for figuring out this case

Best regards

--
luben karavelov

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Craig Ringer 2012-07-27 13:44:28 Re: BUG #6763: Severe memory leak with arrays and hstore
Previous Message Jez Wain 2012-07-27 09:53:16 Re: BUG #6760: make check fails on strings SQL T581 regex test