Re: Why adding BEFORE TRIGGER affect client CPU?
От | Adrian Klaver |
---|---|
Тема | Re: Why adding BEFORE TRIGGER affect client CPU? |
Дата | |
Msg-id | a05fef20-7191-9062-c8a4-4dcbeb5ad3ae@aklaver.com обсуждение исходный текст |
Ответ на | Re: Why adding BEFORE TRIGGER affect client CPU? (George Woodring <george.woodring@iglass.net>) |
Список | pgsql-general |
On 1/18/19 12:21 PM, George Woodring wrote: > Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.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@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.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.klaver@aklaver.com
В списке pgsql-general по дате отправления: