Re: Why adding BEFORE TRIGGER affect client CPU?

On 1/18/19 12:21 PM, George Woodring wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> writes:
> > What is the client server and what is it doing?
>
> Our client creates a temp table
> CREATE TEMP TABLE myraw(LIKE raw INCLUDING DEFAULTS)
> We load data with a copy
> COPY myraw (transferid, pollgrpid, date, data, rrdtypeid, error ) FROM
> STDIN WITH DELIMITER '|'
> We do some checking on the data in the temp table
> SELECT rawdate, raw_maint(rawdate) AS checked FROM (SELECT
> date_trunc('minute', date) AS rawdate FROM myraw GROUP BY rawdate) AS foo;
> Then we save the data into the permanent location
> SELECT transferid, pollgrpid, process_lastpoll(ROW(myraw.*)::raw) AS
> processed FROM myraw ORDER BY transferid
>
> Our change was in the process_lastpoll() function.  We went from
>       IF cntr THEN
>          oval := calc_last_cntr(ptype, pgid, ds, pdate, data);
>       ELSE
>          oval := data;
>       END IF;
>
>       -- Insert data into table
>       -- RAISE DEBUG 'Process: dsnum=%, polltype=%, cntr?=%, pdate=%,
> data=%, oval=%', dsnum, ptype, cntr, pdate, data, oval;
>       UPDATE lastpoll SET polltype=ptype, timeslot=ts, lasttime=pdate,
> lastval=data, outval=oval, error=err WHERE pollgrpid=pgid AND dsnum=ds;
>       IF found THEN
>          RETURN true;
>       END IF;
>       BEGIN
>          INSERT INTO lastpoll (timeslot, pollgrpid, dsnum, polltype,
> lasttime, lastval, outval, error) VALUES (ts, pgid, ds, ptype, pdate,
> data, oval, err);
>          RETURN true;
>       EXCEPTION WHEN unique_violation THEN
>          -- Don't do anything
>       END;
> to this
>       IF NOT cntr THEN
>          oval := data;
>       END IF;
>
>       -- Insert data into table
>       -- RAISE DEBUG 'Process: dsnum=%, polltype=%, cntr?=%, pdate=%,
> data=%, oval=%', dsnum, ptype, cntr, pdate, data, oval;
>       UPDATE lastpoll SET polltype=ptype, timeslot=ts, lasttime=pdate,
> lastval=data, outval=oval, error=err WHERE pollgrpid=pgid AND dsnum=ds;
>       IF found THEN
>          RETURN true;
>       END IF;
>       BEGIN
>          INSERT INTO lastpoll (timeslot, pollgrpid, dsnum, polltype,
> lasttime, lastval, outval, error) VALUES (ts, pgid, ds, ptype, pdate,
> data, oval, err);
>          RETURN true;
>       EXCEPTION WHEN unique_violation THEN
>          -- Don't do anything
>       END;
>
> The calc_last_cntr() was replaced with the trigger.
> from:
> CREATE OR REPLACE FUNCTION calc_last_cntr(ptype text, pgid int4, ds
> int4, pdate timestamp with time zone, data double precision) RETURNS
> double precision AS $$
>    DECLARE
>       mcurr RECORD;
>       res double precision;
>       dwindow int4;
>    BEGIN
>       SELECT lasttime, lastval INTO mcurr FROM lastpoll WHERE
> pollgrpid=pgid AND dsnum=ds;
>       IF mcurr IS NULL THEN
>          -- RAISE DEBUG 'No previous data found for pgid=%', pgid;
>          return NULL;
>       END IF;
>
>       -- Calculate the counter rate
>       -- Check for div by 0
>       dwindow := EXTRACT(EPOCH FROM (pdate - mcurr.lasttime));
>       IF dwindow = 0 THEN
>          RAISE WARNING '(%) Time difference is zero (% - %)',
> current_database(), pdate, mcurr.lasttime;
>          return NULL;
>       END IF;
>
>       res := (data - mcurr.lastval)/dwindow;
>       IF res < 0 THEN
>          -- RAISE DEBUG 'Counter loop for pgid=%', pgid;
>          return data/dwindow;
>       END IF;
>
>       return res;
>    END;
> $$ LANGUAGE plpgsql;
>
> to:
> CREATE OR REPLACE FUNCTION cntr_lastpoll() RETURNS trigger AS $$
>    DECLARE
>       dwindow int4;
>    BEGIN
>       -- Log new value into history if has a date
>       IF NEW.outval IS NULL THEN
>          -- Calculate counter value for this DS.
>          dwindow := EXTRACT(EPOCH FROM (NEW.lasttime - OLD.lasttime));
>          IF dwindow = 0 THEN
>             RAISE WARNING '(%) Time difference is zero (% - %)',
> current_database(), NEW.lasttime, OLD.lasttime;
>             return NEW;
>          END IF;
>
>          NEW.outval := (NEW.lastval - OLD.lastval)/dwindow;
>          IF NEW.outval < 0 THEN
>             NEW.outval := NEW.lastval/dwindow;
>          END IF;
>
>       END IF;
>       RETURN NEW;
>    END;
> $$ LANGUAGE plpgsql;
>
> -- Create Trigger to calculate counter values if needed.
> CREATE TRIGGER lastpoll_counter BEFORE UPDATE ON lastpoll
>    FOR EACH ROW EXECUTE PROCEDURE cntr_lastpoll();
>
> On Fri, Jan 18, 2019 at 2:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
>  what uses that value client-side, exactly?
>

Not claiming to understand all that is going on above.

What I think I see:

1) New process_lastpoll() has:
UPDATE lastpoll SET polltype=ptype, timeslot=ts, lasttime=pdate,
lastval=data, outval=oval, error=err WHERE pollgrpid=pgid AND dsnum=ds;

2) calc_last_cntr() is now function for trigger:
CREATE TRIGGER lastpoll_counter BEFORE UPDATE ON lastpoll
FOR EACH ROW EXECUTE PROCEDURE cntr_lastpoll();

Seems to me you are double clutching on the outval value. You UPDATE it
in 1) then recalculate it again in 2).

As said earlier I do not fully understand what is going on, still seems
to me process_lastpoll() could go away and its code moved into
calc_last_cntr().

>
> The outval number is later read looking for values outside of a
> thresholds.  I did not think about it affecting selects later in the
> code.  All in all everything worked fine, but since the client box is an
> AWS EC2 instance, it started eating all of our CPU credits.
>
> image.png
>
>
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com