Weil MySQL extrem schnelles Tabellensperren beherrscht (mehrfache Leser / einzelne Schreiber), ist das größte verbleibende Problem eine Mischung aus einem laufenden Strom von Einfügevorgängen und langsamen Selects auf dieselbe Tabelle.

Wir glauben, dass diese Wahl auf einer sehr großen Anzahl von Systemen letztlich einen Gewinn darstellt. Auch dieser Fall ist üblicherweise dadurch zu lösen, dass man mehrfache Kopien der Tabelle vorhält, aber man benötigt mehr Anstrengung und Hardware.

Wir arbeiten auch an einigen Erweiterungen, um dieses Problem in Hinsicht auf einige häufige Applikationsnischen zu lösen.

Weil alle SQL-Server unterschiedliche Teile von SQL implementieren, ist es immer Arbeit, portable SQL-Applikationen zu schreiben. Bei sehr einfachen Selects und Inserts ist das sehr einfach, aber je mehr Sie brauchen, desto schwieriger wird es. Wenn Sie eine Applikation wollen, die bei vielen Datenbanken noch schnell läuft, wird es sogar noch schwieriger!

Um eine komplexe Applikation portabel zu machen, müssen Sie sich für eine Reihe von SQL-Servern entscheiden, mit denen sie funktionieren soll.

Sie können das MySQL-Crash-me-Programm bzw. die Webpage http://www.mysql.com/information/crash-me.php benutzen, um Funktionen, Typen und Einschränkungen zu finden, die Sie mit einer Auswahl von Datenbank-Servern benutzen können. Crash-me testet bei weitem nicht alles, was möglich ist, aber mit etwa 450 unterschiedlichen Dingen ist es recht umfassend.

Sie sollten zum Beispiel keine Spaltennamen benutzen, die länger als 10 Zeichen sind, wenn Sie auch Informix oder DB2 benutzen wollen.

Sowohl die MySQL-Benchmarks als auch die Crash-me-Programme sind sehr Datenbank-abhängig. Indem Sie einen Blick darauf werfen, wie wir damit umgegangen sind, bekommen Sie ein Gefühl dafür, was Sie in Ihrer Applikation schreiben müssen, damit diese Datenbank-unabhängig läuft. Die Benchmark-Tests selbst befinden sich im sql-bench-Verzeichnis der MySQL-Quelldistribution. Sie sind in Perl mit der DBI-Datenbank-Schnittstelle geschrieben (die den Zugriffsteil des Problems löst).

Siehe http://www.mysql.com/information/benchmarks.html wegen der Ergebnisse aus diesem Benchmark-Test.

Wie Sie an den Ergebnissen sehen, haben alle Datenbanken einige Schwachpunkte, das heißt, sie haben verschiedene Design-Kompromisse, die zu unterschiedlichem Verhalten führen.

Wenn Sie nach Datenbank-Unabhängigkeit streben, müssen Sie ein gutes Gefühl für die Flaschenhälse jedes SQL-Servers bekommen. MySQL ist SEHR schnell beim Abrufen und Aktualisieren von Dingen, hat aber Probleme, wenn gleichzeitig langsame Leser / Schreiber auf dieselbe Tabelle zugreifen. Oracle hat ein großes Problem, wenn Sie versuchen, auf Zeilen zuzugreifen, der kürzlich aktualisiert wurden (solange, bis sie auf Platte zurückgeschrieben wurden). Transaktionale Datenbanken sind allgemein nicht sehr gut darin, Zusammenfassungstabellen aus Log-Tabellen zu erzeugen, weil in diesem Fall Sperren auf Zeilenebene fast nutzlos ist.

Um Ihre Applikation wirklich Datenbank-unabhängig zu machen, müssen Sie eine leicht erweiterbare Schnittstelle definieren, über die Sie Ihre Daten manipulieren. Weil auf den meisten Systemen C++ verfügbar ist, ist es sinnvoll, C++-Klassen als Schnittstellen zu den Datenbanken zu benutzen.

Wenn Sie irgend ein spezifisches Feature einer Datenbankbenutzung (wie den REPLACE-Befehl in MySQL), sollten Sie eine Methode für die anderen SQL-Server codieren, um dasselbe Feature (wenngleich langsamer) zu implementieren. Bei MySQL können Sie die /*! */-Syntax benutzen, um MySQL-spezifische Schlüsselwörter in einer Anfrage zu verwenden. Der Code innerhalb von /**/ wird von den meisten anderen SQL-Servern als Kommentar behandelt (ignoriert).

Wenn WIRKLICH hohe Performance wichtiger als Exaktheit ist, wie bei einigen Web-Applikationen, besteht eine Möglichkeit darin, eine Applikationsebene zu erzeugen, die alle Ergebnisse cachet, um Ihnen noch höhere Performance zu bringen. Indem Sie alte Ergebnisse nach einer Weile 'auslaufen' lassen, können Sie den Cache in vernünftiger Weise 'frisch' halten. Das ist in Fällen extrem hoher Last recht nett, wobei Sie den Cache dynamisch vergrößern und die Verfallszeit (Expire Timeout) höher setzen können, bis wieder Normalauslastung eintritt.

In diesem Fall sollte die Tabellenerzeugungsinformation Informationen über die ursprüngliche Cache-Größe enthalten und darüber, wie oft die Tabelle normalerweise aktualisiert (refresh) werden sollte.

In der anfänglichen Phase der Entwicklung von MySQL wurden die Features von MySQL für unseren größten Kunden gemacht. Dieser macht Data-Warehousing für eine Reihe der größten Einzelhändler in Schweden.

Aus allen Verkaufsstellen erhalten wir wöchentliche Zusammenfassungen aller Bonuskarten-Transaktionen, und es wird erwartet, dass daraus nützliche Informationen für die Eigentümer der Verkaufsstellen zur Verfügung gestellt werden, damit diese herausfinden können, wie ihre Werbemaßnahmen ihre Kunden beeinflussen.

Die Datenmenge ist recht riesig (etwa 7 Millionen Zusammenfassungs-Transaktionen pro Monat), und wir haben Daten von 4 bis 10 Jahren, die wir dem Benutzer präsentieren müssen. Wir bekamen wöchentliche Anfragen von Kunden, die 'sofortigen' Zugriff auf neue Berichte aus diesen Daten wollten.

Die Lösung bestand darin, alle Informationen monatsweise in komprimierten 'Transaktions-' Tabellen zu speichern. Wir haben einen Satz einfacher Makros (ein Skript), die aus diesen Tabellen Zusammenfassungstabellen erzeugen, die nach verschiedenen Kriterien gruppiert sind (Produktgruppe, Kunden-ID, Verkaufsstelle usw.). Die Berichte sind Web-Seiten, die dynamisch durch ein kleines Perl-Skript erzeugt werden, das eine Web-Seite parst, die enthaltenen SQL-Statements ausführt und die Ergebnisse einfügt. Wir hätten statt dessen PHP oder mod_perl benutzt, aber diese waren damals noch nicht verfügbar.

Für grafische Darstellungen schrieben wir ein einfaches Werkzeug in C, das GIFs auf der Grundlage der Ergebnisse einer SQL-Anfrage erzeugen kann (nach einigem Verarbeiten des Ergebnisses). Dieses wird ebenfalls dynamisch durch ein Perl-Skript ausgeführt, das die HTML-Dateien parst.

In den meisten Fällen kann ein neuer Bericht einfach durch das Kopieren eines bestehenden Skripts und das Verändern der SQL-Anfrage darin erzeugt werden. In einigen Fällen müssen wir einer bestehenden Zusammenfassungstabelle weitere Felder hinzufügen oder eine neue generieren, aber auch das ist recht einfach, weil wir alle Transaktionstabellen auf Platte haben. (Momentan haben wir mindestens 50 GB an Transaktionstabellen und 200 GB weiterer Kundendaten.)

Wir lassen unsere Kunden auch direkt mit ODBC auf die Transaktionstabellen zugreifen, so dass fortgeschrittene Benutzer selbst mit den Daten experimentieren können.

Wir hatten mit der Handhabung keinerlei Probleme, auf einer recht bescheidenen Sun Ultra SPARCstation (2x200 MHz). Kürzlich haben wir einen unserer Server auf eine mit 2 Prozessoren bestückte 400 MHz-UltraSPARC erweitert und planen jetzt, Transaktionen auf Produktebene zu handhaben, was eine zehnfache Steigerung der Datenmenge bedeuten würde. Wir glauben, dass wir auch damit Schritt halten können, indem wir unseren Systemen einfach mehr Festplattenplatz hinzufügen.

Wir experimentieren auch mit Intel-Linux, um in der Lage zu sein, mehr CPU-Power preisgünstiger zu erhalten. Jetzt, wo wir das binäre portable Datenbankformat haben (neu seit Version 3.23), werden wir dieses für einige Teile der Applikation benutzen.

Unser anfängliches Gefühl sagt uns, dass Linux viel besser bei geringer bis mittlerer Last ist, während Solaris wegen der extremen Festplatten-Eingabe-/Ausgabe-Geschwindigkeit (Disk-IO) bei Hochlast besser ist, aber wir können noch nichts Endgültiges darüber aussagen. Nach einigen Diskussionen mit den Linux-Kernel-Entwicklern ist das eventuell ein Seiteneffekt von Linux, das dem Stapel-Job so viel Ressourcen gibt, dass die interaktive Performance sehr gering wird. Dadurch scheint die Maschine sehr langsam und unempfänglich für Eingaben zu lassen, während große Stapel-Jobs abgearbeitet werden. Wir hoffen, dass dies in zukünftigen Linux-Kernels besser gehandhabt wird.

Dieser Abschnitt sollte eine technische Beschreibung der MySQL- Benchmark-Suite (und von crash-me) enthalten, aber diese Beschreibung wurde noch nicht geschrieben. Momentan können Sie eine gute Idee über den Benchmark bekommen, wenn Sie einen Blick auf den Code und die Ergebnisse im sql-bench-Verzeichnis jeder MySQL-Quelldistribution werfen.

Diese Benchmark-Suite ist als Benchmark gedacht, der jedem Benutzer mitteilt, welche Dinge in einer gegebenen SQL-Implementation gut performen und welche schlecht.

Beachten Sie, dass dieser Benchmark single-threaded ist. Daher misst er die minimale Zeit der Operationen. In Zukunft planen wir, auch etliche multi-threaded Test hinzuzufügen.

Beispiele (die auf derselben NT-4.0-Maschine liefen):

Im obigen Test lief MySQL mit einem 8 MB Index-Cache.

Weitere Benchmark-Ergebnisse haben wir unter http://www.mysql.com/information/benchmarks.html gesammelt.

Beachten Sie, dass Oracle nicht beinhaltet ist, weil sie gebeten haben, entfernt zu werden. Alle Oracle-Benchmarks müssen von Oracle freigegeben werden! Wir glauben, dass das die Aussagefähigkeit von Oracle-Benchmarks SEHR zweifelhaft erscheinen läßt, weil alle obigen Benchmarks dafür da sind zu zeigen, was eine Standard-Installation bei einem einzelnen Client machen kann.

Um eine Benchmark-Suite laufen zu lassen, müssen Sie eine MySQL-Quelldistribution herunter laden, den Perl-DBI-Treiber und den Perl-DBD-Treiber für die gewünschte Datenbank installieren und dann folgendes tun:

cd sql-bench
perl run-all-tests --server=#

Wobei # einer der unterstützten Server ist. Sie erhalten eine Auflistung aller Optionen und unterstützten Server, indem Sie run-all-tests --help ausführen.

Crash-me versucht herauszufinden, welche Features eine Datenbank unterstützt und wo ihre Fähigkeiten und Einschränkungen sind, indem tatsächliche Anfragen ausgeführt werden. Beispielsweise stellt es fest:

Sie finden die Ergebnisse von Crash-me für viele verschiedene Datenbanken unter http://www.mysql.com/information/crash-me.php.

Sie sollten Ihre Applikation und Datenbank auf jeden Fall einem Benchmark-Test unterziehen um herauszufinden, wo Flaschenhälse sind. Indem Sie einen Flaschenhals beseitigen (oder ihn durch ein 'Dummy-Modul' ersetzen), können Sie leicht den nächsten Flaschenhals herausfinden (usw.). Selbst wenn die insgesamte Performance für Ihre Applikation ausreichend ist, sollten Sie zumindest einen Plan für jeden Flaschenhals aufstellen und entscheiden, auf welche Weise dieser beseitigt werden soll, wenn Sie eines Tages die zusätzliche Performance benötigen.

Als Beispiel für ein portables Benchmark-Programm schauen Sie sich die MySQL-Benchmark-Suite an. See Abschnitt 6.1.4, „Die MySQL-Benchmark-Suite“. Sie können jedes Programm dieser Suite nehmen und es Ihren Bedürfnissen entsprechend abändern. Wenn Sie das tun, können Sie unterschiedliche Lösungen für Ihr Problem finden und testen, was bei Ihnen wirklich die schnellste Lösung ist.

Es ist häufig der Fall, dass Probleme nur dann auftreten, wenn das System unter schwerer Last läuft. Viele Kunden nahmen mit uns Kontakt auf, nachdem sie ein (getestetes) System in eine Produktionsumgebung stellten und Lastprobleme bekamen. Bei jedem dieser Fälle gab es bislang entweder Probleme mit dem Grund-Design (Tabellen-Scans laufen NICHT gut unter hoher Last) oder im Zusammenhang mit dem Betriebssystem / den Bibliotheken. Das meiste davon wäre SEHR viel einfacher zu beheben, wenn die Systeme nicht bereits in einer Produktionsumgebung liefen.

Um solcherlei Probleme zu vermeiden, sollten Sie einige Anstrengung darauf verwenden, Ihre gesamte Applikation unter der schlimmstmöglichen Last zu benchmarken! Hierfür können Sie Super Smack benutzen, das Sie hier erhalten: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Wie der Name nahelegt, kann es Ihr System auf die Knie zwingen, wenn Sie das wollen. Achten Sie daher darauf, es nur auf Entwicklungssystemen zu verwenden.

     EXPLAIN tabelle
oder EXPLAIN SELECT select_optionen

EXPLAIN tabelle ist ein Synonym für DESCRIBE tabelle oder SHOW COLUMNS FROM tabelle.

Wenn Sie einem SELECT-Statement das Schlüsselwort EXPLAIN voran stellen, erklärt MySQL explains, wie er das SELECT ausführen würde, indem Informationen darüber gemacht werden, wie Tabellen verknüpft (Join) werden und in welcher Reihenfolge.

Mit der Hilfe von EXPLAIN können Sie erkennen, wo Sie Tabellen Indexe hinzufügen müssen, um ein schnelleres SELECT zu erhalten, das Indexe benutzt, um die Datensätze zu finden. Ausserdem sehen Sie, ob der Optimierer die Tabellen in optimaler Reihenfolge verknüpft. Um den Optimierer zu zwingen, eine spezielle Verknüpfungsreihenfolge bei einem SELECT-Statement einzuhalten, fügen Sie eine STRAIGHT_JOIN-Klausel hinzu.

Bei nicht einfachen Verknüpfungen (Joins) gibt EXPLAIN für jede Tabelle, die im SELECT-Statement benutzt wurde, eine Informationszeile zurück. Die Tabellen sind in der Reihenfolge aufgelistet, in der sie gelesen werden würden. MySQL löst alle Joins mit einer Single-Sweep-Multi-Join-Methode auf. Das bedeutet, dass MySQL eine Zeile aus der ersten Tabelle liest, dann die passende Zeile in der zweiten Tabelle sucht, dann in der dritten Tabelle usw. Wenn alle Tabellen verarbeitet wurden, gibt er die ausgewählten Spalten aus und geht rückwärts durch die Tabellenliste durch, bis eine Tabelle gefunden wird, bei der es weitere passende Zeilen gibt. Die nächste Zeile wird aus dieser Tabelle gelesen, und der Prozess fährt mit der nächsten Tabelle fort.

Die Ausgabe von EXPLAIN enthält folgende Spalten:

Die verschiedenen Join-Typen sind unten aufgeführt, sortiert vom besten zum schlechtesten Typ:

Sie erhalten einen guten Anhaltspunkt, wie gut ein Join ist, wenn Sie alle Werte in der rows-Spalte der EXPLAIN-Ausgabe multiplizieren. Das sollte grob aussagen, wie vielen Zeilen MySQL untersuchen muss, um die Anfrage auszuführen. Diese Anzahl wird auch benutzt, wenn Sie Anfragen mit der max_join_size-Variablen begrenzen. See Abschnitt 6.5.2, „Serverparameter tunen“.

Das folgende Beispiel zeigt, wie ein JOIN progressiv optimiert werden kann, indem die Informationen genutzt werden, die EXPLAIN bereit stellt.

Angenommen, Sie haben unten stehendes SELECT-Statement, das Sie mit EXPLAIN untersuchen:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Nehmen wir bei diesem Beispiel folgendes an:

Anfangs, bevor die Optimierung durchgeführt wurde, ergab das EXPLAIN-Statement folgende Informationen:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

Weil type bei jeder Tabelle ALL ist, zeigt die Ausgabe, dass MySQL eine komplette Verknüpfung (Full Join) aller Tabellen durchführt! Das dauert recht lange, weil das Produkt der Zeilenanzahl in jeder Tabelle untersucht werden muss! In diesem Fall ist das 74 * 2.135 * 74 * 3.872 = 45.268.558.720 Zeilen. Wenn die Tabellen größer wären, können Sie sich vorstellen, wie lange das dauern würde.

Ein Problem liegt hier darin, dass MySQL (noch) keine Indexe auf Spalten effizient benutzen kann, wenn sie unterschiedlich deklariert sind. In diesem Zusammenhang sind VARCHAR und CHAR dasselbe, es sei denn, sie sind mit unterschiedlichen Längen deklariert. Weil tt.ActualPC als CHAR(10) und et.EMPLOYID als CHAR(15) deklariert ist, gibt eine Unstimmigkeit der Längen.

Um diese Ungleichheit der Spaltenlängen zu beheben, benutzen Sie ALTER TABLE, um ActualPC von 10 auf 15 Zeichen zu verlängern:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Jetzt sind tt.ActualPC und et.EMPLOYID beide VARCHAR(15). Eine erneute Ausführung des EXPLAIN-Statements ergibt dieses Ergebnis:

table type   possible_keys   key     key_len rew         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

Das ist nicht perfekt, aber viel besser (das Produkt der rows-Werte ist jetzt um einen Faktor 74 niedriger). Diese Version wird innerhalb von ein paar Sekunden ausgeführt.

Eine zweite Änderung kann durchgeführt werden, um die Unstimmigkeit der Spaltenlängen für die tt.AssignedPC = et_1.EMPLOYID- und tt.ClientID = do.CUSTNMBR-Vergleiche zu beheben:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

Jetzt ergibt EXPLAIN folgende Ausgabe:

table type   possible_keys   key     key_len rew            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    rew    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Das ist fast so gut, wie es überhaupt geht.

Das verbleibende Problem besteht darin, dass MySQL vorgabemäßig annimmt, dass die Werte in der tt.ActualPC-Spalte gleichmäßig verteilt sind, was in der tt-Tabelle nicht der Fall ist. Glücklicherweise ist es einfach, MySQL darüber zu informieren:

shell> myisamchk --analyze PFAD_ZU_MYSQL_DATENBANK/tt
shell> mysqladmin refresh

Jetzt ist der Join perfekt und EXPLAIN ergibt dieses Ergebnis:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Beachten Sie, dass die rows-Spalte in der Ausgabe von EXPLAIN eine gehobene Form von Vermutung des MySQL-Join-Optimierers ist. Um eine Anfrage zu optimieren, sollten Sie überprüfen, ob diese Zahlen der Wahrheit nahe kommen. Wenn nicht, erhalten Sie eventuell bessere Performance, wenn Sie STRAIGHT_JOIN in Ihrem SELECT-Statement benutzen und versuchen, die Tabellen in der FROM-Klausel in anderer Reihenfolge anzugeben.

In den meisten Fällen können Sie die Performance schätzen, indem Sie Suchvorgänge auf Festplatte zählen. Bei kleinen Tabellen können Sie die Zeile üblicherweise mit 1 Festplatten-Suchvorgang finden (weil der Index wahrscheinlich im Cache ist). Bei größeren Tabellen können Sie schätzen, dass Sie (bei der Benutzung von B++-Baum-Indexen) brauchen werden: log(zeilen_zahl) / log(index_block_laenge / 3 * 2 / (index_laenge + daten_zeiger_laenge)) + 1 Suchvorgänge, um die Zeile zu finden.

In MySQL ist ein Index-Block üblicherweise 1024 Bytes lang und der Daten-Zeiger üblicherweise 4 Bytes. Eine 500.000-Zeilen-Tabelle mit einer Indexlänge von 3 (medium integer) ergibt: log(500.000)/log(1024/3*2/(3+4)) + 1 = 4 Suchvorgänge.

Da der obige Index etwa 500.000 * 7 * 3/2 = 5,2 MB benötigen würde (angenommen, dass die Index-Puffer zu 2/3 gefüllt sind, was ein typischer Wert sit), haben Sie wahrscheinlich viel vom Index im Arbeitsspeicher und werden wahrscheinlich nur 1 bis 2 Betriebssystem-Aufrufe benötigen, um Daten zu lesen, um die Zeile zu finden.

Bei Schreibvorgängen brauchen Sie jedoch 4 Suchanfragen (wie oben), um herauszufinden, wo der neue Index platziert wird, und normalerweise 2 Suchvorgänge, um den Index zu aktualisieren und die Zeile zu schreiben.

Beachten Sie, dass oben Gesagtes nicht bedeutet, dass Ihre Applikation allmählich mit N log N verfällt! Solange alles durch das Betriebssystem oder den SQL-Server gecachet wird, werden die Dinge nur marginal langsamer, wenn die Tabellen größer werden. Wenn die Daten zu Groß werden, um gecachet zu werden, werden die Dinge anfangen, viel langsamer zu laufen, bis Ihre Applikation schließlich komplett durch Suchvorgänge auf Festplatte ausgebremst wird (die mit N log N zunehmen). Um das zu vermeiden, vergrößern Sie den Index-Cache, wenn die Daten wachsen. See Abschnitt 6.5.2, „Serverparameter tunen“.

Wenn Sie ein langsames SELECT ... WHERE schneller machen wollen, ist im Allgemeinen das erste, was zu prüfen ist, ob Sie einen Index hinzufügen können oder nicht. See Abschnitt 6.4.3, „Wie MySQL Indexe benutzt“. Alle Verweise (Reference) zwischen verschiedenen Tabellen sollten üblicherweise mit Indexen gemacht werden. Sie können den EXPLAIN-Befehl benutzen, um herauszufinden, welche Indexe für ein SELECT benutzt werden. See Abschnitt 6.2.1, „EXPLAIN-Syntax (Informationen über ein SELECT erhalten)“.

Einige allgemeine Tipps:

Die WHERE-Optimierungen wurden hier in den SELECT-Teil aufgenommen, weil sie meist in Verbindung mit SELECT benutzt werden, aber dieselben Optimierungen treffen für WHERE bei DELETE- und UPDATE-Statements zu.

Beachten Sie auch, dass dieser Abschnitt nicht vollständig ist. MySQL führt viele Optimierungen durch und wir hatten noch keine Zeit, alle davon zu dokumentieren.

Einige der Optimierungen, die MySQL durchführt, sind unten aufgeführt:

Einige Beispiele von Anfragen, die sehr schnell sind:

mysql> SELECT COUNT(*) FROM tabelle;
mysql> SELECT MIN(schluessel_teil1),MAX(schluessel_teil1) FROM tabelle;
mysql> SELECT MAX(schluessel_teil2) FROM tabelle
           WHERE schluessel_teil_1=konstante;
mysql> SELECT ... FROM tabelle
           ORDER BY schluessel_teil1,schluessel_teil2,... LIMIT 10;
mysql> SELECT ... FROM tabelle
           ORDER BY schluessel_teil1 DESC,schluessel_teil2 DESC,... LIMIT 10;

Die folgenden Anfragen werden aufgelöst, indem nur der Index-Baum benutzt wird (unter der Annahme, dass die indizierten Spalten numerisch sind):

mysql> SELECT schluessel_teil1,schluessel_teil2 FROM tabelle WHERE schluessel_teil1=val;
mysql> SELECT COUNT(*) FROM tabelle
           WHERE schluessel_teil1=val1 AND schluessel_teil2=val2;
mysql> SELECT schluessel_teil2 FROM tabelle GROUP BY schluessel_teil1;

Die folgenden Anfragen benutzen Indexierung, um die Zeilen in sortierter Reihenfolge abzufragen, ohne einen separaten Sortierdurchgang:

mysql> SELECT ... FROM tabelle ORDER BY schluessel_teil1,schluessel_teil2,... ;
mysql> SELECT ... FROM tabelle ORDER BY schluessel_teil1 DESC,schluessel_teil2 DESC,... ;

DISTINCT wird für alle Spalten in GROUP BY umgewandelt, DISTINCT in Kombination mit ORDER BY benötigt in vielen Fällen ebenfalls eine temporäre Tabelle.

Wenn LIMIT # mit DISTINCT kombiniert wird, hält MySQL an, sobald er # eindeutige Zeilen findet.

Wenn Sie nicht Spalten aus allen benutzten Tabellen verwenden, hält MySQL mit dem Scannen der nicht benutzten Tabellen an, sobald er die erste Übereinstimmung gefunden hat.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

Im Beispiel wird angenommen, dass t1 vor t2 benutzt wird (überprüfen Sie das mit EXPLAIN). In diesem Fall hört MySQL auf, von t2 zu lesen (für diese bestimmte Zeile in t1), sobald die erste Zeile in t2 gefunden wurde.

A LEFT JOIN B ist in MySQL wie folgt implementiert:

RIGHT JOIN ist analog zu LEFT JOIN implementiert.

Die Lese-Reihenfolge der Tabellen, die von LEFT JOIN und STRAIGHT JOIN erzwungen wird, hilft dem Optimierer (der berechnet, in welcher Reihenfolge die Tabellen verknüpft werden sollen), seine Arbeit schneller durchzuführen, weil weniger Tabellenvertauschungen überprüft werden müssen.

Beachten Sie, dass das oben Gesagte bedeutet, dass bei einer Anfrage des folgenden Typs:

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

MySQL einen kompletten Scan von b durchführen wird, weil der LEFT JOIN erzwingt, dass diese vor d gelesen wird.

Das läßt sich in diesem Fall beheben, indem die Anfrage wie folgt geändert wird:

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

In einigen Fällen handhabt MySQL die Anfrage unterschiedlich, wenn Sie LIMIT # statt HAVING benutzen:

Die Zeit, einen Datensatz einzufügen, besteht ungefähr aus:

Wobei die Zahlen in etwa proportional zur Gesamtzeit sind. Diese Berechnung zieht den anfänglichen Overhead, um Tabellen zu öffnen, nicht in Betracht (was einmal für jede gleichzeitig laufende Anfrage gemacht wird).

Die Größe der Tabelle verlangsamt das Einfügen von Indexen um N log N (B-Bäume).

Einige Möglichkeiten, die Geschwindigkeit von Einfügeoperationen zu steigern:

Um sowohl für LOAD DATA INFILE als auch für INSERT mehr Geschwindigkeit zu erzielen, vergrößern Sie den Schlüssel-Puffer. See Abschnitt 6.5.2, „Serverparameter tunen“.

Update-Anfragen werden wie eine SELECT-Anfrage optimiert, mit dem zusätzlichen Overhead eines Schreibvorgangs. Die Geschwindigkeit des Schreibvorgangs hängt von der Größe der Daten und von der Anzahl der Indexe, die aktualisiert werden, ab. Indexe, die nicht geändert werden, werden nicht aktualisiert.

Eine weitere Möglichkeit, Aktualisierungen (Updates) schnell zu machen, ist, sie zu verzögern und dann später viele Aktualisierungen hintereinander zu machen. Viele Aktualisierungen hintereinander sind viel schneller als nur eine zugleich, wenn Sie die Tabelle sperren.

Beachten Sie, dass die Aktualisierung eines Datensatzes bei dynamischem Datensatzformat dazu führen kann, dass der Datensatz aufgespalten wird. Wenn Sie das oft durchführen, ist es daher sehr wichtig, gelegentlich OPTIMIZE TABLE auszuführen. See Abschnitt 5.5.1, „OPTIMIZE TABLE-Syntax“.

Wenn Sie alle Zeilen in der Tabelle löschen wollen, sollten Sie TRUNCATE TABLE tabelle benutzen. See Abschnitt 7.4.7, „TRUNCATE-Syntax“.

Die Zeit, die für das Löschen eines Datensatzes benötigt wird, ist exakt proportional zur Anzahl der Indexe. Um Datensätze schneller zu löschen, können Sie die Größe des Index-Caches herauf setzen. See Abschnitt 6.5.2, „Serverparameter tunen“.

Ungeordnete Liste von Tipps für schnellere Systeme:

Im Anhang finden Sie eine Erörterung zu den unterschiedlichen Sperrmethoden. See Abschnitt D.4, „Sperrmethoden“.

Jedes Sperren in MySQL ist blockierungsfrei. Das wird erreicht, indem alle Sperren zugleich am Anfang einer Anfrage angefordert werden, und indem Tabellen immer in derselben Reihenfolge gesperrt werden.

The Sperrmethode, die MySQL für WRITE-Sperren benutzt, funktioniert wie folgt:

Die Sperrmethode, die MySQL für READSperren benutzt, funktioniert wie folgt:

Wenn eine Sperre aufgehoben wird, wird die Sperren den Threads in der Schreibsperren-Warteschlange verfügbar gemacht, danach den Threads in der Lesesperren-Warteschlange.

Das bedeutet, wenn Sie viele Aktualisierungen auf eine Tabelle haben, warten SELECT-Statements, bis es keine Aktualisierungen mehr gibt.

Um das für den Fall zu umgehen, dass es viele INSERT- und-SELECT-Operationen auf eine Tabelle gibt, können Sie Zeilen in eine temporäre Tabelle einfügen und die echte Tabelle gelegentlich aus den Daten der temporäre Tabelle aktualisieren.

Das machen Sie wie folgt:

mysql> LOCK TABLES echte_tabelle WRITE, einfuege_tabelle WRITE;
mysql> insert into echte_tabelle select * von einfuege_tabelle;
mysql> TRUNCATE TABLE einfuege_tabelle;
mysql> UNLOCK TABLES;

Sie können bei INSERT, UPDATE oder DELETE die LOW_PRIORITY-Option oder bei SELECT die HIGH_PRIORITY-Option benutzen, wenn Sie dem Abruf von Daten in bestimmten Fällen Priorität einräumen wollen. Sie können auch mysqld mit --low-priority-updates starten, um dasselbe Verhalten zu erreichen.

Die Benutzung von SQL_BUFFER_RESULT kann ebenfalls helfen, Tabellensperren kürzer zu machen. See Abschnitt 7.4.1, „SELECT-Syntax“.

Sie können auch den Sperr-Code in mysys/thr_lock.c ändern, um eine einzige Warteschlagen zu benutzen. In diesem Fall haben Schreibsperren und Lesesperren dieselbe Priorität, was bei einigen Applikationen eventuell hilfreich ist.

Der Tabellensperren-Code in MySQL ist blockierungsfrei.

MySQL benutzt Tabellensperren (anstelle von Zeilensperren oder Spaltensperren) für alle Tabellentypen ausser BDB-Tabellen, um eine sehr hohe Sperrgeschwindigkeit zu erzielen. Bei großen Tabellen ist Tabellensperren bei den meisten Applikationen VIEL besser als Zeilensperren, aber es gibt natürlich ein paar Fallstricke.

Bei BDB- und InnoDB-Tabellen benutzt MySQL Tabellensperren, wenn Sie die Tabelle explizit mit LOCK TABLES sperren oder einen Befehl ausführen, der jede Zeile in der Tabelle ändern wird, wie ALTER TABLE. Bei diesen Tabellentypen empfehlen wir, LOCK TABLES überhaupt nicht zu benutzen.

Ab MySQL-Version 3.23.7 können Sie Zeilen in MyISAM-Tabellen zur gleichen Zeit einfügen, während andere Threads aus der Tabelle lesen. Beachten Sie, dass das momentan nur funktioniert, wenn es zu der Zeit, zu der das Einfügen vorgenommen wird, keine durch gelöschte Zeilen verursachte Löcher in der Tabelle gibt. Wenn alle Löcher mit neuen Daten gefüllt wurden, werden gleichzeitige Einfügevorgänge automatisch wieder aktiviert.

Tabellensperren ermöglicht, dass viele Threads gleichzeitig aus einer Tabelle lesen, aber bevor ein Thread in die Tabelle schreiben kann, muss er zunächst exklusiven Zugriff erhalten. Während der Aktualisierung müssen andere Threads, die auf diese Tabelle zugreifen wollen, warten, bis die Aktualisierung fertig ist.

Weil Aktualisierung von Tabellen normalerweise als wichtiger erachtet werden als SELECT, erhalten alle Statements, die eine Tabelle aktualisieren, eine höhere Priorität als Statements, die Informationen aus der Tabelle abrufen. Das sollte sicherstellen, dass Aktualisierungen nicht 'verhungern', wenn viele große Anfragen auf eine bestimmte Tabelle durchgeführt werden. (Sie können das ändern, indem Sie bei dem Statement, dass die Aktualisierung durchführt, LOW_PRIORITY verwenden, oder beim SELECT-Statement HIGH_PRIORITY.)

Ab MySQL-Version 3.23.7 können Sie die max_write_lock_count-Variable benutzen, um MySQL zu zwingen, temporär allen SELECT-Statements, die auf eine Tabelle warten, nach einer bestimmten Anzahl von Einfügevorgängen auf eine Tabelle höhere Priorität einzuräumen.

Tabellensperren ist jedoch bei folgendem Szenario nicht sehr gut:

Mögliche Lösungen dieses Problems sind:

MySQL speichert Zeilendaten und Indexdaten in separaten Dateien. Viele (fast alle) anderen Datenbanken vermischen Zeilen- und Indexdaten in derselben Datei. Wir glauben, dass die Wahl, die MySQL getroffen hat, für einen sehr weiten Bereich moderner Systeme besser ist.

Eine weitere Möglichkeit, Zeilendaten zu speichern, besteht darin, die Information für jede Spalten in einem separaten Bereich zu halten (Beispiele sind SDBM und Focus). Das verursacht Performance-Einbussen für jede Anfrage, die auf mehr als eine Spalte zugreift. Weil das so schnell schlechter wird, wenn auf mehr als eine Spalte zugegriffen wird, glauben wir, dass dieses Modell für Mehrzweck-Datenbanken nicht gut ist.

Der häufigere Fall ist, dass Index und Daten zusammen gespeichert sind (wie bei Oracle, Sybase usw.). In diesem Fall befindet sich die Zeileninformation auf der Leaf-Page des Indexes. Das Gute daran ist, dass man sich damit - abhängig davon, wie gut der Index gecachet ist - einen Festplatten-Lesezugriff spart. Das Schlechte an diesem Layout sind folgende Dinge:

Eine der grundlegendsten Optimierungen besteht darin, Ihre Daten (und Indexe) dazu zu bekommen, dass sie möglichst wenige Platz auf der Platte (und im Arbeitsspeicher) benutzen. Das kann zu gewaltigen Verbesserungen führen, weil Lesezugriffe von der Platte schneller ablaufen und normalerweise weniger Hauptspeicher benutzt wird. Das Indexieren nimmt darüber hinaus weniger Ressourcen in Anspruch, wenn es auf kleinere Spalten durchgeführt wird.

MySQL unterstützt viele verschiedene Tabellentypen und Zeilenformate. Wenn Sie das richtige Tabellenformat benutzen, kann Ihnen das große Performance-Gewinne bringen. See Kapitel 8, MySQL-Tabellentypen.

Sie erhalten bessere Performance auf eine Tabelle und minimieren den benötigten Speicherplatz, wenn Sie die unten aufgeführten Techniken verwenden:

Indexe werden benutzt, um Zeilen mit einem bestimmten Spaltenwert schnell zu finden. Ohne Index müsste MySQL mit dem ersten Datensatz anfangen und dann durch die gesamte Tabelle lesen, bis er die relevanten Zeilen findet. Je größer die Tabelle, desto mehr Zeit kostet das. Wenn die Tabellen für die infrage kommenden Zeilen einen Index hat, kann MySQL schnell eine Position bekommen, um mitten in der Daten-Datei loszusuchen, ohne alle Daten zu betrachten. Wenn eine Tabelle 1.000 Zeilen hat, ist das mindestens 100 mal schneller als sequentielles Lesen. Wenn Sie jedoch auf fast alle 1.000 Zeilen zugreifen müssen, geht sequentielles Lesen schneller, weil man mehrfache Festplattenzugriffe einspart.

Alle MySQL-Indexe (PRIMARY, UNIQUE und INDEX) sind in B-Bäumen gespeichert. Zeichenketten werden automatisch präfix-komprimiert, ebenfalls werden Leerzeichen am Ende komprimiert.

See Abschnitt 7.5.7, „CREATE INDEX-Syntax“.

Indexe werden benutzt, um:

Angenommen, Sie führen folgendes SELECT-Statement aus:

mysql> SELECT * FROM tabelle WHERE spalte1=val1 AND spalte2=val2;

Wenn es einen mehrspaltigen Index auf spalte1 und spalte2 gibt, können die entsprechenden Zeilen direkt geholt werden. Wenn es separate einspaltige Indexe auf spalte1 und spalte2 gibt, versucht der Optimierer, den restriktivsten Index zu finden, indem er entscheidet, welcher Index weniger Zeilen finden wird, und diesen Index dann benutzen, um Zeilen abzurufen.

Wenn die Tabelle einen mehrspaltigen Index hat, kann jedes Präfix auf der linken Seite vom Optimierer verwendet werden, um Zeilen zu finden. Wenn Sie zum Beispiel einen dreispaltigen Index auf (spalte1,spalte2,spalte3) haben, haben Sie Suchmöglichkeiten auf (spalte1), (spalte1,spalte2) und (spalte1,spalte2,spalte3) indiziert.

MySQL kann keinen teilweisen Index verwenden, wenn die Spalten kein ganz linkes Präfix des Indexes bilden. Angenommen, Sie haben folgende SELECT-Statements:

mysql> SELECT * FROM tabelle WHERE spalte1=wert1;
mysql> SELECT * FROM tabelle WHERE spalte2=wert2;
mysql> SELECT * FROM tabelle WHERE spalte2=wert2 AND spalte3=wert3;

Wenn es einen Index auf (spalte1,spalte2,spalte3) gibt, benutzt nur die erste der drei Anfragen den Index. Die zweite und dritte Anfrage umfassen indizierte Spalten, aber (spalte2) und (spalte2,spalte3) sind nicht die ganz linken Präfixe von (spalte1,spalte2,spalte3).

MySQL benutzt Indexe auch für LIKE-Vergleiche, wenn das Argument für LIKE eine Zeichenketten-Konstante ist, die nicht mit einem Platzhalterzeichen anfängt. Die folgenden SELECT-Statements zum Beispiel benutzen Indexe:

mysql> select * from tabelle where schluessel_spalte LIKE "Patrick%";
mysql> select * from tabelle where schluessel_spalte LIKE "Pat%_ck%";

Im ersten Statement werden nur Zeilen mit "Patrick" <= schluessel_spalte < "Patricl" berücksichtigt. Im zweiten Statement werden nur Zeilen mit "Pat" <= schluessel_spalte < "Pau" berücksichtigt.

Die folgenden SELECT-Statements benutzen keine Indexe:

mysql> select * from tabelle where schluessel_spalte LIKE "%Patrick%";
mysql> select * from tabelle where schluessel_spalte LIKE andere_spalte;

Im ersten Statement fängt der LIKE-Wert mit einem Platzhalterzeichen an. Im zweiten Statement ist der LIKE-Wert keine Konstante.

Suchen mit spalte IS NULL benutzt Indexe, wenn spalte ein Index ist.

MySQL benutzt normalerweise den Index, der die geringste Anzahl von Zeilen findet. Ein Index wird benutzt für Spalten, die Sie mit folgenden Operatoren vergleichen: =, >, >=, <, <=, BETWEEN und einem LIKE ohne Platzhalter-Präfix wie 'etwas%'.

Jeder Index, der nicht alle AND-Ebenen in der WHERE-Klausel umfasst, wird nicht benutzt, um die Anfrage zu optimieren. Mit anderen Worte: Um einen Index benutzen zu können, muss ein Präfix des Indexes in jeder AND-Gruppe benutzt werden.

Die folgenden WHERE-Klauseln benutzen Indexe:

... WHERE index_teil1=1 AND index_teil2=2 AND andere_spalte=3
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_teil1='hello' AND index_teil_3=5
          /* optimiert "index_teil1='hello'" */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
          /* kann den Index auf index1 benutzen, aber nicht auf index2 oder index 3 */

Die folgenden WHERE-Klauseln benutzen KEINE Indexe:

... WHERE index_teil2=1 AND index_teil3=2  /* index_teil_1 wird nicht benutzt */
... WHERE index=1 OR A=10                  /* Index wird nicht in beiden AND-Teilen benutzt */
... WHERE index_teil1=1 OR index_teil2=10  /* Kein Index umfasst alle Zeilen */

Beachten Sie, dass MySQL in manchen Fällen keinen Index benutzt, selbst wenn einer verfügbar wäre. Einige solcher Fälle sind hier aufgeführt:

Alle MySQL-Spaltentypen können indiziert werden. Die Benutzung von Indexen auf den relevanten Spalten ist die beste Art, die Performance von SELECT-Operationen zu verbessern.

Die maximale Anzahl von Schlüsseln und die maximale Index-Länge ist durch den Tabellen-Handler vorgegeben. See Kapitel 8, MySQL-Tabellentypen. Bei allen Tabellen-Handlern können Sie zumindest 16 Schlüssel und eine Gesamtindexlänge von zumindest 256 Bytes haben.

Bei CHAR- und VARCHAR-Spalten können Sie ein Präfix einer Spalte indexieren. Das ist viel schneller und erfordert weniger Plattenspeicher als das Indexieren einer ganzen Spalte. Die Syntax, die im CREATE TABLE-Statement benutzt wird, um ein Spaltenpräfix zu indexieren, sieht wie folgt aus:

KEY index_name (spalten_name(laenge))

Das unten stehende Beispiel erzeugt einen Index auf die ersten 10 Zeichen der name-Spalte:

mysql> CREATE TABLE test (
           name CHAR(200) NOT NULL,
           KEY index_name (name(10)));

Bei BLOB- und TEXT-Spalten müssen Sie ein Präfix der Spalte indexieren. Sie können nicht die gesamte Spalte indexieren.

Ab MySQL-Version 3.23.23 können Sie auch spezielle FULLTEXT-Indexe erzeugen. Sie werden für die Volltextsuche benutzt. Nur der MyISAM-Tabellentyp unterstützt FULLTEXT-Indexe. Sie können nur auf VARCHAR- und TEXT-Spalten erzeugt werden. Die Indexierung erfolgt immer über die gesamte Spalte; teilweises Indexieren wird nicht unterstützt. Siehe Abschnitt 7.8, „MySQL-Volltextsuche“ für Details.

MySQL kann Indexe auf mehrfache Spalten erzeugen. Ein Index darf aus bis zu 15 Spalten bestehen. (Auf CHAR- und VARCHAR-Spalten können Sie auch ein Präfix der Spalte als Teil eines Indexes benutzen).

Ein mehrspaltiger Index kann als sortiertes Array betrachtet werden, das Werte enthält, die durch die Verkettung der Werte der indizierten Spalten erzeugt werden.

MySQL benutzt mehrspaltige Indexe in einer Art, dass Anfragen schnell werden, wenn Sie eine bekannte Menge für die erste Spalte des Indexes in einer WHERE-Klausel angeben, selbst wenn Sie keine Werte für die anderen Spalten angeben.

Angenommen, einen Tabelle wurde wie folgt erzeugt:

mysql> CREATE TABLE test (
           id INT NOT NULL,
           nachname CHAR(30) NOT NULL,
           vorname CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX name (nachname,vorname));

Dann ist der Index name ein Index über nachname und vorname. Der Index wird für Anfragen benutzt, die Werte in einem bekannten Bereich für nachname angeben, oder sowohl für nachname als auch für und vorname. Daher wird der name-Index in folgenden Anfragen benutzt:

mysql> SELECT * FROM test WHERE nachname="Widenius";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND vorname="Michael";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND (vorname="Michael" OR vorname="Monty");

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          AND vorname >="M" AND vorname < "N";

In folgenden Anfragen wird der name-Index jedoch NICHT benutzt:

mysql> SELECT * FROM test WHERE vorname="Michael";

mysql> SELECT * FROM test WHERE nachname="Widenius"
                          OR vorname="Michael";

Weitere Informationen über die Art, wie MySQL Indexe benutzt, um die Anfragen-Performance zu verbessern, finden Sie unter Abschnitt 6.4.3, „Wie MySQL Indexe benutzt“.

table_cache, max_connections und max_tmp_tables beeinflussen die maximale Anzahl von Dateien, die der Server offen halten kann. Wenn Sie einen oder mehrere dieser Werte erhöhen, können Sie an eine Begrenzung stoßen, die durch Ihr Betriebssystem in Bezug auf die Anzahl offener Datei-Deskriptoren pro Prozess festgelegt wird. Diese Begrenzung kann man jedoch auf vielen Systemen erhöhen. Sehen Sie im Handbuch Ihres Betriebssystems nach, wie man das macht, weil die Methode, wie die Begrenzung geändert wird, sich von System zu System stark unterscheidet.

table_cache ist verwandt mit max_connections. Für 200 gleichzeitig laufende Verbindungen sollten Sie zum Beispiel einen Tabellen-Cache von mindestens 200 * n haben, wobei n die maximale Anzahl von Tabellen in einem Join ist. Zusätzlich müssen Sie einige externe Datei-Deskriptoren für temporäre Tabellen und Dateien reservieren.

Stellen Sie sicher, dass Ihr Betriebssystem die Anzahl offener Datei-Deskriptoren handhaben kann, die durch die table_cache-Einstellung impliziert wird. Wenn table_cache zu hoch gesetzt wird, hat MySQL eventuell keine Datei-Deskriptoren mehr und verweigert Verbindungen, führt keine Anfragen mehr aus und läuft sehr unzuverlässig. Beachten Sie auch, dass der MyISAM-Tabellen-Handler zwei Datei-Deskriptoren für jede einzelne offene Tabelle benötigt. Sie können die Anzahl von Datei-Deskriptoren, die für MySQL verfügbar sind, in der --open-files-limit=#-Startoption angeben. See Abschnitt A.2.16, „File Not Found“.

Der Cache offener Tabellen kann bis auf table_cache anwachsen (Vorgabewert 64; das kann mit der -O Tabellen-Cache=#-Option für mysqld geändert werden). Eine Tabelle wird nie geschlossen, ausser wen der Cache voll ist und ein anderer Thread versucht, eine Tabelle zu öffnen, oder wenn Sie mysqladmin refresh oder mysqladmin flush-tables benutzen.

Wenn sich der Tabellen-Cache füllt, benutzt der Server folgenden Prozedur, um einen Cache-Eintrag für die Benutzung zu finden:

Eine Tabelle wird für jeden gleichzeitigen Zugriff geöffnet. Das bedeutet, dass die Tabelle zweimal geöffnet werden muss, wenn Sie zwei Threads haben, die auf dieselbe Tabelle zugreifen oder einen Thread, der auf die Tabelle zweimal in derselben Anfrage zugreift (mit AS). Das erste öffnen jeder Tabelle benötigt nur einen Datei-Deskriptor. Der zusätzliche Deskriptor wird für die Index-Datei benötigt; dieser Deskriptor wird mit allen Threads geteilt (shared).

Wenn Sie eine Tabelle mit dem HANDLER tabelle OPEN-Statement öffnen, wird dem Thread ein dediziertes Tabellenobjekt zugewiesen. Diese Tabellenobjekt wird nicht mit anderen Threads geteilt und wird solange nicht geschlossen, bis der Thread HANDLER tabelle CLOSE aufruft oder stirbt. See Abschnitt 7.4.3, „HANDLER-Syntax“.

Sie können prüfen, ob Ihr Tabellen-Cache zu klein ist, indem Sie die mysqld-Variable opened_tables ansehen. Wenn diese recht Groß ist, selbst wenn Sie nicht viele FLUSH TABLES ausgeführt haben, sollten Sie Ihren Tabellen-Cache vergrößern. See Abschnitt 5.5.5.3, „SHOW STATUS“.

Wenn Sie viele Dateien in einem Verzeichnis haben, werden open-, close- und create-Operationen langsam. Wenn Sie ein SELECT-Statements auf viele unterschiedliche Tabellen ausführen, gibt es ein bisschen Overhead, wenn der Tabellen-Cache voll ist, weil für jede Tabelle, die geöffnet wird, eine andere geschlossen werden muss. Sie können diese Overhead verringern, indem Sie den Tabellen-Cache größer machen.

Wenn Sie mysqladmin status ausführen, werden Sie etwa folgendes sehen:

Uptime: 426 Running Threads: 1 Questions: 11082 Reloads: 1 Open Tables: 12

Das kann etwas verwirrend sein, wenn Sie nur 6 Tabellen haben.

MySQL ist multi-threaded, daher kann er viele Anfragen auf dieselbe Tabelle simultan verarbeiten. Um das Problem zu minimieren, dass zwei Threads verschiedene Zustände in Bezug auf dieselbe Datei haben, wird die Tabelle unabhängig für jeden gleichzeitigen Thread geöffnet. Das benötigt etwas Arbeitsspeicher und einen externen Datei-Deskriptor für die Daten-Datei. Der Index-Datei-Deskriptor wird mit allen Threads geteilt.

Wir fangen mit den Dingen auf Systemebene an, weil einige dieser Entscheidungen sehr früh getroffen werden müssen. In anderen Fällen mag ein kurzer Blick auf diesen Teil ausreichen, weil er nicht so wichtig für große Verbesserungen ist. Es ist jedoch immer nett, ein Gefühl dafür zu bekommen, wie viel man gewinnen kann, wenn man Dinge auf dieser Ebene ändert.

Es ist wirklich wichtig, dass vorgabemäßige Betriebssystem zu kennen! Um das meiste aus Mehrprozessor-Maschinen herauszuholen, sollte man Solaris benutzen (weil die Threads wirklich gut funktionieren) oder Linux (weil der 2.2-Kernel wirklich gute Mehrprozessor-Unterstützung bietet). Linux hat auf 32-Bit-Maschinen vorgabemäßig eine Dateigrößenbeschränkung von 2 GB. Das wird hoffentlich bald behoben, wenn neue Dateisysteme herausgebracht werden (XFS/Reiserfs). Wenn Sie dringen Unterstützung für größere Datei als 2 GB auf Linux-Intel-32-Bit benötigen, sollten Sie den LFS-Patch für das ext2-Dateisystem holen.

Weil wir MySQL noch nicht auf allzu vielen Plattformen in einer Produktionsumgebung getestet haben, empfehlen wir, dass Sie Ihre geplante Plattform testen, bevor Sie sich dafür entscheiden.

Weitere Tipps:

Sie erhalten die Puffer-Größen, die der mysqld-Server benutzt, mit diesem Befehl:

shell> mysqld --help

Dieser Befehl erzeugt eine Auflistung aller mysqld-Optionen und konfigurierbaren Variablen. Die Ausgabe enthält die Vorgabewerte und sieht etwa wie folgt aus:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
bdb_cache_size        current value: 1048540
binlog_cache_size     current_value: 32768
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_tabelles  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_binlog_cache_size current_value: 4294967295
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length     current value: 16384
net_retry_count       current value: 10
net_read_timeout      current value: 30
net_write_timeout     current value: 60
query_buffer_size     current value: 0
record_buffer         current value: 131072
record_rnd_buffer     current value: 131072
slow_launch_time      current value: 2
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

Wenn aktuell ein mysqld-Server läuft, können Sie feststellen, welche Werte er für die Variablen tatsächlich benutzt, wenn Sie diesen Befehl ausführen:

shell> mysqladmin variables

Sie finden eine komplette Beschreibung aller Variablen im SHOW VARIABLES-Abschnitt dieses Handbuchs. See Abschnitt 5.5.5.4, „SHOW VARIABLES“.

Wenn Sie SHOW STATUS eingeben, können Sie einige statistische Informationen des Servers sehen. See Abschnitt 5.5.5.3, „SHOW STATUS“.

MySQL benutzt Algorithmen, die sehr skalierbar sind, daher können Sie üblicherweise mit sehr wenig Arbeitsspeicher fahren. Wenn Sie MySQL jedoch mehr Speicher geben, erzielen Sie damit normalerweise auch bessere Performance.

Wenn Sie einen MySQL-Server tunen, sind die zwei wichtigsten Variablen key_buffer_size und table_cache. Sie sollten zunächst sicher sein, dass diese beiden richtig gesetzt sind, bevor Sie versuchen, irgend eine der anderen Variablen zu ändern.

Wenn Sie viel Arbeitsspeicher haben (>= 256 MB) und viele Tabellen und maximale Performance bei einer mäßigen Anzahl von Clients haben wollen, sollten Sie etwas wie das Folgende benutzen:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O record_buffer=1M &

Wenn Sie nur 128 MB und nur wenige Tabellen haben, aber viele Sortiervorgänge durchführen, können Sie etwas wie das Folgende benutzen:

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

Wenn Sie wenig Arbeitsspeicher und viele Verbindungen haben, können Sie etwas wie das Folgende benutzen:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

Oder sogar:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

Wenn Sie GROUP BY oder ORDER BY auf Dateien anwenden, die größer als Ihr verfügbarer Arbeitsspeicher sind, sollten Sie den Wert von record_rnd_buffer heraufsetzen, um das Lesen von Zeilen nach Sortiervorgängen zu beschleunigen.

Wenn Sie MySQL installiert haben, enthält das Support-files-Verzeichnis einige unterschiedliche my.cnf-Beispiel-Dateien: my-huge.cnf, my-large.cnf, my-medium.cnf und my-small.cnf. Diese können Sie als Grundlage nehmen, um Ihr System zu optimieren.

Wenn es sehr viele Verbindungen gibt, können ``Swapping-Probleme'' auftauchen, wen Sie mysqld nicht so konfiguriert haben, dass er für jede Verbindung sehr wenig Speicher benutzt. mysqld bringt natürlich bessere Leistungsdaten, wenn Sie genug Speicher für alle Verbindungen haben.

Beachten Sie, dass Änderungen einer Option für mysqld sich nur auf diese Instanz des Servers auswirken.

Um die Auswirkung einer Parameteränderung zu sehen, geben Sie folgendes ein:

shell> mysqld -O key_buffer=32m --help

Stellen Sie sicher, dass die --help-Option zuletzt kommt, ansonsten wird die Auswirkung jeglicher Optionen, die danach auf der Kommandozeile kommen, in der Ausgabe nicht gezeigt. output.

Die meisten der folgenden Tests wurden mit den MySQL-Benchmarks unter Linux durchgeführt, aber sie sollten einen guten Anhaltspunkt für andere Betriebssysteme und Auslastungen geben.

Sie erhalten die schnellste ausführbare Datei, wenn Sie mit -static linken.

Unter Linux erhalten Sie den schnellsten Code, wenn Sie mit pgcc und -O3 kompilieren. Um sql_yacc.cc mit diesen Optionen zu kompilieren, brauchen Sie etwa 200 MB Arbeitsspeicher, weil gcc/pgcc viel Speicher benötigt, um alle Funktionen inline zu machen. Sie sollten beim Konfigurieren von MySQL auch CXX=gcc setzen, um das Einschließen der libstdc++-Bibliothek zu vermeiden (die nicht benötigt wird). Beachten Sie, dass bei einigen Version von pgcc der erzeugte Code nur auf echten Pentium-Prozessoren läuft, selbst wenn Sie in den Compiler-Optionen angeben, dass Sie wollen, dass der Code auf alle Prozessoren vom Typ x586 läuft (wie AMD).

Einfach durch die Benutzung eines besseren Compilers und / oder besserer Compiler-Optionen können Sie eine 10-30%-ige Geschwindigkeitssteigerung in Ihrer Applikation erhalten. Das ist besonders wichtig, wenn Sie den SQL-Server selbst kompilieren!

Wir haben sowohl Cygnus CodeFusion als auch Fujitsu-Compiler getestet, aber es stellte sich heraus, dass keiner von beiden ausreichend Bug-frei war, damit MySQL mit angeschalteten Optimierungen kompiliert werden konnte.

Wenn Sie MySQL kompilieren, sollten Sie nur Unterstützung für die Zeichensätze einschließen, die Sie benutzen werden (Option --with-charset=xxx). Die Standard-MySQL-Binärdistributionen werden mit Unterstützung für alle Zeichensätze kompiliert.

Hier ist eine Auflistung einiger Messungen, die wir durchgeführt haben:

Die MySQL-Linux-Distribution, die von MySQL AB zur Verfügung gestellt wird, wurde früher mit pgcc kompiliert, aber wir mussten zum normalen gcc zurück gehen, weil es einen Bug in pgcc gibt, der Code erzeugt, der nicht auf AMD läuft. Wir werden gcc solange benutzen, bis dieser Bug behoben ist. Bis dahin können Sie, falls Sie keine AMD-Maschine haben, eine schnellere Binärdatei erhalten, wenn Sie mit pgcc kompilieren. Die Standard-MySQL-Linux-Binärdatei wird statisch gelinkt, um sie schneller und portierbarer zu machen.

Die unten stehende Liste zeigt einige Möglichkeiten, wie der mysqld-Server Speicher benutzt. Wo es zutrifft, wird der Name der für die Speicherbenutzung relevanten Servervariablen angegeben.

ps und andere System-Status-Programme berichten vielleicht, dass mysqld viel Arbeitsspeicher benutzt. Das kann durch Thread-Stacks auf verschiedenen Speicheradressen verursacht werden. ps der Solaris-Version zum Beispiel zählt den unbenutzten Speicher zwischen Stacks zum benutzten Speicher hinzu. Das können Sie bestätigen, wenn Sie den verfügbaren Swap mit swap -s überprüfen. Wir haben mysqld mit kommerziellen Memory-Leak-Detektoren getestet, daher sollte es keine Memory-Leaks geben.

Wenn sich ein neuer Thread mit mysqld verbindet, erzeugt mysqld einen neuen Thread, um die Anfrage zu handhaben. Dieser Thread prüft zuerst, ob der Hostname im Hostnamen-Cache ist. Falls nicht, ruft der Thread gethostbyaddr_r() und gethostbyname_r() auf, um den Hostname aufzulösen.

Wenn das Betriebssystem die oben genannten Thread-sicheren Aufrufe nicht unterstützt, sperrt der Thread ein Mutex und ruft statt dessen gethostbyaddr() und gethostbyname() auf. Beachten Sie, dass in diesem Fall kein anderer Thread andere Hostnamen auflösen kann, die nicht im Hostnamen-Cache sind, bis der erste Thread fertig ist.

Sie können das DNS-Nachschlagen von Hostnamen (DNS-Lookup) abschalten, indem Sie mysqld mit --skip-name-resolve starten. In diesem Fall können Sie jedoch in den MySQL-Berechtigungstabellen nur IP-Nummern verwenden.

Wenn Sie ein sehr langsames DNS und viele Hosts haben, können Sie mehr Performance erzielen, wenn Sie entweder das DNS-Nachschlagen von Hostnamen (DNS-Lookup) abschalten (mit --skip-name-resolve) oder HOST_CACHE_SIZE (Vorgabe: 128) erhöhen und mysqld neu kompilieren.

Sie können den Hostnamen-Cache mit --skip-host-cache abschalten. Sie können den Hostnamen-Cache mit FLUSH HOSTS oder mysqladmin flush-hosts löschen.

Wenn Sie keine Verbindungen über TCP/IP zulassen wollen, starten Sie mysqld mit --skip-networking.

SET [OPTION] SQL_VALUE_OPTION= wert, ...

SET OPTION setzt verschiedene Optionen, die die Arbeitsweise des Servers oder Ihrer Clients beeinflussen. Jede Option, die Sie setzen, bleibt in Kraft, bis die aktuelle Sitzung beendet wird, oder bis Sie die Option auf einen anderen Wert setzen.

Sie können Tabellen und Datenbanken vom Datenbank-Verzeichnis an andere Stellen verschieben und sie mit symbolischen Links auf neue Speicherorte ersetzen. Das könnten Sie zum Beispiel tun, um eine Datenbank auf ein Dateisystem mit mehr freiem Speicherplatz zu verlagern oder um die Geschwindigkeit Ihres System durch Verteilen Ihrer Tabellen auf unterschiedliche Platten zu steigern.

Die empfohlene Art, das zu tun, ist, nur Datenbanken auf unterschiedliche Platten per SymLink zu verknüpfen, und das bei Tabellen nur im Notfall zu tun.

shell> mkdir /dr1/datenbanken/test
shell> ln -s /dr1/datenbanken/test mysqld-datadir

shell> cd /pfad/zu/datadir
shell> ln -s datenbank1 datenbank2

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

if (1)