Hi,
erstmal danke an euch beide!
@symbiont:
-
Ja es sind jede Menge Messwerte:-)
-
Prepared statements denke ich brauche ich nicht, da meine SQL Anweisungen ja doch alle in einer “plpgsql” Funktion ablaufen?
-
Transaktionen klingt schonmal nach er guten Idee, ich könnte 10-100 Messwerte sammeln und in einer Transaktion dann senden.
-
Asynchrone Statement klingt gut, aber so wie cih das bei php im handbuch lesen arbeiten die noch dran: “… um zukünftig asynchrone Verbindungen zu unterstützen…”
-
Jein, erhalten werden müssen die Messwerte sccon, aber mein gedanke ist, (die Rohdaten) nach ein paar Monaten dann in CSV oder sowas gezippt zu exportieren und irgendwo zu archivieren. Dann störts die Datenbank auch nicht mehr:-) Die Mittelwerte würde ich aber gerne alle behalten.
@ads:
-
Warum ist EIN Funktionaufruf pro Messwert schlechter, als 5 einzelne Datenbank INSERTs? Ich dachte gerade dafür wären die Functions da, und die hatten das auch sichtlich beschleunigt damals…
-
ich gebe dir gerne mehr Details, und bin auch gerne bereit für mehr geschwindigkeit alles umzustellen!
Hier genaueres:
-
Quelle der Daten ist eine Socket Anwendung, die die Daten ziemlich zügig weitergibt. (Output in eine TXT Datei oder Telnet Konsole sind problemlos >100 Werte/Sek. möglich, daran haperts also wohl nicht)
-
In PHP habe ich einen Socket Client geschrieben (ja ich könnts ja auch in C++ machen…, aber dessen Geschwindigkeit drosselt auch nicht, und läuft bisher sehr stabil, das ganze ist an sich eine Webanwendung, daher sind die meisten Teile in PHP). Jede Datenzeile die ich dort empfange, sende ich dann mittels:
SELECT insert_value('2009-10-12 12:12:00'::timestamp without time zone,1234,12.45::double precision) as value
an die plpgsql Funktion. Die beiden Tabellen sind wie folgt aufgebaut:
CREATE TABLE values
(
sensor_id bigint NOT NULL,
"value" double precision,
value_text character varying(255),
value_time timestamp without time zone NOT NULL,
CONSTRAINT values_pkey PRIMARY KEY (sensor_id, value_time)
)
WITH (OIDS=FALSE);
CREATE TABLE values_avg
(
sensor_id bigint,
"day" date,
"hour" smallint,
value_avg double precision,
value_min double precision,
value_max double precision,
value_count bigint,
value_variance character varying,
value_stddev double precision,
"minute" smallint,
temp_avg double precision,
CONSTRAINT values_avg_sensor_id_key UNIQUE (sensor_id, day, hour, minute)
)
WITH (OIDS=FALSE);
CREATE TABLE sensors
(
id integer NOT NULL,
value_time timestamp without time zone,
value_cur double precision,
value_min double precision,
value_max double precision,
is_scalar smallint DEFAULT 0,
visible smallint DEFAULT 1,
value_before double precision,
value_beforetime timestamp without time zone,
CONSTRAINT sensors_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
und die Funktion:
CREATE OR REPLACE FUNCTION insert_value(date2 timestamp without time zone, sensor2 integer,
value2 double precision)
RETURNS integer AS
$BODY$DECLARE
tmpmin double precision;
tmpmax double precision;
tmpval double precision;
tmpbefore double precision;
tmpdatebefore timestamp without time zone;
comma2 integer;
hour2 integer;
minute2 integer;
temp_avg2 double precision;
day2 date;
BEGIN
-- Hole alte Daten
SELECT value_min, value_max, value_cur, value_time INTO tmpmin, tmpmax, tmpbefore,
tmpdatebefore FROM sensors WHERE id=sensor2;
tmpval := value2;
-- Suche min, max
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
-- Wenn es der neuste ist Wert, dann Speicher ihn ab
IF ((tmpdatebefore is null OR tmpdatebefore<=date2)) THEN
UPDATE sensors SET value_time = date2, value_min = tmpmin, value_max = tmpmax, value_cur =
tmpval, value_before=tmpbefore, value_beforetime=tmpdatebefore WHERE id=sensor2;
END IF;
--Prüfe ob zu dieser Uhrzeit schon ein Wert viorhanden ist (aus Backups etc.)
SELECT value INTO temp_avg2 FROM values WHERE sensor_id=sensor2 AND value_time=date2;
IF FOUND THEN
UPDATE values SET value=tmpval WHERE sensor_id=sensor2 AND value_time=date2;
ELSE
INSERT INTO values (sensor_id, value, value_time) VALUES (sensor2, tmpval, date2);
END IF;
-- Bilde Mittelwerte für diese Minute
day2 := date2::date;
hour2 := date_part('hour',date2);
minute2 := date_part('minute',date2);
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
-- Bilde Mittelwerte für diese Stunde
minute2 := -1;
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
-- Bilde Mittelwerte für diesen Tag
hour2 := -1;
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
RETURN 0;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Nochmla gesagt, ich baue auch gern alels um, wenns dann merklich besser geht:-) Wichtig waren mir halt eben die Mittelwertbildung, damit ich einfach Diagramme zeichen kann.
Gruß David