Seite 1 von 3 123 LetzteLetzte
Ergebnis 1 bis 10 von 27

Thema: Viele Messwerte schnell speichern

  1. #1
    davidy ist offline Neuer Benutzer
    Registriert seit
    08.10.2009
    Beiträge
    10

    Standard Viele Messwerte schnell speichern

    Hi ihr,

    Ich nutze aktuelles Debian mit PHP und Postgres Datenbank.

    Also meine Problemstellung ist:
    - Viele Messwerte (doubles) in einer Postgres Datenbank speichern (viele heißt dabei >30 pro Sekunde)

    Ich hab das wie folgt gelöst:
    - Eine Tabelle "values": Messwert, Sensor_ID, Uhrzeit
    - Eine Tabelle "values_avg": in der die Druchschnittswerte pro Min/Std/Tag gespeichert werden. (Damit Diagramme ohne Berechnung gezeichnet werden können)
    - Eine plpgsql Function die eben diesen Messwert/Sensor_ID als Input bekommt, und in "values" direkt reinschreibt, und bei "values_avg" jeweils den aktuellen Wert dort einer TMP Spalte addiert, und durch die Anzahl messwerte teilt, also ein schneller Durchschnittswert.(jeweils für Tag/Std/Min)
    - Habe auch alle möglcihen Schrauben in der Config von postgres gredreht, damit er mehr RAM nutzt etc. (geändert ist nun eigentlich nur: shared_buffers = 700MB und effective_cache_size = 1280MB)
    - Indexe sind passende angelegt (jeweils für die Sensor_IDs, und den Tag/Std/Min bei "values_avg")

    Alles läuft soweit auch gut, nur zu langsam, bzw. die Festplatte bremst alles aus (erkennbar am 50% "wait" Wert bei top).

    Momentan dauert ein Aufruf der plpgsql Function ca. 70-100ms, und so bekomme ich ja max. 15 Werte/Sekunde hin.

    Habt ihr vielleicht einen Tipp wie ich das (Softwaremäßig) beschleunigen könnte?

    Ideen:
    - Kann man Postgres sagen, dass er nicht sofort, sondern verzögert, dann in einem Rutsch erst auf die Festplatte schreiben soll, und nicht jeden Einzelwert?
    - Ist es vielleicht sinnvoll in der Anwendung die Daten ein paar Sekunden zu sammeln und dann als "Liste" an eine plpgsql zu geben?
    - Ist vielleicht eine ganz andere Tabellenstruktur besser?
    - Vielleicht eine "Memory" (?) Tabelle im Hauptspeicher, die nur alle par Minunte weggeschrieben wird?

    Vielen Dank fürs durchlesen:-)

    Gruß
    David

  2. #2
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard

    das sind ueber 2,5 mio / tag ..

    da gibt es einiges was man machen kann

    - prepared statements verwenden
    - päckchen deiner messwerte in transaktionen sammeln
    oder vorher in einem string zeilenweise (meinetwegen sowas CSV artiges) und dann mit einem mal auf den server schicken

    - daneben kann man auch asynchrone statements absetzen - ob das aber mit php geht weiss ich nicht. also waerend der server deine daten verarbeitet und aufbereitet koennte ein paralleler prozess/thread bereits neue daten sammeln ..
    - und noch mehr ..

    müssen alle messwerte erhalten bleiben? oder sind nur die aggregierten werte wichtig?

  3. #3
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Ich habe ähnliche Projekte mit wesentlich mehr Daten in dem Zeitraum bereits durchgeführt.

    Vorneweg: der Ansatz mit einem einzelnen Funktionsaufruf pro Messdatensatz wird nicht funktionieren. Das ist ja noch schlechter und langsamer als die Idee, jeden Datensatz einzeln zu schreiben ;-)

    Zu allem weiteren kann ich aber nur etwas sagen, wenn ich (wesentlich) mehr Details kenne und wenn du bereit bist, das gesamte Konzept zu überdenken.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  4. #4
    davidy ist offline Neuer Benutzer
    Registriert seit
    08.10.2009
    Beiträge
    10

    Standard

    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:

    Code:
    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:

    Code:
    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:

    Code:
    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

  5. #5
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Stored Procedures und "plpgsql"-Funktionen sind zwei Namen für ein und die gleiche Sache.

    Letztendlich läuft es darauf hinaus, dass ein einzelner Insert so ziemlich das langsamste ist, wie man Daten schreiben kann. Du verlangsamst das halt noch, indem du jeweils eine Funktion aufrufst.


    Konzeptionsmäßig würde ich Schreiben und Auslesen trennen. Nur weil das eine Webanwendung ist, bedeutet das nicht, dass man das alles zusammenlegen muss. Außerdem weiß ich aus vergangenen Projekten, dass ein gewisser Fallback für Zeiten ohne Datenbank (z. B. Wartung) sehr hilfreich ist. Dadurch muss man keine einhundertprozentige Verfügbarkeit der Datenbank voraussetzen.

    Das Berechnen der Werte würde ich - wenn möglich - nachträglich ausführen. Dadurch kann man die Daten ggf. schneller schreiben und die benötigten Werte für mehrere Zeilen/Datensätze auf einen Schlaf ausrechnen.


    David: vielleicht sollten wir zu dem Projekt mal telefonieren?
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  6. #6
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von davidy Beitrag anzeigen

    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
    Hm. Noch sehr früh und ohne Kaffee.

    Warum speicherst Du einen Timestamp in 3 Feldern (Tag, Stunde, Minute)?

    Ist es nötig, bei jedem Insert auch gleich die values_avg zu aktualisieren oder würde auch ein Trigger reichen, bei bei Insert in die values -Tabelle prüft, ob z.B. eine neue Minute oder Stunde angefangen hat und erst dann die Mittelwerte berechnet und speichert? Das würde enorm die Anzahl der Updates auf die Mittelwerttabelle reduzieren.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  7. #7
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard

    @ne menge messwerte
    nunja ich wollte nur daran erinnern, dass die db sehr schnell stark anwaechst .. ich kenne sowas auch von QS systemen die ich mal gebaut habe - mit ner datenbank die man heute schon fast nicht mehr kennt *g*

    aggregationen (avg) beim einfuegen zu berechnen muss man sich halt ueberlegen ob man das unbedingt moechte ... es ist prinzipiell machbar (auch schnell) - dann aber eben nicht nur mit avg(feld), sondern summen und anzahl mitschreiben -> redundanz mehr daten ..

    ist halt immer so ne sache entweder performance oder platz

    wenn man das alles nicht sofort braucht, dann kann man die messwerte auch gleich in eine textdatei appenden .. pragmatisch CSV -> und dann per bulk copy bei bedarf in die DB importieren .. aber ich denke schon, dass er moeglichst sofort ergebnisse sehen will .. hmm muss man halt entscheiden

    das nadelör bei sovielen transaktionen ist die latenz von client zu dbserver .. 50 - 100 inserts sind ja nicht wirklich eine großenordnung fuer einen modernen rechner - wenn man halt nicht ueber irgednwelche netzwerke operiert und bei jedem aufruf von irgendwas mehrere millisekunden vergeuded .. da sollte man zusammenpacken ..

    vielleicht kann ja die software am messgeraet auch diese messwerte in einer textdatei zusammenfassen .. und dann per upload dem php uebergeben, das php koennte die datei dann mit einem rutsch in den dbserver schieben, wo sie zerflueckt wird - postgres hat sehr maechtige und elegante string-funktionen, oder auch einen sehr schoenen copy befehl
    Geändert von symbiont (09.10.2009 um 08:50 Uhr)

  8. #8
    davidy ist offline Neuer Benutzer
    Registriert seit
    08.10.2009
    Beiträge
    10

    Standard

    @ads:
    Das Stored Procedures und "plpgsql" das gleiche sind wusste ich, wir hatten vorher von "prepared statements" und "asynchrone statements" geredet:-)

    @akretschmer:

    - Den Timestamp speicher ich in 3 Felder, da ich der Meinung war das es schneller geht, einen Index auf drei Intergerwert zu suchen, als bei einem Timstamp größer kleiner Vergleiche. Wie würde ich denn auch den Tagesdurchschnittswert als Timestamp Speichern? Die minunte sind klar, das geht, aber für Stunde und Tag müsste cih ja dann doch wieder zwei Spalten einfügen is_dayavg und is_houravg oder fällt dir eine einfache Lösung ein?

    - Stimmt, ich könnte nur jede Minute den Mittelwert bilden, aber ich habe mal nachgesehen, da es soviele verschiedene Messwerte gibt, wird der Mittelwert/Minute/Messwert meist eh nur aus 1-2 Messwerten gebildet, ein Trigger ändert da also nicht viel, oder? Und der Stundenmittelwert sollte schon recht zeitnah berechnet werden (für LIVE Diagramme etc.)

    @symbiont:
    "software am messgeraet auch diese messwerte in einer textdatei"
    - Ich denke eher ist es schneller, wenn ich 50-100 Messwerte im RAM zwischenspeichern und dann zusammen einfüge.

    Ich habs heute Nacht mal mit Transaktionen versucht:
    Also mit:

    Code:
    BEGIN WORK
    SELECT insert_value('2009-10-12 12:12:00'::timestamp without time zone,1234,12.45::double precision) as value
    SELECT insert_value('2009-10-12 12:12:00'::timestamp without time zone,1235,1.45::double precision) as value
    SELECT insert_value('2009-10-12 12:12:00'::timestamp without time zone,1236,2.45::double precision) as value
    ... 50x
    COMMIT
    Allerdings hat sich für mich da kein großer Unterschied ergeben, im Endeffekt war die Festplatte immernoch total am routieren, und pro Messwert hat eher sogar länger gedauert. Oder sollte ich mla versuchen, nicht alle paar MilliSekunden ein insert zu machen, sondern zuerst die Sachen zu sammeln und als EIN großer Datenbankrequet dann abzusenden? Aber macht ein WORK nicht genau das?

    Wie würdet ihr denn so die Tabellengrundstruktur für so ne Sache anlegen?

  9. #9
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Dein Problem sind immer noch die Funktionsaufrufe, die machen das langsam.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  10. #10
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard

    er koennte ja diese aufrufe im speicher zusammenbauen (wie ers oben darstellt) und miteinem mal an den db-server schicken .. also nicht jede zeile einzeln .. sondern den ganzen block mit execBatch(..) [oder was weiss ich wie so eine fkt in PHP heisst] ausfuehren

    dann waere das ne recht fixe geschichte .. gut .. die funktion koennte man ueberarbeiten - die ist nicht ganz huebsch - aber das ist ein anderes thema

Seite 1 von 3 123 LetzteLetzte

Ähnliche Themen

  1. Select - Zu viele Ergebnisse
    Von karl_ha im Forum SQL
    Antworten: 14
    Letzter Beitrag: 19.02.2010, 13:21
  2. Frage Messwerte in 5-Minuten - Raster zusammenfassen
    Von DerDa im Forum SQL
    Antworten: 2
    Letzter Beitrag: 19.09.2009, 11:02
  3. Antworten: 4
    Letzter Beitrag: 27.11.2008, 09:43
  4. Abfrage beim ersten Mal langsam, dann schnell
    Von slartibartfass im Forum SQL
    Antworten: 1
    Letzter Beitrag: 29.02.2008, 10:34
  5. Zu viele ORs?
    Von Roogla im Forum SQL
    Antworten: 8
    Letzter Beitrag: 20.04.2007, 19:34

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum