DB-Design Tuning via Index & co.

[Net-srac]

Hallo,

ich habe eine ziemlich stark wachsende Community aus dem Boden gestampft und komme nun langsam aber sicher an die grenzen meines Servers. Und das bei grad mal 120 "gleichzeitigen" Usern...

Nun bin ich zu dem Entschluss gekommen, das ich mir mal meine DB anschauen sollte um zu sehen, ob ich nicht etwas optimieren könnte. Ich will nicht alle Tabellen von ihrer Struktur her verändern, da ich imho schon soweit normalisiert hab das es nicht mehr weiter geht. Neben ein paar Sachen, die ich wegen der Performance redundant halte sollte das auch alles passen.

Mir geht es jetzt speziell um das "Tuning" einer solchen Tabelle. Ich hab z.B. gelesen, das Indizierung das lesen aus einer Tabelle beschleunigen kann. Ist das so? Und wenn ja, was sollte ich indizieren??

Ich hab am Beispiel eine Tabelle Messages, die wie folgt aufgebaut ist.

ID | Handle_ID | Absender | Empfaenger | Datum | Betreff | Text

ID ist ein BigInt, mit Autoinkrement obwohl ich mir gar nicht mehr so sicher bin, ob ich den überhaupt brauche... Zumindest benutze ich ihn nicht.

Handle_ID ist ein MD5Schlüssel, der sich aus mehreren Strings generiert. Absender ist der Benutzername des Absenders

Empfaenger ist der Benutzername des Empfaengers

Der Rest sollte selbsterklärend sein.

Die Benutzernamen sind natürlich soweit schon validiert das dort nichts mit SQL schief gehen kann. Diese lese ich bei jedem Aufruf der Mailbox aus der aktuellen Browsersession und bin somit abgesichert.

So. Wenn ein User jetzt seine Mailbox aufruft, mache ich ein select from messages where Empfaenger='benutzer';

Wenn er eine Mail ließt dann mache ich ein select from messages where Empfaenger='benutzer' And Handle_ID='Handle_ID'

Wenn jemand Anwortet wird via Insert eine Zeile eingefügt.

Bisher sehen meine Attribute so aus.

PRIMARY PRIMARY 84786 ID

handle_id INDEX 84786 handle_id

empfaenger INDEX 2231 empfaenger

Habt ihr eine Idee wie ich hier noch optimieren könnte? Bzw. wo ich design bzw. Attributfehler hab?

Ich habe jedes Wochenende Samstags zwischen 3 und 5 Uhr morgens Zeit um größere Änderungen zu updaten. Daher wäre es auch kein Problem die komplette Tabelle auf einem Stagingsystem nochmal umzuwerfen und in eine neue Tabelle zu konvertieren und auf das Echsystem zu uschieben.

Danke für eure Vorschläge.

[Jaraz]

Original geschrieben von Net-srac

Handle_ID ist ein MD5Schlüssel, der sich aus mehreren Strings generiert.

Was heißt das?

Wie oft machst du das?

Einen index solltest du auf alle Spalten die regelmäßig in where abfragen vorkommen anlegen.

Aber das hast du bei der Tabelle ja.

Wieviele Abfragen hast du denn in Spitzenzeiten pro Sekunde und was für eine Maschine?

Gruß Jaraz

[Net-srac]

Also ich setze diverse Daten einer Mail zusammen zu einem String.

Also z. Bsp Empfaenger+Absender+Datum und mache mir daraus einen md5 schlüssel. Der ist dann meine ?25? stellige alphanumerische Handle_ID. Die genaue Länge von md5 hab ich jetzt nicht im Kopf...

Ich hab noch kein Tool gefunden, das mir Statistiken über den DB-Zugriff gibt. Kennst du da eins? Oder kann das MySQL selbst?

Ich habe einen 2.4 Athlon mit 512MB Ram Suse 9.0 und MySQL 4.0.18 Also schon mit QueryCache, der mir aber nicht wirklich viel nutzt, da die meisten Seiten Userspezifisch aufgebaut werden.

Ich habe in meinem Profiler zumindest heute schonmal eine Besserung in der Laufzeit der Scripte gesehen. Habe die Indexe gestern erst gesetzt.

Wir kann man Tabellen optimieren, in die genausooft geschrieben wie gelesen wird?? Ich habe eine Tabelle, die die angemeldeten User verwaltet. Bei dieser Tabelle wird bei jedem Seitenaufruf eines users dessen Timestamp erneuert.

Sind dort indexe auch gut, oder bremsen die das insert bzw. update aus?

Danke

[Jaraz]

Original geschrieben von Net-srac

Ich hab noch kein Tool gefunden, das mir Statistiken über den DB-Zugriff gibt. Kennst du da eins? Oder kann das MySQL selbst?

bei phpMyAdmin gibt es den Punkt: "Show MySQL runtime information"

Müßte auch über den client abrufbar sein, habe ich allerdings noch nicht gemacht.

Original geschrieben von Net-srac

Sind dort indexe auch gut, oder bremsen die das insert bzw. update aus?

Index nur auf Spalten im where Teil.

Natürlich auch bei updates.

Gruß Jaraz

[just_me]

Obwohl ich den Aufwand bei ca. 120 simultanen Zugriffen ehrlich gesagt für übertrieben halte, gibt es bereits anhand dieser Informationsfragmente eine Reihe von Optimierungsmöglichkeiten:

Wir kann man Tabellen optimieren, in die genausooft geschrieben wie gelesen wird??

Zerlege die Tabelle in zwei Relationen:

1. formelle (statische) Daten, wie Name, etc.

2. informelle (dynamische) Daten, wie Zeitstempel und andere Variablen.

Der Rest ist Fleißarbeit...

Wenn ein User jetzt seine Mailbox aufruft, mache ich ein select from messages where Empfaenger='benutzer';

Prinzipiell ist es besser, direkt auf den PK zu suchen. Wenn du also deine statements umstellen kannst, dass sie nach folgendem suchen

select from messages where ID = @id

wirst du spürbar schneller zu Resultaten kommen.

Alternativen: Sicherlich hat Jaraz grundsätzlich Recht, wenn er pauschal feststellt, dass Indizes auf Attribute gelegt werden sollten, die in 'WHERE'-Klauseln vorkommen. Das Indizieren des Empfängers beispielsweise wird dich jedoch allein nicht wesentlich schneller werden lassen.

ID ist ein BigInt, mit Autoinkrement obwohl ich mir gar nicht mehr so sicher bin, ob ich den überhaupt brauche... Zumindest benutze ich ihn nicht.

HIER liegt definitiv das größte Performancepotential.

Die ID ist ein eindeutiger präindizierter Key. Zugriffe hierauf sind hochperformant. (Deswegen ist es ja ein Schlüssel. )

Setze die Breite des PK möglichst treffend - soll heißen: Rechnest du wirklich mit ca. 9'223'372'036'854'775'807 Benutzern, oder reichen vielleicht auch 4'294'967'295 oder gar 16'777'215 Möglichkeiten aus?

- Versuche, wo immer möglich, NUR mit diesem Key zu arbeiten.

(Beispiel:

1.: Finden der ID nach dem Benutzernamen,

2...n.: Finden aller Daten nach der ID (evtl. Restrukturieren der Tabellen))

- Wo immer möglich, vermeide Suchen in Strings und BLOBs. Vergleichsoperationen können, insbesondere, wenn viele Strings (Bytes) identisch sind, reichlich lange dauern. (Im Gegensatz dazu sind Suchen in Ganzzahlen extrem performant.)

- Optimieren der SQL-Statements bringt immer Punkte. (Der Fragenkatalog hierzu ist umfangreich und ich schreibfaul, aber essentiell sagt er aus: Benötigst du WIRKLICH JEDEN abgefragten Wert, oder reicht auch mal weniger Information?)

- Optimieren der Suchbedingungen und Einschränkungen tuned die Performance. Versuche, die Reihenfolge der Einschränkungen so einzurichten, dass sie entsprechend der Wertigkeit ihres Filters stehen: Zuerst die Einschränkung, die die meisten potentiellen Resultate aus- bzw. einschließt, dann die zweitmeisten, usw. usf.

Entsprechend dieser Optimierungen, drängen sich mögliche Indizes fast von allein auf...

- Versuche, Abfragen blockweise auf Tabellen zu setzen. Mit anderen Worten, frage dich, was du aus der gleich anzufassenden Tabelle für Daten brauchst, und hole sie "in einem Schwung" ab. (Beispiel: Angenommen, du brauchst jetzt den Namen, und 15 Zeilen später den Vornamen, dann hole BEIDE Daten auf einmal ab.)

- Gleiches gilt für Inserts und Updates: Führe sie möglichst für eine komplette Zeile gleichzeitig aus. (Sammle die Daten, und füge sie zum spätestmöglichen Zeitpunkt ein.)

- Setze die Breite von Indizes respektive indizierten Attributen möglichst treffend. Es ist unsinnig, Attribute, wie Namen zu indizieren, wenn von 1'000 Einträgen rund 600 mal 'Doe' enthalten ist. Es macht auch wenig Sinn, Geburtsdaten zu indizieren, da es max. 365 Kombinationen gibt. Benutze in solchen Fällen geclusterte Indizes.

Essentiell gilt:

Eine Suche beginnt im PK respektive im Index, wenn dieser vorhanden ist.

Wird hier ein eindeutiges Ergebnis gefunden, wird die Suche beendet, das Tupel direkt angesprungen, die Daten ausgelesen und zurückgegeben.

Wird ein Block möglicher Ergebnisse gefunden, werden diese zeilenweise auf Übereinstimmungen "abgeklopft". (Ergo: Je größer der gefundene/vermutete Block ist, desto langsamer die Suche.)

Im worst case hast du einen Tabellenscan, der in stark fragmentierten Tabellen schier ewig dauert.

Das führt dann auch zum letzten Punkt:

- Konsolidierung der Daten bringt oft mehr Performance, als der 3. Index auf eine Tabelle...

- Die Anzahl der gleichzeitigen Verbindungen zur Datenbank sollte optimal bemessen werden. (Nicht übermäßig viel, damit das DBMS nicht in Narzismus verfällt, aber auch nicht zu wenige, damit die Benutzer nicht vorher sterben.)

- RAM ist das A und O. Je mehr Tabellen gecached (ich liebe dieses Wort:)) werden können, desto weniger Zugriffe auf die Festplatte sind nötig.

In diesem Sinne,

viel Spaß beim Feintuning,

just_me

[Net-srac]

Wenn es nur 120 bleiben würden würde ich da auch nix ändern, da die Leistung jetzt noch ausreicht. Aber ich gehe davon aus, das diese Community noch grösser werden wird.

Aber genau eine Liste wie deine hab ich gebraucht. Ich schätze, das ich über kurz oder lang nicht drumherum kommen werde, die User direkt über ID's und nicht mehr über ihren Namen anzusprechen. Wegen der performanteren Zahlensuche mit PK. Also wird das zwar schon ein kleiner Sack voll Arbeit, aber ich denke, das ich, wenn ich es jetzt mache besser klar komme, als wenn ich es irgendwann im Zeitdruck tue, weil es dann zu spät ist und die Seite schleift.

Ich werde mich dann also mal ans Werk machen und sehen das ich alles irgendwo über numerische ID's abhandeln kann...

Danke erstmal für eure Mühen im Namen meiner User.

Hätte ich mal nur in der BS auf meinen Datenbanklehrer gehört...

Danke

EDIT: ich hab den MySQL-Server jetzt seid 4 Tagen laufen und hab im Schnitt. 54,51 Abfragen pro Sekunde... Nun weiß ich nur nicht, ob das vieeeel zu wenig ist, um so viel last zu erzeugen oder nicht. Habt ihr so mittelwerte?? Ich dachte letztens auch schon, das 80.000Mails viel sind, aber als ich dann von 2GB-Datenbanken gehört hab hab ich mir das ganz schnell wieder anders überlegt... Hab grad mal 30MB

[Jaraz]

Nun ja, der Schnitt ist relativ uninteressant.

"Mein" doppel P3 fängt bei 1400 Select pro Sekunde (real, nicht Durchschnitt) an zu schwitzen.

Und die Datenbank ist deutlich größer.

Deine passt ja locker in den Speicher, du solltest also mal schauen ob "slow queries" gelogt werden und mal deine Joins überprüfen.

Ansonsten mal aufmerksam das hier lesen:

http://www.mysql.com/doc/en/MySQL_Optimisation.html

Besonders key_buffer_size und table_cache solltest du mal manuell hochsetzen.

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \

--sort_buffer_size=4M --read_buffer_size=1M &

Gruß Jaraz