4. db Schema mit großen Datenmengen

db Schema mit großen Datenmengen

  • ?

    Hallo,

    ich habe das Problem, dass ich relativ große Datenmengen in kurzer Zeit in einer postgreSQL Datenbank ablegen und auch wieder auslesen muss. Das (reduzierte) Schema sieht so aus, dass in einer Tabelle Datenpunktdefinitionen stehen und in zwei weiteren die Messwerte zu diesen Datenpunkten (normalisiert). In der Tabelle measurement_data existieren zwei Indexspalten als foreign_key in die Tabellen measurements und measurement_items

    Tabelle measurement_items:

measurement_item_id | name | description  | flags | unit | etc...
--------------------+------+--------------+-------+------+--------
                  1 | DP1  | Datenpunkt 1 |     0 |   °C |
                  2 | DP2  | Datenpunkt 2 |     0 |   °C |

Tabelle measurements:

measurement_id | timestamp           | Status 
---------------+---------------------+--------
             1 | 2012-06-14 14:00:00 |      0
             2 | 2012-06-14 14:00:01 |      0

Tabelle measurement_data:

 measurement_data_id | measurement_id | measurement_item_id | value | status
    (primary_key)    | (foreign_key)  |    (foreign_key)    |       |
---------------------+----------------+---------------------+-------+--------
                   1 |              1 |                   1 |   1.0 |     0
                   2 |              1 |                   2 |   1.0 |     0
                   . |              . |                   . |   1.0 |     0
                   . |              . |                   . |   1.0 |     0
                 300 |              1 |                 300 |   1.0 |     0

    Das Problem ist ein großes Datenaufkommen, pro Sekunde können bis zu 3.000 Messwerte eintreffen, da stoße ich mit dem normalisierten db Schema schnell an die Grenze (Schreibgeschwindigkeit: 0.3 - 0.5 komplette Datensätze/s). Das Erfassungsintervall der einzelnen Datenpunkte ist unterschiedlich, d.h. zu jedem Messzeitpunkt müssen die Werte unterschiedlicher Datenpunkte geschrieben werden.
    Dazu habe ich mir überlegt, die beiden Tabellen measurements und measurement_data zu einer Tabelle measurements zusammenzufassen und in der Datenspalte data Messdaten als Tupel (ID,Value,Status) in Textform abzulegen. Damit wird das Einfügen um den Faktor 400-500 schneller, allerdings ist das Auslesen umständlich und langsam, da der Text jeder Zeile geparst werden muss.

    Tabelle measurements:

measurement_id | timestamp           | Status | data
---------------+---------------------+--------+-----------------------
             1 | 2012-06-14 14:00:00 |      0 | #0001;1.0;0;#0002;2.0;0
             2 | 2012-06-14 14:00:01 |      0 | #0001;1.0;0;#0005;5.0;0

    Meine postgreSQL API unterstützt leider keine Compound Types, damit scheidet ein Array aus (ID,Value,Status) aus. Performancetests mit einem 2D Array aus double lieferten keine Verbesserung.

    Hat jemand Ideen/Erfahrung damit, wie man so ein Problem mit möglichst größter Performance lösen kann?

    0
  • J

    Ich habe gerade mal ausprobiert, wie viele Daten ich speichern kann.

    create table test (lfnr bigint,value1 bigint,value2 bigint,value3 bigint);

create sequence s start 1;

select now();
begin;
insert into test values (nextval('s'),1,2,3);
243040 mal
commit;
select now();

    das hat 78 Sekunden gedauert also 3115 Einträge pro Sekunde.
    Was musst du denn alles schreiben?
    Und wie machst du das genau?

    jenz

    0
  • ?

    Zuerst lege ich einen Datensatz in der Tabelle measurements an, danach folgen 300-400 Datensätze in der Tabelle measurement_data :

    Pseudocode:

    BeginTransaction();
   unsigned int Id = INSERT INTO measurements (timestamp,status) VALUES( '2012-06-15 15:00:00', 0 );

   for( unsigned int i = 0; i < 400; ++i )
   {
      INSERT INTO measurement_data ( measurement_id, measurement_item_id, value, status) VALUES ( Id, MeasurementItemId, 0.0, 0 );
   }
   Commit();

    Die Unterschiede zwischen deinen und meinen Statements sind
    - ich muss jeden measurements Datensatz in einer Transaktion abwickeln, ich kann da keinen Bulk-Insert machen
    - die Tabelle measurement_data enthält zwei foreign keys, für die das DBMS Indizes führen muss. Das kostet Zeit...

    Für ein Einfügen eines kompletten Datensatzes (1 Eintrag in measurements , ca. 300 Einträge in measurement_data ) braucht das DBMS ca. 0.3s.

    0
  • R

    Messwerte in ner relationalen datenbank ... mit ner lfd als key ?
    Ist das nich schon bissi zu naiv ... err falsch gedacht ?

    du weisst scho, das je nach impl, nen indizierter key einfügen immer mehr Ressourcen verbraucht, je groesser die tabelle wird / je mehr daten du hasst.

    Ich wuerd generell hinterfragen, ob du in deiner Logic ne DB-Beziehung ueber nen key brauchst zum messwert !?

    Generell wuerd ich versuchen, die Messwerte nicht in der DB zu speichern ...wenn doch online waehrend der Messung irgendwie in ein system pushen musst, dann wuerd ich keine rdbms nehmen sondern was nosql maessiges ...

    wenn doch unbedingt postgreSQL sein muss, wuerd ich die daten ned als tabelle sondern als blob versuchen zu speichern ....

    ne tabelle in ner rdbms ist definitiv nicht der richtige Ort fuer Messdaten in Massen ^^

    willst du dir dann auch so standard errechnete werte (mittelwert, Standardabweichung) per SQL ausrechnen lassen ? Das performant hinzubekommen wird aber dann kosten ... zur freude des HW anbieters fuer den Server, wo die db drauf laeuft ^^

    ciao

    0
  • ?

    RHBaum schrieb:

    Messwerte in ner relationalen datenbank ... mit ner lfd als key ?
    Ist das nich schon bissi zu naiv ... err falsch gedacht ?

    du weisst scho, das je nach impl, nen indizierter key einfügen immer mehr Ressourcen verbraucht, je groesser die tabelle wird / je mehr daten du hasst.

    Ich wuerd generell hinterfragen, ob du in deiner Logic ne DB-Beziehung ueber nen key brauchst zum messwert !?

    Hab den primary key schon rausgeworfen, aber die Indexe für die Messung und den Datenpunkt brauche ich schon.

    RHBaum schrieb:

    Generell wuerd ich versuchen, die Messwerte nicht in der DB zu speichern ...wenn doch online waehrend der Messung irgendwie in ein system pushen musst, dann wuerd ich keine rdbms nehmen sondern was nosql maessiges ...

    Hatte ich auch schon überlegt, allerdings sprechen zwei Gründe dagegen:

    - Codegear RAD Studio 2007, das wird nicht von besonders vielen Bibliotheken unterstützt und ist selbst auch nicht so berühmt dafür, den C++ Standard zu unterstützen. Ich muss also erst Mal ein NoSQL DBMS finden, das ich benutzen kann.

    - Chef will sich lieber an "Standardlösungen" halten und glaubt, dass NoSQL DBMS nicht dazugehören.

    RHBaum schrieb:

    wenn doch unbedingt postgreSQL sein muss, wuerd ich die daten ned als tabelle sondern als blob versuchen zu speichern ....

    ne tabelle in ner rdbms ist definitiv nicht der richtige Ort fuer Messdaten in Massen ^^

    Hatte ich ja schon versucht, indem ich die Tabellen measurements und measurement_data zusammenlege und die Messdaten als String in eine Tabellenspalte serialisiere. BLOB wären natürlich toll, aber da setzt mir meine PostgreSQL Wrapper API einige Grenzen. Für die Abfrage existieren zwei Modi: schnell und kompatibel. Der schnelle Modus macht Probleme mit BLOBs, der langsame kann zwar BLOBs, ist dafür aber langsam. Die schnellste Lösung ist im Moment das Serialisieren/Parsen von Strings.

    RHBaum schrieb:

    willst du dir dann auch so standard errechnete werte (mittelwert, Standardabweichung) per SQL ausrechnen lassen ? Das performant hinzubekommen wird aber dann kosten ... zur freude des HW anbieters fuer den Server, wo die db drauf laeuft ^^

    Nö, will ich nicht, dass passiert ggf. auf dem Client. Das System skaliert horizontal, zur Not können wir halt mehrere Server aufstellen.

    Danke für deinen Beitrag 🙂

    Mir ist schon klar, dass ich hier aufgrund der Anforderungen zwischen Pest und Cholera wählen muss. Ich glaube, dass ich mit meinem Ansatz schon nah an der maximalen Geschwindigkeit liege, wollte aber mal hören, ob es grundsätzlich noch andere Lösungsansätze gibt oder ob ich vielleicht irgendwas übersehen habe.

    0
  • R

    ok, du kannst also in gewissen Grenzen (mit)entscheiden ....
    Deswegen die 1. Frage, warum RDBMS ???

    Das was Dir nen RDBMS bietet ist in erster Linie:
    - multiuser faehigkeit
    - transaktionssicherheit
    - Integegritaet der Daten

    Brauchst du das wirklich so dringend ? Ist es Dir eventuell mehrere k Euronen Wert, z.b. fuer eigenen Server, weil Mitbenutzer nicht mehr auf euren, an den Rand der Belastbarkeit gedrängten, Server arbeiten wollen?

    Ich glaub eher nicht ...
    Integritaet kann man selber softwaremaessig haendeln, und meist auch entspannter .... ja dann gibts halt mal fehlermeldungen fuer den User wenn er was unsinniges macht ^^
    Transactionen in eurem fall aehnliches Thema...

    Server CLient Architektur und backup tools bekommst aber so nebenbei mit.
    Nur das ist aber auch kein wirklicher Grund fuer nen RDBMS, weil dafuer ists dann zu teuer 🙂

    NoSQL datenbanken und Standards:
    Keine Datenbank, auch die RDBMS, sind nicht durchgaengig stadndardisiert ^^ Der einzige Standard ist SQL ....

    Und aehnliches, nur nicht so maechtig und detailiert und alles in einem, gibts auch im nosql Umfeld. Such mal nach REST und JSON z.b.

    ALso das mit den Standards ist auch kein Argument gegen NoSQL (im Vergleich zu RDBMS)

    Ob ichs fuer Messwerte hernehmen wuerd, glaub eher nicht ... wenn nicht besondere anforderungen noch hinzukämen ...

    Bevorzugen wuerd ich nen eigenes binaeres format (das ist am schnellsten und hat wenn man ahnung hat, am wenigsten overhaed )
    Wenn mehrere tools das zeugs lesen muss, wuerd ich ne Bib fuer den zugriff drauf schreiben ...

    Hasst die Zeit und Mittel nicht fuer nen eigens Format geht auch was fertiges ... csv wird bei sowas ganz gern verndet (komprimieren beim speichern ned vergessen ^^ oder gleich komprimierts csv verwenden).

    Abraten wuerd ich von generischen strukturieren Formaten (xml und co).

    Die verwaltung der Messreihen kann man schon in ner db speichern ... ob rdbms, oder ne lokale datenbank(sqlite,berkleydb) kaeme auf den Anwendungsfall an ...

    die Messreihen wuerd ich aber wie gesagt binaer in der db hinterlegen.

    PosGresSDQL unterstutzt auch "BLOBS", nur heissen die da ned so .... sondern nur LOBS

    http://www.postgresql.org/docs/8.2/static/largeobjects.html

    Und die schnittstelle sieht ganz ok aus, was besseres wirst auch in anderen DBS ned finden ....

    Das System skaliert horizontal, zur Not können wir halt mehrere Server aufstellen.

    RDBMS skalieren nie horizontal !!! von Haus aus ...
    Das könntest nur Du auf App Ebene Implementieren ...
    Das ist ja meist der CLOU(Hintergrund) einiger nosql DBs, die koennen das automatisch

    Ciao ....

    0

  • 0.3 Sekunden für 400 Zeilen kommt mir zu viel vor.
    Gibt's bei Postgres ne INSERT Syntax mit der man mehrere Zeilen auf einmal einfügen kann? Dann pack alle Items einer Messung in ein einziges INSERT. Das kann einiges bringen. Bei MSSQL macht das schnell mal Faktor 100 oder 1000 aus.

    Bzw. was ich auf jeden Fall probieren würde, ist alle für eine Messung nötigen Statements in einen Batch zu packen und den dann am Stück an den Server zu schicken.

    Also quasi

    BEGIN TRAN;

INSERT INTO measurements ...

INSERT INTO measurement_data (...)
    VALUES (...)
    VALUES (...)
    VALUES (...)
    VALUES (...)
    ...

COMMIT TRAN;

    Und unterschätze den Aufwand nicht der entsteht wenn man die Daten wieder löschen möchte. Also nicht wenn man alles löschen möchte, das geht ja einfach und schnell über TRUNCATE. Sondern wenn man einen bestimmten Zeitbereich löschen möchte, also z.B. alles was älter als X Tage ist.
    Das kann dann schonmal ziemlich lange dauern.

    D.h. es könnte Sinn machen die Tabellen zu partitionieren.

    0
  • N

    hustbaer schrieb:

    Gibt'b bei Postgres ne INSERT Syntax mit der mal mehrere Zeilen auf einmal einfügen kann?

    Klar:

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

    http://www.postgresql.org/docs/9.1/static/sql-insert.html

    Macht bei Postgres auch einen groszen Unterschied.

    0
Anmelden zum Antworten