Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway 
On 05/09/2012 10:01 PM, Tom Lane wrote:
> Joe Conway  writes:
>> The attached one-liner seems to plug up the majority (although not quite
>> all) of the leakage.
> 
> Looks sane to me.  Are you planning to look for the remaining leakage?

Actually, now I'm not so sure there really are any other leaks. The last
test I ran, on 9.1 with the original data and plpgsql function, grew to:

  VIRT  RES  SHR
  540m 327m 267m

but then stabilized there through the end of the query, which
successfully returned:

  count
--
 28847766
(1 row)

This was with:

report_log=# show shared_buffers;
 shared_buffers

 256MB
(1 row)

report_log=# show work_mem;
 work_mem
--
 16MB
(1 row)

So I think those memory usage numbers look reasonable.

The bug appears to go back through 8.4 -- kind of surprising no one has
complained before.

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Tom Lane 
Joe Conway  writes:
> The attached one-liner seems to plug up the majority (although not quite
> all) of the leakage.

Looks sane to me.  Are you planning to look for the remaining leakage?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway 
On 05/09/2012 05:06 PM, Joe Conway wrote:
> OK, new script. This more faithfully represents the real life scenario,
> and reproduces the issue on HEAD with out-of-the-box config settings,
> versus 8.1 which completes the query having never exceeded a very modest
> memory usage:
> 
> ---
> On pg 8.1 with out of the box config:
> VIRT  RES  SHR
> 199m  11m 3032
> ---
> On pg head with out of the box config:
> VIRT  RES  SHR
> 1671m 1.5g  16m
> ---

The attached one-liner seems to plug up the majority (although not quite
all) of the leakage.

do_convert_tuple() is allocating a new tuple for every row in the loop
and exec_stmt_return_next() is leaking it.

The query now finishes successfully. On pg head with attached patch and
out of the box config:
VIRT  RES  SHR
196m  35m  31m

This look sane/correct?

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index de1aece..24346c2 100644
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*** exec_stmt_return_next(PLpgSQL_execstate 
*** 2469,2474 
--- 2469,2475 
  	{
  		tuple = do_convert_tuple(tuple, tupmap);
  		free_conversion_map(tupmap);
+ 		free_tuple = true;
  	}
  }
  break;

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway 
On 05/09/2012 03:36 PM, Joe Conway wrote:
> Good call -- of course that just means my contrived example fails to
> duplicate the real issue :-(
> In the real example, even with work_mem = 1 MB I see the same behavior
> on 9.1.

OK, new script. This more faithfully represents the real life scenario,
and reproduces the issue on HEAD with out-of-the-box config settings,
versus 8.1 which completes the query having never exceeded a very modest
memory usage:

---
On pg 8.1 with out of the box config:
VIRT  RES  SHR
199m  11m 3032
---
On pg head with out of the box config:
VIRT  RES  SHR
1671m 1.5g  16m
---

I have not tried your ulimit suggestion yet but will do that next.

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
CREATE OR REPLACE FUNCTION create_parts ()
RETURNS text AS $_$
DECLARE
i int;
sql   text;
BEGIN
   sql := 'CREATE TABLE foo (
   ts timestamp with time zone,
   d1 bigint, d2 bigint, d3 bigint,
   d4 bigint, d5 bigint, d6 bigint,
   s1 text, s2 text, s3 text,
   s4 text, s5 text, s6 text)';
EXECUTE sql;
FOR i IN 1..100 LOOP
  sql := 'CREATE TABLE foo_' || i || ' () INHERITS (foo)';
  EXECUTE sql;
  sql := $$
   INSERT INTO foo_$$ || i || $$ SELECT now(),
   ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
   'a'::text, ''::text,
   'cc'::text,
   'ddd'::text,
   'eee'::text,
   'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,20) as t(id)) ss
 $$;
  EXECUTE sql;
END LOOP;
RETURN 'OK';
END;
$_$ LANGUAGE plpgsql VOLATILE;

SELECT create_parts ();

CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof foo AS $_$
DECLARE
result_rec  record;
sql text;
BEGIN
sql := 'SELECT * FROM foo';
FOR result_rec IN EXECUTE sql LOOP
RETURN NEXT result_rec;
END LOOP;
RETURN;
END;
$_$ LANGUAGE plpgsql STABLE;

select count(*) from selectDetailed();




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway 
On 05/09/2012 03:08 PM, Tom Lane wrote:
> I see no memory leak at all in this example, either in HEAD or 9.1
> branch tip.  Perhaps whatever you're seeing is an already-fixed bug?
> 
> Another likely theory is that you've changed settings from the 8.1
> installation.  I would expect this example to eat about 10 times
> work_mem (due to one tuplestore for each generate_series invocation),
> and that's more or less what I see happening here.  A large work_mem
> could look like a leak, but it isn't.

Good call -- of course that just means my contrived example fails to
duplicate the real issue :-(
In the real example, even with work_mem = 1 MB I see the same behavior
on 9.1.

> If you need further help in debugging, try launching the postmaster
> under a fairly restrictive memory ulimit, so that the backend will get a
> malloc failure before it starts to swap too badly.  The memory map it
> will then print on stderr should point to where the memory is going.

Thanks -- will try that.

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Tom Lane 
Joe Conway  writes:
> I'm working on an upgrade of PostgreSQL embedded in a product from
> version 8.1.x to 9.1.x. One particular PL/pgSQL function is giving us an
> issue as there seems to be a rather severe regression in memory usage --
> a query that finishes in 8.1 causes an out of memory exception on 9.1.

I see no memory leak at all in this example, either in HEAD or 9.1
branch tip.  Perhaps whatever you're seeing is an already-fixed bug?

Another likely theory is that you've changed settings from the 8.1
installation.  I would expect this example to eat about 10 times
work_mem (due to one tuplestore for each generate_series invocation),
and that's more or less what I see happening here.  A large work_mem
could look like a leak, but it isn't.

If you need further help in debugging, try launching the postmaster
under a fairly restrictive memory ulimit, so that the backend will get a
malloc failure before it starts to swap too badly.  The memory map it
will then print on stderr should point to where the memory is going.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway 
I'm working on an upgrade of PostgreSQL embedded in a product from
version 8.1.x to 9.1.x. One particular PL/pgSQL function is giving us an
issue as there seems to be a rather severe regression in memory usage --
a query that finishes in 8.1 causes an out of memory exception on 9.1.

Using the same data on the same machine I see memory use stay steady at
a reasonably low value on the 8.1 installation but steadily climb on 9.1
(I watched it go over 2 GB and canceled the query -- the production
machines are 32 bit)

The attached standalone script seems to reproduce the effect. On 8.1
memory usage remains steady and low, on 9.1 I watched it climb past 1.1
GB and canceled the query.

I suspect the append node to be the culprit because if I skip the "UNION
ALL", i.e. if I use one generate_series with 20 million rows instead of
10 with 2 million each, then I do not see the memory leak. The real
function is actually selecting over many inherited tables (i.e. a
partitioned table).

Thoughts?

Thanks,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof record AS $_$
DECLARE
result_rec  record;
sql text;
BEGIN
sql := $$
   --EXPLAIN
   SELECT * FROM (
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'f