Обсуждение: migration to 9.6 array_accum memory issues

Hi all,

we are in the process of migrating postgresql 9.1 to 9.6 and we
encounter a memory issues with 9.6 - one of our procedure consumed all
free memory (~8GB) of the testing server (and make it to swap), there
was never such problem with 9.1. After some testing we found out that it
is caused by user-defined aggregate array_accum (defined as in postgres
documentation). We rewrote this procedure using built-in array_agg
function and it seems to work better (at least it is usable).

Anyway during the troubleshooting we did simple memory usage comparison
of 9.1 and 9.6 of usage array_accum and array_agg. We run following
query on same hw (machine has 10GB of RAM) and same memory postgresql
settings and watched for memory usage of postgresql backend process
(with ps -o vsize,rss,%mem,size)

postgresql.conf:
  shared_buffers = 4GB
  temp_buffers = 8MB
  work_mem = 1GB
  maintenance_work_mem = 1GB

For array_accum:
SELECT array_accum(n) from generate_series(1,100000000) n GROUP BY n %
100000

Peak memory usage of the backend process was
On 9.1 - 1.7GB
On 9.6 - 4.5GB

Same using array_agg:
SELECT array_agg(n) from generate_series(1,100000000) n GROUP BY n % 100000

Peak memory usage of the backend process was
On 9.1 - 3.3GB
On 9.6 - 2.7GB

My question is if there were any changes between 9.1 and 9.6 which would
explain described behavior or could there be problem somewhere in our
configuration (e.g. missed some new settings) or at last problem is with
the test itself and behavior is correct. Any advice or explanation would
be appreciated.

Regards
Jiri

Jiri Sadek <jiri.sadek@gmail.com> writes:
> we are in the process of migrating postgresql 9.1 to 9.6 and we
> encounter a memory issues with 9.6 - one of our procedure consumed all
> free memory (~8GB) of the testing server (and make it to swap), there
> was never such problem with 9.1. After some testing we found out that it
> is caused by user-defined aggregate array_accum (defined as in postgres
> documentation). We rewrote this procedure using built-in array_agg
> function and it seems to work better (at least it is usable).

9.6.what?

I think you'll find that 9.6.2 makes this significantly better.

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=48a6592da

            regards, tom lane

On 27.3.2017 15:46, Tom Lane wrote:
> Jiri Sadek <jiri.sadek@gmail.com> writes:
>> we are in the process of migrating postgresql 9.1 to 9.6 and we
>> encounter a memory issues with 9.6 - one of our procedure consumed all
>> free memory (~8GB) of the testing server (and make it to swap), there
>> was never such problem with 9.1. After some testing we found out that it
>> is caused by user-defined aggregate array_accum (defined as in postgres
>> documentation). We rewrote this procedure using built-in array_agg
>> function and it seems to work better (at least it is usable).
>
> 9.6.what?
>
> I think you'll find that 9.6.2 makes this significantly better.
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=48a6592da
>
>             regards, tom lane
>

Actually we did all the testing on 9.6.2-1.pgdg16.04+1 from
http://apt.postgresql.org/pub/repos/apt/ repository.

Regards
Jiri

Jiri Sadek <jiri.sadek@gmail.com> writes:
> On 27.3.2017 15:46, Tom Lane wrote:
>> I think you'll find that 9.6.2 makes this significantly better.
>> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=48a6592da

> Actually we did all the testing on 9.6.2-1.pgdg16.04+1 from
> http://apt.postgresql.org/pub/repos/apt/ repository.

Hm, well, that commit adjusted the default SSPACE assumption for a
user-defined aggregate using array_append as transition function
to be 1KB, which should be enough to keep you out of trouble in
most cases.  If you're actually accumulating more than that per
group, you might need to specify a larger SSPACE parameter for your
custom aggregate.

The core problem with your toy example is that the planner has no
understanding that "GROUP BY n % 100000" is going to lead to 100000
distinct groups, so it uses hash aggregation even though it knows
that the per-group space consumption will be significant.  Hopefully
in your real case it's estimating something closer to the true number
of groups.  But one way or another, if you want grouped array_accum
queries not to consume lots of memory, you need to ensure you don't
get a hashed-aggregation plan.  Ideally that would happen because the
planner has an accurate idea of the number of groups and the space
needed per group.  If you can't get the number-of-groups estimate
to be more or less in line with reality, and you don't want to bump
the SSPACE parameter up enough to compensate, you could resort to
turning off "enable_hashagg" for the query.

            regards, tom lane