Datenbank für performante Abfragen auslegen



  • Ich habe hier eine Hobby-Datenbank, die regelmäßig Daten abspeichert (PostgreSQL). Ich speichere sowohl die Rohdaten als auch die geparsten Daten in separaten Tabellen ab.

    Mittlerweile ist meine Haupttabelle, die nur die Rohdaten und Zeitstempel speichert, 54 Gigabyte groß (>400.000.000 Datensätze). Abfragen werden jetzt zur Qual. Alleine wenn ich Rohdaten von einem bestimmten Zeitraum extrahieren möchte, brauche ich Ewigkeiten um die Tabelle durchzuackern. Von weiterführenden Joins mit Datum und IDs will ich gar nicht sprechen 😃

    Der "Server" ist ein recht neuer Rechner mit viel RAM, aber auch nix besonderes.

    Daher die Frage:
    Wie unterscheiden sich professionell aufgesetzte Datenbanken von meiner (abgesehen von der Hardware)? Wird da z.B. für jeden Monat automatisch eine Tabelle angelegt? Wie macht man solche Rohdatenmengen beherrschbar?



  • Hast du denn passende Indizes gesetzt? Du kannst natürlich nach Datum partitionieren (ist in der Postgres-Doku gut erklärt), aber 54GB klingt eigentlich nicht so dramatisch.



  • So pauschal lässt sich das nicht beantworten, dazu brauchen wir schon Details.

    - Wie sieht das Datenbank-Schema aus?
    - Hast du das DBMS denn auch vernünftig konfiguriert? Die Standardkonfiguration ist, gerade was die Speicherausnutzung angeht, sehr konservativ.



  • Also vielleicht erstmal zu den Indizes:

    nman schrieb:

    Hast du denn passende Indizes gesetzt? Du kannst natürlich nach Datum partitionieren (ist in der Postgres-Doku gut erklärt), aber 54GB klingt eigentlich nicht so dramatisch.

    Ich verwende tatsächlich keine, wusste bis eben nichts davon. Habe jetzt einiges gelesen, folgendes ist mir aber nicht klar:

    1.)
    Hauptsächlich interessiert mich eine bestimmte Nummer. Aufbau der großen Haupttabelle ist ungefähr:

    id(PK) | id_geraet (integer) | Rohdaten | ...
    

    In 99% aller Fälle kommen Abfragen wie

    select * from haupttabelle where id_geraet=XXX and datum >= ...
    

    Bringt es jetzt etwas, folgendes Index zu erzeugen:

    CREATE INDEX CONCURRENTLY id_geraet_haupttabelle_index ON haupttabelle (id_geraet);
    

    In den PostgreSQL-Beispielen wird davon ausgegangen, dass man sequentiell nach einem Primary Key sucht. Mein Anwendungsfall unterscheidet sich hier ja ein wenig 😕



  • Meine SQL Kentnisse sind etwas eingerostet, aber der Trend geht ja irgendwie bissi weg davon ... ^^

    where id_geraet=XXX and datum >= ...

    Hier langt der Index auf die ID vielleicht nicht, sondern das Datum muesste auch indiziert werden ...

    Wird da z.B. für jeden Monat automatisch eine Tabelle angelegt?

    Es war damals zumindest nicht unbedingt unüblich, kommt aber sehr auf den Anwendungsfall an. Normal "Denormalized" man Tabellen wenn man technisch mit nem passenden Index nicht hinkam ...
    Mit passenden Indizies hingegen kannst doch recht flott auch auf Tabellen mit Milliarden an Zeilen operieren ...

    Der PK sollte eigentlich scho per default indiziert sein oder ? kenn Postgres ned so ... Ich würde versuchen nen 2. Index der über ID und Datum anzulegen. Keine Ahnung ob das performanter ist wie nen sperater index über nur das Datum. Da unterschieden sich die RDBMS soweiso .... also lesen ^^

    Generell, "Rohdaten" sind eher nicht so das Ding von RDBMS ... wenn es nur ums verwalten der "BLOBS" geht ok ... aber normal koennen das NOSQL Systeme oft besser, mindestens aber genau so gut ^^ Ich würd nochmal evaluieren, ob an der stelle nen RDBMS brauchst ...

    Am schnellsten ist was selbstgestriktes, was die generizität von SQL vermeidet ... aber die Frage ist wirklich was du sonst noch so brauchst ....

    Ciao ...



  • Also Indices bringen es auf jeden Fall. Was vorher 10 Minuten gedauert hat, geht jetzt stellenweise in wenigen Millisekunden.

    Ansonsten habe ich mir auch Partioning angesehen. Dafür müsste ich allerdings größere Anpassungen vornehmen, aber in einer neu aufgesetzten Datenbank habe ich das mal ausprobiert. Fazit: Beim nächsten mal baue ich gleich entsprechende Triggerfunktionen und automatische Partitionierung ein.

    Meine SQL Kentnisse sind etwas eingerostet, aber der Trend geht ja irgendwie bissi weg davon ... ^^

    Wohin geht der Trend denn?



  • Nachtrag:

    Das Indizieren hat mal eben aus ca 55GB satte 155GB gemacht (Datenbankgröße) 😃



  • Ja, Indizes sind das, was ein RDBMS schnell macht. Ohne geht gar nichts. Gut dass dein Problem damit weitgehend gelöst ist.

    KeinDatenbanker schrieb:

    Das Indizieren hat mal eben aus ca 55GB satte 155GB gemacht (Datenbankgröße) 😃

    Sauber. Hast du so viele Indizes gesetzt, oder hast du so viele kleine Datensätze, dass das alles so fett wurde?

    Für Datenbankperformance kann ich http://use-the-index-luke.com sehr empfehlen, bzw. auch das dazugehörige Buch:
    SQL Performance Explained | ISBN: 3950307826

    DB-Performance-Zeug wird da von den Grundlagen bis hin zu relativ fortgeschrittenen Anwendungen erklärt.

    KeinDatenbanker schrieb:

    Wohin geht der Trend denn?

    Der Trend ging vor ein paar Jahren mal in Richtung NoSQL-Systeme. Jetzt wo der Hype abgeklungen ist, geht langsam aber vieles wieder zu klassischen RDBMS zurück.



  • Ach ja, RHBaum hat Recht wenn er sagt, dass "Rohdaten" nicht optimal sind. Warum legst du denn aktuell die Daten doppelt ab? Wenn du ohnehin schon geparsed hast, brauchst du die Rohdaten doch nicht mehr?



  • Hier noch ein Link zur Analyse von Abfragen, wenn du meinst, dass da noch was rauszuholen ist.



  • nman schrieb:

    Ja, Indizes sind das, was ein RDBMS schnell macht. Ohne geht gar nichts. Gut dass dein Problem damit weitgehend gelöst ist.

    KeinDatenbanker schrieb:

    Das Indizieren hat mal eben aus ca 55GB satte 155GB gemacht (Datenbankgröße) 😃

    Sauber. Hast du so viele Indizes gesetzt, oder hast du so viele kleine Datensätze, dass das alles so fett wurde?

    Es sind so viele kleine Datensätze 😞 Danke für deinen Buchtipp, das bekomme ich vlt. durch das Hochschulnetzwerk



  • nman schrieb:

    Ach ja, RHBaum hat Recht wenn er sagt, dass "Rohdaten" nicht optimal sind. Warum legst du denn aktuell die Daten doppelt ab? Wenn du ohnehin schon geparsed hast, brauchst du die Rohdaten doch nicht mehr?

    Weil es verschiedene Telegramme gibt und ich derzeit nur 8 von knapp 30 wirklich zerlege... Klar, die Rohdaten der 8 könnte ich mir dann schenken, aber dazu kommt, dass der Standard nicht wirklich eindeutig ist. 3 Parser liefern 3 ähnliche aber unterschiedliche Ergebnisse. Mein eigener Parser weicht auch wieder leicht ab (in bestimmten Fällen). Deswegen hatte ich beschlossen, die Rohdaten auf jeden Fall zu behalten


Log in to reply