Aufpoliert

Dass MySQL eine der populärsten SQL-Datenbanken ist, hat auch die iX-Leserbefragung im letzten Jahr gezeigt. Anhänger der reinen SQL-Lehre begeistert das wenig, hat dieses DBMS doch lange Zeit recht großen Abstand zum Standard gehalten. Das hat sich in den letzten anderthalb Jahren geändert. Nach und nach statten die Entwickler ihr Kind mit allen Fähigkeiten aus, die eine SQL-Datenbank braucht. Viele der im Folgenden vorgestellten Neuerungen sind im Laufe der Änderungen von Version 3.23 über 4.0 zu 4.1 eingeflossen, ohne dass sie einer bestimmten Versionsnummer zuzuordnen sind.

Von Anfang an war Geschwindigkeit eines der wichtigsten Argumente für den Einsatz von MySQL. Unter anderem durch Verzicht auf bestimmte Eigenschaften (etwa Transaktionen und referenzielle Integrität) konnte die Datenbank Tempo vorlegen. Das brachte ihr viele Anhänger vor allem unter Webentwicklerinnen ein, für die es häufig auf schnelle SELECT-Anfragen ankommt. Wer jedoch mehr Wert auf Datensicherheit als auf flotte Ausgaben legte, war auf andere Produkte angewiesen.

Inzwischen gehört Transaktionsfähigkeit zum MySQL-Repertoire, zumindest wenn man Tabellen vom Typ InnoDB benutzt. Zwar behaupten die Entwickler, dieser Tabellentyp sei unter den transaktionsfähigen ganz besonders schnell, aber zumindest gegenüber den klassischen MySQL-Tabellen ist eine deutliche Tempoeinbuße zu spüren. Vermutlich dürfte das dazu beigetragen haben, dass es seit Version 4.01 einen Query-Cache gibt.

Query-Cache beschleunigt Abfragen

In diesem Zwischenspeicher für Abfragen merkt sich der Daemon sämtliche SELECT-Befehle sowie deren Ergebnis, wenn dieses eine einstellbare Größe nicht überschreitet. Drei Variablen steuern das Caching:

• Ergebnismengen größer als query_cache_limit speichert mysqld nicht.
• Die Gesamtgröße des Query-Cache gibt query_cache_size an.
• query_cache_type schließlich schaltet den Cache ein oder aus (ON beziehungsweise OFF; DEMAND ermöglicht das gezielte Zwischenspeichern einzelner SELECT-Abfragen).

Voreingestellt ist ein query_cache_size-Wert von 0, was das Zwischenspeichern völlig verhindert. Bevor SELECTs schneller laufen, ist deshalb diese Variable zu erhöhen, etwa auf 1 000 000 Bytes. Dies kann der Administrator im laufenden Betrieb mit

set global query_cache_size=1000000

bewerkstelligen. Dauerhaft sollte die Option im Serverabschnitt der MySQL-Konfigurationsdatei my.cnf eingetragen sein.

Zum Test des Query-Cache dienten zwei relativ simple Tabellen. Die erste enthält Daten von rund 86 000 Angriffen auf eine Firewall. Sie besteht aus acht Spalten (ID, Datum, Interface, Protokoll sowie Ziel-/Quellport und -IP-Adresse). Indizes sind nur für die ID und die beiden Portfelder definiert. In der zweiten Tabelle sind gut zwei Millionen Einträge aus einem Apache-Log gespeichert: ID, Referer, Datum, URL, HTTP-Request, Fehlercode und Benutzer. Auf die erste Tabelle losgelassen, braucht die Anfrage

SELECT  datum, interface, srcip,targetport
  FROM logs WHERE targetport > 80 
  GROUP BY srcip

im ersten Durchlauf 1,54 s, um die 4652 passenden Datensätze zu finden. Im zweiten Versuch, nachdem also der Query-Cache Abfrage und Daten enthält, sind noch 0,02 s nötig. Noch deutlicher fällt die Beschleunigung bei

SELECT * FROM http_logs 
  WHERE user <> '' ORDER BY datum;

aus: 23,11 s im ersten Durchgang und wieder 0,02 im zweiten. Das ist über 1000-mal schneller. Übrigens brachte die Definition eines Index für die Spalte user in diesem Fall nur einen Gewinn von 3 s, also rund 10 %.

Konstante Zeit für Gespeichertes

Diese Zahlen deuten darauf hin, dass Ergebnisse aus dem Query-Cache mit konstanter Zeit kommen, was zu erwarten ist. Ziel von Datenbankentwicklern sollte es deshalb sein, besonders lang dauernde Queries gezielt im Cache zu speichern und dadurch zu beschleunigen. Noch sind dabei jedoch einige Besonderheiten zu beachten.

So erkennt MySQL zwei Queries nur dann als identisch, wenn sie genau gleich geschrieben sind - select und SELECT fasst der Daemon als unterschiedlich auf. Zudem dürfen bestimmte Funktionen nicht vorkommen, was nicht nur bei now() nahe liegt: Das liefert nun mal bei jedem Aufruf einen anderen Wert, sodass sich die Ergebnisse der Abfrage ändern können.

Wie gut MySQL den Query-Cache nutzt, zeigt ein Blick auf die passenden Statusvariablen:

SHOW STATUS LIKE '%qc'

liefert etwa das hier:

Variable_name    Value
Qcache_queries_in_cache    3
Qcache_inserts        3
Qcache_hits        2
Qcache_not_cached    0
Qcache_free_memory    9987040
Qcache_free_blocks    1
Qcache_total_blocks    8

Hier hat der Server zurzeit drei Queries im Cache gespeichert (Qcache_queries_in_cache). Zwei davon konnte er wieder benutzen (Qcache_hits), und es bleiben noch knapp 10 MByte Platz im Query-Cache. Zwei SQL-Befehle beeinflussen diesen Wert: FLUSH QUERY CACHE defragmentiert den Speicher und RESET QUERY CACHE löscht alle gespeicherten Abfragen und ihre Ergebnisse. Die Statusvariable Qcache_queries_in_cache ist deshalb danach 0, während sich an Qcache_inserts nichts ändert: Dies ist die Zahl aller während der Serverlebenszeit jemals zwischengespeicherten Abfragen.

Erwartungsgemäß bleiben Queries nicht ewig im Cache. So entfernt jedes UPDATE, DELETE und INSERT alle Abfrageergebnisse, die sich auf die betroffene Tabelle(n) beziehen, aus dem Zwischenspeicher. Das ist unvermeidlich, um stets korrekte Ergebnisse zu erhalten.

Den vollständigen Artikel mit einer detaillierten Beschreibung von Server-Variablen, Subselects, den neuen geometrischen Datentypen und mehr finden Sie in der aktuellen Printausgabe. (ck)