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