MySQL Performance Tuning, Teil 2: QueryCache und Memory-Tables

In den letzten Wochen und Monaten sind uns einige Projekte untergekommen, die unter abenteuerlichen Performance-Problemen aufgrund „zu großer Datenmengen“ in der MySQL-Datenbank litten. Wenn man die Tabellen richtig plant und konfiguriert, dann hat man mit MySQL aber wirklich seltenst Probleme mit der Geschwindigkeit. In einer mehrteiligen Serie von Blog-Artikeln möchten wir einige Schritte aufzeigen, wie man solche Probleme vermeiden bzw. beheben kann.

QueryCache und Memory-Tables

Der MySQL Query Cache speichert die SQL-Abfragen und die ermittelten Ergebnisse im Arbeitsspeicher. Kommt ein identisches SQL-Statement, wird die Abfrage nicht erneut berechnet, sondern aus dem Query-Cache beantwortet. Erst wenn sich die Inhalte der Tabelle ändern (UPDATE, INSERT, DELETE) wird dieses gecachte Element gelöscht, oder wenn der zugewiesene Query-Cache vollgelaufen ist und die Abfrage am längsten nicht mehr abgerufen wurde.

Obwohl der Arbeitsspeicher die teuerste Ressource des Server ist, teilen wir dem Query-Cache im Normalfall 50-75% des Gesamtarbeitsspeichers zu. Der Query-Cache hat allerdings einen gewissen Overhead, der die Performance dann negativ beeinflusst, wenn auf eine Tabelle überwiegend Write-Operationen durchgeführt werden. Leider kann man den Query-Cache nicht auf Datenbank-Ebene konfigurieren, so dass eine Datenbank mit einer Write-Mostly-Tabelle bereits die Performance-Gewinne aller anderen Datenbanken vernichten kann.

Der Overhead lässt sich allerdings vermeiden, wenn alle abfragenden Queries auf die Write-Mostly-Tabelle das SQL-Schlüsselwort „SQL_NO_CACHE“ verwenden, also z. B.:

SELECT SQL_NO_CACHE a, b, c FROM x WHERE ...

Memory Tables

In Situationen, in denen große Mengen an Daten zu einem frei wählbaren Zeitpunkt verarbeitet werden müssen (Cron-Jobs), lohnt es sich, die Daten aus der Haupt-Tabelle in eine MEMORY-Tabelle zu kopieren und mit dieser zu arbeiten. Auch das kostet extrem viel RAM, beschleunigt den Vorgang aber um den Faktor 100-1000 nach dem Kopieren.

Darauf zu achten ist, dass in der globalen MySQL-Konfiguration die maximale Größe für MEMORY-Tabellen sinnvollerweise limitiert ist. Standardmäßig ist das 1GB. Will man mehr Daten in die Tabelle kopieren, kommt die Fehlermeldung

THE TABLE tablename IS FULL

… was man unbedingt abfangen sollte, da man sonst mit unvollständigen Daten weiterarbeiten würde. Die maximale Größe für MEMORY-Tabellen lässt sich aber nach oben korrigieren, wobei darauf zu achten ist, dass sich die Größe aller (voll ausgereizten) MEMORY-Tabellen den verfügbaren Arbeitsspeicher mit dem QUERY-Cache teilen und der Server somit schnell ins SWAPpen gerät, was natürlich die Performance erbärmlich verlangsamt.