On 1/18/19 12:21 PM, George Woodring wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto: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
<mailto:tgl@xxxxxxxxxxxxx>> 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@xxxxxxxxxxx
