MySQL Performance Tuning leicht gemacht

26 November 2007

Eines der interessantesten und wohl auch kniffligsten Themen der Webserver Performance Optimierung dürfte wohl das Tuning des MySQL Severs sein. Man findet unzählige Seiten mit Anleitungen und HowTos, wie man welche Variable der MySQL Config richtig einzustellen hat. Sogar ganze Config Files, die einen Performance Boost versprechen sollen, werden zum Download angeboten . Ich möchte hier keine reinen Zahlenwerte angeben, sondern einen kleinen Überblick über die “Schrauben” an der man drehen sollte geben, um den Server auf seine Verhältnisse anzupassen zu können.

Die optimale Hardware

Voll im Trend liegen heute dedizierte Rootserver, die man bei vielen Hostern mittlerweile für einen Apple und ein Ei günstig anmieten kann.Doch leider hat man hier oft nur begrenzte Auswahlmöglichkeiten an der Hardwareausstattung. Ich möchte dennoch kurz auf die wichtigsten Anforderungen an die Hardware bei einem reinen Datenbank Server eingehen. Zunächst sollte man erwähnen, dass jegliche Festplattenzugriffe generell zu langsam für Datenbankanwendungen sind. Ziel sollte es also sein, speichergesättigte Datenbanken, sprich nach Möglichkeit alle Daten in den Speicher legen, und die Schreibzugriffe so niedrig wie möglich halten, zu bauen. Am Speicher sollte man also als letztes sparen! MySQL arbeitet mit einer Einprozessor Architektur. Ein MySQL Server besitzt mehrere Service Threads und einen Handler Thread pro Verbindung, die sich alle den gemeinsamen Speicher des Prozesses teilen. Viel RAM ist zwar gut, es nützt uns allerding reichlich wenig, wenn wir diesen nicht mit einzelnen Prozessen ansprechen können. Mit anderen Worten: Eine 64 Bit CPU ist hier im Jahre 2007 Stand der Dinge. Will man mehr als 3GB RAM adressieren, kommt ein 64 Bit Betriebssystem noch hinzu. Sollen die Schreizugriffe auch optimiert werden, muss man Geld in den Datendurchsatz bei den Festplatten investieren. Reichen hier schnelle Platten ansich nicht mehr aus, sollte man die Anzahl der Spindeln erhöhen, z. B. ein RAID 10 in Betracht ziehen. Ich selbst habe ein solches Setup noch nicht gefahren, verfüge hierzu also über keinerlei Erfahrungswerte. Die eigentliche CPU Leistung ist bei den heute standardmäßig verbauten Prozessoren eher vernachlässigbar.

Die Konfiguration

In der Regel wird der MySQL Server über die my.cnf konfiguriert. Es ist aber auch möglich zur Laufzeit verschiedene Variablen über den Prompt zu ändern. Aber nun zu den wichtigsten Optionen:

Querycache

Der Querycache sollte sich mittlerweile rumgesprochen haben. Potenziell lassen sich bei Datenbanken mit wenigen Schreibzugriffe, hohe Performance Steigerungen erzielen. Die Konfiguration des Query Cache erfolgt über die gleichnamige Variable in der [mysqld] Sektion. In einem MySQL Kommandozeilenclient kann man sich die aktuelle Einstellung mit show variables like “query_cache%” ansehen. Der Query Cache legt bereits ausgeführte Abfragen in den Speicher. Führt man also z.b. die gleiche SELECT Abfrage 2 mal hintereinander aus, wird der 2. Aufruf wesentlich schneller abgearbeitet, da er nicht wirklich ausgeführt wird, sondern das Ergebnis direkt aus dem Speicher kommt. Viele wissen allerdings nicht, dass es hier auf die wirklich identische Schreibweise ankommt. Ein “select x=2 where y=1″ ist in diesem Sinne nicht identisch mit “Select x=2 where y=1″ . Generell geht das mit dem Cache auch nur solange gut, bis es zu einer Veränderung der zugrundeliegenden Base Table kommt. Daher wird beispielsweise nach einem INSERT das entsprechende Query aus dem Cache gelöscht.

query_cache_limit

Hiermit wird die maximale Größe des Result Sets bestimmt – also die maximale Größe des Ergebnisses einer Abfrage die in den Cache kommen soll. Es macht bei den meisten Anwendungen wenig Sinn den Cache mit wenigen großen Querrys vollzustopfen und somit andere wichtigere Querries rausfliegen zu lassen.

query_cache_type

0 (Off) Query Cache ist ausgeschaltet, alle anderen Query Cache Parameter werden übersprungen. 1 (ON) hier werden sofern möglich alle Querries in den Cache aufgenommen, mit Ausnahme von solchen, die nicht konstante Funktionsaufrufe (wie z.b. rand() now(),..) enthalten. Mit 2 (On Demand) werden nur Querys aufgenommen, die ausdrücklich mit SQL_CACHE makiert sind.

query_cache_size

Hier wird festgelegt wieviel Arbeitspeicher für den Query Cache reserviert werden soll. Die Größe ist nachtürlich von Anwendung zu Anwendung verschieden. Man muss hier ein wenig rumexperimentieren. Mit einem SHOW STATUS kann dies leicht kontrolliert werden.Wird ein Schreiblock auf eine Tabelle gelegt, werden Querrys aus dem Cache dennoch ausgeliefert. Möchte man dies vermeiden, muss man die Option query_cache_wlock_invalidate auf 1 setzen. Nach der ganzen Einstellerei sollte man sich nun mal einen Überblick über das ganze mit einem “SHOW STATUS LIKE ‘%qc’” verschaffen. Je nachdem wie lange der Server schon läuft sollte in etwa sowas zu sehen sein:

mysql> show status like "qc%";

+-------------------------+-----------+


| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1 |
| Qcache_free_memory      | 113943576 |
| Qcache_hits             | 11 |
| Qcache_inserts          | 216961140 |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 44 |
| Qcache_queries_in_cache | 100 |
| Qcache_total_blocks     | 35 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

In diesem Bespiel sieht man, das der Cache noch nie übergelaufen ist (prunes), 44 Querrys konnten nicht gecached werden. Sollte der prunes Wert mit der Lauftzeit steigen, ist der Query Cache offensichtlich zu klein und sollte größer gestellt werden. wenn der Qcache_free_blocks Wert Groß wird, dann fragmentiert der Qcache. Abhilfe schaft hier eine andere Größe der Blocksize die mit qcache_min_block_size eingestellt werden kann.

Key Buffer

Ebenfalls in der [mysqld] Sektion findet man die key_buffer Option. Aber erst zum Hintergrund. MySQL legt die Daten chronologisch in eine MYD File ab. Vergleichen kann man dies mit einer Bücherei. Hier werden neu gekaufte Bücher einfach hinten ans Regal angestellt, wenn Lücken vorhanden sind, werden diese aufgefüllt. Um die Bücher nun besser finden zu können gibt es in jeder verschiedene Indexe, z.b. einen Schlagwortkatalog oder einen Autorenkatalog, mit denen man die Bücher sehr einfach finden kann.Bei MySQL legt man einen Index mit CREATE INDEX an. Der Befehl geht dann die Spalte durch, merkt sich die Werte für jeden Wert und die Datensatznummer, in der der Wert zu finden ist. Dann müssen die Werte sortiert werden, und hierfür wird Platz benötigt. Und dieser Platz sollte sehr großzügig bemessen werden. Beispiel: Nehmen wir an, wir möchten einen Index auf eine INTEGER SPALTE, wird pro Datensatz 4 Byte für das Integer plus 4 Byte für den Zeiger auf die Daten benötigt. Bei 20 Millionen Einträgen sind das also ca. 152 MB. Das stimmt so aber noch nicht ganz. Denn eine Index File wird in Blöcke von key_cache_block_size Byte größe angelegt. In der Bibliothek sind die Indexeinträge auch Gruppiert, es gibt z.b. eine Block “A”, “B” etc. Es macht aber keinen Sinn die Blöcke von anfang an komplett voll zu machen, denn dann müßte bei jedem neuen Eintrag der komplette index “verschoben” werden. Das Verschieben macht auch MySQL und wird Rebalancing genannt. Damit dies nicht jedes mal passieren muss, sollte man die Blöcke nur zu 2/3 füllen. Bei unseren Beispiel heisst es allso 8 Byte pro Zeile geteilt durch 2/3, dann kommt man auf ca. 228 MB