Tabellen für viele kleine Datensätze optimieren

[jobo]

Zitat:

Zitat von Dejan Vu (Beitrag 1265134)

Zitat:

Zitat von jobo (Beitrag 1265130)

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

[Dejan Vu]

Zitat:

Zitat von jobo (Beitrag 1265143)

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

Zitat von DSP (Beitrag 1265145)

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

[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 :D

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? :gruebel:

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:

Zitat von Medium (Beitrag 1265151)

Wo habe ich einen Index vom Typ Double? :gruebel:

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?" :shock:)

[jobo]

Zitat:

Zitat von Dejan Vu (Beitrag 1265146)

Zitat:

Zitat von jobo (Beitrag 1265143)

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

[Dejan Vu]

Zitat:

Zitat von jobo (Beitrag 1265306)

Ich kann Dir das ehrlich gesagt nicht erklären und ich hab da auch keine Ambitionen, mich in die Optimierungsalgorithmen beliebiger Optimizer reinzudenken.

Hmmm. Ich dachte, Du wüsstest das.

Zitat:

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.

Dann ist das kein Optimizer am Werk, denn der würde das ja optimieren, aber das ist mir bei einigen RDBMS auch schon aufgefallen. (Für Oracle gab es / gibt es wohl entsprechende Query-Optimizer, die diese Reihenfolge dann optimieren). Na ja 'Optimizer' sind das schon, aber eigentlich sind es Strategieauswähler, weil bei jedem Schritt der Query (eigentlich nur ein Baum aus Filtern, und Enumeratoren) die vermeindlich kostengünstigste Variante aus eine Pool an Möglichkeiten (Index-Seek, Index-Scan, Table-Scan, Sort, Bucket, Hash etc.) herausgepickt wird.

Übrigens wird ein Index-Scan benutzt, wenn einzelne Felder benötigt werden, die in Index-Informationen enthalten sind und wenn dafür dann kein Datensatz gelesen werden muss. Oder der Scan über den Index, um etwas zu finden, ist schneller als ein Table-Scan. Insofern sind einzelne Index eventuell geeignet, Scan-Vorgange zu beschleunigen, aber 'eigentlich'(*) auch nur dann, wenn sie kombiniert sind, oder weitere Felder huckepack mit ablegen ('INCLUDE' Klausel bei T-SQL in Verbindung mit 'CREATE INDEX')
(*)'Eigentlich'=Ich weiss es nicht genau.

Aber was war das alles doch gleich? Ach ja, sekundär und wie Du schon sagtest per Experiment in ein paar Minuten belegt/widerlegt/erkannt.

Vom Diskussionsstoff her jedoch ein schönes Thema für einen anderen Thread.

[BUG]

Die Annahme, dass die Optimierer nach festen Regeln optimieren, ist meines Wissens falsch. Vielmehr ist es tatsächlich eine Minimierung des geschätzten Aufwandes über die Ausführungspläne der Anfrage.

Dabei hängt schon das Schätzen des Aufwandes von den Daten ab (Menge, Histogrammen, usw.).
Außerdem ist die Menge der Pläne auch zu groß, um komplett durchsucht zu werden. Natürlich können Heuristiken verwendet werden, um Pläne zu verbessern. Es ist aber auch nicht unwahrscheinlich, das bei der Auswahl des betrachteten Suchraums eine (pseudo-)zufällige Komponente mit reinspielt.

Insofern halte ich es für sinnlos, sich als Anwender zu genau mit dem Optimierer zu beschäftigen. Was aber nicht heißen soll, das man ihn nicht mithilfe von Erfahrung einen Tritt in die richtige Richtung geben kann.

[jobo]

Zitat:

Zitat von BUG (Beitrag 1265312)

Die Annahme, dass die Optimierer nach festen Regeln optimieren, ist meines Wissens falsch.

Nein, es gibt auch Optimierer, die nach festen Regeln arbeiten.
Aber was Du schlussfolgerst, läuft auf das hinaus, was ich auch rüberbringen wollte. Es macht wenig Sinn, einen nicht regelbasierten Optimizer "verstehen" zu wollen. Unabhängig von der Qualität der Statistiken, die von manchen Systemen herangezogen werden, sagt man gewissen Systemen nach, dass bspw. sogar physikalische Gegebenheiten berücksichtigt werden. (Welches Device hält welche Datenmenge, ..)
Wie auch immer, im Zweifel kann ich ihn mit Hints lenken. Im Beispiel hier bei den Indizes halt ausprobieren.

Letztlich ist es dennoch so, dass ich im Produktivbetrieb mit schleichenden Änderungen oder plötzlich kippendem Laufzeitverhalten rechnen sollte. Da muss also entweder ein kompetenter DBA ran und nachjustieren oder eben ein Supportvertrag her.

[BUG]

Zitat:

Zitat von jobo (Beitrag 1265315)

Nein, es gibt auch Optimierer, die nach festen Regeln arbeiten.

Okay, vielleicht habe ich an der Stelle etwas übertrieben :wink:

Zitat:

Zitat von jobo (Beitrag 1265315)

Da muss also entweder ein kompetenter DBA ran ...

Wobei es insgesamt keine schlechte Idee wäre, sich im Vorfeld mit einem Spezialisten für das entsprechende Datenbank-System abzusprechen, der das dann auch betreut. Die sind ja nicht ohne Grund so gut bezahlt.