[Artikel] Meine DB ist langam weil...

Hallo zusammen,

Ich höre gerne mal "Der Server ist langsam", "die Datenbank macht nix", "keine CPU Auslastung aber miserable Performance", "Da ist ja ein Dataset schneller" etc.

Deswegen wollte ich euch mal meine Erfahrungen bei schlechter Performance kundtun. Dies bezieht sich eigentlich auf jede Datenbank.

Fall 1: Keine CPU Belastung, Festplatten leuchten durch.

klassischer Fall: Es kommt eine Abfrage rein, und der Server muss die Tabellen nach Treffern durchsuchen. Dabei werden Daten von der Festplatte geladen und verglichen. Dadurch steigt die Festplatten-Auslastung. Zum vergleichen brauchen wir fast keine Rechenzeit, da die Festplatten garnicht so schnell die Daten liefern wie wir vergleichen.
Lösung: Der Server hat zu wenig Arbeitsspeicher. Wenn es möglich wäre Daten im RAM zu halten uind dadurch die langsame Festplatte nicht benutzen zu müssen, dann wäre die Performance besser.

Fall 2: gemäsigte CPU-Belastung, gemäßigte Festplatten-Leuchterei

Analog zu Fall 1 passiert folgendes: Eure CPU rechnet sich nen Wolf, hat dann keine Daten mehr, Die Festplatte dreht durch, die CPU rechnet wieder, die Festplatte leuchtet wieder... Ich denke ihr versteht. Hier werden Berechnungen vorgenommen, welche nur zu einem Teil im Arbeitspeicher vorhanden/abgehandelt werden können. Ein Typisches Beispiel ist, wenn ihr Abfragen auf eine große Tabelle macht, welche mit einer kleinen (im Ram vorhandenen Tabelle) vergleicht. Evtl. ist auch auf einer der beiden Tabellen ein Index und bei der zweiten wird ein belibiges Feld ohne Index abgefragt. Oder Punkt 3.

Fall 3: Viel CPU-Belastung, Festplatte leuchtet nur zu Weihnachten

Hier haben wir schon einen Erfolg, denn Festplatten gehen mit hohen Zugriffszahlen schneller kaputt, CPU's werden endlich für ihr Geld bezahlt. Trozdem kann hier die Performance leiden. Im Normalfall passiert folgendes: Die Daten liegen komplett im Arbeitsspeicher. Es werden Selects auf Tabellen abgesetzt (1. entweder Tabellen sehr gross, oder 2. Selects sehr komplex mit viel Where oder Join)
Nehmen wir nur Fall 1 dann bedeutet ein

Select * from Table1 where xField='ABC'

dass der Server
a) die Tabelle komplett durchsuchen muss - denn es kann ja ganz am Ende ein Datensatz sein, der passt.
b) mindestens 1 max 4 Vergleiche (in diesem Fall) notwendig sind. Bei 1 Mio Datensätzen heisst das aber 1-4 Mio Vergleiche. Bei einer 3 Ghz-CPU (1Mio=Mega, 1000 Mio=1000Mega=1Giga) kein Problem? Rechnet mal dazu, dass da andere Prozesse etwas wollen, dass man Daten aus dem Ram lesen muss, dass die Register und der Prozessorcache bestückt wird etc. Dann muss ja jeder Treffer gespeichert und etwaige Daten zusätzlich geladen werden. Glaubt mir, 1000ms sind schnell verbraten.

Fall 4 der Erfolg - keine Platte, keine CPU Belastung, Ram ausgenutzt bis zum Ende

Festplatte lebt 10 Jahre, CPU dümpelt rum und die Performance ist Klasse. Als Programmierer oder Sysadmin kann man sich endlich anderen Themen widmen (z.B. myCsharp.de lesen oder die nette Sekretärin anbaggern (wenn sie eben nicht myCsharp.de liest)) - ja aber was ist passiert?

Hier will ich noch ein wenig ausholen - wer das kennt darf gerne weiter unten weiterlesen. Nehmen wir an, wir haben eine Liste von "a-z" und suchen "s". Der erste Ansatz ist immer "vorne Anfangen und weitersuchen bis zum Ende oder gefunden". Das bedeutet, "s" zu suchen benötigt 19 Vergleiche. Wenn ich ¢ suche, dann ist das nicht dabei und ich such trozdem bis zum Ende. Das gilt übrigens für jeden Vergleich, denn der Prozessor hat keine Ahnung von Zeichen, er vergleicht nur "Bits&Bytes".
So, bei "a-Z" haben wir abr das Glück, dass diese Liste sortiert ist. Wir wissen "S" kommt vor "T" und nach"R". Das heisst, wenn wir "in der Mitte" vergleichen, dann bekommen wir die Info "S ist kleiner/größer".
Bespiel mit ABC - Mitte ist "M" - S ist größer; Mitte zwischen "M und Z" ist "T" - Mitte zwischen "M und T" ist "P" - Mitte zwischen "P und T" ist "R" - Mitte zwischen "R und T" ist "S" - gefunden. (Deswegen studieren manche Informatik^^)
Man sieht sehr schnell, dass man in diesem Fall nicht alles ansehen muss, sondern mit einer sortierten List schneller zu einem Ergebnis kommt (in dem Fall Wurzel 2 als maximala Anzahl)

[Hier weiterlesen] bei 1Mio vergleichen fällt auf 1000 Vergleiche . Das ist doch ein Unterschied, oder? Dieses Sortierte-Liste-Verhalten wird bei Datenbanken als Index bezeichnet. Man merkt sich in einer zusätzlichen (unsichtabren) Tabelle die Datensatz-Nummern und den Wert. Dann kann man immer wenn man den Wert sucht schnell nachsehen welche Datensatznummer zutrifft und bekommt die restlichen Daten. Das hat natürlich nicht nur Vorteile. Der Haupt-Nachteil ist der zusätzliche Speicheplatz. Bei z.B. 10 Byte im Suchfeld (string) und 4 Byte (int32) im Nummernfeld sind da bei 1Mio. Datensätzen dann ja rechnerisch 14MB.

Also - je bessere Indizes desto besser die Leistung und desto größer die DB. Hier haben wir eben den "Hund-Schwanz-Beis-Vergleich" - Weniger Hauptspeicher als mindesten von den Index-Tabellen benötigt reduziert die Performance erheblich. Mehr Hauptspeicher reduziert die "Kosten-Leistung-Euro-Nutzen" Rechnung.

Also "Auge mal PI"" würde ich ca. 50% der DB-Größe als Minimum Hauptspeicher angeben. Besser ist klar die komplette DB im Ram halten können.

Ich hoffe ihr konntet mir Folgen

😃

Xynratron

Lösung: Der Server hat zu wenig Arbeitsspeicher. Wenn es möglich wäre Daten im RAM zu halten uind dadurch die langsame Festplatte nicht benutzen zu müssen, dann wäre die Performance besser.

Ich darf hierzu ergänzen!

Es ist zu überprüfen, nach welchen Schlüsseln auf die Tabellen in der Datenbank zugegriffen wird. Danach sind Indizies zu setzen. Eine indizierte Tabelle ist immer schneller und verursacht bedeutend weniger Last als ein Fulltablescan.

Binäre Suche ist das, was du beschreibst 😉

Edit: Bitte den unteren post loeschen, war zu schnell beim abschicken 😉

http://de.wikipedia.org/wiki/Bin%C3%A4re_Suche

Besser ist klar die komplette DB im Ram halten können.

Meine DB ist 500GB groß. Was soll ich tun? 🙂

Zusätzlich sollte zu den Indizes erwähnt werden, das man damit nicht übertreiben soll. Wenn man eine Tabelle mit, sagen wir mal, 20 Spalten hat und man denkt sich "Hey, Indizes bringen doch Performance" und ballert auf jede Spalte einen Index, hat man verloren. Pro Tabelle sollte mit Indizes sparsam umgegangen werden. Denn aus dem, was Performance bringen soll, kann ganz schnell eine Schnecke werden...

Das folgende ist auf den SQL Server bezogen:
Wenn man Statements schreibt, und man hat das Gefühl das es langsam ist, immer auf den Ausführungsplan schauen. Dieser zeigt ein exakt warum was langsam ist. Allerdings ist es nicht gerade einfach den Plan zu lesen, wenn man z.B. gerade erst mit TSQL anfängt.

Zusätzlich sollte zu den Indizes erwähnt werden, das man damit nicht übertreiben soll.

Eigentlich sollte jedem klar sein, der etwas Erfahrung mit einer DB hat, das die Indizies für größere Abfragen gedacht sind und nur auf den Feldern liegen sollten, die von allen, bzw. den meisten Abfragen genutzt werden. Da es sich hier meistens um Keyfelder handelt, sollte das kein Problem darstellen.
Andernfalls würde ich den Aufbau der DB als falsch bezeichnen.

Meine DB ist 500GB groß. Was soll ich tun?

Hierzu ne blöde Antwort. Aber es gibt ihn tatsächlich, den 500 GB Speicherchip für den PC. Aber frag mich jetzt bitte nicht nach dem Preis.

Eigentlich sollte jedem klar sein, der etwas Erfahrung mit einer DB hat, ...

Tja, wobei hier die Betonung auf "Eigentlich" liegt. Ich hab da schon ganz lustige Erfahrungen gemacht. Wenn Firmen ankommen und sagen "Unsere DB ist langsam, was können wir noch tun?" Und ich nach einer Stunde antworte: "Den DB Admin feuern". Weil ungefähr genau sowas aufgetreten ist.

Gibt es solche Pläne eigentlich auch in anderen DBMS? Oracle? mySQL? Tät mich mal interessieren){gray}

Bei Oracle gibt es sowas auch, sieht auch ähnlich aus. mySQL weiß ich nicht.

Bei PostgreSQL zum Beispiel mit EXPLAIN ANALYZE "Statement" ... das schlüsselt den Queryplan, verwendete Indices etc. ganz gut auf. Falls das hier jemand benutzen sollte.

Der MS SQL Server hat einen Datenbank-Optimierungsratgeber. Dieser wird mit einer Ablaufverfolgungsdatei vom SQL Profiler gefüttert und spuckt dann Vorschläge aus, wie Indizes und Statistiken zu setzen sind, um die optimale Leistung zu haben.

Das ist der Weg, den man gehen sollte, um Indizes anzulegen. In den allermeisten Fällen wird das Ergebnis wesentlich besser sein, als eine manuelle Optimierung. Als Anwendungsentwickler ist man in der SQL Server Interna meist gar nicht so tief drin, um das überhaupt richtig machen zu können.

Generell ist wichtig, dass ein Index nur dann greift, wenn er über alle Spalten geht, die in der WHERE-Klausel einer Abfrage auftauchen. Das wird oft falsch verstanden.

Also "Auge mal PI"" würde ich ca. 50% der DB-Größe als Minimum Hauptspeicher angeben. Besser ist klar die komplette DB im Ram halten können.

Sorry, aber das ist absoluter Unfug. Moderne RDBMS wie z.B. der MS SQL Server haben ein ausgefeiltes Caching. Das kümmert sich darum, dass der DB-Server nicht unnötig ins rödeln verfällt. Der RAM sollte auf jeden Fall so groß sein, dass das RDBMS nicht darum mit anderen Programmen konkurrieren muss. Für ein Business-System mit ca. 50 gleichzeitigen Benutzer sind z.B. 4 GB RAM ein guter Wert. Auch wenn die DB 200 GB groß ist.

Prinzipiell ist man immer gut beraten, genug RAM zu haben, so dass alle Indizes in den RAM passen.

Es bringt also nichts, auf jede SPalte einen Index zu setzen, weil das dann irgendwann zuviel Ressource frisst, und somit kein Performancegewinn mehr ist. Auch das Schreiben von Records wird dadurch spürbar langsamer.

@Rainbird:
Der Optimierungsratgeber baut aber auch ab und zu murks. Ist zwar selten, kommt aber vor.

Zum Thema:
Jelly hat da einen ganz wichtigen Punkt angesprochen. Je mehr Indizes auf einer Tabelle liegen, je langsamer wird das INSERT. Gerade also bei z.B. Log Tabellen, sollte man eigentlich nur ein Index haben, sprich den Primary Key. Auswertungen von Log Tabellen müssen IMHO nicht schnell sein. Das darf ruhig dauern. Bei sowas muss man dann im Gleichgewicht bleiben, bzw. entscheiden was man will: Schnelles INSERT, oder schnelles SELECT.

Für die Performance sind halt nur 2 Dinge richtig wichtig: a) Ein gutes Datenbankschema mit wenig bis keinen Redundanzen (Normalisierung etc...) b) Geschickt gesetzte Indizes. Mehr ist es eigentlich nicht. Sicherlich spielt die Hardware auch eine große Rolle, nur ist diese bei kleineren DBs zu vernachlässigen.

Nochmal zur meiner DB. Der Server der die DB hostet hat 24GB RAM. Wie gesagt, die DB ist ca. 500GB groß. Das reicht vollkommen aus. Laut Taskmanager belegt die SQL Instanz ca. 12-16GB RAM. Gleichzeitig arbeiten meist ca. 100-500 Clients auf der DB. Und der Server schnurrt...

BTW: Im SQL Server 2008 sollte man sich unbedingt mal den neuen "Filtered Index" anschauen.

@mabo, Luth: Danke

Nochmal zur meiner DB. Der Server der die DB hostet hat 24GB RAM. Wie gesagt, die DB ist ca. 500GB groß. Das reicht vollkommen aus. Laut Taskmanager belegt die SQL Instanz ca. 12-16GB RAM. Gleichzeitig arbeiten meist ca. 100-500 Clients auf der DB. Und der Server schnurrt...

Kann ich nur bestätigen, zumindest bei MSSQL. Unser DB Server hostet knapp 80 Datenbank, mit einem Gesamtvolumen von 100GB und knapp 1000 Clients die verbunden sind, und der Server langweilt sich regelrecht (Prozessorlast 10% maximal, vielleicht mal 20% bei einigen Peaks).

huhu,

So Jungs, danke für den Input und ich muss mich entschuldigen, dass ich leider etwas länger indisponiert war.

Das ganze ist etwas zu mitten in der Nacht entstanden, hat mich in den Fingern gejuckt. Werde mal eure Anmerkungen noch mit einbringen und so weiter. Hierzu muss ich mir aber erstmal selbst nochmal durchlesen was ich da überhaupt geschrieben hab^^

Im Endeffekt sind es persönliche Erfahrungen über die Jahre, als Leitfaden: was läuft denn da grundlegend falsch, um Anhaltspunkte zu bekommen. Aber wie gesagt, ich muss erst nochmal drübergucken^^

😃

Xynratron