Tabellen für viele kleine Datensätze optimieren

[Namenloser]

Zitat von Dejan Vu:

Kommt eine Spalte hinzu, muss ich das Programm ändern, an vielen Stellen, Ändere ich die Aggregierung, auch. Ich muss also laufend an der Software rumfummeln.

Dann ist dein Code nicht vernünftig gekapselt. Mir fallen eigentlich nur zwei Stellen ein, die man ändern muss: Speichern eines Datensatzes und Laden eines Datensatzes. Das ist bei der RDBMS-Variante nicht anders.

Aber ich will das jetzt nicht weiter diskutieren, wird langsam Off Topic.

[DSP]

Zitat von Dejan Vu:

[Das nennt sich Tracing und ist im ISO-Prozess zwingend.

Sorry, merkst du nicht, wie du dich her disqualifizierst? Ich sagte nicht, dass man die Daten löschen soll, sondern archivieren. Und für manche gibts da auch längere aufbewarungsfristen, zehn Jahre sind da eher die untere Grenze. Dann sag mir mal, wie willst du das mit eine wirtschaftlich vertretbaren aufwand realisieren? Dein aktueller Ansatz ist wirtschaftlich komplett inakzeptabel .. Daneben sollte die Zuverlässigkeit des System über die nächsten zehn Jahre gewährleistet sein mit geringen Wartungsaufwand und Folgekosten. Das sehe ich bei deinen Ansatz nicht. Eher die Tendenz, die Softwarequalität immer miserabler zu gestalten ...

[DSP]

Zitat von Dejan Vu:

ich will aus EXCEL heraus die 3 Stunden vom Januar 1980 in meiner Auswertung darstellen, wegen der Anomalie.

exakt, das ist Blödsinn, welcher troll will schon 50 Terra vorhalten um dann festzustellen, dass in Excel, in der neusten Version, nur zwei Gig passen?

Sorry, das kannst komplett vergessen.

[Medium]

Wow! Piano die Herren. Die Anforderungen sind recht klar, und ich bin mit dem aktuellen Stand bisher nicht unzufrieden. Um ein paar Punkte zu klären, die hier auftauchten:

Ein eigenes Dateiformat kommt nicht in die Tüte. Der Kunde wünscht explizit die Verwendung eines verbreiteten RDBMS, so dass im schlimmsten aller Fälle (für mich) auch ein anderes Softwarehaus leichtes Spiel mit der Weiterentwicklung und Wartung hat. Irgend welche obskure und ggf. proprietäre Dateiformate haben da einfach nichts verloren. Langzeitarchive, die weitere Schritte benötigen um sie auswerten zu können sind auch passé. Der Kunde will 2016 in seinem Viewer sagen können: Zeige mir die Ströme der Betriebsbereiche 1, 3 und 7 von 2014-2016, GO! Und dann will der innerhalb von einstelligen Sekunden seine Kurven sehen. Das entwickel ich NICHT selbst, das macht bitte sehr ein etabliertes DBMS. (Also zumindest das Anliefern der zum Zeichnen nötigen Daten.)
Und falls doch dann mal erweiterte Auswertungen gewünscht werden, werde ich endlos glücklich sein ein Backend zu haben, dass mir die Arbeit daran auf das Schreiben eines vergleichsweise kleinen Scriptes vereinfacht. Dass nachfolgende Wünsche dann ggf. nicht mehr in Millisekunden Ausführungszeit geschehen ist im Zweifel darstellbar, und stellt dann auch vermutlich nicht täglich genutzte Funktionalität dar.

Betrachtungen der Art "immer Donnerstags zwischen 7:00 und 12:00" wird es, laut nun mehrmonatig erstelltem Anforderungsprofil des Kunden (durch uns begleitet), nicht geben.

Ja, es ist ein großer industrieller Produktionsbetrieb. Festplattenkapazität ist nicht relevant. Der Kunde ging von sich aus schon von reichlich dimensionierten Backplanes mit zig HDDs aus, was IMHO sogar etwas hoch gegriffen ist. Ich erzeuge hochgerechnet jetzt etwa 15 MB Daten pro Tag, also pessimistisch gerechnet rund 6GB pro Jahr. Da reicht mir eine 1TB Platte für mindestens 15 Jahre, also nehme ich ein Mirror-Stripe (RAID 10) mit 4x 2TB SAS Platten und habe 30 Jahre lang Ruhe. Vorausgesetzt die technischen Standards entwickeln sich nicht zwischenzeitlich weiter, was sicherlich Umrüst-Begehrlichkeiten wecken müsste.

Abfragen werden immer die zuvor gezeigte Struktur haben: Gib Daten aus Zeitraum a bis b, der Messstellen 2, 6, 7 und 12. Der kombinierte Index aus "valueID, vdate" hat sich in meinen heutigen Tests als immense Verschnellerung erwiesen! Zusammen mit den kummulierten Tabellen, die das Zeichnen bei gröberem Zoom verschnellern sollen, dürfte ich auf akzeptable Reaktionszeiten kommen. Auch über ein handlesübliches Gigabit-Netzwerk.

Excel spielt keine Rolle. Ich schreibe ein vollumfängliches Auswertungsprogramm mit Graph und Reports, und das könnte von mir aus eine Exportfunktion bekommen. Gefordert ist dies bisher nicht, aber problemlos denkbar. Die Daten sind dann ja bereits gefiltert und aufbereitet.

"Materialized Views" erscheinen mir als ungeeignet. Beziehungsweise mache ich ja mit meinen Aggregat-Tabellen etwas sehr ähnliches, und das "Gewürgel" dies auf die verlinkte Art in reinem SQL auf MySQL abzubilden... da gefällt mir meines besser. Zumal es ziemlich einfach auch für Pflegeläufe nutzbar ist, sollte es dort wirklich mal Probleme geben.

Partitioning ist eventuell interessant. Da sich das aber durchaus auch nachträglich umsetzen lässt, würde ich gerne erstmal ohne testen. Wenn der Kunde dann doch irgendwann nicht mehr akzeptable Auswertezeiten berichtet, oder sich das noch besser in meinen noch kommenden "großen" Tests abzeichnet, wäre dies ein möglicher Ansatzpunkt.

Der wesentliche Grund für die Erstellung des gesamten Systems ist das Identifizieren der Ursachen für teilweise auftretende Verbrauchsspitzen. Verträge mit Energielieferanten definieren in diesen Größenordnungen Tarife nach aktuellem Verbrauch, heisst dass die ersten paar hundert kW normal berechnet werden, ab dann aber ein immens hoher "Straftarif" einsetzt. Die Quellen dafür sollen gefunden und bestenfalls eliminiert werden.
Der zweite Grund (nicht minder wichtig) ist die genauere Anrechenbarkeit von Energiekosten auf ein spezifisches Produkt. So, dass nachher gesagt werden kann: Für Produkt X entfallen Y kWh auf eine Produktionseinheit, verrechnet mit Tarif T sind also P% des Preises reine Energiekosten. Zusammen mit den Messungen für teils einzelne Motoren zeigen sich hier dann ggf. gute Einsparpotenziale.
Um gewissen Rankings zu entsprechen, und vereinbarte Einsparungen durch staatliche Stellen vorgegeben nachweisen zu können, sind Langzeitbetrachtungen notwendig. Auch rückwirkend über mehrere Jahre. (Manche Baugenehmigung kommt mit derartigen Anforderungen daher.)


Abschließende Bewertungen mit definitiven Werten müssen noch folgen. Hierfür muss ich zunächst ein zumindest rudimentäres Auswertetool bauen, und einen Generator für Echtwelt-ähnliche Daten, der mir flott Daten von ein paar Jahren simulieren kann. Die bisherigen Ideen und Infos bzgl. Indizes und Aggregat-Tabellen haben mich immerhin schon ein mal so weit gebracht, dass ich mir gut vorstellen kann den gewünschten Funktionsumfang mit erschwinglicher Hardware und akzeptablen Laufzeiten anbieten zu können. Sehr geil!

[hstreicher]

mal ne blöde Frage, wieso muessen die Daten denn in festen Zeitabständen gespeichert werden?

man könnte doch auch einfach einen Datensatz speichern wenn der Messwert einen gegeben Wert vom letzten gespeicherten Wert abweicht,
dann kann man einerseits die Daten im Sekundentakt reinholen , ohne die DB mit Abermillionen von gleichen Werten sich widerholenden aufzublasen
man haette dann die granularitaet Abnormalitaeten sekundengenau zu untersuchen

Nachteil: würde aber die Auswertungen etwas Komplizierter machen


mfg Hannes

[mkinzler]

Und auh das eigentliche Logprogramm. So wird einfach zeitgesteuert Werte protokolliert, bei Deinem Vorschlag müsste immer zuerst mit dem letzten Wert verglichen werden)

[jobo]

Zitat von Dejan Vu:

Zitat von jobo:

Ich würde es mit getrennten Indizes machen. Das ist flexibler.

Erklär mal bitte. Ist der kombinierte Index über den Zeitraum und Messstationen nicht besser, als nur über den Zeitraum? Den Index über die ValueID wird man imho nie benutzen, oder irre ich mich?

Ein kombinierter Index ist naheliegend, lässt sich durch das RDBMS bzw. den Optimizer aber oftmals nicht so vielseitig nutzen.
Das Thema ist sehr DB spezifisch, vermutlich sogar (Optimizer)Version-spezifisch.

Einzeln aufgebaute Indizes werden durch die Optimizer idR besser nach Bedarf zusammengesetzt bzw. abgegriffen, also wirklich verwendet, als Kombi-Indizes. Deren Aufbau und Nutzen ist halt viel spezifischer.
Die Verwendung / Filterung nach Meßstation halte ich für sehr naheliegend. Auch wenn der Kunde was anderes behauptet.

Letztlich ist das Indexthema aber sekundär, weil man es unabhängig von der Logik mit ein paar Handgriffen variieren und testen kann, notfalls sogar auf einem Produktivsystem.

Zitat von hstreicher:

mal ne blöde Frage, wieso muessen die Daten denn in festen Zeitabständen gespeichert werden?

man könnte doch auch einfach einen Datensatz speichern wenn der Messwert einen gegeben Wert vom letzten gespeicherten Wert abweicht,
dann kann man einerseits die Daten im Sekundentakt reinholen , ohne die DB mit Abermillionen von gleichen Werten sich widerholenden aufzublasen
man haette dann die granularitaet Abnormalitaeten sekundengenau zu untersuchen

Nachteil: würde aber die Auswertungen etwas Komplizierter machen


mfg Hannes

Das ist eine prima Sache.
Wenn Anzahl der Messwerte und Toleranz gespeichert werden, kann man die Daten wenn es sein muss (>Auswertung/Export/Archiv) auch rekonstruieren (mit Toleranzverlust natürlich). Vermutlich sogar transparent on the fly per View bspw.

[DSP]

Dann solltest du mal gut testen, ein Stresstest welcher mal 5 oder 7 Jahre simmuliert, sehe ich hier als absolute Notwendigkeit und dies nicht nur bei den Auswertungen sondern auch beim Insert. Wenn du hier, die Berechnung des max-/durschnittswertes innerhalb des kleinsten Intervalls in der DB realsieren möchtest, sehe ich hier schon mal ein Problem. Da würd ich zumindest die ersten 10 Sekunden im Speicher halten, berechnen und dann einen Insert auslösen ohne da noch verschiedene Ergebnistabellen generieren zu müssen. Nichts desto troz, würde ich eine Archivierungsfunktion vorsehen und eine temporäre Einlesefunktion für die archivierten Daten.

Anmerkung, du darfst auch nicht nur die Daten rechnen, sondern musst bei der Grössenberechnung auch den Index mit einbeziehen. Auch taugt meist ein Index der aus einen Double besteht nichts. Wünsche schon mal viel Glück.

Noch ein Hinweis, die Orginaldaten sollten natürlich auch gespeichert werden, damit man im Notfall das ganze rekonstruieren und belegen kann. Ein sequenzielles File, ist auch schnell und einfach zu lesen und zu schreiben.

Grüsse
DSP

[Dejan Vu]

Bezüglich der benötigten HD würde ich den Nettoverbrauch, also die reinen Nutzdaten mit dem Faktor 3-4 multiplizieren, da Du hochredundante Indexe(60-80% der Daten sind dort nochmals vorhanden) und Aggregationstabellen verwendest etc.

Zitat von Medium:

Betrachtungen der Art "immer Donnerstags zwischen 7:00 und 12:00" wird es, laut nun mehrmonatig erstelltem Anforderungsprofil des Kunden (durch uns begleitet), nicht geben.

Das war nur ein Beispiel, um die Flexibilität eines RDBMS ggü. einem handgebissenen Dateiformates zu belegen. Ich denke, das wird kommen, denn beim Datamining und dem Suchen nach Ursachen spielen auch Tageszeiten, Wochentage eine Rolle (können! könnten!). Aber da die Spec das nicht vorsieht, ist es ok. Ich würde es in der Hinterhand halten und eventuell den Timestamp doppelt abbilden: vDateTime, vDate, vTime. Das nachzurüsten geht natürlich auch, würde aber ein einmalig stundenlanges rumrödeln bedeuten. Aber gut: Das würde bezahlt werden.

Zitat:

Excel spielt keine Rolle.

Wichtig ist: Da du hier mit einem Standard-RDBMS arbeitest, sorge nur für einen Ole-DB provider (ODBC mit ADO geht auch).

Zitat:

"Materialized Views" erscheinen mir als ungeeignet. Beziehungsweise mache ich ja mit meinen Aggregat-Tabellen etwas sehr ähnliches, und das "Gewürgel" dies auf die verlinkte Art in reinem SQL auf MySQL abzubilden... da gefällt mir meines besser. Zumal es ziemlich einfach auch für Pflegeläufe nutzbar ist, sollte es dort wirklich mal Probleme geben.

Ich kenne die Tools für mySQL nicht, aber ich finde einen zeitgesteuerten SQL-Job wesentlich besser, weil das alles in einer Umgebung läuft, die alles schon eingebaut hat: Wiederholung bei Fail, E-Mail-Benachichtigung im Fehlerfall etc. Logging ist auch vorbildlich: Wozu also das Rad neu erfinden... Schau mal, ob Du dich mit professionellen ETL/cron-Tools für mySQL anfreunden kannst.

Bezüglich deiner Auswertetools, die Du alle selbst schreiben willst, würde ich auch mal prüfen, ob es etwas derartiges nicht gibt. Natürlich macht Programmieren Spaß, aber wenn es etwas Fertiges gibt... Wieso nicht? Oder zumindest Denkansätze besorgen.

Zum Abschluss noch meine persönliche Meinung:
Ich würde mir noch überlegen, ob ein SQL-Server nicht eher in Frage kommt, zumal Du dort alles hast, was Du brauchst: SSIS um die Jobs zu definieren, SSAS, um OLAP-Cubes zu entwerfen und zeitgesteuert zu füllen (Eine noch bessere Alternative als die handgebissenen Aggregattabellen), SSRS um Reports zu generieren etc.

Je weniger ich selbst programmieren muss, desto besser: Denn ich werde mir die gleichen blutigen Nase an genau der gleichen fiesen Ecke holen, wie tausende Programmierer vor mir: Nur warum soll ich mir das antun?

Zitat von jobo:

Einzeln aufgebaute Indizes werden durch die Optimizer idR besser nach Bedarf zusammengesetzt

Du kannst Indexe doch nicht zusammensetzen? Ein Index auf 'B' bringt dir doch gar nichts, wenn Du nach 'A' und 'B' suchst, und schon einen Index 'A' verwendest. Wie soll das denn gehen? Was richtig ist: Mit einzelnen Indexen kannst Du unterschiedlichen Queries idR besser begegnen, aber wenn es hier eh nur ein Query-Pattern gibt, nimmt man besser auch nur einen Index.

Zitat:

Letztlich ist das Indexthema aber sekundär, weil man es unabhängig von der Logik mit ein paar Handgriffen variieren und testen kann, notfalls sogar auf einem Produktivsystem.

Da wird der Kunde aber ziemlich kotzen, wenn Du in seinem Produktivsystem mit Indexen experimentierst: Schließlich ist das System doch ziemlich ausgelastet (und die Tabelle gelockt), wenn Du mal den Kombi-Index über die TB an Daten neu erstellen willst. Da würden dann alle Messstationen meckern, weil der Server nicht erreichbar ist => keine gute Idee.

Lieber vorher Testdaten erzeugen (10 Jahre: 150.000 x 365 x 10= 547.500.000), damit rumspielen und ein Gefühl für die Queries bekommen.

Aber sekundär ist das Thema wirklich.

Zitat von DSP:

..Wenn du hier, die Berechnung des max-/durschnittswertes innerhalb des kleinsten Intervalls in der DB realsieren möchtest, sehe ich hier schon mal ein Problem

Welches?

Zitat:

Auch taugt meist ein Index der aus einen Double besteht nichts.

Natürlich taugt der was: Beim Aggregieren nimmt das RDBMS nämlich diesen Wert und muss den Datensatz nicht laden. *Suchen* nach Messwerten wird man nie

Zitat:

Wünsche schon mal viel Glück.

Glück wird hier nicht benötigt. Das ist eine Standardanforderung an eine Messdatenerfassung. Das ist, wie man so schön sagt: 'No big deal'.

[mkinzler]

Die Auswertung muss ja nicht zwangsläufig auf dem System erfolgen, welche die Logdaten erzeugt. GGf kann man ja die Datenbank auf eine anderes System kopieren und dort dann wen nötig auch nachträglich Indizes erzeugen.