pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction execution consuming lot of memory and eventually making server unresponsive
5+ messages / 5 participants
[nested] [flat]
* Function execution consuming lot of memory and eventually making server unresponsive
@ 2011-02-24 09:52 Gnanakumar <gnanam@zoniac.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gnanakumar @ 2011-02-24 09:52 UTC (permalink / raw)
To: pgsql-performance
Hi,
We're using PostgreSQL v8.2.3 on RHEL5.
I'm developing a PostgreSQL plpgsql function for one of our application
report. When I try to run the function multiple times (even twice or
thrice), I'm seeing considerable amount of memory being taken up by
PostgreSQL and thereby after sometime, complete server itself comes to
standstill and not responding at all, even am not able to login to my server
using PuTTY client. I then end up physically restarting the server.
Pasted below the function which I'm developing.
Is there something am doing differently in the function that would cause
PostgreSQL to consume lot of memory? In my experience, I can say, this is
the first time I'm seeing PostgreSQL consuming/eating lot of memory and
causing severe performance issue and eventually making server come to
standstill. Also, I can say that another 2 functions which I'm calling from
within this function ("get_hours_worked" and
"convert_hours_n_minutes_to_decimal") do not have any performance issues,
since those 2 functions we're already using in some other reports and have
not found any performance issues.
Experts suggestions/recommendations on this are highly appreciated.
For example, I would call this function like: SELECT
hours_worked_day_wise_breakup(90204,23893,38921, '01-01-2010 00:00:00',
'12-31-2010 23:59:59');
Output of this function will be like this:
8.00-typ1,4.25-typ2,0.00-typ5,6.00-typ3,8.00-typ4
Logic of this function: Given any 2 dates and filter inputs (input1, input2,
input3), it would return hours worked for each day (along with a suffix -
typ[1,2,3,4]) in comma separated form. In above example, I'm trying to run
this function for one year.
CREATE or replace FUNCTION hours_worked_day_wise_breakup(numeric, numeric,
numeric, varchar, varchar) RETURNS VARCHAR AS '
DECLARE
p_input1 ALIAS FOR $1;
p_input2 ALIAS FOR $2;
p_input3 ALIAS FOR $3;
p_startdate ALIAS FOR $4;
p_enddate ALIAS FOR $5;
v_loopingdate VARCHAR;
v_cur_start_date VARCHAR;
v_cur_end_date VARCHAR;
v_hours_in_decimal NUMERIC := 0.00;
v_returnvalue VARCHAR := '''';
BEGIN
v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP
v_cur_start_date := v_loopingdate || '' 00:00:00'';
v_cur_end_date := v_loopingdate || '' 23:59:59'';
IF (LENGTH(TRIM(v_returnvalue)) >0) THEN
v_returnvalue := v_returnvalue || '','';
END IF;
v_hours_in_decimal :=
convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 7,
1, -1, p_input3, v_cur_start_date, v_cur_end_date));
IF (v_hours_in_decimal > 0) THEN
v_returnvalue := v_returnvalue || v_hours_in_decimal
|| ''-typ1'';
v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
interval ''1 day''), ''mm-dd-yyyy'');
CONTINUE;
END IF;
v_hours_in_decimal :=
convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 6,
1, -1, p_input3, v_cur_start_date, v_cur_end_date));
IF (v_hours_in_decimal > 0) THEN
v_returnvalue := v_returnvalue || v_hours_in_decimal
|| ''-typ2'';
v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
interval ''1 day''), ''mm-dd-yyyy'');
CONTINUE;
END IF;
v_hours_in_decimal :=
convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 4,
1, -1, p_input3, v_cur_start_date, v_cur_end_date));
IF (v_hours_in_decimal > 0) THEN
v_returnvalue := v_returnvalue || v_hours_in_decimal
|| ''-typ3'';
v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
interval ''1 day''), ''mm-dd-yyyy'');
CONTINUE;
END IF;
v_hours_in_decimal :=
convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 3,
1, -1, p_input3, v_cur_start_date, v_cur_end_date));
IF (v_hours_in_decimal > 0) THEN
v_returnvalue := v_returnvalue || v_hours_in_decimal
|| ''-typ4'';
v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
interval ''1 day''), ''mm-dd-yyyy'');
CONTINUE;
END IF;
v_hours_in_decimal := 0.00;
v_returnvalue := v_returnvalue || v_hours_in_decimal ||
''-typ5'';
v_loopingdate := TO_CHAR((DATE(v_loopingdate) + interval ''1
day''), ''mm-dd-yyyy'');
END LOOP;
RETURN v_returnvalue;
END ;'
LANGUAGE 'plpgsql';
Regards,
Gnanam
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function execution consuming lot of memory and eventually making server unresponsive
@ 2011-02-24 10:29 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gnanakumar <gnanam@zoniac.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2011-02-24 10:29 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-performance
Hello
It hard to say where can be a problem
I see a some risks
a) v8.2.3 isn't last version of 8.2 line
b) why you use a varchar data type for v_loopingdate,
v_cur_start_date, v_cur_end_date - it's bad idea? - you have to do
cast between date and varchar - all operation are slower and needs
more memory - it should be timestamp
Regards
Pavel Stehule
2011/2/24 Gnanakumar <gnanam@zoniac.com>:
> Hi,
>
> We're using PostgreSQL v8.2.3 on RHEL5.
>
> I'm developing a PostgreSQL plpgsql function for one of our application
> report. When I try to run the function multiple times (even twice or
> thrice), I'm seeing considerable amount of memory being taken up by
> PostgreSQL and thereby after sometime, complete server itself comes to
> standstill and not responding at all, even am not able to login to my server
> using PuTTY client. I then end up physically restarting the server.
>
> Pasted below the function which I'm developing.
>
> Is there something am doing differently in the function that would cause
> PostgreSQL to consume lot of memory? In my experience, I can say, this is
> the first time I'm seeing PostgreSQL consuming/eating lot of memory and
> causing severe performance issue and eventually making server come to
> standstill. Also, I can say that another 2 functions which I'm calling from
> within this function ("get_hours_worked" and
> "convert_hours_n_minutes_to_decimal") do not have any performance issues,
> since those 2 functions we're already using in some other reports and have
> not found any performance issues.
>
> Experts suggestions/recommendations on this are highly appreciated.
>
> For example, I would call this function like: SELECT
> hours_worked_day_wise_breakup(90204,23893,38921, '01-01-2010 00:00:00',
> '12-31-2010 23:59:59');
> Output of this function will be like this:
> 8.00-typ1,4.25-typ2,0.00-typ5,6.00-typ3,8.00-typ4
> Logic of this function: Given any 2 dates and filter inputs (input1, input2,
> input3), it would return hours worked for each day (along with a suffix -
> typ[1,2,3,4]) in comma separated form. In above example, I'm trying to run
> this function for one year.
>
> CREATE or replace FUNCTION hours_worked_day_wise_breakup(numeric, numeric,
> numeric, varchar, varchar) RETURNS VARCHAR AS '
>
> DECLARE
> p_input1 ALIAS FOR $1;
> p_input2 ALIAS FOR $2;
> p_input3 ALIAS FOR $3;
> p_startdate ALIAS FOR $4;
> p_enddate ALIAS FOR $5;
>
> v_loopingdate VARCHAR;
> v_cur_start_date VARCHAR;
> v_cur_end_date VARCHAR;
> v_hours_in_decimal NUMERIC := 0.00;
> v_returnvalue VARCHAR := '''';
>
> BEGIN
> v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
> WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP
> v_cur_start_date := v_loopingdate || '' 00:00:00'';
> v_cur_end_date := v_loopingdate || '' 23:59:59'';
>
> IF (LENGTH(TRIM(v_returnvalue)) >0) THEN
> v_returnvalue := v_returnvalue || '','';
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 7,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ1'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 6,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ2'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 4,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ3'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 3,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ4'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal := 0.00;
> v_returnvalue := v_returnvalue || v_hours_in_decimal ||
> ''-typ5'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) + interval ''1
> day''), ''mm-dd-yyyy'');
> END LOOP;
>
> RETURN v_returnvalue;
>
> END ;'
> LANGUAGE 'plpgsql';
>
> Regards,
> Gnanam
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function execution consuming lot of memory and eventually making server unresponsive
@ 2011-02-24 12:29 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2011-02-24 12:29 UTC (permalink / raw)
To: pgsql-performance; gnanam@zoniac.com
"Gnanakumar" wrote:
> We're using PostgreSQL v8.2.3 on RHEL5.
http://www.postgresql.org/support/versioning
The 8.2 release is up to 8.2.20:
http://www.postgresql.org/
By the way, 8.2 is scheduled to go out of support later this year:
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
You might want to start planning to upgrade.
> I'm developing a PostgreSQL plpgsql function for one of our
> application report. When I try to run the function multiple times
> (even twice or thrice), I'm seeing considerable amount of memory
> being taken up by PostgreSQL and thereby after sometime, complete
> server itself comes to standstill and not responding at all, even
> am not able to login to my server using PuTTY client. I then end up
> physically restarting the server.
You might want to review the bug fixes since 8.2.3 and see if any
involve memory leaks:
http://www.postgresql.org/docs/8.2/static/release.html
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function execution consuming lot of memory and eventually making server unresponsive
@ 2011-02-24 15:14 Merlin Moncure <mmoncure@gmail.com>
parent: Gnanakumar <gnanam@zoniac.com>
1 sibling, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2011-02-24 15:14 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-performance
On Thu, Feb 24, 2011 at 3:52 AM, Gnanakumar <gnanam@zoniac.com> wrote:
> Hi,
>
> We're using PostgreSQL v8.2.3 on RHEL5.
>
> I'm developing a PostgreSQL plpgsql function for one of our application
> report. When I try to run the function multiple times (even twice or
> thrice), I'm seeing considerable amount of memory being taken up by
> PostgreSQL and thereby after sometime, complete server itself comes to
> standstill and not responding at all, even am not able to login to my server
> using PuTTY client. I then end up physically restarting the server.
>
> Pasted below the function which I'm developing.
>
> Is there something am doing differently in the function that would cause
> PostgreSQL to consume lot of memory? In my experience, I can say, this is
> the first time I'm seeing PostgreSQL consuming/eating lot of memory and
> causing severe performance issue and eventually making server come to
> standstill. Also, I can say that another 2 functions which I'm calling from
> within this function ("get_hours_worked" and
> "convert_hours_n_minutes_to_decimal") do not have any performance issues,
> since those 2 functions we're already using in some other reports and have
> not found any performance issues.
>
> Experts suggestions/recommendations on this are highly appreciated.
>
> For example, I would call this function like: SELECT
> hours_worked_day_wise_breakup(90204,23893,38921, '01-01-2010 00:00:00',
> '12-31-2010 23:59:59');
> Output of this function will be like this:
> 8.00-typ1,4.25-typ2,0.00-typ5,6.00-typ3,8.00-typ4
> Logic of this function: Given any 2 dates and filter inputs (input1, input2,
> input3), it would return hours worked for each day (along with a suffix -
> typ[1,2,3,4]) in comma separated form. In above example, I'm trying to run
> this function for one year.
>
> CREATE or replace FUNCTION hours_worked_day_wise_breakup(numeric, numeric,
> numeric, varchar, varchar) RETURNS VARCHAR AS '
>
> DECLARE
> p_input1 ALIAS FOR $1;
> p_input2 ALIAS FOR $2;
> p_input3 ALIAS FOR $3;
> p_startdate ALIAS FOR $4;
> p_enddate ALIAS FOR $5;
>
> v_loopingdate VARCHAR;
> v_cur_start_date VARCHAR;
> v_cur_end_date VARCHAR;
> v_hours_in_decimal NUMERIC := 0.00;
> v_returnvalue VARCHAR := '''';
>
> BEGIN
> v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
> WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP
> v_cur_start_date := v_loopingdate || '' 00:00:00'';
> v_cur_end_date := v_loopingdate || '' 23:59:59'';
>
> IF (LENGTH(TRIM(v_returnvalue)) >0) THEN
> v_returnvalue := v_returnvalue || '','';
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 7,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ1'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 6,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ2'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 4,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ3'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 3,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
> IF (v_hours_in_decimal > 0) THEN
> v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ4'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
> CONTINUE;
> END IF;
>
> v_hours_in_decimal := 0.00;
> v_returnvalue := v_returnvalue || v_hours_in_decimal ||
> ''-typ5'';
> v_loopingdate := TO_CHAR((DATE(v_loopingdate) + interval ''1
> day''), ''mm-dd-yyyy'');
> END LOOP;
>
> RETURN v_returnvalue;
>
> END ;'
> LANGUAGE 'plpgsql';
It's a pretty safe bet you are stuck in the loop (either infinite, or
very long) using string concatenation operator on the return code. ||
is not designed for extremely heavy use on large strings in a loop.
Your entire function could probably be reduced to one SQL expression
with some thought.
merlin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function execution consuming lot of memory and eventually making server unresponsive
@ 2011-02-24 15:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-02-24 15:58 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: gnanam@zoniac.com; pgsql-performance
Merlin Moncure <mmoncure@gmail.com> writes:
> Your entire function could probably be reduced to one SQL expression
> with some thought.
Or if not that, at least try to get rid of the use of varchar. All
those forced varchar-to-date-and-back conversions are expensive.
I'm also more than a tad worried by this:
> v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
> WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP
There's nothing here guaranteeing that DATE() will think its input
is in mm-dd-yyyy format. If DateStyle is set to something else,
the logic would at least be wrong, and very possibly that explains
your infinite loop.
Learn to use PG's type system instead of fighting it. Your code
will be shorter, clearer, faster, and less error-prone.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-02-24 15:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-24 09:52 Function execution consuming lot of memory and eventually making server unresponsive Gnanakumar <gnanam@zoniac.com>
2011-02-24 10:29 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-24 15:14 ` Merlin Moncure <mmoncure@gmail.com>
2011-02-24 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-24 12:29 Re: Function execution consuming lot of memory and eventually making server unresponsive Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox