Обсуждение: array_agg crash?

"Chris Spotts" <rfusca@gmail.com> writes:
> LOG:  00000: autovacuum launcher process (PID 10264) was terminated by
> signal 9: Killed

Looks like the OOM killer is loose on your system.  Disable memory
overcommit in the kernel and things will get better.
http://www.postgresql.org/docs/8.4/static/kernel-resources.html#AEN24152

            regards, tom lane

> "Chris Spotts" <rfusca@gmail.com> writes:
> > LOG:  00000: autovacuum launcher process (PID 10264) was terminated
> by
> > signal 9: Killed
>
> Looks like the OOM killer is loose on your system.  Disable memory
> overcommit in the kernel and things will get better.
> http://www.postgresql.org/docs/8.4/static/kernel-
> resources.html#AEN24152
>
>             regards, tom lane
[Spotts, Christopher]

That seems to have fixed it in the sense that it doesn't crash, but I killed
it after 15 minutes and no results...rather I tried to kill it, but it looks
like I'm going to have to -9 it...

"Chris Spotts" <rfusca@gmail.com> writes:
> That seems to have fixed it in the sense that it doesn't crash, but I killed
> it after 15 minutes and no results...rather I tried to kill it, but it looks
> like I'm going to have to -9 it...

How big were the arrays you were trying to push around here?  I tried
interrupting a similar query and it came right back; but if you were
building some really enormous arrays I could see the final array
build loop taking awhile, and there's no CHECK_FOR_INTERRUPTS in it...

            regards, tom lane

> killed
> > it after 15 minutes and no results...rather I tried to kill it, but
> it looks
> > like I'm going to have to -9 it...
>
> How big were the arrays you were trying to push around here?  I tried
> interrupting a similar query and it came right back; but if you were
> building some really enormous arrays I could see the final array
> build loop taking awhile, and there's no CHECK_FOR_INTERRUPTS in it...
>
>             regards, tom lane
[Spotts, Christopher]
Like I'd said originally, there were no arrays that ended up being more 4
elements long -  all integers. The vast majority of them were 1 or 2 long.

If it was having trouble allocating the memory for arrays, why doesn't using
a ARRAY(subselect)  yield the same issue?

"Chris Spotts" <rfusca@gmail.com> writes:
>>> killed
>>> it after 15 minutes and no results...rather I tried to kill it, but
>>> it looks like I'm going to have to -9 it...
>>
>> How big were the arrays you were trying to push around here?

> Like I'd said originally, there were no arrays that ended up being more 4
> elements long -  all integers. The vast majority of them were 1 or 2 long.

Hm, maybe the problem is not so much array size as too many arrays.  How
many groups are you expecting in that query?  Does the plan for the
array_agg query show hash or group aggregation?

            regards, tom lane

> >> How big were the arrays you were trying to push around here?
>
> > Like I'd said originally, there were no arrays that ended up being
> more 4
> > elements long -  all integers. The vast majority of them were 1 or 2
> long.
>
> Hm, maybe the problem is not so much array size as too many arrays.
> How
> many groups are you expecting in that query?  Does the plan for the
> array_agg query show hash or group aggregation?
>
>             regards, tom lane
[Spotts, Christopher]
GroupAggregate

"Chris Spotts" <rfusca@gmail.com> writes:
>> many groups are you expecting in that query?  Does the plan for the
>> array_agg query show hash or group aggregation?

> GroupAggregate

Huh, there's no reason it should take much memory then.  Maybe you've
found a memory leak.  Can you put together a self-contained test case?

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, July 21, 2009 12:16 PM
> To: Chris Spotts
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] array_agg crash?
>
> "Chris Spotts" <rfusca@gmail.com> writes:
> >> many groups are you expecting in that query?  Does the plan for the
> >> array_agg query show hash or group aggregation?
>
> > GroupAggregate
>
> Huh, there's no reason it should take much memory then.  Maybe you've
> found a memory leak.  Can you put together a self-contained test case?
>
>             regards, tom lane
[Spotts, Christopher]
I'll try to put something together tonight and let you know tomorrow.

Tom Lane wrote:
> "Chris Spotts" <rfusca@gmail.com> writes:
>
>>> many groups are you expecting in that query?  Does the plan for the
>>> array_agg query show hash or group aggregation?
>>>
>
>
>> GroupAggregate
>>
>
> Huh, there's no reason it should take much memory then.  Maybe you've
> found a memory leak.  Can you put together a self-contained test case?
>
>             regards, tom lane
>
What do you want specifically as far as details for the test case?  I
exported just the table that that was reading from.  Installed a new
clean virtual  machine ubuntu (jaunty) and then installed 8.4.0.
Imported the table and definition.  Ran the same query and the same
thing happened.

Table its selecting from is:
          Table "public.trip_ids_to_customer_upload_ids"
       Column       |  Type   | Modifiers | Storage | Description
--------------------+---------+-----------+---------+-------------
 trip_id            | bigint  |           | plain   |
 customer_upload_id | integer |           | plain   |
Indexes:
    "trips_customer_id" btree (trip_id, customer_upload_id)
Has OIDs: no

There is 3801347 rows in the table.  There are 3773039 distinct trip_id
values.  So you can see that the vast majority of rows here are just a
single  element array.

Chris Spotts <rfusca@gmail.com> writes:
> What do you want specifically as far as details for the test case?  I
> exported just the table that that was reading from.  Installed a new
> clean virtual  machine ubuntu (jaunty) and then installed 8.4.0.
> Imported the table and definition.  Ran the same query and the same
> thing happened.

The table dump and the query would be enough then.  Can you send it
to me off-list?

            regards, tom lane