Search Postgresql Archives

Re: Why adding BEFORE TRIGGER affect client CPU?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:
 what uses that value client-side, exactly?

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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux