Thread: 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