Обсуждение: BUG #7916: memory leak with array_agg

The following bug has been logged on the website:

Bug reference:      7916
Logged by:          Sergey Burladyan
Email address:      eshkinkot@gmail.com
PostgreSQL version: 9.2.3
Operating system:   Debian GNU/Linux 7.0 (wheezy)
Description:        =


Looks like array_agg have memory leak. In my example it eat 3 Gb RSS, but
equal user defined aggregate
created by myself use only 7Mb RSS.

PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
set work_mem to '5MB';

Just query without aggregate:

$ ps axu | grep ^postgres | grep idle
postgres  8910  0.0  0.0 3337028 5156 ?        Ss   02:21   0:00 postgres:
seb seb [local] idle

seb=3D> explain (analyze,verbose,buffers) select n, (random() * 10)::int fr=
om
generate_series(1, 700000) n, generate_series(1, 10);
                                                                 QUERY PLAN =

                                                                =

---------------------------------------------------------------------------=
------------------------------------------------------------------
 Nested Loop  (cost=3D0.01..27510.01 rows=3D1000000 width=3D4) (actual
time=3D82.694..2827.922 rows=3D7000000 loops=3D1)
   Output: n.n, ((random() * 10::double precision))::integer
   Buffers: temp read=3D1198 written=3D1197
   ->  Function Scan on pg_catalog.generate_series n  (cost=3D0.00..10.00
rows=3D1000 width=3D4) (actual time=3D82.653..190.192 rows=3D700000 loops=
=3D1)
         Output: n.n
         Function Call: generate_series(1, 700000)
         Buffers: temp read=3D1198 written=3D1197
   ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
         Output: generate_series.generate_series
         Function Call: generate_series(1, 10)
 Total runtime: 3318.348 ms

$ ps axu | grep ^postgres | grep idle
postgres  8910  5.0  0.0 3337176 6376 ?        Ss   02:21   0:03 postgres:
seb seb [local] idle

RSS 6376 after it

Ok, now problematic query with array_agg:

seb=3D> explain (analyze,verbose,buffers) select n, array_agg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
                                                                    QUERY
PLAN                                                                     =

---------------------------------------------------------------------------=
------------------------------------------------------------------------
 HashAggregate  (cost=3D32510.01..32512.51 rows=3D200 width=3D4) (actual
time=3D7276.445..7853.732 rows=3D700000 loops=3D1)
   Output: n.n, array_agg(((random() * 10::double precision))::integer)
   Buffers: temp read=3D1198 written=3D1197
   ->  Nested Loop  (cost=3D0.01..20010.01 rows=3D1000000 width=3D4) (actual
time=3D83.624..2318.832 rows=3D7000000 loops=3D1)
         Output: n.n
         Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series n =

(cost=3D0.00..10.00 rows=3D1000 width=3D4) (actual time=3D83.606..217.716
rows=3D700000 loops=3D1)
               Output: n.n
               Function Call: generate_series(1, 700000)
               Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.=
00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
               Output: generate_series.generate_series
               Function Call: generate_series(1, 10)
 Total runtime: 8110.143 ms

$ ps axu | grep ^postgres | grep idle
postgres  8910  6.8 21.0 9160796 3474628 ?     Ss   02:21   0:11 postgres:
seb seb [local] idle

RSS _3474628_ =3D 3Gb allocated after it

reconnect and use user defined aggregate:

create aggregate myagg (int) ( sfunc =3D array_append, stype =3D int[]);
set work_mem to '5MB';

$ ps axu | grep ^postgres | grep idle
postgres  8962  0.0  0.0 3337036 5728 ?        Ss   02:25   0:00 postgres:
seb seb [local] idle

seb=3D> explain (analyze,verbose,buffers) select n, myagg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
                                                                    QUERY
PLAN                                                                     =

---------------------------------------------------------------------------=
------------------------------------------------------------------------
 HashAggregate  (cost=3D32510.01..32512.01 rows=3D200 width=3D4) (actual
time=3D8589.773..8917.671 rows=3D700000 loops=3D1)
   Output: n.n, myagg(((random() * 10::double precision))::integer)
   Buffers: temp read=3D1198 written=3D1197
   ->  Nested Loop  (cost=3D0.01..20010.01 rows=3D1000000 width=3D4) (actual
time=3D83.273..2364.212 rows=3D7000000 loops=3D1)
         Output: n.n
         Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series n =

(cost=3D0.00..10.00 rows=3D1000 width=3D4) (actual time=3D83.247..196.978
rows=3D700000 loops=3D1)
               Output: n.n
               Function Call: generate_series(1, 700000)
               Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.=
00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
               Output: generate_series.generate_series
               Function Call: generate_series(1, 10)
 Total runtime: 8978.695 ms

$ ps axu | grep ^postgres | grep idle
postgres  8962 12.9  0.0 3337832 7928 ?        Ss   02:25   0:08 postgres:
seb seb [local] idle

RSS 7928 =3D 7Mb after it

Current git master (542eeba26992305d872be699158cb3ab1c2be6e6) also have this
problem.

eshkinkot@gmail.com writes:
> Looks like array_agg have memory leak. In my example it eat 3 Gb RSS, but
> equal user defined aggregate
> created by myself use only 7Mb RSS.

AFAICT there's no actual leak here; array_agg is just optimized for
speed rather than space.  It eats about 8K per hashtable entry.
While the planner knows that, it's got no good idea how many groups
will be produced by the query, so it underestimates the space needed
--- and the HashAggregate code is not currently capable of spilling
the hashtable to disk, so the table balloons well past the intended
work_mem limit.

Although no real fix for this is within easy reach, it strikes me
that we could possibly ameliorate things a bit by tweaking the
memory context size parameters used by accumArrayResult().
It would likely be reasonable to set the min size to 1K or so not 8K.
This would make things a trifle slower when the actual space requirement
exceeds 1K, but probably not by enough to notice.

BTW, I don't believe your assertion that the handmade aggregate does
this in 7MB.  Even a very optimistic calculation puts the space needed
for 700000 10-element integer arrays at forty-some MB, and when I try
it I see more like 100MB consumed thanks to hashtable overhead.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> AFAICT there's no actual leak here; array_agg is just optimized for
> speed rather than space.  It eats about 8K per hashtable entry.
> While the planner knows that, it's got no good idea how many groups
> will be produced by the query, so it underestimates the space needed
> --- and the HashAggregate code is not currently capable of spilling
> the hashtable to disk, so the table balloons well past the intended
> work_mem limit.

Aha, I found this "8K per hashtable entry" with ltrace and now understand
what is going on here:
  === array_agg malloc calls count ===  === myagg malloc calls count===
      3 malloc(1024)                        3 malloc(1024)
      3 malloc(1048576)                     3 malloc(1048576)
      3 malloc(131072)                      3 malloc(131072)
      1 malloc(16056)                       4 malloc(16384)
      5 malloc(16384)                       2 malloc(16440)
      2 malloc(16440)                       1 malloc(2048)
      1 malloc(2048)                        3 malloc(2097152)
      3 malloc(2097152)                     3 malloc(262144)
      3 malloc(262144)                      3 malloc(32768)
      3 malloc(32768)                       1 malloc(32824)
      1 malloc(32824)                       1 malloc(4096)
      1 malloc(4096)                        3 malloc(4194304)
      3 malloc(4194304)                     3 malloc(524288)
      3 malloc(524288)                      3 malloc(65536)
      3 malloc(65536)                      12 malloc(8192)
 724151 malloc(8192)                        1 malloc(8296)
      1 malloc(8296)                       29 malloc(8360)
     44 malloc(8360)                       16 malloc(8388608)
      8 malloc(8388608)

Thank you for answer Tom!

> Although no real fix for this is within easy reach, it strikes me
> that we could possibly ameliorate things a bit by tweaking the
> memory context size parameters used by accumArrayResult().
> It would likely be reasonable to set the min size to 1K or so not 8K.
> This would make things a trifle slower when the actual space requirement
> exceeds 1K, but probably not by enough to notice.

Looks good.

> BTW, I don't believe your assertion that the handmade aggregate does
> this in 7MB.  Even a very optimistic calculation puts the space needed
> for 700000 10-element integer arrays at forty-some MB, and when I try
> it I see more like 100MB consumed thanks to hashtable overhead.

Yes you are right, Tom. My mistake.

--
Sergey Burladyan