Tabellen für viele kleine Datensätze optimieren

[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)

[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.

[jobo]

Zitat von Dejan Vu:

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
-....
Aber sekundär ist das Thema wirklich.

Ich setze keine Indizes zusammen, ich überlasse es dem Optimizer, die beste Kombination zu verwenden. Der ist sehr wohl in der Lage, mit dem was er vorfindet, sinnvolle Rangescans usw. durchzuführen.
Und meinetwegen, wenn es wirklich nur ein "Querypattern" gibt- was laut Plan so sein mag, in der Praxis aber vlt ganz anders aussieht- kann es auch ein Kombiindex sein.
Da es ursprünglich auch um Platzbedarf ging, war es eine Möglichkeit zu sparen. Man kann es bequem und schnell ausprobieren.

Natürlich wird der Kunde kotzen, wenn man das im laufenden Betrieb macht, ich schrieb aber notfalls, und darüber hinaus traue ich es dem TE zu solcherlei Überlegungen und weit mehr alleine anzustellen ohne diese Annahme auch noch explizit hinzuschreiben.


Und ja, es ist sekundär.

[DSP]

Noch ein Hinweis, das System sollte redundant ausgelegt sein, dass bei einen Hardware defekt die Daten nicht verloren sind. Also eine Duplikateprüfung mit integrieren, damit man da auch mal Wartungsarbeiten vornehmen kann. Auch sollte ein DB unabhängiges Log geschrieben werden, mit dem man auch mal die Datenbank Nachfahren kann, sollte sie au h mL gewartet werden

Grüsse
DSP

[Medium]

Es wird wie gesagt ein RAID 10 zum Einsatz kommen, und sehr wahrscheinlich sogar zwei Mal, sprich zwei identische PCs. Ob dort die Redundanz von MySQL eingesetzt wird oder ein nächtlicher DB-Abgleich ist noch nicht entschieden. Ein Log wird es geben, aber nur ein Fehler-Log. Würde ich normale Aktionen alle loggen, kann ich gleich das Datacenter auf dem Gelände nebenan in Auftrag geben

Dass mein neuerdings kombinierter Index für meine Fälle scheinbar sehr gut geeignet ist, sehe ich allein schon wenn ich Abfragen auf eine Hand voll Daten mache. Die sind von ~1sek auf bis weit unter 200ms Antwortzeit runter. Groß-Test wie gesagt in Mache. Stellt sich dabei raus, dass es noch immer zu langsam ist, wird da was anderes probiert. Und nur dann!

Die eigentlichen Rohdaten werden zwar sekündlich gelesen (bzw. alle 200ms bis 10s je nach Messstelle), ich bilde aber bereits "im Speicher" daraus die minütlichen Maxima und Durchschnitte. Eine Speicherung nach Hysterese wäre prinzipiell denkbar und wurde überlegt, zugunsten der Lesbarkeit von Ausdrucken in Listen-Form aber verworfen. Ganze Minuten lesen sich einfach "sauberer" und übersichtlicher, und "lügen" durch Runden der Schönheit wegen ist auch nicht im Sinne des ganzen gewesen

Bei meinen geschätzten 15MB/Tag waren die Indizes und Aggregattabellen inkl. derer Indizes enthalten.

Wo habe ich einen Index vom Typ Double?

Das RDBMS wird vom Kunden gestellt. Unsere Alternative wäre Oracle, und die IT Abteilung hat nicht ganz undeutlich signalisiert, dass sie keinen wirklich hätten, der diese administrieren wöllte. Ich auch nicht. Ein DB Wechsel ist ja aber auch kein Hexenwerk, sollte sich später herausstellen, dass es überhaupt nicht geht mit MySQL.

[Dejan Vu]

Zitat von Medium:

Wo habe ich einen Index vom Typ Double?

Den hatte ich ins Spiel gebracht, wenn man die Aggregierung einzelner Messwerte optimieren will;

Code:

select sum (value) from Messwerte where vDate Between :From And :To and ValueID in (1,3,6)

geht bei vielen RDMBS noch schneller, wenn der Index über vDate+ValueID+value geht. Denn dann muss ja 'value' nicht mehr aus dem Datensatz gezogen werden.

Bezüglich 'geht nicht mit mySQL' kann ich dich beruhigen: Das geht schon. Die Nachteile und Lizenzmodelle sind bekannt und wenn es die zusätzlichen Tools gibt (Agenten, ETL etc.) Super. Wenn nicht, haste doch noch was zu Programmieren.

Das ist wirklich ein sehr interessantes Projekt, wo man sich schöne Lorbeeren verdienen kann, speziell in den Auswertungen, wenn die in Quasi Echtzeit erstellt werden ("Wie schaffen Sie das bloß, aus den Millionen Daten die Monatsmittelwerte zu berechnen, auf Knopfdruck?" )

[Dejan Vu]

Zitat von jobo:

..es dem Optimizer, die beste Kombination zu verwenden. Der ist sehr wohl in der Lage, mit dem was er vorfindet, sinnvolle Rangescans usw. durchzuführen.

Erklär mir mal bitte, wie das gehen soll.
Ein Index sortiert nach Vorname, der andere nach dem Nachnamen. Wie soll man die Indexe kombinieren, wenn nach 'Max' und 'Müller' gesucht wird? Der Optimizer wird den Index mit dem niedrigeren distinct/total count nehmen, denn dort ist die Wahrscheinlichkeit eines eindeutigen Treffers am höchsten (oder einen Clustered Index, oder den, mit der besseren Strategie). Und dann scannt er alle Treffer durch, lädt den jeweiligen Datensatz und prüft, ob der Nachname = 'Müller' ist. Kann er den zweiten Index da denn verwenden?. Nee, oder? Genau hier fehlt mir ein Beispiel, das das belegt. Zu sagen 'Der macht das schon irgendwie' reicht mir nicht.

Und weil das imho so ist, verfällt man ja so leicht in das Anti-Pattern "Index-Shotgun", also eine Tabelle mit Indexen zu überladen, um bloß alle Queries optimal durchführen zu können.

Ich wiederhole nochmal (da sind wir einig): Ein Index auf einer einzelnen Spalte ist im täglichen Betrieb einem kombinierten Index in jedem Fall vorzuziehen (wegen der vielfältigen Einsatzmöglichkeiten, 'Flexibilität'). Nur in Ausnahmefällen sollte ein kombinierte Index zusätzlich verwendet werden, eventuell als Ersatz für einen einfachen. In der Regel (also eigentlich fast immer) sind die Abfragen schnell genug, weil die meisten Daten eh im Cache sind.

Hier ist es wirklich so, das es nur ein Pattern gibt:
Suche für bestimmte Stationen Daten innerhalb eines bestimmten Zeitraumes.
Suche alle Messwerte im Zeitraum ist sinnlos.
Suche alle Messwerte einer Station ebenso.

Ergo bleibt hier nur der Index ValueID+vDate.

Zitat:

...in der Praxis aber vlt ganz anders aussieht-

Absolut, da kommt noch was, sehe ich auch so.

Zitat von DSP:

Noch ein Hinweis...

RAID + Cluster, dann wirds aber langsam teuer. Wartungsarbeiten per Agent/SSIS. Ich glaub, SSIS macht das, der Agent sowieso. DB-Unabhängige Logs? Nö. Brauchts nicht, das macht das RDBMS alles von ganz alleine. Deshalb heißt es Daten*bank* (nicht zum draufsetzen, sondern weil es sicher ist).

[jobo]

Zitat von Dejan Vu:

Zitat von jobo:

..es dem Optimizer, die beste Kombination zu verwenden. Der ist sehr wohl in der Lage, mit dem was er vorfindet, sinnvolle Rangescans usw. durchzuführen.

Erklär mir mal bitte, wie das gehen soll.

Ich kann Dir das ehrlich gesagt nicht erklären und ich hab da auch keine Ambitionen, mich in die Optimierungsalgorithmen beliebiger Optimizer reinzudenken. In der Realität gibt es mit verschiedenen RDBMS und deren Versionen eine beträchtliche Anzahl von Varianten / Verfahren, wie ein Optimizer sich verhält. (Genaugenommen auch nochmals abhängig von Datenlage aka Statistiken, die wiederum falsch oder schlecht sein können)
Es ist eine allgemeine Aussage meinerseits (Anbieter-unspezifisch). Fakt ist, es kostet den TE 3 Minuten, beide Varianten am echten System auszuprobieren. Ich selbst habe nicht das Problem, sondern nur eine Anmerkung dazu gemacht, Du musst also ohne eine Erklärung leben, kannst mir aber gerne beweisen, dass es falsch ist.
negativer Erfahrungswert mit kombinierten Indizes:
Schon bei vertauschter Reihenfolge der Felder in der Where Bedingung gegenüber der Indexdefinition wird der Index gar nicht genutzt oder nur teilweise bis zum vertauschten Feld.
positiver Erfahrungswert bei nicht kombinierten Indizes:
Es wird ein "Range Index Scan" durchgeführt, das Ergebnis liegt geschwindigkeitsmäßig zwischen "Nur ein Index" und "Kombi Index"