[Reconnoiter-users] Limit postgresql memory usage during rollup job?

Ask Bjørn Hansen ask at develooper.com
Mon Jul 26 12:30:17 EDT 2010


On Jul 26, 2010, at 9:11, Ask Bjørn Hansen wrote:

Alright, in between getting breakfast for the two year old and catching up on email I couldn't let it go.  My previous mail sorta gave the answer -- here's a fix as far as I can tell.

 - ask

--- sql/sprocs/stratcon.rollup_metric_numeric.sql	(revision 1365)
+++ sql/sprocs/stratcon.rollup_metric_numeric.sql	(working copy)
@@ -19,6 +19,8 @@
     v_count         INTEGER;
 BEGIN
 
+    v_i := 0;
+
     -- Get rollup config based on given name, and fail if its wrong name.
     SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;
     IF NOT FOUND THEN
@@ -115,9 +118,10 @@
                 EXECUTE v_sql USING v_segment.count_rows, v_segment.avg_value, v_segment.counter_dev, v_stored_rollup_tm, v_segment.sid, v_segment.name; 
             END IF;
 
-        v_i := v_i + 1;
         END LOOP;
 
+        v_i := v_i + 1;
+
         -- Delete from whence log table
         DELETE FROM metric_numeric_rollup_queue WHERE whence=v_min_whence AND "interval"=in_roll;
 




> I had another quick look this morning.  I noticed that when I added 'raise notice' debugs with v_i it was always NULL.  It seems like in the pgsql language null + 1 is null.
> 
> This patch make it not go on endlessly; so in that sense it's fixing the problem for me.  However, I don't think it's rolling up as much as intended per batch with the fix.   When it gets to the v_i > ... check, v_i is in the thousands; so basically it goes through the rollup loop once -- as far as I can tell.
> 
> 
> - ask
> 
> --- sql/sprocs/stratcon.rollup_metric_numeric.sql	(revision 1365)
> +++ sql/sprocs/stratcon.rollup_metric_numeric.sql	(working copy)
> @@ -19,6 +19,8 @@
>     v_count         INTEGER;
> BEGIN
> 
> +    v_i := 0;
> +
>     -- Get rollup config based on given name, and fail if its wrong name.
>     SELECT * FROM metric_numeric_rollup_config WHERE rollup = in_roll INTO v_conf;
>     IF NOT FOUND THEN
> 
> 
> _______________________________________________
> Reconnoiter-users mailing list
> Reconnoiter-users at lists.omniti.com
> http://lists.omniti.com/mailman/listinfo/reconnoiter-users




More information about the Reconnoiter-users mailing list